programacion en pl_sql
TRANSCRIPT
![Page 1: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/1.jpg)
Programación en PL/SQLProgramación en PL/SQL
![Page 2: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/2.jpg)
PL/SQL-2
Estructura de un bloque PL/SQL Estructura de un bloque PL/SQL
• DECLARE – Opcional– Variables, cursores, excepciones
definidas por el usuario• BEGIN – Obligatorio
– Órdenes SQL– Órdenes PL/SQL
• EXCEPTION – Opcional– Acciones a realizar cuando
sucede un error• END; – Obligatorio
DECLARE
BEGIN
EXCEPTION
END;
![Page 3: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/3.jpg)
PL/SQL-3
Estructura de un bloque PL/SQLEstructura de un bloque PL/SQL
DECLAREvariable VARCHAR2(5);
BEGINSELECT nombre_columnaINTO variableFROM nombre_tabla;
EXCEPTIONWHEN nombre_excepcion THEN...
END;
DECLARE
BEGIN
EXCEPTION
END;
![Page 4: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/4.jpg)
PL/SQL-4
Tipos de BloquesTipos de BloquesAnónimo Procedimiento Función
[DECLARE]
BEGIN--órdenes
[EXCEPTION]
END;
PROCEDURE nombreIS
BEGIN--órdenes
[EXCEPTION]
END;
FUNCTION nombreRETURN tipoISBEGIN--órdenesRETURN valor;
[EXCEPTION]
END;
![Page 5: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/5.jpg)
PL/SQL-5
AplicacionesAplicaciones
Bloqueanónimo
Trigger deaplicación
Procedimiento/función
almacenada
Trigger deBase de Datos
Procedimiento/función
de aplicación
Procedimiento/función
en paquete
DECLARE
BEGIN
EXCEPTION
END;
![Page 6: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/6.jpg)
PL/SQL-6
Tipos de VariablesTipos de Variables
• Variables PL/SQL– Escalar– Compuesta– Referencia– LOB (large objects)
• Variables no-PL/SQL• Variables host y acotadas (bind)
• Variables PL/SQL– Escalar– Compuesta– Referencia– LOB (large objects)
• Variables no-PL/SQL• Variables host y acotadas (bind)
![Page 7: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/7.jpg)
PL/SQL-7
Declaración de Variables PL/SQLDeclaración de Variables PL/SQL
Sintaxis
Ejemplos
Sintaxis
Ejemplos
identificador [CONSTANT] tipo_datos [NOT NULL] [:= | DEFAULT expr];
Declarev_fecha DATE;v_nodepto NUMBER(2) NOT NULL := 10;v_ciudad VARCHAR2(13) := 'Atlanta';c_ cpostal CONSTANT NUMBER := 1400;
![Page 8: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/8.jpg)
PL/SQL-8
Asignación de Valores a VariablesAsignación de Valores a Variables
v_ename := 'Maduro';
v_hiredate := '31-DEC-98';
Sintaxis
Ejemplos
Sintaxis
Ejemplos
identifier := expr;
![Page 9: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/9.jpg)
PL/SQL-9
Tipos de datos escalaresTipos de datos escalares• VARCHAR2 (longitud_máxima)• NUMBER [(precisión, escala)]• DATE• CHAR [(longitud_máxima)]• LONG• LONG RAW• BOOLEAN• BINARY_INTEGER• PLS_INTEGER
• VARCHAR2 (longitud_máxima)• NUMBER [(precisión, escala)]• DATE• CHAR [(longitud_máxima)]• LONG• LONG RAW• BOOLEAN• BINARY_INTEGER• PLS_INTEGER
![Page 10: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/10.jpg)
PL/SQL-10
Declaraciones de Variables escalares
Declaraciones de Variables escalares
v_valido BOOLEAN NOT NULL := TRUE;
v_trabajo VARCHAR2(9);v_contador BINARY_INTEGER := 0;v_salario NUMBER(9,2) := 0;v_fecha DATE := SYSDATE + 7;c_iva CONSTANT NUMBER(3,2) := 8.25;v_valido BOOLEAN NOT NULL := TRUE;
EjemplosEjemplos
![Page 11: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/11.jpg)
PL/SQL-11
Atributo %TYPEAtributo %TYPE
• Sufijo que declara una variable coincidente con: – Una definición de una columna de la
base de datos– Otra variable previamente declarada
• Sufijo que declara una variable coincidente con: – Una definición de una columna de la
base de datos– Otra variable previamente declarada
...v_nombre emp.nombre%TYPE;v_balance NUMBER(7,2);v_balance_min v_balance%TYPE := 10;
...
![Page 12: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/12.jpg)
PL/SQL-12
Tipos de datos compuestosTipos de datos compuestos
Tipos• TABLAS PL/SQL• REGISTROS PL/SQL
Tipos• TABLAS PL/SQL• REGISTROS PL/SQL
![Page 13: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/13.jpg)
PL/SQL-13
Referenciando variables No PL/SQL
Referenciando variables No PL/SQL
Almacenar el salario anual en una variable host de SQL*Plus.
• Referencia variables no-PL/SQL como variables host.
• Prefijar las referencias con (:).
Almacenar el salario anual en una variable host de SQL*Plus.
• Referencia variables no-PL/SQL como variables host.
• Prefijar las referencias con (:).
:g_sal_mensual := v_sal / 12;
![Page 14: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/14.jpg)
PL/SQL-14
ComentariosComentarios• Una línea con dos guiones (- -).• Múltiples líneas entre los símbolos /* y */.
Ejemplo
• Una línea con dos guiones (- -).• Múltiples líneas entre los símbolos /* y */.
Ejemplo
...v_sal NUMBER (9,2);
BEGIN/* Calcula el salario anual basado en el salario
mensual introducido por el usuario */v_sal := v_sal * 12;
END; -- Final de la transacción
![Page 15: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/15.jpg)
PL/SQL-15
Funciones SQL en PL/SQLFunciones SQL en PL/SQL• Disponibles:
– Numéricas una-fila – De caracteres una-fila– Conversión de tipos– Fecha
• No disponibles:– GREATEST – LEAST– DECODE– Funciones de grupo
• Disponibles:– Numéricas una-fila – De caracteres una-fila– Conversión de tipos– Fecha
• No disponibles:– GREATEST – LEAST– DECODE– Funciones de grupo
Igual en SQL
![Page 16: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/16.jpg)
PL/SQL-16
Funciones PL/SQLFunciones PL/SQL
Ejemplos• Construir una lista de correo.
• Conversión a minúsculas.
Ejemplos• Construir una lista de correo.
• Conversión a minúsculas.
v_direccion_correo := v_nombre||CHR(10)||
v_direccion||CHR(10)||v_ciudad||
CHR(10)||v_codigopostal;
v_nombre := LOWER(v_nombre);
![Page 17: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/17.jpg)
PL/SQL-17
Funciones de conversiónFunciones de conversión
– TO_CHAR– TO_DATE– TO_NUMBER
– TO_CHAR– TO_DATE– TO_NUMBER
BEGINSELECT TO_CHAR(fecha,
'MON. DD, YYYY')FROM emp;
END;
v_comment := USER||': '||TO_CHAR(SYSDATE);
![Page 18: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/18.jpg)
PL/SQL-18
Bloques anidados y VariablesBloques anidados y Variables
...x BINARY_INTEGER;
BEGIN...DECLARE
y NUMBER;BEGIN
...END;...
END;
Alcance de x
Alcance de y
• Los bloques pueden anidarse.• Los bloques pueden anidarse.
![Page 19: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/19.jpg)
PL/SQL-19
Operadores en PL/SQLOperadores en PL/SQL
• Lógicos• Aritméticos• Concatenación • Paréntesis• Operador exponencial (**)
• Lógicos• Aritméticos• Concatenación • Paréntesis• Operador exponencial (**)
Igual en SQL
v_valido := (v_empno IS NOT NULL);
![Page 20: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/20.jpg)
PL/SQL-20
Variables BindVariables Bind
Para referenciar una variable bind debe prefijarse con :.Ejemplo
Para referenciar una variable bind debe prefijarse con :.EjemploDECLARE
v_sal emp.sal%TYPE;BEGIN
SELECT salINTO v_salFROM empWHERE empno = 7369;:salary := v_sal;
END;
![Page 21: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/21.jpg)
Interaccionando con el servidor Oracle
Interaccionando con el servidor Oracle
![Page 22: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/22.jpg)
PL/SQL-22
Órdenes SQL en PL/SQLÓrdenes SQL en PL/SQL
• Orden SELECT . Sólo puede obtenerse una tupla (cláusula INTO).
• Órdenes DML.• Control de transacciones:
COMMIT, ROLLBACK, o SAVEPOINT
• Orden SELECT . Sólo puede obtenerse una tupla (cláusula INTO).
• Órdenes DML.• Control de transacciones:
COMMIT, ROLLBACK, o SAVEPOINTSELECT lista_selectINTO {nombre_variable[, nombre_variable]...
| nombre_registro}FROM tablaWHERE condición;
![Page 23: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/23.jpg)
PL/SQL-23
Órdenes SQL en PL/SQLÓrdenes SQL en PL/SQLDECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);
BEGINSELECT deptno, locINTO v_deptno, v_locFROM deptWHERE dname = ‘VENTAS';
...END;
DECLAREv_empno emp.empno%TYPE;
BEGINSELECT secuencia_empno.NEXTVAL INTO v_empnoFROM dual;
INSERT INTO emp(empno, nombre, trabajo, deptno)VALUES(v_empno, ‘PEPE', ‘AUXILIAR', 10);
END;
![Page 24: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/24.jpg)
PL/SQL-24
Órdenes COMMIT y ROLLBACKÓrdenes COMMIT y ROLLBACK
• Una transación se inicia con la primera orden DML command que sigue a COMMIT o ROLLBACK.
• Usar las órdenes SQL COMMIT y ROLLBACK para terminar explícitamente una transacción
• Una transación se inicia con la primera orden DML command que sigue a COMMIT o ROLLBACK.
• Usar las órdenes SQL COMMIT y ROLLBACK para terminar explícitamente una transacción
![Page 25: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/25.jpg)
PL/SQL-25
Cursor SQLCursor SQL• Un cursor es un área privada de trabajo
SQL.• Hay dos tipos de cursores:
– Implícitos– Explícitos
• El servidor Oracle usa cursores implícitos para analizar y ejecutar las órdenes SQL.
• Los cursores explícitos se declaran por el programador explícitamente.
• Un cursor es un área privada de trabajo SQL.
• Hay dos tipos de cursores:– Implícitos– Explícitos
• El servidor Oracle usa cursores implícitos para analizar y ejecutar las órdenes SQL.
• Los cursores explícitos se declaran por el programador explícitamente.
![Page 26: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/26.jpg)
PL/SQL-26
Atributos de CursorAtributos de CursorSe utilizan para chequear la salida de las órdenes SQL. Se utilizan para chequear la salida de las órdenes SQL. SQL%ROWCOUNT Número de filas afectadas por la
orden SQL más reciente (valor de tipo integer).
SQL%FOUND Atributo booleano que se evalúa a TRUE si la orden SQL más reciente afecta a una o más filas.
SQL%NOTFOUND Atributo booleano que se evalúa a TRUE si la orden SQL más reciente no afecta a ninguna fila.
SQL%ISOPEN Siempre evaluado a FALSE porque PL/SQL cierra los cursores implícitos después de la ejecución.
![Page 27: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/27.jpg)
PL/SQL-27
Atributos de CursorAtributos de CursorBorra las filas de la tabla ITEM que tienen el número de orden especificado. Imprime el número de filas borradas.Ejemplo
Borra las filas de la tabla ITEM que tienen el número de orden especificado. Imprime el número de filas borradas.EjemploVARIABLE filas_borradasDECLAREv_ordid NUMBER := 605;
BEGINDELETE FROM itemWHERE ordid = v_ordid;
filas_borradas := SQL%ROWCOUNT||' filas borradas.');
END;PRINT filas_borradas
![Page 28: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/28.jpg)
Estructuras de ControlEstructuras de Control
![Page 29: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/29.jpg)
PL/SQL-29
Órdenes IFÓrdenes IF
IF condición THENórdenes;
[ELSIF condición THEN órdenes;]
[ELSE órdenes;]
END IF;
SintaxisSintaxis
IF v_ename = 'OSBORNE' THEN v_mgr := 22;
END IF;
![Page 30: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/30.jpg)
PL/SQL-30
Flujo IF-THEN-ELSEFlujo IF-THEN-ELSE
IF CondiciónTRUE
THEN Acciones(incluyendo IFs)THEN Acciones(incluyendo IFs)
FALSE
ELSE acciones(incluyendo IFs)ELSE acciones(incluyendo IFs)
![Page 31: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/31.jpg)
PL/SQL-31
Flujo IF-THEN-ELSIF Flujo IF-THEN-ELSIF
IF CondiciónIF CondiciónTRUE
THEN AccionesTHEN Acciones
FALSE
ELSIFCondición
ELSIFCondición
TRUE
THEN AccionesTHEN Acciones
FALSE
ELSEAcciones
ELSEAcciones
![Page 32: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/32.jpg)
PL/SQL-32
Valores nulosValores nulos
• Se pueden gestionar valores nulos con el operador IS NULL.
• Cualquier expresión que contenga un valor nulo se evalúa a NULL.
• Las expresiones que se concatenan con valores nulos los tratan como cadenas vacías.
• Se pueden gestionar valores nulos con el operador IS NULL.
• Cualquier expresión que contenga un valor nulo se evalúa a NULL.
• Las expresiones que se concatenan con valores nulos los tratan como cadenas vacías.
![Page 33: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/33.jpg)
PL/SQL-33
Control de iteracionesControl de iteraciones
Hay tres tipos de bucles:– Bucle básico– Bucle FOR– Bucle WHILE
Hay tres tipos de bucles:– Bucle básico– Bucle FOR– Bucle WHILE
![Page 34: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/34.jpg)
PL/SQL-34
Bucle básicoBucle básicoSintaxisSintaxisLOOP orden1;. . .EXIT [WHEN condición];
END LOOP;
donde: condición es una variable booleana o expresión (TRUE, FALSE, o NULL);
-- delimitador
-- órdenes
-- orden EXIT
-- delimitador
![Page 35: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/35.jpg)
PL/SQL-35
Bucle básicoBucle básico
DECLAREv_ordid item.ordid%TYPE := 101;v_contador NUMBER(2) := 1;
BEGINLOOPINSERT INTO item(ordid, itemid) VALUES(v_ordid, v_contador);
v_contador := v_contador + 1;EXIT WHEN v_contador > 10;
END LOOP;END;
EjemploEjemplo
![Page 36: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/36.jpg)
PL/SQL-36
Bucle FORBucle FORSintaxis
No hay que declarar el índice; se declara implícitamente.
Sintaxis
No hay que declarar el índice; se declara implícitamente.
FOR contador in [REVERSE] cota_inerior..cota_superior LOOP
órdenes;. . .
END LOOP;
DECLAREv_ordid item.ordid%TYPE := 101;
BEGINFOR i IN 1..10 LOOPINSERT INTO item(ordid, itemid) VALUES(v_ordid, i);
END LOOP;END;
![Page 37: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/37.jpg)
PL/SQL-37
Bucle WHILEBucle WHILE
SintaxisSintaxisWHILE condicion LOOPorden1;orden2;. . .
END LOOP;
La condiciónse evalúa al Comienzo decada iteración.
![Page 38: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/38.jpg)
PL/SQL-38
Bucle WHILEBucle WHILEEjemploEjemplo
ACCEPT p_precio PROMPT ‘Introduce el precio: 'ACCEPT p_itemtot PROMPT ‘Introduce el máximo: 'DECLARE...v_ctd NUMBER(8) := 1;v_total NUMBER(7,2) := 0;BEGIN...WHILE v_total < &p_itemtot LOOP...
v_ctd := v_ctd + 1;v_total := v_ctd * &p_precio;END LOOP;
...
![Page 39: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/39.jpg)
PL/SQL-39
Bucles anidados y etiquetasBucles anidados y etiquetas
• Se pueden anidar bucles en múltiples niveles.
• Se pueden utilizar etiquetas para distinguir entre bucles y bloques.
• Se puede salir del bucle externo con la orden EXIT referenciando la etiqueta.
• Se pueden anidar bucles en múltiples niveles.
• Se pueden utilizar etiquetas para distinguir entre bucles y bloques.
• Se puede salir del bucle externo con la orden EXIT referenciando la etiqueta.
![Page 40: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/40.jpg)
PL/SQL-40
Bucles anidados y etiquetasBucles anidados y etiquetas...BEGIN<<bucle_externo>>LOOPv_contador := v_contador+1;
EXIT WHEN v_contador>10;<<bucle_interno>>LOOP...EXIT bucle_externo WHEN total_realizado = ‘SI';-- Deja ambos buclesEXIT WHEN interno_realizado = ‘SI';-- Deja sólo el bucle interno...
END LOOP bucle_interno;...
END LOOP bucle_externo;END;
![Page 41: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/41.jpg)
Tipos de Datos CompuestosTipos de Datos Compuestos
![Page 42: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/42.jpg)
PL/SQL-42
Tipos de datos compuestosTipos de datos compuestos
• Tipos:– REGISTROS PL/SQL– TABLAS PL/SQL
• Contienen componentes internos• Son reutilizables
• Tipos:– REGISTROS PL/SQL– TABLAS PL/SQL
• Contienen componentes internos• Son reutilizables
![Page 43: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/43.jpg)
PL/SQL-43
Registro PL/SQLRegistro PL/SQL
Sintaxis
Donde declaración_campo es:
Sintaxis
Donde declaración_campo es:
TYPE nombre_tipo IS RECORD(declaración_campo[, declaración_campo]…);
identificador nombre_tipo;
nombre_campo {tipo_campoe | variable%TYPE | tabla.columna%TYPE | tabla%ROWTYPE}[[NOT NULL] {:= | DEFAULT} expr]
![Page 44: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/44.jpg)
PL/SQL-44
Registro PL/SQLRegistro PL/SQL
EjemploEjemplo
...TYPE empleado_tipo_reg IS RECORD(nombre VARCHAR2(10),trabajo VARCHAR2(9),salario NUMBER(7,2));
emp_registro empleado_tipo_reg;...
![Page 45: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/45.jpg)
PL/SQL-45
Atributo %ROWTYPEAtributo %ROWTYPE• Declara una variable de acuerdo a una
colección de columnas en una tabla de base de datos o vista.
• Prefija %ROWTYPE con la tabla.• Los campos del registro toman
nombres y tipos de las columnas de la tabla o vista.
• Declara una variable de acuerdo a una colección de columnas en una tabla de base de datos o vista.
• Prefija %ROWTYPE con la tabla.• Los campos del registro toman
nombres y tipos de las columnas de la tabla o vista.
dept_record dept%ROWTYPE;
emp_record emp%ROWTYPE;
![Page 46: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/46.jpg)
PL/SQL-46
Tablas PL/SQLTablas PL/SQL
• Están constituidas por dos componentes:– Clave Primaria de tipo BINARY_INTEGER– Columnas de tipo escalar or registro
• Crece dinámicamente porque no está limitada
• Están constituidas por dos componentes:– Clave Primaria de tipo BINARY_INTEGER– Columnas de tipo escalar or registro
• Crece dinámicamente porque no está limitada
![Page 47: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/47.jpg)
PL/SQL-47
Tablas PL/SQLTablas PL/SQLSintaxisSintaxisTYPE nombre_tipo IS TABLE OF {tipo_columna | variable%TYPE| tabla.columna%TYPE} [NOT NULL] [INDEX BY BINARY_INTEGER];
Identificador nombre_tipo;
...TYPE nombre_tipo_tabla IS TABLE OF emp.nombre%TYPEINDEX BY BINARY_INTEGER;
nombre_tabla nombre_tipo_tabla;...
EjemploEjemplo
![Page 48: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/48.jpg)
PL/SQL-48
Estructura de tabla PL/SQLEstructura de tabla PL/SQL
Clave Primaria Columna... ...
1 Luís2 Pepe3 Manolo
... ...
BINARY_INTEGER Escalar
![Page 49: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/49.jpg)
PL/SQL-49
Tabla PL/SQLTabla PL/SQLDECLARETYPE nombre_tipo_tabla IS TABLE OF emp.nombre%TYPEINDEX BY BINARY_INTEGER;
TYPE fecha_tipo_tabla IS TABLE OF DATEINDEX BY BINARY_INTEGER;
nombre_tabla nombre_tipo_tabla; fecha_tabla fecha_tipo_tabla;
BEGINnombre_tabla(1) := 'CARMELO';fecha_tabla(8) := SYSDATE + 7;IF nombre_tabla.EXISTS(1) THEN
INSERT INTO ......
END;
![Page 50: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/50.jpg)
PL/SQL-50
Tabla de Registros PL/SQLTabla de Registros PL/SQL
• Define a variable TABLE con el atributo %ROWTYPE.
• Declara una variable PL/SQL para almacenar información del departmento.
Ejemplo
• Define a variable TABLE con el atributo %ROWTYPE.
• Declara una variable PL/SQL para almacenar información del departmento.
EjemploDECLARETYPE dept_tipo_tabla IS TABLE OF dept%ROWTYPEINDEX BY BINARY_INTEGER;
dept_tabla dept_tipo_tabla;-- Cada elemento de dept_tabla is un registro
![Page 51: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/51.jpg)
PL/SQL-51
Métodos de Tabla PL/SQLMétodos de Tabla PL/SQL
Para facilitar el uso de la tabla PL/SQL:• EXISTS• COUNT• FIRST and LAST• PRIOR
Para facilitar el uso de la tabla PL/SQL:• EXISTS• COUNT• FIRST and LAST• PRIOR
• NEXT• EXTEND• TRIM• DELETE
• NEXT• EXTEND• TRIM• DELETE
![Page 52: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/52.jpg)
Cursores ExplícitosCursores Explícitos
![Page 53: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/53.jpg)
PL/SQL-53
CursoresCursores
Cada orden SQL ejecutada por el servidor Oracle tiene asociada un cursor individual:• Cursores implícitos: Declarados para
todas las órdenes DML y órdenes SELECT PL/SQL.
• Cursores explícitos: Declarados y nominados por el programador.
Cada orden SQL ejecutada por el servidor Oracle tiene asociada un cursor individual:• Cursores implícitos: Declarados para
todas las órdenes DML y órdenes SELECT PL/SQL.
• Cursores explícitos: Declarados y nominados por el programador.
![Page 54: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/54.jpg)
PL/SQL-54
Cursor ExplícitoCursor Explícito
Conjunto resultado
Fila actualCursor
7369 SMITH CLERK7566 JONES MANAGER7788 SCOTT ANALYST7876 ADAMS CLERK7902 FORD ANALYST
![Page 55: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/55.jpg)
PL/SQL-55
Control de cursores explícitosControl de cursores explícitos
• Crea un área SQL nominada
DECLARE
• Identifica el conjunto activo
OPEN
• Almacena la fila actual en variables
FETCH
• Chequea si existen filas
VACÍO?
• Retorna a FETCH si encuentra filas
No
• Elimina el conjunto activo
CLOSESi
![Page 56: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/56.jpg)
PL/SQL-56
Declarando el CursorDeclarando el CursorSintaxisSintaxis
CURSOR nombre_cursor IS
orden_select;
DECLARECURSOR c1 IS SELECT empno, nombreFROM emp;
CURSOR c2 ISSELECT *FROM deptWHERE deptno = 10;
BEGIN...
![Page 57: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/57.jpg)
PL/SQL-57
Abriendo el CursorAbriendo el CursorSintaxisSintaxisOPEN nombre_cursor;
Obteniendo Datos del CursorObteniendo Datos del Cursor
FETCH nombre_cursor INTO [variable1, variable2, ...]
| nombre_registro];
Cerrando el CursorCerrando el CursorCLOSE nombre_cursor;
![Page 58: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/58.jpg)
PL/SQL-58
Uso del CursorUso del CursorEjemploEjemploFETCH c1 INTO v_empno, v_nombre;
...OPEN cursor1;LOOPFETCH cursor1 INTO variablesEXIT WHEN ...;...-- Procesa los datos obtenidos
...END;CLOSE cursor1;
![Page 59: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/59.jpg)
PL/SQL-59
Atributos de Cursor ExplícitoAtributos de Cursor ExplícitoObtiene información sobre el estado del cursor.Obtiene información sobre el estado del cursor.
Atributo Tipo Descripción
%ISOPEN Boolean Evalúa a TRUE si el cursor está abierto
%NOTFOUND Boolean Evalúa a TRUE si el fetch más reciente no retorna filas
%FOUND Boolean Evalúa a TRUE si el fetch másreciente retorna filas; complemento de %NOTFOUND
%ROWCOUNT Number Evalúa al número total de filas retornadas
![Page 60: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/60.jpg)
PL/SQL-60
EjemplosEjemplosIF NOT c1%ISOPEN THEN
OPEN c1;END IF;LOOPFETCH c1...
... CURSOR c1 ISSELECT empno, nombreFROM emp;
emp_registro c1%ROWTYPE;BEGINOPEN c1;
. . .FETCH c1 INTO emp_registo;
![Page 61: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/61.jpg)
PL/SQL-61
Sintaxis
• Acorta el proceso para cursores explícitos.• Incluye implicitamente open, fetch, y close.• El registro se declara implícitamente.
Sintaxis
• Acorta el proceso para cursores explícitos.• Incluye implicitamente open, fetch, y close.• El registro se declara implícitamente.
Bucle FOR CursorBucle FOR Cursor
FOR nombre_registro IN nobre_cursor LOOP
orden1;
orden2;
. . .
END LOOP;
![Page 62: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/62.jpg)
PL/SQL-62
Bucle FOR CursorBucle FOR CursorEjemploEjemplo
DECLARECURSOR c1 ISSELECT empno, nombreFROM emp;
BEGINFOR emp_registro IN c1 LOOP
-- se ejecuta open y fetch implícitamenteIF emp_registro.empno = 7839 THEN...
END LOOP; -- se ejecuta close implícitamenteEND;
![Page 63: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/63.jpg)
PL/SQL-63
Bucle FOR Cursor Usando Subconsultas
Bucle FOR Cursor Usando Subconsultas
No es necesario declarar el cursor.EjemploNo es necesario declarar el cursor.EjemploBEGINFOR emp_registro IN ( SELECT empno, nombre
FROM emp) LOOP-- se ejecuta open y fetch implícitamente
IF emp_registro.empno = 7839 THEN...
END LOOP; -- se ejecuta close implícitamenteEND;
![Page 64: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/64.jpg)
PL/SQL-64
Cursores con ParámetrosSintaxis
• Pasa parámetros al cursor cuando se abre y la consulta se ejecuta.
• Abre un cursor explícito varias veces con un conjunto activo distinto cada vez.
CURSOR nombre_cursor[(nombre_parámetro tipo_de_datos, ...)]
ISorden_select;
![Page 65: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/65.jpg)
PL/SQL-65
Cursores con ParámetrosCursores con Parámetros
EjemploEjemploDECLARECURSOR c1 (v_deptno NUMBER, v_trabajo VARCHAR2) ISSELECT empno, nombreFROM empWHERE deptno = v_deptno AND trabajo = v_trabajo;
BEGINOPEN c1(10, ‘AUXILIAR');
...
![Page 66: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/66.jpg)
PL/SQL-66
Cláusula FOR UPDATECláusula FOR UPDATE
Sintaxis
• Bloquea explícitamente durante la transacción.
• Bloquea las filas antes de actualizar o borrar.
Sintaxis
• Bloquea explícitamente durante la transacción.
• Bloquea las filas antes de actualizar o borrar.
SELECT ... FROM ...FOR UPDATE [OF referencia_columna][NOWAIT]
![Page 67: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/67.jpg)
PL/SQL-67
Cláusula FOR UPDATECláusula FOR UPDATE
EjemploEjemplo
DECLARECURSOR c1 IS SELECT empno, nombre FROM empFOR UPDATE NOWAIT;
![Page 68: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/68.jpg)
PL/SQL-68
Cláusula WHERE CURRENT OFSintaxis
• Usa cursores para actualizar o borrar la fila actual.
• Incluir la cláusula FOR UPDATE en la consulta del cursor para primero bloquear las filas.
• Usar la cláusula WHERE CURRENT OF para referenciar la fila actual de un cursor explícito.
WHERE CURRENT OF cursor
![Page 69: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/69.jpg)
PL/SQL-69
Cláusula WHERE CURRENT OF
DECLARECURSOR c1 ISSELECT ...FOR UPDATE NOWAIT;
BEGIN...FOR emp_registro IN c1 LOOPUPDATE ...WHERE CURRENT OF c1;
...END LOOP;COMMIT;
END;
EjemploEjemplo
![Page 70: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/70.jpg)
PL/SQL-70
Cursores con SubconsultasCursores con Subconsultas
DECLARECURSOR cursor1 IS SELECT t1.deptno, nombre, PLANTILLAFROM dept t1, (SELECT deptno,
count(*) PLANTILLAFROM empGROUP BY deptno) t2
WHERE t1.deptno = t2.deptno AND PLANTILLA >= 5;
EjemploEjemplo
![Page 71: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/71.jpg)
ExcepcionesExcepciones
![Page 72: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/72.jpg)
PL/SQL-72
Gestionando Excepciones con PL/SQLGestionando Excepciones con PL/SQL
• ¿Qué es una excepcion?– Identificador en PL/SQL que surge
durante la ejecución.• ¿Cómo surge?
– Cuando ocurre un error Oracle.– O se crea explícitamente.
• ¿Cómo gestionarlo?– Capturarlo con un manejador.– Propagarlo al entorno padre.
• ¿Qué es una excepcion?– Identificador en PL/SQL que surge
durante la ejecución.• ¿Cómo surge?
– Cuando ocurre un error Oracle.– O se crea explícitamente.
• ¿Cómo gestionarlo?– Capturarlo con un manejador.– Propagarlo al entorno padre.
![Page 73: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/73.jpg)
PL/SQL-73
Gestionando ExcepcionesGestionando Excepciones
Captura la Excepción
DECLARE
BEGIN
END;
Se crea la Excepción
EXCEPTION
Se capturala Excepción
Propaga la Excepción
DECLARE
BEGIN
END;
Se crea la Excepción
EXCEPTION
No se atrapa la Excepción
La Excepción se propaga al entorno
padre
![Page 74: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/74.jpg)
PL/SQL-74
Tipos de ExcepciónTipos de Excepción
• Predefinida por el servidor Oracle• No predefinida por el servidor Oracle • Definida por el usuario
• Predefinida por el servidor Oracle• No predefinida por el servidor Oracle • Definida por el usuario
} Surge Implícita-mente
Surge Explícitamente
![Page 75: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/75.jpg)
PL/SQL-75
Capturando ExcepcionesCapturando Excepciones
EXCEPTIONWHEN excepción1 [OR excepción2 . . .] THENorden1;orden2;. . .
[WHEN excepción3 [OR excepción4 . . .] THENorden1;orden2;. . .]
[WHEN OTHERS THENorden1;orden2;. . .]
SintaxisSintaxis
![Page 76: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/76.jpg)
PL/SQL-76
Capturando errores predefinidos del servidor Oracle
Capturando errores predefinidos del servidor Oracle• Referenciar el nombre estandard en la
rutina de gestión de la excepción.• Ejemplos de excepciones predefinidas:
– NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX
• Referenciar el nombre estandard en la rutina de gestión de la excepción.
• Ejemplos de excepciones predefinidas: – NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX
![Page 77: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/77.jpg)
PL/SQL-77
Excepción PredefinidaExcepción Predefinida
BEGIN SELECT ... COMMIT;EXCEPTIONWHEN NO_DATA_FOUND THENorden1; orden2;
WHEN TOO_MANY_ROWS THENorden1;
WHEN OTHERS THENorden1; orden2; orden3;
END;
SintaxisSintaxis
![Page 78: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/78.jpg)
PL/SQL-78
Capturando errores del servidor Oracle no predefinidos
Capturando errores del servidor Oracle no predefinidos
Declarar
• Nombrar la excepción
Asociar
• Codificar el PRAGMA EXCEPTION_INIT
Sección Declarativa
Referenciar
• Gestionar la excepción surgida
Sección de gestión de excepción
![Page 79: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/79.jpg)
PL/SQL-79
DECLAREe_producto_invalido EXCEPTION;PRAGMA EXCEPTION_INIT (
e_producto_invalido, -2292);v_mensaje VARCHAR2(50);
BEGIN. . .EXCEPTIONWHEN e_producto_invalido THEN:g_mensaje := ‘El códio dl producto
especificado no es válido.';. . .END;
Error No-PredefinidoError No-PredefinidoCaptura del error de Oracle número -2292 una violación de restricción de integridadCaptura del error de Oracle número -2292 una violación de restricción de integridad
e_producto_invalido EXCEPTION; 1PRAGMA EXCEPTION_INIT (
e_producto_invalido, -2292); 2
e_producto_invalido 3
![Page 80: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/80.jpg)
PL/SQL-80
Capturando Excepciones definidas por el usuario
Capturando Excepciones definidas por el usuario
• Nombrar la excepción
Declarar
SecciónDeclarativa
Crear
• Generar explicita-mente la excepción usando la orden RAISE
Sección Ejecutable
Referenciar
• Gestionar la excepción
Sección Excepción
![Page 81: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/81.jpg)
PL/SQL-81
Excepción definida por el usuarioExcepción definida por el usuario
[DECLARE]e_cantidad_restante EXCEPTION;
. . .BEGIN. . .RAISE e_cantidad_restante;
. . .EXCEPTIONWHEN e_cantidad_restante THEN:g_mensaje := ‘Hay stock.';
. . .END;
EjemploEjemplo
e_cantidad_restante EXCEPTION; 1
RAISE e_cantidad_restante;2
e_cantidad_restante3
![Page 82: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/82.jpg)
PL/SQL-82
Funciones para capturar Excepciones
Funciones para capturar Excepciones
• SQLCODERetorna el valor númerico del código de error
• SQLERRMRetorna el mensaje asociado con el número de error
• SQLCODERetorna el valor númerico del código de error
• SQLERRMRetorna el mensaje asociado con el número de error
![Page 83: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/83.jpg)
PL/SQL-83
Funciones para capturar ExcepcionesFunciones para capturar Excepciones
DECLAREv_codigo_error NUMBER;v_mensaje_error VARCHAR2(255);
BEGIN...EXCEPTION...WHEN OTHERS THENROLLBACK;v_codigo_error := SQLCODE ;v_mensaje_error:= SQLERRM ;
INSERT INTO errores VALUES(v_codigo_error,v_mensaje_error);
END;
EjemploEjemplo
SQLCODESQLERRM
![Page 84: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/84.jpg)
PL/SQL-84
Propagando ExcepcionesPropagando Excepciones
BEGINSELECT ...UPDATE ...IF SQL%NOTFOUND THENRAISE e_no_filas;
END IF;EXCEPTION
WHEN e_integridad THEN ...WHEN e_no_filas THEN ...
END;
DECLARE. . .e_no_filas exception;e_integridad exception;PRAGMA EXCEPTION_INIT (e_integridad, -2292);
BEGINFOR c_registro IN emp_cursor LOOP
END LOOP;EXCEPTION
WHEN NO_DATA_FOUND THEN . . .WHEN TOO_MANY_ROWS THEN . . .
END;
Subbloques pueden gestionar una excepción o pasarla al bloque que lo contiene.
BEGINSELECT ...UPDATE ...IF SQL%NOTFOUND THENRAISE e_no_filas;
END IF;EXCEPTION
WHEN e_integridad THEN ...WHEN e_no_filas THEN ...
END;
![Page 85: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/85.jpg)
PL/SQL-85
RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR
Sintaxis
• Procedimiento que permite crear mensajes de error definidos por el usuario desde suprogramas almacenados.
• Llamado sólo desde un subprograma almacenado en ejecución.
Sintaxis
• Procedimiento que permite crear mensajes de error definidos por el usuario desde suprogramas almacenados.
• Llamado sólo desde un subprograma almacenado en ejecución.
raise_application_error (numero_error,mensaje[, {TRUE | FALSE}]);
![Page 86: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/86.jpg)
PL/SQL-86
RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR
• Se usa en dos lugares diferentes:– Sección ejecutable– Sección Excepción
• Retorna condiciones de error al usuario de una forma consistente con otros errores del servidor Oracle
• Se usa en dos lugares diferentes:– Sección ejecutable– Sección Excepción
• Retorna condiciones de error al usuario de una forma consistente con otros errores del servidor Oracle
![Page 87: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/87.jpg)
ProcedimientosProcedimientos
![Page 88: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/88.jpg)
PL/SQL-88
ProcedimientosProcedimientos• Un procedimiento es un bloque nominado
PL/SQL que realiza una acción.• Puede almacenarse como un objeto de la
base de datos, para su ejecución repetida.
• Un procedimiento es un bloque nominado PL/SQL que realiza una acción.
• Puede almacenarse como un objeto de la base de datos, para su ejecución repetida.
CREATE [OR REPLACE] PROCEDURE nombre_procedimiento(argumento1 [modo1] tipo_de_datos1,argumento2 [modo2] tipo_de_datos2,. . .
IS [AS]Bloque PL/SQL;
![Page 89: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/89.jpg)
PL/SQL-89
Modos de ParámetrosModos de Parámetros
Entorno de ejecución
Procedimiento
(DECLARE)
BEGIN
EXCEPTION
END;
Parámetro IN
Parámetro OUT
Parámetro IN OUT
![Page 90: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/90.jpg)
PL/SQL-90
IN OUTDebe especificarsePasado al subprograma; retornado al entorno de ejecuciónVariable inicializada
Debe ser una variable
OUTDebe especificarseRetornado al entorno de ejecución
variable no ininicializadaDebe ser una variable
Modos de ParámetrosModos de Parámetros
INPor defectoEl Valor sepasa al subprograma
El parámetro actúa como constantePuede ser:Literal, Expresión, Constante, oVariable inicializada
![Page 91: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/91.jpg)
PL/SQL-91
Parámetro IN : EjemploParámetro IN : Ejemplo
SQL> CREATE OR REPLACE PROCEDURE act_salario2 (v_id in emp.empno%TYPE)3 IS4 BEGIN5 UPDATE emp6 SET sal = sal * 1.107 WHERE empno = v_id;8 END act_salario;9 /
Procedure created.
SQL> EXECUTE act_salario (7369)PL/SQL procedure successfully completed.
![Page 92: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/92.jpg)
PL/SQL-92
Parámetro OUT : EjemploParámetro OUT : Ejemplo
SQL> CREATE OR REPLACE PROCEDURE consulta_emp1 (v_id IN emp.empno%TYPE,2 v_nombre OUTemp.nombre%TYPE,3 v_salario OUTemp.sal%TYPE,4 v_comision OUTemp.com%TYPE)5 IS6 BEGIN7 SELECT nombre, sal, com8 INTO v_nombre, v_salario, v_comision9 FROM emp
10 WHERE empno = v_id;11 END consulta_emp;12 /
![Page 93: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/93.jpg)
PL/SQL-93
Parámetros OUT y SQL*PlusParámetros OUT y SQL*Plus
SQL> START consulta_emp.sqlProcedure created.
SQL> VARIABLE g_nombre varchar2(15)SQL> VARIABLE g_salario numberSQL> VARIABLE g_comision number
SQL> EXECUTE consulta_emp (7654,:g_nombre,:g_salario,2 :g_comision)
PL/SQL procedure successfully completed.
SQL> PRINT g_nombreG_NOMBRE---------------MARTIN
![Page 94: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/94.jpg)
PL/SQL-94
PL/SQL> .CREATE CHAR g_nombre LENGTH 10 PL/SQL> .CREATE NUMBER g_salario PRECISION 4PL/SQL> .CREATE NUMBER g_comision PRECISION 4PL/SQL> ACT_SALARIO (7654, :g_nobre, :g_salario,
+> :g_comision);PL/SQL> TEXT_IO.PUT_LINE (:g_nombre || ' gana ' ||
+> TO_CHAR(:g_salario) || ' + comisión de ' +> || TO_CHAR(:g_comision));
MARTIN gana 1250 + comisión de 1400
Parámetros OUT y Procedure Builder
Parámetros OUT y Procedure Builder
![Page 95: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/95.jpg)
PL/SQL-95
Parámetros IN OUTParámetros IN OUTSQL> CREATE OR REPLACE PROCEDURE formatea_tlf2 (v_tlf IN OUT VARCHAR2)3 IS4 BEGIN5 v_tlf := '(' || SUBSTR(v_tlf,1,3) ||6 ')' || SUBSTR(v_tlf,4,3) || 7 '-' || SUBSTR(v_tlf,7);8 END formatea_tlf;9 /
SQL>VARIABLE g_tlf varchar2(15)
SQL> BEGIN :g_tlf := '8006330575'; END;
SQL> EXECUTE formatea_tlf (:g_tlf)
![Page 96: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/96.jpg)
PL/SQL-96
Métodos de paso de ParámetrosMétodos de paso de Parámetros
• Posicional: Los valores se listan en el mismo orden que se declaran
• Nominada:Los valores se listan en cualquier orden asociando cada uno con su parámetro usando =>
• Combinación:Lista los primeros valores posicionalmente y el resto de forma nominada
• Posicional: Los valores se listan en el mismo orden que se declaran
• Nominada:Los valores se listan en cualquier orden asociando cada uno con su parámetro usando =>
• Combinación:Lista los primeros valores posicionalmente y el resto de forma nominada
![Page 97: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/97.jpg)
PL/SQL-97
Paso de Parámetros: EjemploPaso de Parámetros: EjemploSQL> CREATE OR REPLACE PROCEDURE inserta_dept1 (v_nombre IN dept.nombre%TYPE DEFAULT ‘desconocido',2 v_loc IN dept.loc%TYPE DEFAULT ‘desconocido')3 IS4 BEGIN5 INSERT INTO dept6 VALUES (dept_deptno.NEXTVAL, v_nombre, v_loc);7 END inserta_dept;8 /
SQL> begin2 add_dept;3 add_dept ( 'TRAINING', 'NEW YORK');4 add_dept ( v_loc => 'DALLAS', v_name =>'EDUCATION');5 add_dept ( v_loc => 'BOSTON') ;6 end;7 /
PL/SQL procedure successfully completed.
![Page 98: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/98.jpg)
PL/SQL-98
Borrando ProcedimientosBorrando Procedimientos
Usando SQL*Plus:• Sintaxis
• Ejemplo
Usando SQL*Plus:• Sintaxis
• Ejemplo
DROP PROCEDURE nombre_porcedimiento
SQL> DROP PROCEDURE act_salario;Procedure dropped.
![Page 99: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/99.jpg)
FuncionesFunciones
![Page 100: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/100.jpg)
PL/SQL-100
FuncionesFunciones• Una función es un bloque nominado
PL/SQL que retorna un valor.• Puede ser almacenado como un objeto
de la base de datos.• Puede ser llamada como parte de una
expresión.
• Una función es un bloque nominado PL/SQL que retorna un valor.
• Puede ser almacenado como un objeto de la base de datos.
• Puede ser llamada como parte de una expresión.
CREATE [OR REPLACE] FUNCTION nombre_funcion(argumento1 [modo1] tipo_de_datos1,argumento2 [modo2] tipo_de_datos2,. . .
RETURN tipo_de_datosIS|ASBloque PL/SQL;
![Page 101: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/101.jpg)
PL/SQL-101
Función almacenada: EjemplosFunción almacenada: EjemplosSQL> CREATE OR REPLACE FUNCTION obten_sal2 (v_id IN emp.empno%TYPE)3 RETURN NUMBER4 IS5 v_salario emp.sal%TYPE :=0;6 BEGIN7 SELECT sal INTO v_salary8 FROM emp WHERE empno = v_id;9 RETURN (v_salary);
10 END get_sal;11 /
FUNCTION tax (v_value IN NUMBER)RETURN NUMBER
ISBEGIN
RETURN (v_value * .08);END tax;
SQL*
Plus
SQL*
Plus
Proc
edur
e B
uild
erPr
oced
ure
Bui
lder
![Page 102: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/102.jpg)
PL/SQL-102
Ejecutando Funciones:EjemplosEjecutando Funciones:EjemplosSQL> START obten_salario.sqlProcedure created.
SQL> VARIABLE g_salario number
SQL> EXECUTE :g_salario := obten_sal(7934)PL/SQL procedure successfully completed.
SQL> PRINT g_salarioG_SALARIO
------------------1300
PL/SQL> .CREATE NUMBER x PRECISION 4PL/SQL> :x := tasa(1000);PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x));80
SQL*
Plus
SQL*
Plus
Proc
edur
e B
uild
erPr
oced
ure
Bui
lder
![Page 103: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/103.jpg)
PL/SQL-103
Llamada a Funciones desde expresiones: RestriccionesLlamada a Funciones desde expresiones: Restricciones
• Una función definida por el usuario debe ser almacenada, y no de grupo.
• Una función definida por el usuario sólo puede utilizar parámetros IN.
• Tipos de datos CHAR, DATE, or NUMBER, no tipos PL/SQL tales como BOOLEAN, RECORD, or TABLE.
• El tipo de retorno debe ser un tipo interno de Oracle Server.
• No se permiten INSERT, UPDATE, or DELETE.• No se permiten llamadas a subprogramas que
violen las restricciones anteriores.
• Una función definida por el usuario debe ser almacenada, y no de grupo.
• Una función definida por el usuario sólo puede utilizar parámetros IN.
• Tipos de datos CHAR, DATE, or NUMBER, no tipos PL/SQL tales como BOOLEAN, RECORD, or TABLE.
• El tipo de retorno debe ser un tipo interno de Oracle Server.
• No se permiten INSERT, UPDATE, or DELETE.• No se permiten llamadas a subprogramas que
violen las restricciones anteriores.
![Page 104: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/104.jpg)
PL/SQL-104
Borrando FuncionesBorrando Funciones
Usando SQL*Plus• Sintaxis
• Ejemplo
Usando SQL*Plus• Sintaxis
• EjemploSQL> DROP FUNCTION obten_salario;Function dropped.
DROP FUNCTION nombre_funcion
![Page 105: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/105.jpg)
PaquetesPaquetes
![Page 106: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/106.jpg)
PL/SQL-106
PaquetesPaquetes• Grupo lógico que relaciona tipos, items
y subprogramas.• Consiste de dos partes:
– Especificación– Cuerpo
• No puede ser llamado, parametrizado o anidado.
• Oracle permite leer múltiples objetos en memoria simultáneamente.
• Grupo lógico que relaciona tipos, items y subprogramas.
• Consiste de dos partes:– Especificación– Cuerpo
• No puede ser llamado, parametrizado o anidado.
• Oracle permite leer múltiples objetos en memoria simultáneamente.
![Page 107: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/107.jpg)
PL/SQL-107
Ventajas de los paquetesVentajas de los paquetes
• Modularidad• Fácil diseño de la aplicación• Ocultación de la información• Funcionalidad añadida• Mejor rendimiento• Sobrecarga
• Modularidad• Fácil diseño de la aplicación• Ocultación de la información• Funcionalidad añadida• Mejor rendimiento• Sobrecarga
![Page 108: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/108.jpg)
PL/SQL-108
Desarrollando un paqueteDesarrollando un paquete
DeclaraciónProcedimiento A
DefiniciónProcedimiento B
Especificacióndel paquete
Cuerpo delpaquete
DefiniciónProcedimiento A
Variables públicas
Procedimientopúblico
Procedimientoprivado
Variables globales
ProcedimientopúblicoVariables
locales
![Page 109: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/109.jpg)
PL/SQL-109
CREATE [OR REPLACE] PACKAGE nombre_paqueteIS | AS
declaracione de items y tipos publicosespecificaciones de subprogramas
END nombre_paquete;
Especificación del paqueteEspecificación del paquete
SintaxisSintaxis
• Una especificación puede existir sin cuerpo pero no al contrario.
• No puede existir un procedimiento en un paquete e individual simultáneamente.
• Una especificación puede existir sin cuerpo pero no al contrario.
• No puede existir un procedimiento en un paquete e individual simultáneamente.
![Page 110: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/110.jpg)
PL/SQL-110
Especificación del paquete: Ejemplo
Especificación del paquete: Ejemplo
SQL>CREATE OR REPLACE PACKAGE com_package IS
2 g_com NUMBER := 10; --inicializado a 10
3 PROCEDURE reset_com
4 (v_com IN
NUMBER);
5 END com_package;
6 /
SQL>EXECUTE com_package.g_com := 5
SQL>EXECUTE com_package.reset_com(8)
![Page 111: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/111.jpg)
PL/SQL-111
Cuerpo del paqueteCuerpo del paqueteSintaxisSintaxisCREATE [OR REPLACE] PACKAGE BODY nombre_paqueteIS | AS
declaraciones de items y tipos privadoscuerpos de subprogramas
END nombre_paquete;
![Page 112: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/112.jpg)
PL/SQL-112
Cuerpo del paquete: EjemploCuerpo del paquete: Ejemplo
SQL>CREATE OR REPLACE PACKAGE BODY com_package IS2 FUNCTION valida_com3 (v_com IN NUMBER) RETURN BOOLEAN4 IS5 v_max_com NUMBER;6 BEGIN7 SELECT MAX(com)8 INTO v_max_com9 FROM emp;
10 IF v_com > v_max_com THEN RETURN(FALSE);11 ELSE RETURN(TRUE);12 END IF;13 END valida_com;14 END com_package;15 /
![Page 113: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/113.jpg)
PL/SQL-113
SQL>PROCEDURE reset_com2 (v_com IN NUMBER)3 IS4 v_validoBOOLEAN;5 BEGIN6 v_valido := valida_com(v_com);7 IF v_valido = TRUE THEN8 g_com := v_com;9 ELSE
10 RAISE_APPLICATION_ERROR11 (-20210,‘ Comisión no válida');12 END IF;13 END reset_com;14 END com_package;15 /
Cuerpo del paquete: EjemploCuerpo del paquete: Ejemplo
![Page 114: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/114.jpg)
PL/SQL-114
SQL>EXECUTE com_package.reset_com(1500);
SQL> EXECUTE scott.com_package.reset_com(1500);
SQL> EXECUTE com_package.reset_com@ny (1500);
Ejemplo 1Ejemplo 1
Invocando constructores del paquete
Invocando constructores del paquete
![Page 115: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/115.jpg)
PL/SQL-115
Invocando constructores del paquete
Invocando constructores del paquete
Ejemplo 2Ejemplo 2CREATE OR REPLACE PROCEDURE contrato_emp
(v_nombre IN emp.nombre%TYPE,v_mgr IN emp.mgr%TYPE,v_trabajoIN emp.trabajo%TYPE,v_sal IN emp.sal%TYPE)
ISv_com emp.com%TYPE;
. . .BEGIN. . .
v_com := com_package.g_com;. . .END contrato_emp;
![Page 116: Programacion en PL_SQL](https://reader033.vdocumento.com/reader033/viewer/2022051114/55cf987e550346d03397f895/html5/thumbnails/116.jpg)
PL/SQL-116
DROP PACKAGE nombre_paquete
Borrando paquetesBorrando paquetesPara borrar la especificación y cuerpo del paquete:Para borrar la especificación y cuerpo del paquete:
Para borrar el cuerpo del paquete:DROP PACKAGE BODY nombre_paquete