Oracle Live

19/08/2016

PLSQL – Tutorial

Filed under: Documentacion,PLSQL — mogukiller @ 4:14 pm

Hace unos años me hice una chuleta donde recogia elementos básicos de PLSQL. Os recomienod utilizar el menu indice para poder acceder al contenido

-- PAQUETES
-- FUNCIONES Y PROCEDIMIENTOS
-- CURSORES VARIABLES
-- CURSORES
-- RECORD
-- COLLECTION
-- MANEJO DE EXCEPCIONES
-- EXECUTE IMMEDIATE
-- SELECT FOR UPDATE
-- ESTRUCTURAS DE CONTROL
-- BUCLES
-- VARIABLES
-- GOTO
-- TIPOS DE DATOS
	-- NUMBER
	-- DATE | INTERVAL
-- FUNCIONES UTILES

-----------------------------------------
-- PAQUETES
-----------------------------------------
'
Un paquete es un objeto de base de datos que lo podemos entender como un conjunto de subprogramas, variables y tipos de PL/SQL.
Un paquete esta compuesto por una cabecera y un cuerpo. 
La cabecera del paquete es donde especificamos los subprogramas que componen el paquete o las variables globales que van poder utilizar esos subprogramas. 
Los subprogramas declarados en la cabecera del paquete son los que van a poder ser invocados por los usuarios.'

-- Ejemplo de cabecera

CREATE PACKAGE trans_data AS -- bodiless package
	TYPE TimeRec IS RECORD (
		minutes SMALLINT,
		hours SMALLINT);

	TYPE TransRec IS RECORD (
		category VARCHAR2(10),
		account INT,
		amount REAL,
		time_of TimeRec);

	minimum_balance CONSTANT REAL := 10.00;
	number_processed INT;
	insufficient_funds EXCEPTION;
	
END trans_data;
/

-- Ejemplo de cuerpo de un paquete:

CREATE PACKAGE emp_bonus AS
	PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
	END emp_bonus;
/

CREATE PACKAGE BODY emp_bonus AS
	PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS
	BEGIN
		DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get bonus.');
	END;
END emp_bonus;
/

'El cuerpo del paquete puede tener declaraciones privadas, que seran funciones a las que podran llamar el resto de subprogramas, pero que no podran ser accesibles por los usuarios.'

-- Ejemplo de definicion y utilizacion de un paquete:

CREATE TABLE emp_audit(date_of_action DATE, user_id VARCHAR2(20),package_name VARCHAR2(30));

CREATE OR REPLACE PACKAGE emp_admin AS
	TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);

	CURSOR desc_salary RETURN EmpRecTyp;
	
	invalid_salary EXCEPTION;
	
	FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2,
	email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER,
	commission_pct NUMBER, manager_id NUMBER, department_id NUMBER)	RETURN NUMBER;
	
	PROCEDURE fire_employee (emp_id NUMBER); -- overloaded subprogram
	
	PROCEDURE fire_employee (emp_email VARCHAR2); -- overloaded subprogram
	
	PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
	
	FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;
/

CREATE OR REPLACE PACKAGE BODY emp_admin AS
	number_hired NUMBER; -- variable privada
	CURSOR desc_salary RETURN EmpRecTyp IS
		SELECT employee_id, salary FROM employees ORDER BY salary DESC;
	FUNCTION hire_employee (last_name VARCHAR2, first_name VARCHAR2,
		email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER,
		commission_pct NUMBER, manager_id NUMBER, department_id NUMBER)
	RETURN NUMBER IS new_emp_id NUMBER;
	BEGIN
		SELECT employees_seq.NEXTVAL INTO new_emp_id FROM dual;
		INSERT INTO employees VALUES (new_emp_id, last_name, first_name, email,
			phone_number, SYSDATE, job_id, salary, commission_pct, manager_id,
			department_id);
		number_hired := number_hired + 1;
		DBMS_OUTPUT.PUT_LINE('The number of employees hired is '|| TO_CHAR(number_hired) );
		RETURN new_emp_id;
	END hire_employee;
	
	PROCEDURE fire_employee (emp_id NUMBER) IS
	BEGIN
		DELETE FROM employees WHERE employee_id = emp_id;
	END fire_employee;
	
	PROCEDURE fire_employee (emp_email VARCHAR2) IS
	BEGIN
		DELETE FROM employees WHERE email = emp_email;
	END fire_employee;

	-- Funcion privada
	FUNCTION sal_ok (jobid VARCHAR2, sal NUMBER) RETURN BOOLEAN IS
		min_sal NUMBER;
		max_sal NUMBER;
	BEGIN
		SELECT MIN(salary), MAX(salary) INTO min_sal, max_sal FROM employees
		WHERE job_id = jobid;
		RETURN (sal >= min_sal) AND (sal <= max_sal);
	END sal_ok;

	PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
		sal NUMBER(8,2);
		jobid VARCHAR2(10);
	BEGIN
		SELECT job_id, salary INTO jobid, sal FROM employees
		WHERE employee_id = emp_id;
		IF sal_ok(jobid, sal + amount) THEN
			UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
		ELSE
			RAISE invalid_salary;
		END IF;
	EXCEPTION
		WHEN invalid_salary THEN
		DBMS_OUTPUT.PUT_LINE('The salary is out of the specified range.');
	END raise_salary;

	FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp IS
		emp_rec EmpRecTyp;
	BEGIN
		OPEN desc_salary;
		FOR i IN 1..n LOOP
			FETCH desc_salary INTO emp_rec;
		END LOOP;
		CLOSE desc_salary;
		RETURN emp_rec;
	END nth_highest_salary;
	
BEGIN -- parte para la inicializacion
	INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ADMIN');
	number_hired := 0;
END emp_admin;
/

-- LLamada de la funcion.

DECLARE
	new_emp_id NUMBER(6);
BEGIN
	new_emp_id := emp_admin.hire_employee('Belden', 'Enrique', 'EBELDEN',
	'555.111.2222', 'ST_CLERK', 2500, .1, 101, 110);
	DBMS_OUTPUT.PUT_LINE('The new employee id is ' || TO_CHAR(new_emp_id) );
	EMP_ADMIN.raise_salary(new_emp_id, 100);
	DBMS_OUTPUT.PUT_LINE('The 10th highest salary is '||
	TO_CHAR(emp_admin.nth_highest_salary(10).sal) || ', belonging to employee: '
	|| TO_CHAR(emp_admin.nth_highest_salary(10).emp_id) );
	emp_admin.fire_employee(new_emp_id);
END;
/

'Como vemos para poder utilizar un paquete utilizamos el nombre del paquete.procedimiento.'

--------------------------------------
-- FUNCIONES Y PROCEDIMIENTOS
--------------------------------------

-- Procedimiento:

DECLARE
	in_string VARCHAR2(100) := ''This is my test string.'';
	out_string VARCHAR2(200);
	PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS
	BEGIN
		new_string := original || '' + '' || original;
	EXCEPTION
		WHEN VALUE_ERROR THEN
		DBMS_OUTPUT.PUT_LINE(''Output buffer not long enough.'');
	END;
BEGIN
	double(in_string, out_string);
	DBMS_OUTPUT.PUT_LINE(in_string || '' - '' || out_string);
END;
/

'Como vemos la diferencia entre el procedimiento y la funcion es qeu el procedimiento no devuelve ningun valor mientras que en la definicion de la funcion tenemos que indicar el valor qeu vamos a devolver.'

-- Funcion

CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
RETURN NUMBER IS
BEGIN
	RETURN (original * original)/2 + (original * 4);
END half_of_square;
/

-- Procedimientos anidados:
DECLARE
	PROCEDURE proc1(number1 NUMBER); -- forward declaration
	PROCEDURE proc2(number2 NUMBER) IS
	BEGIN
		proc1(number2); -- calls proc1
	END;
	
	PROCEDURE proc1(number1 NUMBER) IS
	BEGIN
		proc2 (number1); -- calls proc2
	END;
BEGIN
	NULL;
END;
/

DECLARE
	emp_num NUMBER(6) := 120;
	bonus NUMBER(6) := 100;
	merit NUMBER(4) := 50;
	PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
	BEGIN
		UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
	END raise_salary;
BEGIN
	raise_salary(emp_num, bonus); -- procedure call specifies actual parameters
	raise_salary(emp_num, merit + bonus); -- expressions can be used as parameters
END;
/

-- Utilizar valores por defecto:
DECLARE
	emp_num NUMBER(6) := 120;
	bonus NUMBER(6);
	merit NUMBER(4);
	PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER DEFAULT 100,
	extra IN NUMBER DEFAULT 50) IS
	BEGIN
		UPDATE employees SET salary = salary + amount + extra WHERE employee_id = emp_id;
	END raise_salary;
BEGIN
	raise_salary(120); -- same as raise_salary(120, 100, 50)
	raise_salary(emp_num, extra => 25); -- same as raise_salary(120, 100, 25)
END;
/

------------------------------------------
-- CURSORES VARIABLES
------------------------------------------

-- Declaracion

DECLARE
	TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE; -- fuerte.
	TYPE genericcurtyp IS REF CURSOR; -- debil

-- Para utilizar el cursor necesitamos una variable:

DECLARE
	TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE; -- fuerte.
	tmp_cv DeptCurTyp;

-- Tambien podemos crear un cursor que apunte a un record:
DECLARE
	TYPE EmpRecTyp IS RECORD (
		employee_id NUMBER,
		last_name VARCHAR2(25),
		salary NUMBER(8,2));
		
	TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
	
	emp_cv EmpCurTyp; -- declare cursor variable

-- Utilizacion de un cursor variable:
DECLARE
	TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
	emp empcurtyp;
	PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
	person employees%ROWTYPE;
	BEGIN
		DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
		LOOP
		FETCH emp_cv INTO person;
			EXIT WHEN emp_cv%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||' ' || person.last_name);
		END LOOP;
	END;
BEGIN
	-- First find 10 arbitrary employees.
	OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
	process_emp_cv(emp);
	CLOSE emp;
	-- find employees matching a condition.
	OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
	process_emp_cv(emp);
	CLOSE emp;
END;
/

'Como podemos ver los valores que recibe este cursor salen de esta sentencia:
OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';'


-- Ejemplo:
BEGIN
	IF choice = 1 THEN
		OPEN emp_cv FOR SELECT * FROM employees WHERE commission_pct IS NOT NULL;
	ELSIF choice = 2 THEN
		OPEN emp_cv FOR SELECT * FROM employees WHERE salary > 2500;
	ELSIF choice = 3 THEN
		OPEN emp_cv FOR SELECT * FROM employees WHERE department_id = 100;
	END IF;
END;

-- Ejemplo simple de como utilizar un cursor variable:
DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
	emp_cv empcurtyp;
	emp_rec employees%ROWTYPE;
BEGIN
	OPEN emp_cv FOR SELECT * FROM employees WHERE employee_id < 0;
	LOOP
	FETCH emp_cv INTO emp_rec; -- fetch from cursor variable
	EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
		-- process data record
		DBMS_OUTPUT.PUT_LINE('Name = ' || emp_rec.first_name || ' ' ||emp_rec.last_name);
	END LOOP;
CLOSE emp_cv;
END;
/

-- Ejemplo, utilizando BULK COLLECT para meter en una coleccion varias lineas.

DECLARE
	TYPE empcurtyp IS REF CURSOR;
	TYPE namelist IS TABLE OF employees.last_name%TYPE;
	TYPE sallist IS TABLE OF employees.salary%TYPE;
	emp_cv empcurtyp;
	names namelist;
	sals sallist;
BEGIN
	OPEN emp_cv FOR SELECT last_name, salary FROM employees
	WHERE job_id = 'SA_REP';
		FETCH emp_cv BULK COLLECT INTO names, sals;
		CLOSE emp_cv;
		-- loop through the names and sals collections
		FOR i IN names.FIRST .. names.LAST
		LOOP
			DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
		END LOOP;
END;
/

-------------------------------------------
-- CURSORES
-------------------------------------------
/*
	PLSQL puede utilizar cursores de forma implicita o de forma explicita. 
	PLSQL utilizara un cursor de forma implicita en cada sentencia DML que ejecutemos e incluso con queries que devuelven una unica linea. 
	Si queremos tener algo mas de control podemos definir cursores de forma explicita, por lo que sera necesario declararlos.
*/

-- Ejemplo cursor implicito:

CREATE TABLE dept_temp AS SELECT * FROM departments;

DECLARE
	dept_no NUMBER(4) := 270;
BEGIN
	DELETE FROM dept_temp WHERE department_id = dept_no;
	IF SQL%FOUND THEN -- delete succeeded
		INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
	END IF;
END;
'
El cursor implicito que utiliza PLSQL es el cursor SQL este cursor tiene una serie de atributos como %FOUND %NOTFOUND que indica de forma boleana si se el cursor tiene algun objeto.
El ejemplo anterior es util para evitar violar claves primarias.
Otro atributo util de los cursores es %ROWCOUNT, que devuelve los valores afectados en sentencias DML.
'

DECLARE
	l_id_prueba number:=100;
BEGIN
	DELETE FROM PRUEBA_RMAN WHERE ID_PRUEBA<l_id_prueba;
	DBMS_OUTPUT.put_line('Registros borrados: '||SQL%ROWCOUNT);
	rollback;
END;
/

-- Ejemplo cursor implicito en un bucle FOR:

BEGIN
FOR item IN
( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%'
AND manager_id >12)
LOOP
	DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||
	item.job_id);
END LOOP;
END;
/

-- Cursores explicitos

'Antes de utilizar un cursor de forma explicita lo tenemos que declarar'

DECLARE
	  -- Declaracion 1:
	CURSOR c1 IS 
	SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000;
	  -- Declaracion 2:
	CURSOR c2 RETURN departments%ROWTYPE IS
		SELECT * FROM departments WHERE department_id = 110;


'Podemos declarar un cursor para que reciba parametros que luego pueden aparecen en la query.'

-- Ejemplo:

DECLARE
	CURSOR c1 (low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS
		SELECT * 
		FROM departments 
		WHERE 
			department_id > low AND department_id < high;
			
'nota: Para empezar a trabajar con un cursor primero debemos abrirlo'

-- Ejemplo:

DECLARE
	CURSOR c1 IS 
		SELECT employee_id, last_name, job_id, salary 
		FROM employees
		WHERE salary > 2000;
BEGIN
	OPEN C1;
	
'nota: Y para que devuelva una linea utilizamos el comando FETCH.'

-- Ejemplo:

DECLARE
	v_jobid employees.job_id%TYPE; -- variable for job_id
	v_lastname employees.last_name%TYPE; -- variable for last_name

	CURSOR c1 IS 
	SELECT last_name, job_id FROM employees
	WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK');
	v_employees employees%ROWTYPE; -- record variable for row

	CURSOR c2 is 
	SELECT * FROM employees
	WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]');

	BEGIN
	OPEN c1; -- open the cursor before fetching
	LOOP
		FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
		EXIT WHEN c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
	END LOOP;
	
	CLOSE c1;
	
	DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
	
	OPEN c2;
	LOOP
		FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
		EXIT WHEN c2%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id );
	END LOOP;
	
	CLOSE c2;
END;

'nota: Cada vez que utilicemos FETCH el cursor devolvera un fila:'

DECLARE
	CURSOR c1 IS 
		SELECT last_name FROM employees ORDER BY last_name;
	name1 employees.last_name%TYPE;
	name2 employees.last_name%TYPE;
	name3 employees.last_name%TYPE;
BEGIN
	OPEN c1;
	FETCH c1 INTO name1; -- this fetches first row
	FETCH c1 INTO name2; -- this fetches second row
	FETCH c1 INTO name3; -- this fetches third row
	CLOSE c1;
END;
/

'nota: Podemos directamente leer todos los elementos de un cursor si en el FETCH utilizamos BULK COLLECT.'

-- Ejemplo

DECLARE
	TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;
	TYPE NameTab IS TABLE OF employees.last_name%TYPE;
	ids IdsTab;
	names NameTab;
	CURSOR c1 IS
	SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK';
BEGIN
	OPEN c1;
	FETCH c1 BULK COLLECT INTO ids, names;
	CLOsE c1;
	-- Here is where you process the elements in the collections
	FOR i IN ids.FIRST .. ids.LAST
	LOOP
		IF ids(i) > 140 THEN
			DBMS_OUTPUT.PUT_LINE( ids(i) );
		END IF;
	END LOOP;
	FOR i IN names.FIRST .. names.LAST
	LOOP
		IF names(i) LIKE '%Ma%' THEN
			DBMS_OUTPUT.PUT_LINE( names(i) );
		END IF;
	END LOOP;
END;
/

' nota: Algunos atributos que tienen los cursores explicitos son: %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT.
%FOUND devolvera TRUE la primera vez que hagamos un FETCH del cursor. 
%ROWCOUNT se ira incrementando con cada FETCH ejecutado.
'
 
'nota: Podemos meter un cursor explicito dentro de un bucle FOR y no sera necesario ni abrirlo ni cerrarlo'

DECLARE
	CURSOR c1 IS 
		SELECT last_name, job_id FROM employees
		WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
BEGIN
	FOR item IN c1
	LOOP
		DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||
		item.job_id);
	END LOOP;
END;
/

'nota: Podemos definir cursores que admitan parametros'

DECLARE
	CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
		SELECT * FROM employees WHERE job_id = job AND salary > max_wage;
BEGIN
	FOR person IN c1('CLERK', 3000)
	LOOP	
		-- process data record
		DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
		person.salary || ', Job Id = ' || person.job_id );
	END LOOP;
END;
/

-- EJEMPLO:
/*Nota: Este script elimina los indices de una tabla*/

set serverout on lines 132 trimsp on
	declare
	 g_tab_name USER_TABLES.TABLE_NAME%type := '<MiTabla>';
	 cursor CI is
	  select INDEX_NAME
	  from USER_INDEXES
	  where table_name = g_tab_name;
	begin
	 for I in CI loop
	  begin
	   execute immediate 'drop index ' || I.INDEX_NAME;
	  exception
	   when others then
		dbms_output.put_line( 'Error eliminando indice: ' || I.INDEX_NAME );
		dbms_output.put_line( sqlerrm );
	  end;
	 end loop;
	end;
	/
	
-----------------------------------------
-- RECORD
-----------------------------------------

'Podemos considerar a un record como un objeto que puede almacenar otros objetos de diferentes tipos.'

/* SINTAXIS
	TYPE type_name IS RECORD
	(field_name1 datatype1 [[NOT NULL]:=|DEFAULT default_value],
	 field_name2 datatype2 [[NOT NULL]:=|DEFAULT default_value],
	 ...
	 field_nameN datatypeN [[NOT NULL]:=|DEFAULT default_value]
	);
*/

-- Ejemplo definicion y asignacion

DECLARE
	TYPE DeptRecTyp IS RECORD (
		deptid NUMBER(4) NOT NULL := 99,
		dname departments.department_name%TYPE,
		loc departments.location_id%TYPE,
		region regions%ROWTYPE );
		dept_rec DeptRecTyp;
BEGIN
	dept_rec.dname := 'PURCHASING';
END;
/

-- Ejemplo record anidados

DECLARE
	TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
	TYPE meetin_typ IS RECORD (
		date_held DATE,
		duration timerec, -- nested record
		location VARCHAR2(20),
		purpose VARCHAR2(50));
BEGIN
	NULL;
END;
/


-- Ejemplo devolver un objeto de tipo record
 
DECLARE

	TYPE EmpRecTyp IS RECORD (
		emp_id NUMBER(6),
		salary NUMBER(8,2));

	CURSOR desc_salary RETURN EmpRecTyp IS
		SELECT employee_id, salary FROM employees ORDER BY salary DESC;

		emp_rec EmpRecTyp;

	FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
	BEGIN
		OPEN desc_salary;
		FOR i IN 1..n LOOP
			FETCH desc_salary INTO emp_rec;
		END LOOP;
		CLOSE desc_salary;
	RETURN emp_rec;
	END nth_highest_salary;
BEGIN
	NULL;
END;
/

-- Ejemplo Record pasado como parametro.

DECLARE
	TYPE EmpRecTyp IS RECORD (
		emp_id NUMBER(6),
		emp_sal NUMBER(8,2) );
	PROCEDURE raise_salary (emp_info EmpRecTyp) IS
	BEGIN
		UPDATE employees SET salary = salary + salary * .10
			WHERE employee_id = emp_info.emp_id;
	END raise_salary;
BEGIN
	NULL;
END;
/

-- Ejemplo asignacion

DECLARE
	-- Two identical type declarations.
	TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
	TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
	dept1_info DeptRec1;
	dept2_info DeptRec2;
	dept3_info DeptRec2;
BEGIN
	-- ERROR No se pueden igualar dos records con diferentes tipos
	-- dept1_info := dept2_info;
	-- Si se pueden igualar
	dept2_info := dept3_info;
END;
/

-- Ejemplo asignacion

DECLARE
    TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, id employees.employee_id%TYPE);
   	rec1 RecordTyp;
BEGIN
    SELECT last_name, employee_id INTO rec1 FROM employees WHERE ROWNUM < 2;
    DBMS_OUTPUT.PUT_LINE('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

-- Ejemplo declaracion record de forma implicita

DECLARE
    dept_info departments%ROWTYPE;
BEGIN
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
INSERT INTO departments VALUES dept_info;
UPDATE departments SET ROW = dept_info WHERE department_id = 300;
END;
/

-- Ejemplo

DECLARE
    TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
    underpaid EmployeeSet;
    CURSOR c1 IS SELECT first_name, last_name FROM employees;
    TYPE NameSet IS TABLE OF c1%ROWTYPE;
    some_names NameSet;
BEGIN

SELECT * BULK COLLECT INTO underpaid 
FROM employees
WHERE salary < 5000 ORDER BY salary DESC;

DBMS_OUTPUT.PUT_LINE(underpaid.COUNT || ' people make less than 5000.');

FOR i IN underpaid.FIRST .. underpaid.LAST
LOOP
    DBMS_OUTPUT.PUT_LINE(underpaid(i).last_name || ' makes ' ||underpaid(i).salary);
END LOOP;

SELECT first_name, last_name BULK COLLECT INTO some_names 
FROM employees
WHERE ROWNUM < 11;

FOR i IN some_names.FIRST .. some_names.LAST
LOOP
    DBMS_OUTPUT.PUT_LINE('Employee = ' || some_names(i).first_name || ' ' ||some_names(i).last_name);
END LOOP;

END;
/

--------------------------------------------
-- COLLECTION
--------------------------------------------
'
 Tipos de Collections:

a) Nested tables: Podemos pensar en un array de valores sin tener que declarar su longitud previamente. 
	Podemos generar nested tables multidimensionales haciendo nested tables de nested tables.
b) Varrays: Permite acceder a sus elementos de forma individual o trabajar con el array como si fuese un todo. 
	Un varray tiene un tamaño maximo que se debe especificar en su declaracion.
c) Arrays asociativos: Los podemos entender como un array compuesto por duplas llave - valor.
'

-- Ejemplo

DECLARE
    TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
    country_population population_type;
    continent_population population_type;
    howmany NUMBER;
    which VARCHAR2(64);
BEGIN
    country_population('Greenland') := 100000; -- Nueva entrada
    country_population('Iceland') := 750000; -- Nueva entrada
-- Obtenemos el valor asociado a una entrada
    howmany := country_population('Greenland');
    continent_population('Australia') := 30000000;
    continent_population('Antarctica') := 1000; -- Nueva entrada
    continent_population('Antarctica') := 1001; -- Modificamos valor
-- .FIRST devuelve en orden alfabetico
    which := continent_population.FIRST;
    which := continent_population.LAST;
    howmany := continent_population(continent_population.LAST);
END;
/
'
Para crear una coleccion primero debemos de definir un tipo y despues declarar variables de ese tipo.

Para un Varray seria:
TYPE Calendario IS VARRAY(366) OF DATE;

Para un array asociativo:
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

Para una nested table:
TYPE nested_type IS TABLE OF VARCHAR2(30);
'

Un ejemplo seria:
DECLARE
    TYPE nested_type IS TABLE OF VARCHAR2(30);
    TYPE varray_type IS VARRAY(5) OF INTEGER;
    TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
    TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
    v1 nested_type;
    v2 varray_type;
    v3 assoc_array_num_type;
    v4 assoc_array_str_type;
    v5 assoc_array_str_type2;
BEGIN
    v1 := nested_type('Shipping','Sales','Finance','Payroll');
    v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
    v3(99) := 10; -- Just start assigning to elements
    v3(7) := 100; -- Subscripts can be any integer values
    v4(42) := 'Smith'; -- Just start assigning to elements
    v4(54) := 'Jones'; -- Subscripts can be any integer values
    v5('Canada') := 'North America'; -- Just start assigning to elements
    v5('Greece') := 'Europe'; -- Subscripts can be string values
END;
/

'Podemos hacer un Varray de records'

DECLARE
TYPE name_rec IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );
TYPE names IS VARRAY(250) OF name_rec;
BEGIN
NULL;
END;
/

-- Inicializar collections

'Para un varray:'

DECLARE
TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
dept_names dnames_var;
BEGIN
dept_names := dnames_var('Shipping','Sales','Finance','Payroll');
END;

'Para una nested table:'

DECLARE
	TYPE dnames_tab IS TABLE OF VARCHAR2(30);
	dept_names dnames_tab;
BEGIN
	dept_names := dnames_tab('Shipping', NULL,'Finance', NULL);
END;

-- Referenciar Objetos

'Para una nested table Para acceder a un determinado elemento seria nombre_variable(id_elemento)'
DECLARE
	TYPE Roster IS TABLE OF VARCHAR2(15);
	names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
	PROCEDURE verify_name(the_name VARCHAR2) IS
	BEGIN
		DBMS_OUTPUT.PUT_LINE(the_name);
	END;
BEGIN
	FOR i IN names.FIRST .. names.LAST
	LOOP
		IF names(i) = 'J Hamil' THEN
			DBMS_OUTPUT.PUT_LINE(names(i)); -- reference to nested table element
		END IF;
	END LOOP;
	verify_name(names(3)); -- procedure call with reference to element
END;
/

'Para array asociativos:'

DECLARE
	TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
	n PLS_INTEGER := 5; -- number of multiples to sum for display
	sn PLS_INTEGER := 10; -- number of multiples to sum
	m PLS_INTEGER := 3; -- multiple

FUNCTION get_sum_multiples(multiple IN PLS_INTEGER, num IN PLS_INTEGER)
	RETURN sum_multiples IS s sum_multiples;
	BEGIN
	FOR i IN 1..num LOOP
		s(i) := multiple * ((i * (i + 1)) / 2) ; -- sum of multiples
	END LOOP;
	RETURN s;
END get_sum_multiples;
BEGIN
	-- call function to retrieve the element identified by subscript (key)
	DBMS_OUTPUT.PUT_LINE('Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
	TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n)));
END;
/

-- Operar con colecciones en bloque

DECLARE
	TYPE nested_typ IS TABLE OF NUMBER;
	nt1 nested_typ := nested_typ(1,2,3);
	nt2 nested_typ := nested_typ(3,2,1);
	nt3 nested_typ := nested_typ(2,3,1,3);
	nt4 nested_typ := nested_typ(1,2,4);
answer nested_typ;

PROCEDURE print_nested_table(the_nt nested_typ) IS output VARCHAR2(8);
BEGIN
	IF the_nt IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
		RETURN;
	END IF;
	IF the_nt.COUNT = 0 THEN
		DBMS_OUTPUT.PUT_LINE('Results: empty set');
		RETURN;
	END IF;
	FOR i IN the_nt.FIRST .. the_nt.LAST
	LOOP
		output := output || the_nt(i) || ' ';
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('Results: ' || output);
END;
BEGIN
	answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
	print_nested_table(answer);
	answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
	print_nested_table(answer);
	answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
	print_nested_table(answer);
	answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
	print_nested_table(answer);
	answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1)
	print_nested_table(answer);
	answer := SET(nt3); -- (2,3,1)
	print_nested_table(answer);
	answer := nt3 MULTISET EXCEPT nt2; -- (3)
	print_nested_table(answer);
	answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- ()
	print_nested_table(answer);
END;
	
'Podemos hacer operaciones con collections como si fuesen un unico objeto.'
DECLARE
    TYPE nested_typ IS TABLE OF NUMBER;
    nt1 nested_typ := nested_typ(1,2,3);
    nt2 nested_typ := nested_typ(3,2,1);
    nt3 nested_typ := nested_typ(2,3,1,3);
    nt4 nested_typ := nested_typ(1,2,4);
    answer BOOLEAN;
    howmany NUMBER;
    PROCEDURE testify(truth BOOLEAN DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
    BEGIN
   	 IF truth IS NOT NULL THEN
   		 DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
   	 END IF;
   	 IF quantity IS NOT NULL THEN
   		 DBMS_OUTPUT.PUT_LINE(quantity);
   	 END IF;
    END;
BEGIN
    answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2
    testify(truth => answer);
    answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
    testify(truth => answer);
    answer := nt1 NOT SUBMULTISET OF nt4; -- also true
    testify(truth => answer);
    howmany := CARDINALITY(nt3); -- number of elements in nt3
    testify(quantity => howmany);
    howmany := CARDINALITY(SET(nt3)); -- number of distinct elements
    testify(quantity => howmany);
    answer := 4 MEMBER OF nt1; -- false, no element matches
    testify(truth => answer);
    answer := nt3 IS A SET; -- false, nt3 has duplicates
    testify(truth => answer);
    answer := nt3 IS NOT A SET; -- true, nt3 has duplicates
    testify(truth => answer);
    answer := nt1 IS EMPTY; -- false, nt1 has some members
    testify(truth => answer);
END;
/
-- Metodos para trabajar con colecciones
'
EXISTS(N) devuelve TRUE si el elemenot N existe. 
DELETE(N) elimina, el elemento N de la coleccion.
'
Ejemplo:
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete the second element
IF n.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
END IF;
IF n.EXISTS(2) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #2 has been deleted.');
END IF;
IF n.EXISTS(99) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
END IF;
END;
/
'COUNT cuenta el numero de elementos que tiene la coleccion.'
'EXTEND(n), TRIM(n) incrementa o decrementa el tamaño de la coleccion, inserta elementos nulos.'

DECLARE
	TYPE NumList IS TABLE OF NUMBER;
	n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
	DBMS_OUTPUT.PUT_LINE('There are ' || n.COUNT || ' elements in N.');
	n.EXTEND(3); -- Add 3 new elements at the end.
	DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
	n := NumList(86,99); -- Assign a completely new value with 2 elements.
	DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
	n.TRIM(2); -- Remove the last 2 elements, leaving none.
	DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
END;
/

'FIRST, LAST devuelve el primer y ultimo elemento de la conexion'
DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    n NumList := NumList(1,3,5,7);
    counter INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);
    DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);
    -- When the subscripts are consecutive starting at 1,
    -- it's simple to loop through them.
    FOR i IN n.FIRST .. n.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
    END LOOP;
    n.DELETE(2); -- Delete second element.
    -- When the subscripts have gaps or the collection might be uninitialized,
    -- the loop logic is more extensive. We start at the first element, and
    -- keep looking for the next element until there are no more.
    IF n IS NOT NULL THEN
    counter := n.FIRST;
    WHILE counter IS NOT NULL
    LOOP
    DBMS_OUTPUT.PUT_LINE('Element #' || counter || ' = ' || n(counter));
    counter := n.NEXT(counter);
    END LOOP;
    ELSE
    DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.');
    END IF;
END;
/

'PRIOR(n), NEXT(n) Para recorrer una coleccion utiliamos estos procedimientos.'

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
	DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));
	DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));
	n.DELETE(3); -- Delete an element to show how NEXT can handle gaps.
	DBMS_OUTPUT.PUT_LINE('Now the element after #2 is #' || n.NEXT(2));
	IF n.PRIOR(n.FIRST) IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('Can''t get PRIOR of the first element or NEXT of the
	last.');
	END IF;
END;
/
DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    n NumList := NumList(1,3,5,7);
    counter INTEGER;
BEGIN
    n.DELETE(2); -- Delete second element.
    -- When the subscripts have gaps, the loop logic is more extensive. We start at
    -- the first element, and keep looking for the next element until there are no
    more.
    counter := n.FIRST;
    WHILE counter IS NOT NULL
    LOOP
   	 DBMS_OUTPUT.PUT_LINE('Counting up: Element #' || counter || ' = ' ||n(counter));
   	 counter := n.NEXT(counter);
    END LOOP;
    -- Run the same loop in reverse order.
    counter := n.LAST;
    WHILE counter IS NOT NULL
    LOOP
    DBMS_OUTPUT.PUT_LINE('Counting down: Element #' || counter || ' = ' ||n(counter));
    counter := n.PRIOR(counter);
    END LOOP;
END;
/

-- Manejo de excepcion con colecciones
'
Las excepciones que obtenemos al trabajar con colecciones:
- COLLECTION_IS_NULL: la coleccion no tiene elementos.
- NO_DATA_FOUND: el elemento al que se intenta acceder no existe.
- SUBSCRIPT_BEYOND_COUNT: se excede el numero maximo de elementos de una coleccion.
'
DECLARE
    TYPE WordList IS TABLE OF VARCHAR2(5);
    words WordList;
    err_msg VARCHAR2(100);
    PROCEDURE display_error IS
    BEGIN
   	 err_msg := SUBSTR(SQLERRM, 1, 100);
   	 DBMS_OUTPUT.PUT_LINE('Error message = ' || err_msg);
    END;
BEGIN
    BEGIN
   	 words(1) := 10; -- Raises COLLECTION_IS_NULL
   	 -- A constructor has not been used yet.
   	 -- Note: This exception applies to varrays and nested tables,
   	 -- but not to associative arrays which do not need a constructor.
    EXCEPTION
   	 WHEN OTHERS THEN display_error;
    END;
    -- After using a constructor, we can assign values to the elements.
    words := WordList('1st', '2nd', '3rd'); -- 3 elements created
    -- Any expression that returns a VARCHAR2(5) is valid.
    words(3) := words(1) || '+2';
    BEGIN
   	 words(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR
   	 -- The assigned value is too long.
    EXCEPTION
   	 WHEN OTHERS THEN display_error;
    END;
    BEGIN
   	 words('B') := 'dunno'; -- Raises VALUE_ERROR
   	 -- The subscript (B) of a nested table must be an integer.
   	 -- Note: Also, NULL is not allowed as a subscript.
    EXCEPTION
   	 WHEN OTHERS THEN display_error;
    END;
    BEGIN
   	 words(0) := 'zero'; -- Raises SUBSCRIPT_OUTSIDE_LIMIT
   	 -- Subscript 0 is outside the allowed subscript range.
    EXCEPTION
    WHEN OTHERS THEN display_error;
    END;
    BEGIN
   	 words(4) := 'maybe'; -- Raises SUBSCRIPT_BEYOND_COUNT
   	 -- The subscript (4) exceeds the number of elements in the table.
   	 -- To add new elements, call the EXTEND method first.
    EXCEPTION
   	 WHEN OTHERS THEN display_error;
    END;
    BEGIN
   	 words.DELETE(1);
   	 IF words(1) = 'First' THEN NULL; END IF; -- Raises NO_DATA_FOUND
   	 -- The element with subcript (1) has been deleted.
    EXCEPTION
   	 WHEN OTHERS THEN display_error;
    END;
END;
/

------------------------------------------
-- MANEJO DE EXCEPCIONES
------------------------------------------

DECLARE
	stock_price NUMBER := 9.73;
	net_earnings NUMBER := 0;
	pe_ratio NUMBER;
BEGIN
	-- Generamos una excepcion
	pe_ratio := stock_price / net_earnings; 
	DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION -- exception handlers begin
	WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
		DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.');
		pe_ratio := NULL;
WHEN OTHERS THEN -- handles all other errors
	DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
	pe_ratio := NULL;
END; 
/

-- La estructura dentro del EXCEPTION

EXCEPTION
	WHEN exception1 THEN -- handler for exception1
		sequence_of_statements1
	WHEN exception2 THEN -- another handler for exception2
		sequence_of_statements2
	WHEN OTHERS THEN -- optional handler for all other errors
		sequence_of_statements3
END;

'La excepcion de tipo OTHERS es la generica donde caerian las excepciones no controladas de bloques anteriores.'
'PLSQL permite la posibilidad de definir nuestros propias excepciones.'

DECLARE
	past_due EXCEPTION;
	acct_num NUMBER;
BEGIN
	DECLARE
		past_due EXCEPTION;
		acct_num NUMBER;
		due_date DATE := SYSDATE - 1;
		todays_date DATE := SYSDATE;
	BEGIN
		IF due_date < todays_date THEN
			RAISE past_due; -- this is not handled
		END IF;
	END;
EXCEPTION
	WHEN past_due THEN
		DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/
'Para provocar una excepcion creada por el usuario tenemos que utilizar el comando RISE <MiExcepcion>'
'Tambien podemos hacer nuestros propios codigos de error, con sus mensajes asociados:'

DECLARE
	num_tables NUMBER;
BEGIN
	SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
	IF num_tables < 1000 THEN
		raise_application_error(-20101, 'Expecting at least 1000 tables');
	ELSE
		NULL;
	END IF;
END;
/

'Hay que tener en cuenta que si una excepcion no se controla en su bloque EXCEPTION, debera de ser controlada en bloques EXCEPTION superiores. Si definiesemos una excepcion y no la controlasemos Oracle devolveria este error:
ORA-06510: PL/SQL: unhandled user-defined exception'

'Podemos propagar excepciones a niveles superiores haciendo un RISE al nivel superior:'

DECLARE
	salary_too_high EXCEPTION;
	current_salary NUMBER := 20000;
	max_salary NUMBER := 10000;
	erroneous_salary NUMBER;
BEGIN
	BEGIN
		IF current_salary > max_salary THEN
			RAISE salary_too_high; -- raise the exception
		END IF;
	EXCEPTION
		WHEN salary_too_high THEN
			DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary || ' is out of range.');
			DBMS_OUTPUT.PUT_LINE('Maximum salary is ' || max_salary || '.');
			RAISE; -- relanza la misma excepcion
	END;
EXCEPTION
	WHEN salary_too_high THEN
		erroneous_salary := current_salary;
		current_salary := max_salary;
		DBMS_OUTPUT.PUT_LINE('Revising salary from ' || erroneous_salary ||
							 ' to ' || current_salary || '.');
END;
/

'Dentro del bloque exception podemos obtener informacion de la excepcion producida utilizando las variables SQLCODE y SQLERRM.'
DECLARE
	name employees.last_name%TYPE;
	v_code NUMBER;
	v_errm VARCHAR2(64);
BEGIN
	SELECT last_name INTO name FROM employees WHERE employee_id = -1;
EXCEPTION
	WHEN OTHERS THEN
		v_code := SQLCODE;
		v_errm := SUBSTR(SQLERRM, 1 , 64);
		DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
		INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP);
END;
/

'Ejemplo practico para comprobar en que parte del codigo se ha producido una excepcion:'
CREATE OR REPLACE PROCEDURE loc_var AS
	stmt_no NUMBER;
	name VARCHAR2(100);
BEGIN
	stmt_no := 1;
	SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%';
	stmt_no := 2;
	SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%';
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Table name not found in query ' || stmt_no);
END;
/
CALL loc_var();

---------------------------------------
-- EXECUTE IMMEDIATE
---------------------------------------

'En este post vamos a introducir la definicion de SQL dinamico. Esto consiste en construir sentencias de SQL en tiempo de ejecucion. Para ello utilizaremos la sencentia EXECUTE IMMEDIATE. Junto con este comando podemos utilizar las sentencias INTO, USING y RETURNING INTO.'

-- Ejemplo 1:

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,
	emp_column VARCHAR2, amount NUMBER) IS
	v_column VARCHAR2(30);
	sql_stmt VARCHAR2(200);
BEGIN
	-- determine if a valid column name has been given as input
	SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS	
		WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
	sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '|| v_column || ' = :2';
	EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
	IF SQL%ROWCOUNT > 0 THEN
		DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column|| ' = ' || column_value);
	END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

-- Ejemplo 2:

'En la sentencia EXECUTE IMMEDIATE vemos como puede utilizar variables con el formato :mi_variable, para asignarle valor a esas variables utiliza USING.'

CREATE TABLE employees_temp AS SELECT * FROM employees;


CREATE OR REPLACE PROCEDURE delete_rows (
	table_name IN VARCHAR2,
	condition IN VARCHAR2 DEFAULT NULL) AS
	where_clause VARCHAR2(100) := ' WHERE ' || condition;
	v_table VARCHAR2(30);
BEGIN
	-- first make sure that the table actually exists; if not, raise an exception
	SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
	WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
	IF condition IS NULL THEN 
		where_clause := NULL; 
	END IF;
	EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
	delete_rows('employees_temp', 'employee_id = 111');
END;

-- Ejemplo 3:

'En este ejemplo vemos como con EXECUTE IMMEDIATE podemos ademas de definir las variables que configuran el predicado configurar nombres de tabla y columnas.'

CREATE PROCEDURE create_dept (
	deptid IN OUT NUMBER,
	dname IN VARCHAR2,
	mgrid IN NUMBER,
	locid IN NUMBER) AS
BEGIN
	SELECT departments_seq.NEXTVAL INTO deptid FROM dual;
	INSERT INTO departments VALUES (deptid, dname, mgrid, locid);
END;
/

DECLARE
	plsql_block VARCHAR2(500);
	new_deptid NUMBER(4);
	new_dname VARCHAR2(30) := 'Advertising';
	new_mgrid NUMBER(6) := 200;
	new_locid NUMBER(4) := 1700;
BEGIN
	plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
	EXECUTE IMMEDIATE plsql_block
	USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

-- Ejemplo 4:
'Vemos como parametrizamos USING con IN OUT y asi poder utilizar esas variables tanto de entrada como de salida.'

DECLARE
	TYPE EmpCurTyp IS REF CURSOR;
	emp_cv EmpCurTyp;
	emp_rec employees%ROWTYPE;
	sql_stmt VARCHAR2(200);
	v_job VARCHAR2(10) := 'ST_CLERK';
BEGIN
	sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
	OPEN emp_cv FOR sql_stmt USING v_job;
	LOOP
		FETCH emp_cv INTO emp_rec;
		EXIT WHEN emp_cv%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' ||emp_rec.job_id);
	END LOOP;
	CLOSE emp_cv;
END;
/

---------------------------------------
-- SELECT FOR UPDATE
---------------------------------------

-- Ejemplo

DECLARE
	CURSOR c1 IS 
		SELECT employee_id, salary FROM employees
		WHERE job_id = 'SA_REP' AND commission_pct > .10
	FOR UPDATE NOWAIT;
	
'Podemos utilizar NOWAIT para que en el caso que una de las filas afectadas por nuestra query se encuentra bloqueada por otro usuario.'
'Si utilizamos SELECT FOR UPDATE sobre una query con multiples tablas se produce un bloqueo sobre esas tablas, pero si unicamente tenemos pensado modificar el los registros de una unica tabla podemos hacer SELECT FOR UPDATE OF <Tabla>.'
DECLARE
	CURSOR c1 IS 
		SELECT last_name, department_name FROM employees, departments
		WHERE employees.department_id = departments.department_id
		AND job_id = 'SA_MAN'
		FOR UPDATE OF salary;
	
-----------------------------------------
-- ESTRUCTURAS DE CONTROL
-----------------------------------------
'
Introduccion
Tenemos que tener una serie de consideraciones previas respecto a las estructuras de control:
-Los valores nulos (NULL) no se pueden evaluar siempre devuelven FALSE. Si que remos utilizarlos habra que utilizar las expresiones IS NULL o IS NOT NULL.
- PLSQL es un lenguaje que no distingue entre mayusculas y minusculas.
-Una variable no puede tener el mismo nombre que una columna de una tabla, es recomendable el añadir prefijos o nombrar el bloque y utilizar ese espacion de nombres.
'
-- Ejemplo

<<Mibloque>>
DECLARE
	id_tabla 	NUMBER :=10;
BEGIN
	UPDATE tablas SET id_tabla=Mibloque.id_tabla;
END;
/

-- IF 

A) IF CONDICION THEN 
		<sentencias>
	END IF;
	
B) IF CONDICION THEN
		<sentencias>
	ELSE 
		<sentencias>
	END IF;
	
C)  IF CONDICION THEN
		<sentencias>
	ELSIF CONDICION2 THEN 
		<sentencias>
	ELSE
		<sentencias>
	END IF;

-- CASE

-- Tipo 1:
	
	CASE v_nota
	WHEN 9 THEN V_calificacion : = 'Sobresaliente'
	WHEN 7 THEN V_calificacion : = 'Notable'
	WHEN 5 THEN V_calificacion : = 'Aprobado'
	ELSE V_calificacion : = 'Suspenso'
	END CASE;

-- Tipo 2

	CASE WHEN SALARY>100 THEN SALARY+10
	 CASE WHEN SALARY<100 THEN SALARY+20
	 END CASE;

----------------------	 
-- BUCLES
----------------------

-- Bucle clasico

'Se va ejecutando de forma indefinida hasta que forzamos la salida con EXIT.'

DECLARE
	CONTADOR NUMBER(2):= 1;
BEGIN
	LOOP
		DBMS_OUTPUT.PUT_LINE(CONTADOR);
		EXIT WHEN CONTADOR = 50;
		CONTADOR := CONTADOR +1;
	END LOOP;
END;
/

'NOTA: EXIT WHEN permite establecer una sentencia de control en una unica linea.'

-- Bucle WHILE
'Con un bucle WHILE indicamos la condicion de salida.'

DECLARE
	V_CONT NUMBER;
BEGIN
	V_CONT :=0;
	WHILE V_CONT<50 LOOP
		DBMS_OUTPUT.PUT_LINE(V_CONT);
		V_CONT:=V_CONT + 1;
	END LOOP;
END;
/

-- Bucle FOR
'REVERSE hacemos que vaya desde el limite superior al limite inferior.'

BEGIN
	FOR I IN 1..50 LOOP
		DBMS_OUTPUT.PUT_LINE(I);
	END LOOP;
END;
/

BEGIN
	FOR I IN REVERSE 1..50 LOOP
		DBMS_OUTPUT.PUT_LINE(I);
	END LOOP;
END;
/

BEGIN 
	FOR l_current_year IN (SELECT r_year FROM YEARS) LOOP
		NULL;
	END LOOP;
END;

-- Etiquetar bloques.
'Cuando trabajamos con bucles anidados los podemos etiquetar, y generar secuencias de escape que provoquen la salida de uno o de varios niveles.'
DECLARE
		s PLS_INTEGER := 0;
		i PLS_INTEGER := 0;
		j PLS_INTEGER;
	BEGIN
	<<outer_loop>>
	LOOP
		i := i + 1;
		j := 0;
		<<inner_loop>>
		LOOP
			j := j + 1;
			s := s + i * j; -- sum a bunch of products
			EXIT inner_loop WHEN (j > 5);
			EXIT outer_loop WHEN ((i * j) > 15);
		END LOOP inner_loop;
	END LOOP outer_loop;
	DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
	END;


-- GOTO
'Dentro de un bloque nos permite saltar la linea de ejecucion a una determinada etiqueta.'

DECLARE
		total NUMBER(9) := 0;
		counter NUMBER(6) := 0;
	BEGIN
	<<calc_total>>
		counter := counter + 1;
		total := total + counter * counter;
		-- branch to print_total label when condition is true
		IF total > 25000 THEN GOTO print_total;
		ELSE GOTO calc_total;
		END IF;
		<<print_total>>
		DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' ||
		TO_CHAR(total));
END;
/

-- Ejemplos:

-- Ejemplo 1: combinaciones de estructuras de control.
DECLARE
		jobid employees.job_id%TYPE;
		empid employees.employee_id%TYPE := 115;
		sal employees.salary%TYPE;
		sal_raise NUMBER(3,2);
	BEGIN
	SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;
	CASE
	WHEN jobid = 'PU_CLERK' THEN
		IF sal < 3000 THEN sal_raise := .12;
		ELSE sal_raise := .09;
		END IF;
	WHEN jobid = 'SH_CLERK' THEN
		IF sal < 4000 THEN sal_raise := .11;
		ELSE sal_raise := .08;
		END IF;
	WHEN jobid = 'ST_CLERK' THEN
		IF sal < 3500 THEN sal_raise := .10;
		ELSE sal_raise := .07;
		END IF;
	ELSE
		DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
	END CASE;
END;
/

-- Ejemplo 2: Case que lanza un excepcion

DECLARE
	grade CHAR(1);
	BEGIN
	grade := 'B';
	CASE
		WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
		WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
		WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');    
		ELSE RAISE CASE_NOT_FOUND;
	END CASE;
	EXCEPTION
	 WHEN CASE_NOT_FOUND THEN
	 DBMS_OUTPUT.PUT_LINE('No such grade');
	 END;
	/
	
-----------------------------------------
-- BLOQUES
-----------------------------------------

-- BLOQUES

'PLSQL trabaja mediante la definicion de bloque, estos pueden ser bloques anonimos o pueden tener nombre como es el caso de procedimientos, funciones o paquetes.
La estructura de un bloque es:'

	DECLARE
		<Declaracion de variables>
	BEGIN
		<Cuerpo del bloque>
	EXCEPTION
		<Getion de excepciones>
	END;

-- Ejemplo de bloque anonimo:

DECLARE
	mi_variable NUMBER (5);
	l_valor	NUMBER;
BEGIN
	l_valor:=1;
	DECLARE
	l_valor:=2;
	BEGIN
		SELECT salario INTO mi_variable
		FROM empleados
		WHERE numero_empleado = l_valor;
		EXCEPTION
		WHEN nombre_excepción THEN ...
	END;
END;

-----------------------------------------
-- TIPOS DE DATOS
-----------------------------------------
'
Tipos de variables en Oracle:

CHAR [(longitud máxima)]
VARCHAR2 (longitud máxima)
NUMBER [(precisión, escala)]
BINARY_INTEGER
PLS_INTEGER					-2,147,483,648 a 2,147,483,647 , size 32 bits
BOOLEAN
DATE
'
-- NUMBER

	NUMBER(9,2) -- 9999999.9
	
DECLARE
	v_contador	NUMBER :=5;

'Tambien si queremos declarar una variable que recoja el valor de una columna y no sabemos el tipo de esa columna podemos utilizar la clausula %TYPE.'

DECLARE
	v_id_empleado	EMPLEADOS.ID_EMPLEADO%type;

'O en el caso de cursores podemos utilizar %ROWTYPE.'

'Utilizando el caracter & podemos hacer que Oracle pare la ejecucion del script y nos pida un valor que se le asignara a la variable.'

v_tabla:=&tabla;



-- DATE | INTERVAL

DECLARE
leave_on_trip TIMESTAMP := TIMESTAMP '2005-03-22 06:11:00.00';
return_from_trip TIMESTAMP := TIMESTAMP '2005-03-25 15:50:00.00';
trip_length INTERVAL DAY TO SECOND;
BEGIN
trip_length := return_from_trip - leave_on_trip;
DBMS_OUTPUT.PUT_LINE('Length in days hours:minutes:seconds is ' || trip_length);
END;

/*SALIDA:
	+03 09:39:00.000000 -- 3 dias, 9 horas, 39 minutos
*/

DECLARE
	dts1 INTERVAL DAY TO SECOND := '2 3:4:5.6';
	dts2 INTERVAL DAY TO SECOND := '1 1:1:1.1';
	ytm1 INTERVAL YEAR TO MONTH := '2-10';
	ytm2 INTERVAL YEAR TO MONTH := '1-1';	
BEGIN
	DBMS_OUTPUT.PUT_LINE(dts1 - dts2);
	DBMS_OUTPUT.PUT_LINE(ytm1 - ytm2);	
END;
/
/*SALIDA
	+000000001 02:03:04.500000000
	+000000001-09
*/

-------------------------------------------
-- VARIABLES
-------------------------------------------

VARIABLE v_name VARCHAR2(100);
BEGIN
	:v_name:='Luis';
END;
/

print :v_name;

--------------------------------------------
-- FUNCIONES UTILES
--------------------------------------------

1.- INSTR -- Busca la posicion de un string dentro de una cadena

	SELECT INSTR('Mi cadena, cadena',',') FROM DUAL;

2.- REPLACE -- Sustituye todas las apariciones de una cadena.

	SELECT 'SALIDA',REPLACE('VALOR1|VALOR2|VALOR3','|',chr(10)) FROM DUAL; -- Cambia todos los pipes por newlines.

3.- SUBSTR(cadena,start_position, [length]) -- Obtiene una subcadena. Si start_position es negativo empezaria desde el final

	SELECT SUBSTR('Mi nombre es luismi',4,6) FROM DUAL;
	SELECT SUBSTR('Mi nombre es luismi',-4,6) FROM DUAL;

4.- LOWER | UPPER -- Minusculas mayusculas de una cadena
	
5.- RPAD | LPAD -- Rellena con caracteres hasta una longitud dada
	
	SELECT LPAD('Salida',10,'.') SALIDA from dual; 
	/*		
		SALIDA
		----------
		....Salida
	*/


Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: