Archivo

Posts Tagged ‘SQL’

JQL (Java Query Language)

Hace un tiempo atrás conocí YQL, un servicio de Yahoo! que permite hacer consultas tipo SQL a sus APIs o a sitios webs… Buscando algo así para Java no encontré nada parecido, por lo que empece a hacerlo yo. El resultado fueron unas cuantas lineas de código que permiten filtrar ,manipular y unificar datos de páginas Web , simplemente con una consulta tipo SQL muy similar a YQL.

Por ejemplo si quisiera filtar los datos de una página web debería usar una conexión, obtener la página , filtarla usando expresiones regulares y devolver los datos que quisiera. Con ésta librería es tan simple como crear una consulta SQL:

Ejemplo:
Acá obtengo los resultados de búsqueda de goear.com

//Creo la consulta SQL
String sql = "SELECT href,title FROM html WHERE url='http://goear.com/search.php?q=rafaga' AND xpath=\"//a[@class='b1']\"";
//Creo un objeto de la clase JQL y le paso como parametro la query sql y el tipo que quiero que devuelva (JSON)
JQL jql = new JQL(sql,JQLResultType.JSON);
//Agrego un listener para que sea lanzado cuando me devuelva los resultados
jql.addJQLResultListener(new JQLResultListener() {
	//Implemento el método jqlResult para obtener los resultados
	public void jqlResult(JQLResultEvent event) {
	    //Imprimo los resultados de mi consulta
	    System.out.println(event.getJqlResult());
	}
});
//creo un hilo y le paso como parametro el objeto JQL
Thread th = new Thread(jql);
//lanzo el hilo
th.start();

Simplemente Selecciono el href y el titulo de los links de goear donde la url de donde sacare esos datos sea la página de goear y le paso el xpath de donde están los elementos que quiero obtener. Al hacer correr ese codigo me devuelve un String con JSON listo para ser manipulado:

run:
{
        "query": {
        "count": "10",
        "created": "2010-05-23T10:19:48Z",
        "lang": "en-US",
        "diagnostics": {
                "url": {
                        "execution-time": "4072",
                        "proxy": "DEFAULT",
                        "content": "http://goear.com/search.php?q=rafaga"
                }
        },
        "results": {
                "a": [
                        {"href":"listen/c77464e/rafaga-radio-andalucia-(1)-radio-andalucia-informacion","title":"Escuchar Rafaga Radio Andalucia (1) de radio andalucia informacion"},
                        {"href":"listen/6d87274/una-rafaga-de-amor-rafaga","title":"Escuchar Una rafaga de amor de rafaga"},
                        {"href":"listen/fd1b84c/una-rafaga-de-amor-rafaga","title":"Escuchar Una Rafaga De Amor de rafaga"},
                        {"href":"listen/3b043de/jambo-jambo---rafaga-sunshine-rafaga-sunshine","title":"Escuchar JAMBO JAMBO - Rafaga Sunshine de rafaga sunshine"},
                        {"href":"listen/1dbcd0b/una-rafaga-de-amor-rafaga","title":"Escuchar una rafaga de amor de rafaga"},
                        {"href":"listen/71b1c55/una-rafaga-de-amor---calidad-media---faviomundo.pe.kz-rafaga","title":"Escuchar Una Rafaga De Amor - Calidad Media - FavioMundo.Pe.Kz de rafaga"},
                        {"href":"listen/9ff4361/una-rafaga-de-amorr-rafaga","title":"Escuchar una rafaga de amorr de rafaga"},
                        {"href":"listen/b9cdad7/rafaga-feat-korekt---sabes(((-www.robney.com.pe-)))-rafaga-feat-korekt---sabes","title":"Escuchar Rafaga Feat Korekt - Sabes((( wWw.Robney.CoM.Pe ))) de Rafaga Feat Korekt - Sabes"},
                        {"href":"listen/1286fa6/rafaga---mentirosa-(danny-romero-remix)-rafaga---mentirosa-(danny-romero-remix)","title":"Escuchar Rafaga - Mentirosa (Danny Romero Remix) de Rafaga - Mentirosa (Danny Romero Remix)"},
                        {"href":"listen/b229d9c/no-te-vallas-remix-rafaga-dj-efexts-rafaga-dj-efexts","title":"Escuchar No Te Vallas Remix Rafaga Dj Efexts de Rafaga Dj Efexts"}
                ]
        }
        }
}

La librería solo es un prototipo de lo que va a ser. La idea es poder obtener Objetos Java como Map o Document para manipularlo aun mas fácil desde Java.

Además se podrá definir el método HTTP de la consulta (GET o POST) para aumentar aun mas su funcionalidad. La idea es poder hacer la mayoría de los comando SQL que funcionen en JQL.

Bueno acá les dejo el link de la librería si quieres aportar:
http://kenai.com/projects/javaquerylanguage/

PD: El código esta muy desordenado D:

Saludos!

ACTUALIZACIÓN 01 (25/05/2010):
– Puede devolver Map y String con JSON.
– Ordenado el Código y programado Orientado a Objetos.
– Mejorado el codigo para parsear las consultas

Categorías:Java, Proyectos Etiquetas: , , , ,

Practica 03 Lección 03 Introducción a Oracle Server [OCADBA]

1.- Conéctese a la base de datos como usuario SYS y ciérrela.

SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

2.- Con la base de datos cerrada, cree un archivo SPFILE a partir de PFILE. El archivo SPFILE se creará en $ORACLE_HOME/dbs

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> CREATE SPFILE FROM PFILE;
File created.

3.- Desde el sistema operativo, visualice el archivo SPFILE.

4.- Conéctese como usuario SYS e inicie la base de datos con el archivo SPFILE.

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 26706720 bytes
Fixed Size 729888 bytes
Variable Size 20971520 bytes
Database Buffers 4194304 bytes
Redo Buffers 811008 bytes
Database mounted.

5.-
a.- Cierre la base de datos y ábrala en modo de sólo lectura.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
Total System Global Area 26706720 bytes
Fixed Size 729888 bytes
Variable Size 20971520 bytes
Database Buffers 4194304 bytes
Redo Buffers 811008 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

b.- Conéctese como usuario HR con la contraseña HR e inserte una fila en la tabla REGIONS , ¿Qué sucede?

/* Creado usuario */
SQL> CREATE USER HR IDENTIFIED BY HR;
/* privilegios */
SQL> GRANT ALL PRIVILEGES TO HR;
/* crear tabla regions */
SQL> CREATE TABLE regions (
	id NUMBER,
	nombre VARCHAR2(50)
);
SQL> CONNECT HR/HR
Connected.
SQL> INSERT INTO regions VALUES (5, 'Mars');
INSERT INTO regions VALUES (5, 'Mars')
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system
tablespace
'SAMPLE'

c.- Vuelva a poner la base de datos en modo de lectura y escritura.

SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 26706720 bytes
Fixed Size 729888 bytes
Variable Size 20971520 bytes
Database Buffers 4194304 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened..

6.-
a.- Conéctese como usuario HR con la contraseña HR e inserte la siguiente fila en la tabla REGIONS; no valide ni salga de la base de datos.

INSERT INTO regions VALUES (5, ‘Mars’);

HR SESSION
SQL> CONNECT HR/HR
Connected.
SQL> INSERT INTO regions VALUES (5, 'Mars');
1 row created.

b.- En una nueva sesión telnet, inicie SQL*Plus. Conéctese como SYS y realice un SHUTDOWN TRANSACTIONAL.

SYS SESSION
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN TRANSACTIONAL

c.- Realice un rollback de la inserción en la sesión HR y salga.

HR SESSION
SQL> ROLLBACK;
Rollback complete.
SQL> EXIT;
ERROR:
ORA-01089: immediate shutdown in progress - no operations are
permitted
JServer Release 9.0.0.0.0 - Beta (with complications)
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
- 64bit Productn
With the Partitioning, Oracle Label Security, OLAP and Oracle
Data Mining optios
JServer Release 9.2.0.1.0 - Production

¿Qué le ocurre a la sesión HR?
Se recibe un mensaje ORA-01089. El usuario HR no puede salir (EXIT) porque el usuario SYS ha emitido una sentencia SHUTDOWN TRANSACTIONAL. No se permite ninguna otra operación.

¿Qué le ocurre a la sesión SYS?
Cuando la sesión HR completa un ROLLBACK,la sesión SYS cierra la base de datos.

SYS SESSION
Database closed.
Database dismounted.
ORACLE instance shut down.

La sesión HR se desconectará basándose en el comando EXIT.

HR SESSION
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0
- 64bit Productn
With the Partitioning, Oracle Label Security, OLAP and Oracle
Data Mining optios
JServer Release 9.2.0.1.0 - Production

7.-
a.-  Inicie la base de datos en la sesión de usuario SYS.

SYS SESSION
SQL> STARTUP
ORACLE instance started.
Total System Global Area 26706720 bytes
Fixed Size 729888 bytes
Variable Size 20971520 bytes
Database Buffers 4194304 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.

b.-  En la sesión telnet abierta, inicie SQL*Plus y conéctese como usuario HR.

Nota: Mantenga las dos sesiones SQL*Plus abiertas, una como usuario SYS y otra como usuario HR.


c.- Como usuario SYS, active una sesión restringida.

SYS SESSION
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

d.-  Como usuario HR, realice un SELECT en la tabla REGIONS. ¿Se ha realizado la operación SELECT correctamente?

HR SESSION
SQL> SELECT * FROM regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

e.- Salga de la sesión y, a continuación, vuélvase a conectar como HR. ¿Qué sucede? El usuario HR no tiene el privilegio RESTRICTED SESSION y, por lo tanto, no puede conectarse.

HR SESSION
SQL> EXIT
Disconnected from Oracle9i Enterprise Edition Release
9.2.0.1.0 - 64bit Productn
With the Partitioning, Oracle Label Security, OLAP and
Oracle Data Mining optios
JServer Release 9.2.0.1.0 - Production
SQL> CONNECT HR/HR
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED
SESSION privilege
Warning: You are no longer connected to ORACLE.

f.-  Como usuario SYS, desactive la sesión restringida.

SYS SESSION
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.

g.-  Salga de la sesión telnet de HR

HR SESSION
$ EXIT
This session is no longer connected.

Creando Un mini WebService en PHP -> JSON-JSONP

Bueno hoy un poco aburrido hice este pequeño código en PHP para crear un mini WebService JSON-JSONP con base de datos ya creadas.

Para eso usaré una clase muy buena para la Base de datos “mysql-database-class-wrapper“. Para esa clase hice una función que consulta, y los resultados  los muestra como JSON o JSONP, acá la “function”:

#-#############################################
# autor: Esteban Fuentealba
# description: Con esta funcion podrás consultar y devolver el resultado en JSON
# params: Consulta SQL, nombre del callback
# returns: JSON
function query2JSON($sql,$callback='') {
	$rows = $this->fetch_all_array($sql);
	$total_rows=count($rows);
	$i=0;
	$str=$callback."({\r\n\tstatus: \"".(($this->query_id != 0) ? 1 : 0)."\",\r\n\titems: [";
	foreach($rows as $k){
		$j=0;
		$total_colums = count($k);
		$str .= "\r\n\t\t{ ";
		foreach($k as $key => $val) {
			$str .= $key." : \"".$val."\"";
			$j++;
			if($j<$total_colums) $str .= ",";
		}
		$str .= " }";
		$i++;
		if($i<$total_rows) $str .= ",";
	}
	$str .="\r\n\t]\r\n})";
	return $str;
}

Con la clase DataBase.class.php lista ahora vamos a nuestra base de datos y creamos una nueva tabla

CREATE TABLE Method (
			name	VARCHAR(100) PRIMARY KEY,
			tabla	VARCHAR(100),
			sentence	TEXT
		);

Ésta tabla guardará todos los métodos de nuestro WS casero xD.

Ahora vamos con el codigo en PHP , este archivo recibirá los parámetros por el metodo GET y me mostrará los resultados en JSON.

<?PHP
	//###
	//	@Autor	: 	Esteban Fuentealba
	//	@Url	:	https://estebanfuentealba.wordpress.com/
	//	@Email	:	mi [dot] warezx [at] gmail [dot] com
	// Ejemplo:		api.php?method=user.getNombre&id=<rut>
	//###

	//## Configuracion de la Base de datos
	require_once 'config.inc.php';
	//## Clase Base de Datos
	require_once 'class/Database.class.php';
	//## Obtengo los parametros pasados por el metodo GET
	$params	= $_GET;
	//## Si los parametros son mas de 0
	if(count($params) > 0) {
		//## Creo un objeto de Base de datos, con los parametros de configuración
		$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
		$db->connect();
		/*
			CODE
			200 OK
			202	OK pero sin resultados
			404	Method no existe
			405 ERROR Method nombre
		*/
		if($params['method']) {
			/*
				## Corto el parametro method en '.' y asi obtengo:
					- [0] 	nombre de la Tabla
					- [1]	nombre del WebService
			*/
			$data = explode('.',$params['method']);
			if(count($data) == 2) {
				//## Compruebo si el metodo del WS existe en la base de datos
				$rows = $db->query_first("SELECT * FROM Method WHERE tabla='".$data[0]."' AND name='".$data[1]."'");
				if($rows['sentence']) {
					//## Busco y reemplazo los parametros {ejemplo} por los parametros pasados por metodo GET
					preg_match_all('#\{(.[^\}]+)\}#',$rows['sentence'],$r);
					$sentenceSQL = $rows['sentence'];
					foreach($r[1] as $param) {
						$sentenceSQL = str_replace('{'.$param.'}',(is_numeric($params[$param])) ? ((int)$params[$param]) : "'".$db->escape($params[$param])."'",$sentenceSQL);
					}
					if($db->affected_rows>0) {
						//## Consulto la bd segun la sentencia y los resultados los muestro como JSON
						echo $db->query2JSON($sentenceSQL,$params['callback']);
					} else {
						echo '{ status :"ok",code: 202 }';
					}
				} else {
					echo '{ status :"error",code: 406 }';
				}
			} else {
				echo '{ status :"error",code: 405 }';
			}
		} else {
			echo '{ status :"error",code: 404 }';
		}
		$db->close();
	}
?>

Con eso ya podemos Usar nuestro WebService. la forma de uso es la siguiente:
Ejemplo:

api.php?method=tabla.nombreMethod&param1=valor1&callback=holaMundo

Ahora dejaré 3 archivos que hice para testear y agregar WebServices (Usenlos solo para Agregar metodos a los WS y luego los eliminan ya que no están validadas ni limpiadas las consultas)

Primero dejo este archivo PHP que agregara los WS a nuestra base de datos
Archivo: addApi.php

<?PHP
	require_once 'config.inc.php';
	require_once 'class/Database.class.php';
	$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
	$db->connect();
	$name			= $_POST['name'];
	$tabla			= $_POST['tabla'];
	$query			= $_POST['query'];
	$in['name'] 	= $name;
	$in['tabla']	= $tabla;
	$in['sentence']	= $query;
	$db->query_insert('Method',$in);
	$db->close();
?>

Ahora les dejo el archivo que testea las consultas:
Archivo: queryTest.php

<?PHP
	require_once 'config.inc.php';
	require_once 'class/Database.class.php';
	$sql	= $_GET['query'];
	$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
	$db->connect();
	echo $db->query2JSON($sql);
	$db->close();
?>

y por ultimo la pagina con el formulario para crear los webservice

<html>
<head>
	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
	<script type="text/javascript">
		$(function() {
			getSQL("SHOW TABLES","json",function(data) {
				$.each(data.items,function(i,it) {
					if(it.Tables_in_api != 'method') {
						$('<option>').attr('value',it.Tables_in_api).append(
							it.Tables_in_api
						).appendTo(
							$('#tables')
						);
					}
				});
			});
			$('#tables').change(function() {
				var thisTable = $(this).val();
				if(thisTable != "-1") {
					var sql = 'SELECT ';
					var fields = new Array();
					getSQL("DESC "+thisTable,"json",function(data) {
						$.each(data.items,function(i,it) {
							fields.push(it.Field);
						});
						sql += fields+" FROM "+thisTable;
						$('#query').val(sql);
					});
				}
			});
			$('#query').keyup(function(event) {
				var url = $('#query').val();
				var re  = /\{(.[^\}]+)\}/g;
				var match;
				var html = $('<fieldset>').append('Parametros:').attr('id','parametros');
				while (match = re.exec(url)) {
					$('<div>').append(
						match[1]+" "
					).append(
						$('<input>').attr('type','text').attr('id','{'+match[1]+'}')
					).appendTo(html);
				}
				$('#params').html(html);
			});
			$('#qTest').click(function() {
				var sql = $('#query').val();
				$.each($('#parametros :input'),function(i,item) {
					sql = sql.replace($(this).attr('id'),$(this).val());
				});
				getSQL(sql,"html",function(data) {
					$('#out').html(data);
				});
			});
			$('#qAdd').click(function() {
				$.post('addApi.php',{
									name: $('#name').val(),
									tabla: $('#tables').val(),
									query: $('#query').val()
				},function(data) {
					$('#boxB').html('OK').hide('slow');
				});
			});
			$('#name').blur(function() {
				getSQL("SELECT * FROM Method WHERE name='"+$(this).val()+"' and tabla='"+$('#tables').val()+"'","json",function(data) {
					if(data.items.length > 0) {
						alert('Method ya existe');
						$('#name').val('');
					}
				});
			});
		});
		function getSQL(sql,type,callback) {
			$.ajax({
				type: "GET",
				url: 'queryTest.php',
				data: 'query='+sql,
				dataType: type,
				success: callback
			});
		}
	</script>
</head>
<body>
<form>
	<fieldset>
		WebService Name:
		<select id="tables">
			<option value="-1">Tablas:</option>
		</select>.<input type="text" id="name" />
	</fieldset>
	<fieldset>
		WebService Query:
		<textArea id="query" style="width: 100%; height: 50px;"></textArea>
	</fieldset>
	<div id="params"></div>
	<input id="qTest" type="button" value="Test Query" />
	<input id="qAdd" type="button" value="Agrega WebService" />
</form>
<div id="box">
	<textArea id="out" style="width: 100%; height: 500px;"></textArea>
</div>
<div id="boxB"></div>
</body>
</html>

Acá les dejo una ScreenShot del sistema montado en mi netbook.

Selecciono la tabla, le agrego un nombre a mi Method , ingreso la consulta SQL y testeo. La consulta SQL puede tener Parametros , ejemplo en la ScreenShot definí un parametro entre llaves {id}, ese parametro lo asignaré cuando llame al WebService api.php.Dejo el Ejemplo de la llamada al WebService en la siguiente Imagen:

En la tabla user tenía 2 registros la id=1 pertenece al usuario caca Bueno ese es un ejemplo básico, ustedes pueden darle el uso que quieran.

Espero que les sirva esta pequeña aplicación. solo es una idea y la primera versión, debe tener muchos errores pero si alguien quiere aportar con alguna idea o mejora estará bien.

Saludos!.

Demo WebService :

Codigo de Fuente WebService: Webservice.EstebanFuentealba.rar

Categorías:PHP Etiquetas: , , , , , , ,

Certamen 2 DAI4501 [PHP/SESSION/JAVASCRIPT/CAPAS]

diciembre 7, 2009 2 comentarios

Acá dejo el último certamen de Desarrollo de Aplicaciones para Internet (DAI4501) de la profesora Yasna Meza

Enunciado:

Considerando la siguiente tabla de datos y su correspondiente diccionario de datos:

Se  pide  construir  una  aplicación  en  PHP  usando  capas  que  permita  cumplir  con  los
siguientes requerimientos:
R01 – Inicio y cierre de sesión
R02 – Agregar registros a la tabla de datos
R03 – Modificar registros de la tabla de datos
R04 – Listar todos los registros de la tabla
R05 –  Listar  usando  como  criterio  de  búsqueda  un  rango  de  sueldo.  El  sueldo  del profesor se calcula de acuerdo a la siguiente fórmula:
SUELDO = NÚMERO HORAS * VALOR HORA
OBSERVACIONES
·  DEBERÁ tener en consideración todas las validaciones
·  La base de datos DEBERÁ llamarse C2

Codigo de Fuente:
http://estebanfuentealba.net/archive/Certamen2-DAI-Esteban-Fuentealba.rar

Saludos!

Certamen 2 PROGRAMA DE BASES DE DATOS (OCA DEVELOP) ORACLE PL/SQL

Este es el Certamen 2 de PROGRAMA DE BASES DE DATOS (OCA DEVELOP)

[ 1 ] Crear una tabla EMP , una función CAL_NEWSAL, los los procedimientos UPD_NEWSAL , NEW_EMP y el trigger VAL_SALARY
para insertar y actualizar datos de un empleado (55 pts)

1A Crear una tabla EMP con información a partir de una consulta que seleccione el
employee_id,first_name,last_name y salary de la tabla EMPLOYEES (4 pts)

CREATE TABLE emp AS
	SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY
	FROM employees;

1B Agrega a la tabla EMP dos nuevas columnas PERCENT de tipo NUMBER(8,2) y NEW_SALARY de tipo NUMBER de (8,2) (5 pts)

ALTER TABLE emp ADD percent NUMBER(8,2);
ALTER TABLE emp ADD new_salary NUMBER(8,2);

1C Crear una Funcion llamada CALL_NEWSAL que calcule el nuevo salario del empleado a partir del salariode éste
y un factor que representa el porcentaje en términos decimales. La formula para calcular el nuevo slario es: (6 pts)
(v_salary + ((v_salary – (v_salary * 12 * v_factor)) * v_factor))

CREATE OR REPLACE FUNCTION cal_newsal (v_salary IN employees.SALARY%TYPE,v_factor IN NUMBER) RETURN NUMBER
IS
BEGIN
	RETURN (v_salary + ((v_salary - (v_salary * 12 * v_factor)) * v_factor));
END cal_newsal;
/

1D CreValid Employee ID

CREATE OR REPLACE FUNCTION valid_employee(v_employee_id IN employees.EMPLOYEE_ID%TYPE) RETURN BOOLEAN
IS
	v_temporal VARCHAR(1);
BEGIN
	SELECT 'x'
	INTO v_temporal
	FROM emp
	WHERE EMPLOYEE_ID = v_employee_id;
	RETURN TRUE;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN FALSE;
END;
/

1D Crear un Procedimiento UPD_NEWSAL que permita actualizar, en la columna PERCENT el porcentaje de
incremento y en la columna NEW_SAL el nuevo incremento y en la columna NEW_SAL el nuevo salario. Para ello deberá pasar
el identificador del empleado y la función que calcula el nuevo salario creada en el punto anterior. En caso de actualizar
un empleado que no existe dse deberá informar lo ocurrido a través de un mensaje apropiado. Finalmente hacer los cambios permanentes.

CREATE OR REPLACE PROCEDURE upd_newsal(v_employee_id IN employees.EMPLOYEE_ID%TYPE,v_factor IN NUMBER)
IS
	empleado_salary employees.SALARY%TYPE;
BEGIN
	IF valid_employee(v_employee_id) THEN
		SELECT SALARY
		INTO empleado_salary
		FROM emp
		WHERE EMPLOYEE_ID = v_employee_id;

		UPDATE emp
		SET NEW_SALARY = cal_newsal (empleado_salary,v_factor)
		WHERE EMPLOYEE_ID = v_employee_id;
	ELSE
		RAISE_APPLICATION_ERROR(-20001,'ERROR: El id de Usuario No Existe');
	END IF;
END upd_newsal;
/

1E Testear el procedimiento UPD_NEWSAL para los empleados 100,105,110, con un factor de 0.01,0.02 y 0.03 correspondiente (3 pts)

SET SERVEROUTPUT ON
BEGIN
	upd_newsal(100,0.01);
END;
/
SET SERVEROUTPUT ON
BEGIN
	upd_newsal(105,0.05);
END;
/
SET SERVEROUTPUT ON
BEGIN
	upd_newsal(110,0.03);
END;
/

1F Crear un PROCEDIMIENTO NEW_EMP que permita para un nuevo empleado ingresar employee_id,first_name,last_name,salary,percent,new_salary,
para el new_salary utilizar la funcion cal_newsal. Conjuntamente deberá validar el salario, a través de un trigger de DML por fila llamado
VAL_SALARY que controle que el salario no debe exceder de 25.000 (18 pts)

CREATE OR REPLACE TRIGGER val_salary
BEFORE INSERT OR UPDATE OF SALARY
ON emp
FOR EACH ROW
BEGIN
	IF :new.SALARY &gt; 25000 THEN
		RAISE_APPLICATION_ERROR(-20002,'ERROR: El salario sobrepasa los 25.000');
	END IF;
END val_salary;
/

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE new_emp(v_employee_id 	IN employees.EMPLOYEE_ID%TYPE,
									v_first_name 	IN employees.FIRST_NAME%TYPE,
									v_last_name 	IN employees.LAST_NAME%TYPE,
									v_salary 		IN employees.SALARY%TYPE,
									v_percent 		IN NUMBER
									)
IS
	nuevoSalario NUMBER;
BEGIN
	IF valid_employee(v_employee_id) THEN
		DBMS_OUTPUT.PUT_LINE('ERROR: Ya Hay un usuario con esa ID');
	ELSE
		nuevoSalario := cal_newsal(v_salary,v_percent);
		INSERT INTO emp (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,PERCENT,NEW_SALARY)
		VALUES(	v_employee_id,
				v_first_name,
				v_last_name,
				v_salary,
				v_percent,
				nuevoSalario
				);
	END IF;

END new_emp;
/

1G TESTEAR EL PROCEDIMIENTO NEW_EMP Para los siguientes datos (2 pts)
Fila1:
employee_id:200, first_name: Albert,last_name: Einstein, salary: 20000, percent: 0.01, new_salary: cal_newsal
Fila2:
employee_id:300, first_name: Hilary,last_name: Clinton, salary: 26000, percent: 0.02, new_salary: cal_newsal

SET SERVEROUTPUT ON
BEGIN
	new_emp(200,'Albert','Einstein',20000,0.01);
END;
/

SET SERVEROUTPUT ON
BEGIN
	new_emp(300,'Hilary','Clinton',20000,0.02);
END;
/

1H Elimina tabla EMP (1 pts)

DROP TABLE emp;

1I Elimina Funcion cal_newSal (1 pts)

DROP FUNCTION cal_newsal;

1J Elimina Procedure upd_newsal (1 pts)

DROP PROCEDURE  upd_newsal;

1K Elimina Procedure new_emp (1 pts)

DROP PROCEDURE  new_emp;

1L Elimina Trigger val_salary (1 pts)

DROP TRIGGER val_salary;

[ 2 ] CREAR Y LLAMAR UN PACKAGE QUE CONTIENE CONSTRUCTORES PÚBLICOS Y PRIVADOS (45 pts)

2A Crear una tabla EMP con información, a partir de una consulta que seleccione el employee_id,first_name,
last_name y department_id de la tabla EMPLOYEES (4 pts)

CREATE TABLE emp AS
	SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID
	FROM employees;

2B Crear una especificacion y un cuerpo de package llamado EMP_PACK que contiene un procedimiento
NEW_EMP como constructor público, y una función VALID_DEPT_ID como un constructor privado (29 pts)

CREATE OR REPLACE PACKAGE emp_pack IS
	PROCEDURE NEW_EMP(	v_employee_id IN employees.EMPLOYEE_ID%TYPE,
						v_first_name IN employees.FIRST_NAME%TYPE,
						v_last_name IN employees.LAST_NAME%TYPE,
						v_department_id IN employees.DEPARTMENT_ID%TYPE
					);
END emp_pack;
/
SET SERVEROUTPUT ON
CREATE OR REPLACE PACKAGE BODY emp_pack IS
	---------------------------------------------------------------------------------
	--	FUNCION PARA VALIDAR EL DEPARTAMENTO 						RETURN [BOOLEAN]
	---------------------------------------------------------------------------------
	FUNCTION VALID_DEPT_ID(v_id_depto IN employees.DEPARTMENT_ID%TYPE) RETURN BOOLEAN
	IS
		v_temporal VARCHAR(1);
	BEGIN
		SELECT 'x'
		INTO v_temporal
		FROM DEPARTMENTS
		WHERE DEPARTMENT_ID = v_id_depto;
		RETURN TRUE;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			RETURN FALSE;
	END;
	---------------------------------------------------------------------------------
	--	PROCEDIMIENTO NEW_EMP
	---------------------------------------------------------------------------------
	PROCEDURE NEW_EMP(	v_employee_id IN employees.EMPLOYEE_ID%TYPE,
						v_first_name IN employees.FIRST_NAME%TYPE,
						v_last_name IN employees.LAST_NAME%TYPE,
						v_department_id IN employees.DEPARTMENT_ID%TYPE
					)
	IS
	BEGIN
		IF VALID_DEPT_ID(v_department_id) THEN
			INSERT INTO emp(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID)
			VALUES(v_employee_id,v_first_name,v_last_name,v_department_id);
		ELSE
			DBMS_OUTPUT.PUT_LINE('ERROR: No existe Departamento');
		END IF;
	END;
END emp_pack;
/

2C Llamar al procedimiento NEW_EMP, utilizando como identificador de departamento el número 15. Como
el departamento 15 no existe en la tabla DEPARTMENT, se deberá desplegar un mensaje informando lo ocurrido (5 pts)

SET SERVEROUTPUT ON
BEGIN
	emp_pack.NEW_EMP(305,'Juan','Perez',15);
END;
/

2D Eliminar Tabla emp (1 pts)

DROP TABLE emp;

2E Eliminar funcion VALID_DEPT_ID (1 pts)

DROP FUNCTION emp_pack.VALID_DEPT_ID;

2F Eliminar procedimiento NEW_EMP (1 pts)

DROP PROCEDURE emp_pack.NEW_EMP;

2G Eliminar especificacion y cuerpo del package llamado EMP_PACK (1 pts)

DROP PACKAGE emp_pack;
Categorías:ORACLE, PL/SQL Etiquetas: , , , ,

ORACLE PL/SQL Repaso

Cursores:

	-- Declarar Cursor
	CURSOR emp_cursor IS
		SELECT last_name, department_id
		FROM employees;
	emp_record emp_cursor%ROWTYPE;

	-- Recorrer Secursar
	FOR emp_record IN emp_cursor LOOP --implicitamente abre y recupera la ocurrencia
     	IF emp_record.department_id = 80 THEN
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(emp_record.department_id)||' '||emp_record.last_name);
		END IF;
   	END LOOP; -- implícitamente cierra la ocurrencia

Funciones

CREATE OR REPLACE FUNCTION g_job (p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
	v_jobtitle jobs.job_title%TYPE;
BEGIN
	SELECT job_title
	INTO v_jobtitle
	FROM jobs
	WHERE job_id = p_jobid;
	RETURN (v_jobtitle);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN(NULL);
END g_job;
/
VARIABLE g_title VARCHAR2(30)
EXECUTE :g_title:= g_job('SA_REP')
PRINT g_title

Packages

CREATE OR REPLACE PACKAGE job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE);
	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE,p_job_title IN jobs.job_title%TYPE);
	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE);
	FUNCTION q_job    (p_job_id IN jobs.job_id%TYPE)RETURN VARCHAR2;
END job_pack;
/

CREATE OR REPLACE PACKAGE BODY job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		INSERT INTO jobs(job_id, job_title)
		VALUES (p_jobid, p_jobtitle);
	END add_jobs;

	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		UPDATE jobs
		SET job_title=p_jobtitle
		WHERE job_id=p_jobid;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20202,'No Job Updated');
		END IF;
	END upd_job;

	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE) IS BEGIN
		DELETE FROM jobs
		WHERE job_id = p_job_id;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20203,'No job deleted');
		END IF;
	END del_job;

	FUNCTION q_job (p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2 IS
		v_jobtitle jobs.job_title%TYPE;
	BEGIN
		SELECT job_title
		INTO v_jobtitle
		FROM jobs
		WHERE job_id = p_jobid;
		RETURN (v_jobtitle);
	END q_job
END job_pack;
/	


Procedimientos

create or replace procedure procedimiento (p_empid IN employees.employee_id%TYPE, p_sal OUT employees.salary%TYPE, p_job OUT employees.job_id%TYPE)
IS BEGIN
	SELECT salary,job_id
	INTO p_sal, p_job
	FROM employees
	WHERE employee_id = p_empid;
END procedimiento;
/

-- imprimir
VARIABLE g_sal NUMBER
VARIABLE g_job VARCHAR2(15)
EXECUTE procedimiento(120,:g_sal,:g_job)
PRINT g_sal
PRINT g_job


Triggers

create or replace trigger xxx
after update or insert or delete on employees for each row -- After:Antes
	   if updating  then

	elsif inserting then

	elsif deleting then

	end if;
end xxx;
/
Categorías:ORACLE, PL/SQL Etiquetas: , ,

PRACTICO 7 SQL SOLUTION

diciembre 6, 2008 1 comentario

Práctica 7

Determine si las siguientes dos preguntas son verdadera o falsa

1.- La siguiente sentencia es valida: (V) (F)
DEFINE & p_val = 100
F
2.- El comando DEFINE es un comando SQL (V) (F)
F
3.- Escriba un script para desplegar el apellido de un empleado, cargo y fecha de contrato para todos los empleados que se iniciaron entre un rango dado. Concatenar nombre y trabajo juntos, separado por un espacio y coma, y etiquetar la columna como Empleado. En un archivo Script SQL separado, use el comando DEFINE para proveer los dos rangos. Use el formato MM/DD/YYYY. Grabar los archivos script como lab7-3a.sql y lab7-3b.sql

DEFINE fechaInicio = 01/01/1987
DEFINE fechaTermino = 01/01/2007
SELECT last_name || ', ' || job_id AS "Empleado" , hire_date
FROM employees
WHERE hire_date BEETWEEN TO_DATE(&amp;fechaInicio,'MM/DD/YYYY') AND TO_DATE(&amp;fechaTermino,'MM/DD/YYYY');

4.- Escribir un script para desplegar el apellido, el identificador del cargo y nombre del departamento para cada empleado en una localización dada. Grabar el archivo script como lab7-4.sql

SELECT e.last_name , e.job_id, d.departament_name
FROM employees e, departaments d, locations l
WHERE e.departament_id = d.departament_id AND d.location_id = l.location_id
AND l.city = '&amp;entrada';
Categorías:SQL Etiquetas: ,