PL/SQL Francisco Moreno Universidad Nacional

1. PL/SQL Francisco Moreno Universidad Nacional

2. Paquetes: sobrecarga de subprogramas • Sobrecarga de subprogramas: Varios subprogramas pueden tener el mismo nombre en un paquete siempre y cuando no haya ambigüedad con los tipos de los parámetros y de retorno

3. No puede haber sobrecarga si la diferencia entre los subprogramas con el mismo nombre solo se basa en: • el modo (IN, OUT) • el tipo de retorno de una función • Tipos de datos de la misma familia como CHAR y VARCHAR* * Se permite para ciertos tipos de datos, por ejemplo NUMBER vs POSITIVE, aunque el comportamiento es bastante peculiar.

4. PROCEDURExxx(a VARCHAR) PROCEDUREyyy(a IN NUMBER) FUNCTIONzzz(a IN NUMBER) RETURN NUMBER PROCEDURE xxx(b CHAR) PROCEDURE yyy(b OUT NUMBER) FUNCTIONzzz(b IN NUMBER) RETURN VARCHAR No puede haber sobrecarga entre:

5. Ejemplo CREATE OR REPLACE PACKAGE mat IS FUNCTION area(radio NUMBER) RETURN NUMBER; FUNCTION area(largo NUMBER, ancho NUMBER) RETURN NUMBER; END; /

6. CREATE OR REPLACE PACKAGE BODY mat IS FUNCTION area(radio NUMBER) RETURN NUMBER IS BEGIN RETURN 3.1416*radio*radio; END; FUNCTION area(largo NUMBER, ancho NUMBER) RETURN NUMBER IS BEGIN RETURN largo*ancho; END; END; /

7. Para ejecutar: BEGIN DBMS_OUTPUT.PUT_LINE(mat.area(10)); DBMS_OUTPUT.PUT_LINE(mat.area(20,30)); END; / SELECT mat.area(10), mat.area(20,30) FROM dual;

8. Variables en los Paquetes • Las variables de los paquetes son persistentes durante la sesión • Las variables declaradas en la especificación sonpúblicas: se pueden acceder y modificar directamente desde otros subprogramas o bloques anónimos • Las variables declaradas en el cuerpo son privadas: solo se pueden acceder dentro del cuerpo. Si se desea ver su valor desde afuera del cuerpo, se debe crear un subprograma (público) a través del cual se acceden (cf. encapsulamiento)

9. Ejemplo CREATE OR REPLACE PACKAGE vbles IS nro_veces NUMBER(3) := 0; -- Vble pública PROCEDURE incrementa_veces; PROCEDURE muestra_veces; PROCEDURE muestra_impresiones; END; / Este procedimiento no es estrictamente necesario aunque es recomendable ¿por qué? Subprogramas públicos

10. CREATE OR REPLACE PACKAGE BODY vbles IS nro_impresiones NUMBER(3) := 0; --Variable privada PROCEDURE incrementa_veces IS BEGIN nro_veces := nro_veces + 1; END; PROCEDURE muestra_veces IS BEGIN DBMS_OUTPUT.PUT_LINE(nro_veces); nro_impresiones := nro_impresiones + 1; END; PROCEDURE muestra_impresiones IS BEGIN DBMS_OUTPUT.PUT_LINE(nro_impresiones); END; END; /

11. Para ejecutar: • EXECUTE vbles.muestra_veces; • EXECUTEvbles.muestra_impresiones; • EXECUTEvbles.incrementa_veces; • EXECUTE vbles.muestra_veces; • EXECUTE vbles.muestra_veces; • EXECUTEvbles.muestra_impresiones;  0  1  1  1  3

12. Directamente se pueden acceder las variables públicas pero NO las privadas. Igual sucede cuando hay subprogramas privados. Por ejemplo: BEGIN vbles.nro_veces := vbles.nro_veces + 1; END; / BEGIN DBMS_OUTPUT.PUT_LINE(vbles.nro_veces); END; / BEGIN DBMS_OUTPUT.PUT_LINE(vbles.nro_impresiones); END; / Genera error porque nro_impresiones NO es pública

13. Cursores REF • Los cursores REF permiten pasar los resultados de una consulta a otro subprograma • El programa que recibe el cursor REF lo puede recorrer como un cursor normal • Hay cursores REF fuertes (se les especifica el tipo de retorno) y débiles.

14. Ejemplo de cursor REF fuerte --Sea la tabla: DROP TABLE emp; CREATE TABLE emp( cedula NUMBER(8) PRIMARY KEY, nom VARCHAR2(10) NOT NULL ); INSERT INTO emp VALUES(8,'Loui'); INSERT INTO emp VALUES(7,'Dina'); INSERT INTO emp VALUES(5,'Xena');

15. Se define el tipo del cursor REF y su tipo de retorno: CREATE OR REPLACE PACKAGE prueba_ref_cursor IS TYPE ref_emp_type IS REF CURSOR RETURN emp%ROWTYPE; END; / Tipo de retorno del REF CURSOR: es lo que lo hace “fuerte”

16. CREATE OR REPLACE PROCEDURE devuelve_cursor /*Se envía el cursor REF fuerte como parámetro de salida:*/ (c OUT prueba_ref_cursor.ref_emp_type) AS BEGIN OPEN c FOR SELECT * FROM emp; --Se llena END; / Ya el cursor REF c queda lleno (pero los datos no se recorren acá)

17. --Se invoca el procedimiento anterior y se imprimen --los resultados: DECLARE resultados prueba_ref_cursor.ref_emp_type; a emp%ROWTYPE; BEGIN devuelve_cursor(resultados); --Se invoca LOOP --Se recorre FETCH resultados INTO a; EXIT WHEN resultados%NOTFOUND; DBMS_OUTPUT.PUT_LINE(a.cedula || ' ' || a.nom); END LOOP; CLOSE resultados; END; /

18. Ejemplo de cursor REF débil: SYS_REFCURSOR CREATE OR REPLACE PROCEDURE devuelve_cursor /*Se envía el cursor REF débil (parámetro de salida) y el nombre de la tabla a consultar */ (c OUT SYS_REFCURSOR, nom_tabla IN VARCHAR) AS BEGIN OPEN c FOR 'SELECT * FROM ' || nom_tabla; --Se llena END; / Nótese que la consulta asociada con un REF CURSOR puede ser ¡una cadena de caracteres!, esto permite crear cursores cuya consulta no se conoce en tiempo de compilación.

19. --Sea la tabla: DROP TABLE cliente; CREATE TABLE cliente( ced NUMBER(8) PRIMARY KEY, nom VARCHAR2(10) NOT NULL, ciudad VARCHAR2(10) NOT NULL ); INSERT INTO cliente VALUES(1,'Pedra', 'Cali'); INSERT INTO cliente VALUES(2,'Kathy', 'Londres'); INSERT INTO cliente VALUES(3,'Donna', 'Paris');

20. DECLARE --Una variable de tipo SYS_REFCURSOR mi_ref_cursor SYS_REFCURSOR; a1 emp%ROWTYPE; a2 cliente%ROWTYPE; BEGIN devuelve_cursor(mi_ref_cursor,'emp'); --Se invoca LOOP FETCH mi_ref_cursor INTO a1; EXIT WHEN mi_ref_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(a1.cedula || ' ' || a1.nom); END LOOP; CLOSE mi_ref_cursor; El programa continua en la próxima diapositiva

21. devuelve_cursor(mi_ref_cursor, 'cliente'); --Se invoca LOOP FETCH mi_ref_cursor INTO a2; EXIT WHEN mi_ref_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(a2.ced || ' ' || a2.nom || ' ' || a2.ciudad); END LOOP; CLOSE mi_ref_cursor; END; /

22. Problema: ¿Se podrá adaptar el programa anterior de tal forma que quede funcionando para imprimir los resultados de cualquier consulta? Es decir, solo en tiempo de ejecución se sabe, por ejemplo, cual es la tabla a consultar  Usar el paquete DBMS_SQL.

23. DECLARE rcursor SYS_REFCURSOR; theCursor INTEGER; colCnt NUMBER; dTbl DBMS_SQL.DESC_TAB; val VARCHAR2(100); BEGIN OPEN rcursor FOR SELECT * FROM cliente; theCursor := DBMS_SQL.TO_CURSOR_NUMBER(rcursor); DBMS_SQL.DESCRIBE_COLUMNS(theCursor,colCnt,dTbl); FOR i IN 1 .. colCnt LOOP DBMS_SQL.DEFINE_COLUMN(theCursor,i,val,100); END LOOP; Ensayar con emp

24. WHILE(DBMS_SQL.FETCH_ROWS(theCursor) > 0) LOOP FOR i IN 1 .. colCnt LOOP DBMS_SQL.COLUMN_VALUE(theCursor,i,val); DBMS_OUTPUT.PUT_LINE(dTbl(i).col_name || ': ' || val); END LOOP; DBMS_OUTPUT.PUT_LINE('Siguiente registro'); END LOOP; DBMS_SQL.CLOSE_CURSOR(theCursor); END; /