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
*/
Oracle Live 
Deja un comentario