Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Introduccioacuten a PLSQL

PLSQL es un lenguaje de programacioacuten estructurado Es un lenguaje procedimental que ampliacutea la funcionalidad de SQL antildeadiendo estructuras habituales en otros lenguajes de programacioacuten entre las que se encuentran

bull Variables y Tiposbull Estructuras de controlbull Procedimientos y Funcionesbull Tipos de Objetos y Meacutetodos

La unidad baacutesica en PLSQL es el bloque Todos los programas PLSQL estaacuten compuestos por bloques que pueden estar anidados Un bloque PLSQL estaacute compuesto de tres partes principales

bull Seccioacuten declarativa (opcional) Contiene las variables constantes bull Seccioacuten ejecutable (obligatoria) Contiene oacuterdenes SQL para manipular

datosbull De la base de datos y oacuterdenes PLSQL para manipular los datos del

bloquebull Seccioacuten de excepciones (opcional) Especifica las acciones a realizar en

casobull de error o cuando se producen excepciones en la ejecucioacuten

La estructura general es

[DECLAREvariables constantes excepciones de usuario]

BEGINoacuterdenes SQLoacuterdenes PLSQL

[EXCEPTIONacciones a realizar al ocurrir un error]

ENDPara ejecutar un bloque PLSQL siempre hay que colocar al final la barra

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Podemos crear diferentes tipos de bloques

bull Bloques anoacutenimos Se construyen de forma dinaacutemica y se suelen ejecutar una sola vez

bull Bloques nominados Igual que los anoacutenimos pero con una etiqueta que les da nombre

bull Subprogramas Procedimientos paquetes y funciones almacenados en la base de datos y que se ejecutan en muacuteltiples ocasiones Los subprogramas se ejecutaraacuten mediante una llamada

bull Disparadores (ldquoTriggersrdquo) Bloques nominados que se almacenan en la base de datos y que se ejecutan ante alguacuten suceso

Para poner nombre a un bloque se le pone una etiqueta antes del DECLARE encerrado por ltltgtgt Por ejemplo para darle el nombre ldquoMi_Bloquerdquo a un bloque PLSQL pondriacuteamos

ltltMi_Bloquegtgt DECLAREhellipBEGINhellipEND

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Declaracioacuten de Identificadores PLSQL

Variables

Identificadores

Un identificador es un nombre para las unidades de PLSQL las cuales pueden ser

bull Constantes bull Variables bull Excepciones bull Cursores bull Variables de cursor bull Sub programas bull Paquetes

Un identificador consiste de letras seguidas de manera opcional por maacutes letras numerales underscores hasta un maacuteximo de 30 caracteres ademaacutes que debe comenzar con una letra Otros caracteres como guiones (-) diagonales () y espacios no son aceptados

Ejemplos invaacutelidos

Hombreampmujeres no es valido por el ampersand

Debito-total no es valido por el guioacuten

Onoff no es valido por la diagonal

User id no es valido por los espacios

`

Ejemplos validos

Total$endolares

NoEmp

User_id

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

El nombre del identificador puede ir en mayuacutesculas o minuacutesculas para PLSQL no hay diferencia al respecto

Los siguientes significan lo mismo

Apellido_parterno

APELLIDO_PATERNO

Palabras reservadas

Algunos identificadores llamados palabras reservadas tienen un significado especial para PLSQL Por ejemplo las palabras BEGIN y END son reservadas

Identificadores entre comillas ldquordquo

Para flexibilidad PLSQL permite tener identificadores entre comillas

Ejemplos

ldquoX+Yrdquo

ldquoapellido paternordquo

ldquoonoffrdquo

Aunque anterior mente se menciono que no era posible tener caracteres ldquorarosrdquo en el nombre en el caso que se utilice un identificador entre comillas este puede contener cualquier caraacutecter

Las variables se definen en la seccioacuten declarativa de los bloques PLSQL doacutende tambieacuten pueden inicializarse

La asignacioacuten de nuevos valores a las variables puede hacerse en la parte ejecutable del bloque

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Pueden utilizarse para pasar valores como argumentos a subprogramas Estas podraacuten ser de tipo IN (variable de entrada OUT variable de salida o INOUT variable de entradasalida)

Tambieacuten podraacuten utilizarse para almacenar valores devueltos o requeridos por una orden SQL Todas las variables tienen un tipo Los posibles tipos de una variable son

bull Escalar Almacenan un valor uacutenico Son los mismos que los de las columnas de las tablas (VARCHAR2 NUMBER DATE CHAR LONG LONG_RAW

bull BINARY_INTEGER LAW_INTEGER) maacutes el BOOLEAN

bull Compuesto Grupos de datos tablas PLSQL registros

bull Puntero Designan elementos de otros programas

bull LOB (Large OBjects) Almacenan gran cantidad de informacioacuten Las variables de tipo LOB permiten almacenar datos no estructurados (imaacutegenes texto) de hasta 4 GB de tamantildeo

Declaracioacuten de variables

Sintaxisltidentificadorgt [CONSTANT] lttipo_de_datogt [NOT NULL] [= | DEFAULTltexpresioacutengt]

Ejemplo

DECLAREfecha DATEdep_num NUMBER(2) NOT NULL = 10ciudad VARCHAR2(10) = lsquoCiudad RealrsquoKm_a_milla CONSTANT NUMBER = 14

Las variables declaradas como NOT NULL siempre deben ser inicializadas

bull La inicializacioacuten puede hacerse utilizando = o la palabra reservada DEFAULT

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

bull Si una variable no se inicializa contendraacute el valor NULL

bull Las constantes deben ser inicializadas

bull Asignacioacuten de valores a variables

Sintaxisltidentificadorgt = ltvalorgt

Atributo TYPE

El atributo TYPE se utiliza para declarar una variable con el mismo tipo que una columna de una tabla o que otra variable definida anteriormente

Sintaxisltidentificadorgt lttablagtltcolumnagt | ltnombre_variablegtTYPE

Ejemplo

var_nombre EmpleadosnombreTYPEbalance NUMBERbalance_minimo balanceTYPE = 10

Variables BOOLEANAS

Las variable BOOLEANAS pueden tomar el valor TRUE FALSE o NULL Las variables pueden combinarse mediante operadores loacutegicos (NOT AND OR)

Las expresiones pueden devolver valores BOOLEANOS utilizando operadores

relacionales (lt lt=)

Expresiones y operadores

Al igual que otros lenguajes la unioacuten de variables mediante operadores aritmeacuteticos y loacutegicos forman las expresiones aritmeacuteticas y loacutegicas a continuacioacuten estos operadores

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Operador de asignacioacuten

= (dos puntos + igual)

Operadores aritmeacuteticos

+ (suma)

- (resta)

(multiplicacioacuten)

(divisioacuten)

(exponente)

Operadores relacionales o de comparacioacuten

= (igual a)

ltgt= (distinto de)

lt (menor que)

gt (mayor que)

gt= (mayor o igual a)

lt= (menor o igual a)

Operador de concatenacioacuten

||

Comentarios comentario de dos o maacutes liacuteneas

-- comentario de una liacutenea

Identificacioacuten de Estructura de Bloque PLSQL

Estructuras de control

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Estructura de Seleccioacuten IF

Su Sintaxis es la siguiente

IF ltexpresioacuten1gt THENltSecuencia_ordenes1gt[ELSIF ltexpresioacuten2gt THENltSecuencia_ordenes2gt]hellip[ELSEltSecuencia_ordenesNgt]END IF

Ejemplo

SET ServerOutput ONSET VERIFY OFFDECLAREv_num NUMBER = ampvBEGIN IF v_num lt 50 THEN DBMS_OUTPUTPUT_LINE(Valor pequentildeo) ELSIF v_num lt 100 THEN DBMS_OUTPUTPUT_LINE(Valor mediano) ELSE DBMS_OUTPUTPUT_LINE(Valor grande) END IFEND

Orden LOOP

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Los bucles LOOP son bucles que se utilizan para ejecutan instrucciones repetitivas para salir de ellos tendremos que poner una instruccioacuten de salida dentro del bucle

Su Sintaxis es

LOOP [EXIT WHEN ltcondicioacutengt]END LOOP

Ejemplo

SET ServerOutput ONSET VERIFY OFFDECLAREnum NUMBER =1BEGIN LOOP DBMS_OUTPUTPUT_LINE(Valor || num) num = num +1 EXIT WHEN num gt 10 END LOOPEND

Oacuterden FOR

Los bucles FOR se repiten un nuacutemero determinado de veces

SintaxisFOR ltcontadorgt IN ltmingtltmaxgt [REVERSE] LOOPEND LOOP

EjemploSET ServerOutput ONSET VERIFY OFFDECLAREnum NUMBERBEGIN

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

FOR num IN 110 LOOP DBMS_OUTPUTPUT_LINE(Valor || num) END LOOPEND

Oacuterden WHILE

Los bucles WHILE son iguales que en otro lenguajes de programacioacuten

SintaxisWHILE ltcondiciongt LOOPEND LOOP

EjemploSET ServerOutput ONSET VERIFY OFFDECLAREnum NUMBER=1BEGIN WHILE num lt=10 LOOP DBMS_OUTPUTPUT_LINE(Valor || num) num = num + 1 END LOOPEND

Tipos de datos compuestos

Los tipos de datos compuestos o colecciones pueden ser o bien registros PLSQL o bien tablas PLSQL

REGISTRO PLSQL

Un registro PLSQL es un grupo de elementos de datos relacionados en campos Cada uno con su propio nombre

Caracteriacutesticasbull Cada registro debe tener al menos un campo y tantos como sea necesariobull Un registro NO es una fila de una tabla de sqlbull Son tratados como unidades loacutegicas

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

bull Son apropiados para recuperar el contenido de una fila de una tabla sqlbull Son tipos de variables Por lo que hay que declarar primero el tipo de

variable para despueacutes poder usarla

SINTAXISTYPE nombre IS RECORD (DECLARACIOacuteN DE CAMPOS)donde (declaracioacuten de campos) es

nombre_de_campo tipo de dato donde tipo de dato puede ser nombre_de_campotipo de dato | variableTYPE | tablacolumnaTYPE| tablaROWTYPE [ [NOT NULL]= | DEFAULT expresion]

EJEMPLODECLARE TYPE currante_tipo_de_record IS RECORD

(nombre varchar2(20)curro varchar2(10)cobra number(72)) -- hasta aqui he declarado el tipo de variable

currantes currante_tipo_de_record -- aqui uso la variableBEGIN

SELECT first_name job_id salaryINTO currantesFROM employeesWHERE employee_id = 200

END

ESTRUCTURA DEL REGISTRO PLSQL

A los campos de un registro plsql se accede por el nombre Asiacute en el registro usado en el ejemplo anterior se accederiacutea mediante currantescobra

ROWTYPE

Es lo mismo que TYPE pero en este caso sirve para declarar que una variable es igual que una liacutenea de una tabla O sea que la variable contiene tantos campos y del mismo tipo que una tabla Tiene la ventaja que no tienes que declarar el tipo de variable y puedes usarlo directamente Ademaacutes de

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

estar seguro de que no vas a tener problemas de tipos distintos entre el origen de los datos y tu registro

EJEMPLODECLARE currantes employeesROWTYPE -- aqui uso la variableBEGIN SELECT INTO currantes FROM employees WHERE employee_id = 200END

TABLAS PLSQL

Una tabla PLSQL es un como una tabla normal de la base de datos con las siguientes caracteriacutesticas

bull Debe contener una clave primaria del tipo BINARY_INTEGERbull Debe contener una columna de tipo escalar o de registro

SINTAXISTYPE nombre_de_tipo_de_tabla IS TABLE OFtipo de columna | variableTYPE | tablacolumnaTYPE [NOT NULL] INDEX BY BINARY_INTEGER

EJEMPLOSET PAGESIZE 100000DECLARE TYPE tabla_como_empleados IS TABLE OF employeesROWTYPE INDEX BY BINARY_INTEGER currelas tabla_como_empleados c_max INTEGERBEGIN select count() into c_max from employees DBMS_OUTPUTPUT_LINE() DBMS_OUTPUTPUT_LINE(number of employees || c_max) -- start of the bucle FOR i IN 1c_max LOOP -- tantas vueltas como valores

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

SELECT INTO currelas(i) FROM employees -- en cada vuelta inserto una liacutenea WHERE employee_id=(99+i) -- el primer empleado es el 100 DBMS_OUTPUTPUT_LINE(getting employe || (99+i)) END LOOP--Desplegando employees FOR i IN 1c_max LOOP -- tantas vueltas como valores DBMS_OUTPUTPUT_LINE(currelas(i)last_name) END LOOPEND

Uso de Cursores

Cursores

Los cursores son aacutereas de trabajo que permiten ejecutar sentencias SQL y procesar la informacioacuten obtenida de ellos

Hay dos tipos de cursores impliacutecitos y expliacutecitos PLSQL declara impliacutecitamente un cursor para todas las sentencias de manipulacioacuten de datos incluyendo las consultas que retornan soacutelo una fila Para consultas que devuelven maacutes de una fila es posible declarar expliacutecitamente un cursor que procese las filas en forma individual

Sintaxis-

CURSOR Nombre_Cursor|(Par1 tipo Par2 Tipo Par3 Tipo Parn Tipo) IS Consulta_SQLPor ejemploDECLARECURSOR curs_01 IS

Ejemplo

CURSOR Empleados_Dpto10 IS SELECT empno ename job FROM emp WHERE deptno=20

El conjunto de filas retornado se denomina set de resultados Su tamantildeo estaacute determinado por el nuacutemero de filas que calzan con el criterio de seleccioacuten de la consulta que implementa el cursor Las filas son procesadas de a una cada vez

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

La figura anterior ilustra la recuperacioacuten de filas a traveacutes de un cursor

Apertura de un Cursor-

La Setencia OPEN abre y prepara un cursor para ser usado

OPEN Nombre_Cursorordm | (Par1 Par2 Par3 ParN)

Ejemplo

OPEN Empleados_Dpto10

Pasando columnas de un cursor a Variables

FETCH Nombre_Cursor INTO Var1 Var2 Var3hellipVarN

Ejemplo

FETCH Empleados_Dpto10 INTO v_empno v_ ename v_job

Uso de NOTFOUND

Este Atributo toma valor verdadero (TRUE) cuando ya no quedan mas filas que recuperar en el cursor y Falso (FALSE) cuando aun quedan filas

Ejemplo

IF Empleados_Dpto10NOTFOUND THEN EXIT END IF

Uso de ISOPEN

El mismo toma el valor verdadero (TRUE) cuando un cursor se encuentra abierto De otra manera retorna FALSO

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

IF Empleados_Dpto10ISOPEN THEN CLOSE Empleados_Dpto10END IF

Uso de ROWCOUNT

Cuando un cursor es abierto este atributo es seteado en 0 (cero) En adelante cada vez que se recuperen filas exitosamente con un FETCH este valor se iraacute incrementando en uno

Cuando se utiliza con cursores impliacutecitos este atributo devuelve el total de filas afectadas por una instruccioacuten del tipo INSERT UPDATE o DELETE

Cierre de un Cursor

La sentencia que deshabilita un cursor CLOSE se utiliza de la siguiente manera

CLOSE cursor

Una vez que un cursor ya ha sido cerrado es posible volverlo a abrir sin tener que declararlo otra vez Cualquier otra operacioacuten que se desee efectuar sobre un cursor no operativo (cerrado) provocaraacute una excepcioacuten del tipo invalid_cursor

Ejemplo

DECLARE V_Ename EmpEnameTYPE V_Sal EmpSalTUPECURSOR Cur01 ISSELECT Ename SalFROM EmpBEGINOPEN Cur01LOOPFETCH Cur01 INTO V_Ename V_SalEXIT WHEN Cur01NOTFOUNDINSERT INTO Emp2 VALUES(V_Ename V_Sal)END LOOPCLOSE Cur01

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

END

Manejo de Excepciones

Excepciones predefinidas

Las excepciones predefinidas no necesitan ser declaradas Simplemente se utilizan cuando estas son gatilladas por alguacuten error determinado

La siguiente es la lista de las excepciones predeterminadas por PLSQL y una breve descripcioacuten de cuaacutendo son accionadas

Nombre Excepcioacuten Gatillada cuandohellip SQLCODE

ACCESS_INTO_NULL El programa intentoacute asignar valores a los atributos de un objeto no inicializado

-6530

COLLECTION_IS_NULL El programa intentoacute asignar valores a una tabla anidada auacuten no inicializada

-6531

CURSOR_ALREADY_OPEN El programa intentoacute abrir un cursor que ya se encontraba abierto Recuerde que un cursor de ciclo FOR automaacuteticamente lo abre y ello no se debe especificar con la sentencia OPEN

-6511

DUP_VAL_ON_INDEX El programa intentoacute almacenar valores duplicados en una columna que se mantiene con restriccioacuten de integridad de un iacutendice uacutenico (unique index)

-1

INVALID_CURSOR El programa intentoacute efectuar -1001

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

una operacioacuten no vaacutelida sobre un cursor

INVALID_NUMBER En una sentencia SQL la conversioacuten de una cadena de caracteres hacia un nuacutemero falla cuando esa cadena no representa un nuacutemero vaacutelido

-1722

LOGIN_DENIED El programa intentoacute conectarse a Oracle con un nombre de usuario o password invaacutelido

-1017

NO_DATA_FOUND Una sentencia SELECT INTO no devolvioacute valores o el programa referencioacute un elemento no inicializado en una tabla indexada

+100

NOT_LOGGED_ON El programa efectuoacute una llamada a Oracle sin estar conectado

-1012

PROGRAM_ERROR PLSQL tiene un problema interno

-6501

ROWTYPE_MISMATCH Los elementos de una asignacioacuten (el valor a asignar y la variable que lo contendraacute) tienen tipos incompatibles Tambieacuten se presenta este error cuando un paraacutemetro pasado a un subprograma no es del tipo esperado

-6504

SELF_IS_NULL El paraacutemetro SELF (el primero que es pasado a un meacutetodo MEMBER) es nulo

-30625

STORAGE_ERROR La memoria se terminoacute o estaacute corrupta

-6500

SUBSCRIPT_BEYOND_COUNT El programa estaacute tratando de -6533

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

referenciar un elemento de un arreglo indexado que se encuentra en una posicioacuten maacutes grande que el nuacutemero real de elementos de la coleccioacuten

SUBSCRIPT_OUTSIDE_LIMIT El programa estaacute referenciando un elemento de un arreglo utilizando un nuacutemero fuera del rango permitido (por ejemplo el elemento -1)

-6532

SYS_INVALID_ROWID La conversioacuten de una cadena de caracteres hacia un tipo rowid falloacute porque la cadena no representa un nuacutemero

-1410

TIMEOUT_ON_RESOURCE Se excedioacute el tiempo maacuteximo de espera por un recurso en Oracle

-51

TOO_MANY_ROWS Una sentencia SELECT INTO devuelve maacutes de una fila

-1422

VALUE_ERROR Ocurrioacute un error aritmeacutetico de conversioacuten o truncamiento Por ejemplo sucede cuando se intenta calzar un valor muy grande dentro de una variable maacutes pequentildea

-6502

ZERO_DIVIDE El programa intentoacute efectuar una divisioacuten por cero

-1476

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Excepciones definidas por el usuario

PLSQL permite al usuario definir sus propias excepciones las que deberaacuten ser declaradas y gatilladas expliacutecitamente utilizando otros comandos del lenguaje

Declaracioacuten

Las excepciones soacutelo pueden ser declaradas en el segmento Declare de un bloque subprograma o paquete Se declara una excepcioacuten escribiendo su nombre seguida de la palabra clave EXCEPTION Las declaraciones son similares a las de variables pero recuerde que una excepcioacuten es una condicioacuten de error no un iacutetem de datos Aun asiacute las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones

Ejemplo

DECLAREerror_01 EXCEPTION

Reglas de Alcance

Una excepcioacuten no puede ser declarada dos veces en un mismo bloque Tal como las variables una excepcioacuten declarada en un bloque es local a ese bloque y global a todos los sub-bloques que comprende

La sentencia RAISE

La sentencia RAISE permite gatillar una excepcioacuten en forma expliacutecita Es factible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepcioacuten

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Ejemplo

DECLAREout_of_stock EXCEPTION -- declaracioacuten de la excepcioacutentotal NUMBER(4)BEGINhellipIF total lt 1 THENRAISE out_of_stock -- llamado a la excepcioacutenEND IFEXCEPTIONWHEN out_of_stock THEN-- manejar el error aquiacuteWHEN OTHERS THENhellipEND

Finalmente cabe destacar la existencia de la excepcioacuten OTHERS que simboliza cualquier condicioacuten de excepcioacuten que no ha sido declarada Se utiliza comuacutenmente al final del bloque de excepciones para absorber cualquier tipo de error que no ha sido previsto por el programador En ese caso es comuacuten observar la sentencia ROLLBACK en el grupo de sentencias de la excepcioacuten o alguna de las funciones SQLCODE ndash SQLERRM que se detallan en el proacuteximo punto

Uso de SQLCODE y SQLERRM

Al manejar una excepcioacuten es posible apoyarse con las variables predefinidas SQLCode y SQLErrm para aclarar al usuario la situacioacuten de error acontecida

Sqlcode siempre retornaraacute el nuacutemero del error de Oracle y un 0 (cero) en caso exitoso al ejecutarse una sentencia SQL

Por otra parte Sqlerrm retornaraacute el correspondiente mensaje de error para la situacioacuten ocurrida Tambieacuten es posible entregarle a la funcioacuten SQLERRM un nuacutemero negativo que represente un error de Oracle y eacutesta devolveraacute el mensaje asociado

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Estas funciones son muy uacutetiles cuando se utilizan en el bloque de excepciones para aclarar el significado de la excepcioacuten OTHERS cuando eacutesta ocurre

Estas funciones no pueden ser utilizadas directamente en una sentencia SQL pero siacute se puede asignar su valor a alguna variable de programa y luego usar esta uacuteltima en alguna sentencia

Ejemplo

DECLAREerr_num NUMBERerr_msg VARCHAR2(100)BEGINhellipEXCEPTIONWHEN OTHERS THENerr_num = SQLCODEerr_msg = SUBSTR(SQLERRM 1 100)INSERT INTO errores VALUES(err_num err_msg)END

Creacioacuten de Procedimientos Almacenados

Para crear procedimientos almacenas se utiliza la Sentencia CREATE los procedimientos pueden recibir o devolver valores a traveacutes de paraacutemetros que se definen en la misma creacioacuten del procedimiento y la sintaxis es como sigue

CREATE PROCEDURE

Esta sentencia se utiliza para crear un procedimiento almacenado en la base de datos y su sitaxis es

CREATE [OR REPLACE] PROCEDURE Nombre_Procedimiento

[(Par1 IN OUT Tipo_Dato

Par2 IN OUT Tipo_Dato

Par3 IN OUT Tipo_Dato

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

ParN IN OUT Tipo_Dato)] IS

NOTA [OR REPLACE] es Condicional si no se ha creado lo crea y si ya esta creado lo reemplaza

Funciones definidas por el Usuario

Ademaacutes de las muacuteltiples funciones que posee ORACLE es posible crear tus propias funciones y utilizarlas tanto en una consulta como en procedimientos dichas funciones pueden ser Procedimentales o no las primeras devuelven mas de un valor y solo pueden ser utilizadas en Procedimientos u otras funciones las funciones normales devuelven un solo valor y por tanto pueden ser utilizadas en las consultas

CREATE FUNCTION

Esta se utiliza para crear funciones la sintaxis es como sigue

CREATE [OR REPLACE] FUNTION Nombre_Funcion (Par1 IN OUT Par2 IN OUT Par3 IN OUT ParN IN OUT)

RETURN Tpo_Dato_Returna IS

Existen dos tipos de funciones

Funciones Procedimentales

Estas pueden retornar maacutes de un valor pero solo pueden ser utilizadas dentro de procedimientos triguers y otras funciones

CREATE [OR REPLACE] FUNCTION Nombre_Funcion (Par1 IN OUT Par2 IN OUT Par3 IN OUT ParN IN OUT)

RETURN Tipo_dato_Retorna IS

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Funciones que retornan un solo valor

Estas pueden ser usadas en una sentencia SQL otros procedimientos Triguers y otras funciones

CREATE [OR REPLACE] FUNCTION Nombre_Funcion (Par1 IN Par2 IN Par3 IN ParN IN)

RETURN Tipo_Dato_Retorna IS

Creacioacuten de Disparadores

Definicioacuten de trigger

Los disparadores son procedimientos que se ejecutan cuando se produce un suceso de base de datos (una operacioacuten DML INSERT UPDATE o DELETE) en una tabla especiacutefica El acto de ejecutar un disparador se conoce como disparo

Uso de los triggers

Los disparadores pueden emplearse para muchas cosas diferentes incluyendo

1El mantenimiento de restricciones de integridad complejas que no sean posibles con las restricciones declarativas definidas en el momento de crear la tabla

2La auditoriacutea de la informacioacuten contenida en una tabla registrando los cambios realizados y la identidad del que los llevoacute a cabo

3El aviso automaacutetico a otros programas de que hay que llevar a cabo una determinada accioacuten cuando se realiza un cambio en una tabla

Sintaxis general de trigger

La sintaxis general para crear un disparador es

CREATE [OR REPLACE] TRIGGER nombre_disparador

BEFORE | AFTER suceso_disparo ON referencia_tabla

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

[FOR EACH ROW [ WHEN condicioacuten_disparo ]]

cuerpo_disparador

Donde

Nombre_disparador es el nombre del disparador

Suceso_disparo especifica cuaacutendo se activa el disparador

Referencia_tabla es la tabla para la cual se define el disparador y

cuerpo_disparador es el coacutedigo principal del disparador Antes se evaluacutea la

condicioacuten_disparo incluida en la claacuteusula WHEN si es que estaacute presente El cuerpo del disparador se ejecuta soacutelo cuando dicha condicioacuten se evaluacutea como verdadera

Componentes de un disparador

Los componentes de un disparador son el nombre el suceso de disparo y el cuerpo La claacuteusula WHEN es opcional

Nombres de disparadores

El espacio de nombres para los disparadores es diferente del de otros subprogramas El espacio de nombres es el conjunto de identificadores vaacutelidos que pueden emplearse como nombres de un objeto

Los disparadores existen en un espacio de nombres separado del de los procedimientos paquetes y tablas por lo que un disparador puede tener el mismo nombre que una tabla o procedimiento Sin embargo dentro de un mismo esquema deben tener nombres diferentes entre siacute

Tipos de disparadores

El suceso de disparo determina el tipo de disparador Los disparadores pueden definirse para las operaciones INSERT UPDATE o DELETE y

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

pueden dispararse antes o despueacutes de la operacioacuten Finalmente el nivel de los disparadores puede ser la fila o la orden

La Figura 1 muestra los tipos de disparadores

Categoriacutea

Valores

Comentarios

Orden

INSERT DELETE UPDATE

Define queacute tipo de orden DML provoca la activacioacuten del disparador

Temporizacioacuten

BEFORE o AFTER

Define si el disparador se activa antes o despueacutes de que se ejecute la orden (disparador previo o posterior)

Nivel

Fila u orden

Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocoacute el disparo Los disparadores con nivel de orden se activan soacutelo una vez antes o despueacutes de la orden Los disparadores con nivel de fila se identifican por la claacuteusula FOR EACH ROW en la definicioacuten del disparador

Figura 1 Tipos de disparadores [2]

Disparadores de sustitucioacuten

Oracle proporciona un tipo adicional de disparador Los disparadores de sustitucioacuten ( INSTEAD OF ) pero tienen una serie de restricciones [3]

INSTEAD OF es una claacuteusula vaacutelida solo para vistas no se puede especificar un disparador INSTEAD OF en una tabla

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Si una vista tiene un disparador INSTEAD OF cualquier vista creada sobre eacutesta debe tener a su vez un disparador INSTEAD OF

Cuando definimos disparadores INSTEAD OF para columnas LOB podemos leer tanto el seudo-registro OLD como el seudo-registro NEW pero no se puede modificar sus valores

Restricciones de los disparadores

El cuerpo de un disparador es un bloque PLSQL Cualquier orden que sea legal en un bloque PLSQL es legal en el cuerpo de un disparador con las siguientes restricciones

1Un disparador no puede emitir ninguna orden de control de transacciones COMMIT ROLLBACK o SAVEPOINT

2El disparador se activa como parte de la ejecucioacuten de la orden que provocoacute el disparo y forma parte de la misma transaccioacuten que dicha orden Cuando la orden que provoca el disparo es confirmada o cancelada se confirma o cancela tambieacuten el trabajo realizado por el disparador

3Por razones ideacutenticas ninguacuten procedimiento o funcioacuten llamado por el disparador puede emitir oacuterdenes de control de transacciones

4El cuerpo del disparador no puede contener ninguna declaracioacuten de variables LONG o LONG RAW

5Existen restricciones acerca de a queacute tablas puede acceder el cuerpo de un disparador Dependiendo del tipo de disparador y de las restricciones que afecten a las tablas

Eliminacioacuten y deshabilitacioacuten de los disparadores

La sintaxis de la orden que elimina un disparador es

DROP TRIGGER nombre_disparador

donde nombre_disparador es el nombre del disparador que se desea eliminar Esta orden elimina el disparador de forma permanente del diccionario de datos

La sintaxis de la orden que deshabilita un disparador es

ALTER TRIGGER nombre_disparador [DISABLE | ENABLE]

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

donde nombre_disparador es el nombre del disparador Todos los disparadores estaacuten habilitados en el momento de su creacioacuten Cuando se deshabilita un disparador continua existiendo en el diccionario de datos

Tambieacuten se puede habilitar o deshabilitar todos los disparadores de una tabla determinada utilizando la orden ALTER TABLE con la claacuteusula ENABLE ALL TRIGGERS o DISABLE ALL TRIGGERS

1Orden de activacioacuten de los disparadores

Los disparadores se activan al ejecutarse la orden DML El algoritmo de ejecucioacuten de una orden DML es el siguiente

2Ejecutar si existe el disparador de tipo BEFORE (disparador previo) con nivel de orden

Para cada fila a la que afecte la orden

Ejecutar si existe el disparador de tipo BEFORE con nivel de fila

Ejecutar la propia orden

Ejecutar si existe el disparador de tipo AFTER (disparador posterior) con nivel de fila

Ejecutar si existe el disparador de tipo AFTER con nivel de orden

Utilizacioacuten de old y new en los disparadores con nivel de fila

Un disparador con nivel de fila se ejecuta una vez por cada fila procesada por la orden que provoca el disparo Dentro del disparador puede accederse a la fila que estaacute siendo actualmente procesada utilizando para ello dos seudo-registros old y new

La Figura 2 muestra este concepto

Orden de disparo

old

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

new

INSERT

No definido todos los campos toman valor NULL

Valores que seraacuten insertados cuando se complete la orden

UPDATE

Valores originales de la fila antes de la actualizacioacuten

Nuevos valores que seraacuten escritos cuando se complete la orden

DELETE

Valores antes del borrado de la fila

No definidos todos los campos toman el valor NULL

Figura 2 old y new

El tipo de estos seudo-registros es

tabla_disparoROWTYPE

donde tabla_disparo es la tabla sobre la que se ha definido el disparador

Los registros new y old son soacutelo vaacutelidos dentro de los disparadores con nivel de fila Si se intenta Hacer referencia a cualquiera de los dos dentro de un disparador con nivel de orden se obtendraacute un error de compilacioacuten Puesto que un disparador con nivel de orden se ejecuta una sola vez incluso si la orden procesa varias filas entonces old y new no tienen ninguacuten sentido ya que no se sabe a que fila se estaacuten refiriendo

La claacuteusula WHEN

La claacuteusula WHEN soacutelo es vaacutelida para los disparadores con nivel de fila Si estaacute presente el cuerpo del disparador soacutelo se ejecutaraacute para las filas que cumplan la condicioacuten especificada en la claacuteusula

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

La claacuteusula WHEN tiene la forma

WHEN condicioacuten

Donde condicioacuten es una expresioacuten booleana que seraacute evaluada para cada fila Se puede hacer tambieacuten referencia a los registros new y old dentro de la condicioacuten pero en ese caso no se utilizan los dos puntos

Utilizacioacuten de predicados de los disparadores INSERTING UPDATING y DELETING

Dentro de un disparador en el que se disparan distintos tipos de oacuterdenes DML (INSERT UPDATE y DELETE) hay tres funciones booleanas que pueden emplearse para determinar de queacute operacioacuten se trata Estos predicados son INSERTING UPDATING y DELETING Su comportamiento es el siguiente

La Figura 3 muestra el comportamiento de INSERTING UPDATING y DELETING

Predicado

Comportamiento

INSERTING

TRUE si la orden de disparo es INSERT FALSE en otro caso

UPDATING

TRUE si la orden de disparo es UPDATE FALSE en otro caso

DELETING

TRUE si la orden de disparo es DELETE FALSE en otro caso

Figura 3 INSERTING UPDATING y DELETING [2]

Ejemplo

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Un ejemplo de su uso es proporcionar una facilidad de auditoria donde se realiza automaacuteticamente un registro de actividades siempre que se cambia una fila de una tabla Sin los disparadores de bases de datos esta funcioacuten seriacutea implementada en los programas de seccioacuten de entrada (front-end) que realizan el cambio en la base de datos sin embargo alguien que se pase por alto el coacutedigo de los programas de seccioacuten de entrada (utilizando SQLPlus por ejemplo) no pasariacutea por las comprobaciones y el procesamiento definidos

Su poacutengase que tenemos una tabla llamada SAL (en la que almacenamos los salarios de los empleados de la empresa) y resulta necesario conocer cuaacutendo esta siendo accedida la tabla y el tipo de operacioacuten que se realiza El ejemplo que presentamos a continuacioacuten contiene un paquete de muestra que rastrea esta informacioacuten registrando la hora y la accioacuten ejecutada (UPDATE DELETE o INSERT) en la tabla SAL Mediante una variable global STATROWCNT inicializada a cero por el trigger BEFORE e incrementada cada vez que un disparador con nivel de fila es ejecutado tenemos la informacioacuten estadiacutestica que necesitamos salvar en el disparador AFTER

DROP TABLE stat_tab

CREATE TABLE stat_tab(utype CHAR(8)

rowcnt INTEGER uhour INTEGER)

CREATE OR REPLACE PACKAGE stat IS

rowcnt INTEGER

END

CREATE TRIGGER bt BEFORE UPDATE OR DELETE OR INSERT ON sal

BEGIN

statrowcnt = 0

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

END

CREATE TRIGGER rt BEFORE UPDATE OR DELETE OR INSERT ON sal

FOR EACH ROW BEGIN

statrowcnt = statrowcnt + 1

END

CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON sal

DECLARE

typ CHAR(8)

hour NUMBER

BEGIN

IF updating

THEN typ = update END IF

IF deleting THEN typ = delete END IF

IF inserting THEN typ = insert END IF

hour = TRUNC((SYSDATE - TRUNC(SYSDATE)) 24)

UPDATE stat_tab

SET rowcnt = rowcnt + statrowcnt

WHERE utype = typ

AND uhour = hour

IF SQLROWCOUNT = 0 THEN

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

INSERT INTO stat_tab VALUES (typ statrowcnt hour)

END IF

EXCEPTION

WHEN dup_val_on_index THEN

UPDATE stat_tab

SET rowcnt = rowcnt + statrowcnt

WHERE utype = typ

AND uhour = hour

TABLAS MUTANTES

Una tabla que estaacute mutando es una tabla que estaacute siendo modificada por un INSERT un DELETE o un UPDATE o una tabla que podriacutea modificarse por los efectos de un ON DELETE CASCADE (integridad referencial) Una tabla estaacute restringiendo (tabla de restriccion o tabla padre) si una sentencia activadora podriacutea necesitar leerlo directamente por una sentencia SQL o indirectamente por una restriccioacuten de Integridad referencial

Paquetes-

Los paquetes tienen el objetivo de agrupar procedimientos y funciones de forma loacutegica De esta manera se consigue agrupar en un uacutenico objeto toda la casuiacutestica asociada a un determinado tipo de tarea Por ejemplo si tenemos un conjunto de procedimientos y funciones para realizar caacutelculos matemaacuteticos complejos los podemos poner en un paquete

La ventaja de los paquetes es que la primera vez que se invoca es cargado en memoria por lo que las siguientes veces ya no seraacute necesario hacerlo de nuevo Ademaacutes el paquete mantiene una copia en memoria para todos los usuarios Otra ventaja es que podemos encapsular procedimientos y funciones que no forman parte de la interfaz de usuario Podemos ocultar ciertos objetos y solo hacer puacuteblicos los que se necesiten Tambieacuten se permite la sobrecarga dentro de los paquetes

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

El paquete se divide en

bull Especificacioacuten bull Cuerpo

Especificacioacuten

Es obligatorio en la creacioacuten de un paquete Se declaran todos los subprogramas puacuteblicos Lo loacutegico es declarar en esta seccioacuten aquellos procedimientos o funciones que actuacutean como interfaz Tambieacuten se declaran las variables o constantes que se quieren tratar como globales y que se puedan cambiar o referenciar fuera del paquete

La sintaxis seriacutea

CREATE [OR REPLACE] PACKAGE nombre_paquete IS | AS declaraciones de variables cursores subprogramas en PLSQL END nombre_paquete

Cuerpo

En el cuerpo es donde se definen los procedimientos y funciones puacuteblicos y privados

La sintaxis seriacutea

CREATE [OR REPLACE] PACKAGE BODY nombre_paquete IS | ASdeclaraciones de variables cursoressubprogramas en PLSQLEND nombre_paquete

Para realizar llamadas a objetos dentro de un paquete habriacutea que diferenciar si la llamada es desde un subprograma dentro del mismo paquete o si la llamada es externa al paquete

bull Llamada interna

Se pone el nombre del subprograma y entre pareacutentesis los paraacutemetros que se deben pasar

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Por ej para llamar a la funcioacuten Multiplica(real r1 real r2) desde la funcioacuten CalculaBeneficios se pondriacutea r3= Multiplica(2535)

bull Llamada externa

Hay que preceder el nombre del paquete al nombre del procedimiento o funcioacuten Ademaacutes el subprograma llamado debe ser puacuteblico para que pueda ser referenciado Por ej para llamar a la funcioacuten Multiplica que se encuentra en el paquete OperacionesMatematicas desde un procedimiento externo al paquete seriacutea r3= OperacionesMatematicasMultiplica(2535)

Para eliminar un paquete de la base de datos tanto la especificacioacuten como el cuerpo DROP PACKAGE nombre_paquete

Oracle proporciona algunos paquetes que permiten la realizacioacuten de distintas tareas muy comunes al desarrollar o administrar la base de datos Entre estos paquetes se pueden destacar

bull DBMS_SQL Para acceder a la base de datos con SQL dinaacutemico bull DBMS_UTILITY Para el anaacutelisis de objetos Por ejemplo se pueden compilar

todos los objetos que se encuentran en un determinado esquema de base de datos bull UTL_FILE Antildeade capacidades de entradasalida a ficheros

Obtenido de httpeswikipediaorgwikiPaquetes_en_PLSQL- Declaracioacuten de la especificacioacuten - Un paquete se compone de dos elementos La Especificacioacuten y El cuerpo

- En la especificacioacuten se declaran todos los procedimientos y funciones que formaran parte del mismo y la sintaxis es como sigue

CREATE [OR REPLACE] PACKAGE package_name [AUTHID CURRENT_USER | DEFINER] IS | AS [PRAGMA SERIALLY_REUSABLE] [collection_type_definition ] [record_type_definition ] [subtype_definition ] [collection_declaration ] [constant_declaration ] [exception_declaration ] [object_declaration ]

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

[record_declaration ] [variable_declaration ] [cursor_spec ] [function_spec ] [procedure_spec ] [call_spec ] [PRAGMA RESTRICT_REFERENCES(assertions) ]END [package_name]

cuerpo de un paquete

[CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS [PRAGMA SERIALLY_REUSABLE] [collection_type_definition ] [record_type_definition ] [subtype_definition ] [collection_declaration ] [constant_declaration ] [exception_declaration ] [object_declaration ] [record_declaration ] [variable_declaration ] [cursor_body ] [function_spec ] [procedure_spec ] [call_spec ][BEGIN sequence_of_statements]END [package_name]]

- Sobrecarga de paquetes

Los paquetes pueden ser compilados y almacenados en una base de datos Oracle y su contenido puede ser compartido por varias aplicaciones Cuando un paquete es llamado para su ejecucioacuten eacuteste se almacena completamente en memoria la primera vez Las siguientes llamadas no requieren efectuar este procedimiento cada vez y por esto aumentan la eficiencia de los programas

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Uso de Paquetes Suministrados por Oracle en el Desarrollo de Aplicaciones

Son numerosos los paquetes proporcionados por ORACLE en este manual mencionaremos algunos si el estudiante esta interesado o tiene la necesidad puede indagar por su cuenta

DBMS_ALERT Procedimientos y funciones que permiten a las aplicaciones nombrar y emitir sentildeales de alerta sin sondeo

DBMS_AQ DBMS_AQADM Procedimientos y funcipones para poner en cola la ejecucioacuten de transacciones y administrar los mecanismos de encolamiento

DBMS_DDL DBMS_UTILITY Procedimiento que permite el acceso a un numero determinado de sentencias DDL dentro de los programas PLSQL

DBMS_DESCRIBE Procedimiento que describe la API de funciones y procedimientos almacenados

DBMS_JOB Procedimientos y funciones que administran los mecanismos de las tareas de encolamiento de una bas de datos

DBMS_LOB Procedimientos y Funciones que manipulan BLOB CLOB NCLOB y BFILE

DBMS_LOCK Procedimientos y funciones que permiten a las aplicaciones coordinar el acceso a los recursos compartidos

DBMS_OUTPUT Procedimientos y funciones que permiten a un programa PLSQL generar salida de Terminal

DBMS_PIPE Procedimientos y funciones que permiten a las sesiones de base de datos comunicarse usando conductos (canales de comunicacioacuten)

DBMS_ROWID Procedimientos y funciones que permiten a las aplicaciones interpretar faacutecilmente un caraacutecter ROWID externo de base 64

DBMS_SESSION Procedimientos y funciones que controla la sesion de usuario de una aplicacioacuten

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

DBMS_SQL Procedimientos y funciones que realizan SQL dinamico desde dentro de un programa PLSQL

DBMS_TRANSACTION Procedimientos que realizan un control limitado de las transacciones

UTL_FILE Procedimientos y funciones que permiten a un programa PLSQL leer y escribir archivos de texto al sistema de archivos del servidor

SQL Dinaacutemico y Metadatos

Sentencias DML con SQL dinamico

PLSQL ofrece la posibilidad de ejecutar sentencias SQL a partir de cadenas de caracteres Para ello debemos emplear la instruccioacuten EXECUTE IMMEDIATE

Podemos obtener informacioacuten acerca de nuacutemero de filas afectadas por la instruccioacuten ejecutada por EXEXUTE IMMEDIATE utilizando SQLROWCOUNT

El siguiente ejemplo muestra la ejecucioacuten de un comando SQL dinamico

DECLARE ret NUMBER FUNCTION fn_execute RETURN NUMBER IS sql_str VARCHAR2(1000) BEGIN sql_str = UPDATE DATOS SET NOMBRE = NUEVO NOMBRE WHERE CODIGO = 1 EXECUTE IMMEDIATEsql_str RETURN SQLROWCOUNT END fn_execute BEGIN ret = fn_execute() dbms_outputput_line(TO_CHAR(ret))END

Podemos ademaacutes parametrizar nuestras consultas a traveacutes de variables host Una variable host es una variable que pertenece al programa que estaacute ejecutando la sentencia SQL dinaacutemica y que podemosasignar enel interior de

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

la sentencia SQL con la palabra clave USING Las variables host van precedidas de dos puntos

El siguiente ejemplo muestra el uso de variables host para parametrizar una sentencia SQL dinamica

DECLARE ret NUMBER FUNCTION fn_execute (nombre VARCHAR2 codigo NUMBER) RETURN NUMBER IS sql_str VARCHAR2(1000) BEGIN sql_str = UPDATE DATOS SET NOMBRE = new_nombre WHERE CODIGO = codigo EXECUTE IMMEDIATEsql_str USING nombre codigo RETURN SQLROWCOUNT END fn_execute BEGIN ret = fn_execute(Devjoker1) dbms_outputput_line(TO_CHAR(ret))END

Cursores con SQL dinaacutemico

Con SQL dinaacutemico tambieacuten podemos utilizar cursores

Para utilizar un cursor impliacutecito solo debemos construir nuestra sentencia SELECT en una variable de tipocaraacutecter y ejecutarla con EXECUTE IMMEDIATEutilizando la palabra clave INTO

DECLARE str_sql VARCHAR2(255) l_cntVARCHAR2(20)BEGIN str_sql = SELECT count() FROM PAISES EXECUTE IMMEDIATE str_sql INTO l_cnt dbms_outputput_line(l_cnt)END

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Trabajar con cursores expliacutecitos es tambieacuten muy faacutecil Uacutenicamente destacar el uso de REF CURSOR para declarar una variableparareferirnos al cursorgenerado con SQL dinaacutemico

DECLARE TYPE CUR_TYP IS REF CURSOR c_cursor CUR_TYP fila PAISESROWTYPE v_query VARCHAR2(255)BEGIN v_query = SELECT FROM PAISES

OPEN c_cursor FOR v_query LOOP FETCH c_cursor INTO fila EXIT WHEN c_cursorNOTFOUND dbms_outputput_line(filaDESCRIPCION) END LOOP CLOSE c_cursorEND

Las varibles host tambien se pueden utilizar en los cursores

DECLARE TYPE cur_typ IS REF CURSOR c_cursor CUR_TYP fila PAISESROWTYPE v_query VARCHAR2(255) codigo_pais VARCHAR2(3) = ESPBEGIN

v_query = SELECT FROM PAISES WHERE CO_PAIS = cpais OPEN c_cursor FOR v_query USING codigo_pais LOOP FETCH c_cursor INTO fila EXIT WHEN c_cursorNOTFOUND dbms_outputput_line(filaDESCRIPCION) END LOOP CLOSE c_cursorEND

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Consideraciones de Disentildeo para Coacutedigo PLSQL

Esta parte de nuestro Manuel es muy subjetiva y se basa en lo que para mi resulta coacutemodo y faacutecil de atender no representa la verdad absoluta en PLSQL y mis consideraciones son

Identar los bloques- para los que no manejan la palabra ldquoidentarrdquo es escribir todas las sentencias que estaacuten subordinadas o entre el comienzo y el final de un bloque a 1 o 2 o 3 o las columnas que consideren prudente

Ejemplo

BEGIN IF Sal gt 24000 THEN Sal_Des = sal ndash 24000 ndashIdentado a tres columnas del IF ELSE Sal_Des = 0 END IFEND

Siempre que se usa un SQL dentro de un PL encerrarlo en un bloque tratando de manejar los posibles errores que devolveraacute ese SQL

EjEMPLO

BEGIN UPDATE emp SET sal = 8000 WHERE NoEmp = V_NoEmpEXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUTPUT_LINE(lsquoNo existe el empleado lsquo||V_NoEmp) WHEN OTHERS THEN DBMS_OUTPUTPUT_LINE(lsquoNo existe el empleado lsquo||V_NoEmp) END

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Recomiendo poner una letra delante de las variables que indique que corresponden a Una Variable (V) un Cursor (C) una Excepcioacuten (E) un Paraacutemetro (P) Separar con un guioacuten bajo ( UnderScore _ ) las variables que tengan mas de una letra Ejemplo VSueldo_Bruto

En general en programacioacuten muchos autores recomiendan

Escribir las palabras reservadas del lenguaje en mayuacutesculas (UPDATE) variables y nombres de campos en Capitalize (Primera letra en mayuacutesculas y las siguientes en minuacutesculas)

En fin tratar de hacer el coacutedigo limpio y entendible para usted y cualquier otra persona

Gestioacuten de Dependencias

Dependencias

Recompilacioacuten de objetos descompilados del esquema Las actualizaciones parches y cambios de libreriacuteas pueden invalidar objetos del esquema Una vez realizados los cambios los objetos dependientes seraacuten revalidados de forma automaacutetica cuando se usan Esto puede tardar un rato e incluso tardar un tiempo inaceptable por lo que lo loacutegico es recompilar las dependencias antes de las llamadas de los usuarios Esto ademaacutes permite descubrir si los cambios han afectado al resto del coacutedigo Identificacioacuten de objetos descompilados

COLUMN object_name FORMAT A30

SELECT owner object_type object_name statusFROM dba_objectsWHERE status = INVALIDORDER BY owner object_type object_name

Con esta informacioacuten podemos decidir que metodo seguir para recompilar

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Recompilacioacuten manual

Para un nuacutemero pequentildeo de objetos es probable que la recompilacioacuten manual sea suficiente

ALTER PACKAGE my_package COMPILEALTER PACKAGE my_package COMPILE BODYALTER PROCEDURE my_procedure COMPILEALTER FUNCTION my_function COMPILEALTER TRIGGER my_trigger COMPILEALTER VIEW my_view COMPILE

Hay que tener en cuenta que es necesario recompilar cabecera y cuerpo del paquete por separado Alternativamente podemos usar el paquete

DBMS_DDL EXEC DBMS_DDL(PACKAGE MY_SCHEMA MY_PACKAGE)EXEC DBMS_DDL(PACKAGE BODY MY_SCHEMA MY_PACKAGE)EXEC DBMS_DDL(PROCEDURE MY_SCHEMA MY_PROCEDURE)EXEC DBMS_DDL(FUNCTION MY_SCHEMA MY_FUNCTION)EXEC DBMS_DDL(TRIGGER MY_SCHEMA MY_TRIGGER)

Este metodo esta limitado a los objetos PLSQL por lo que no es aplicable a vistas

Script a medida

En algunos casos puede ser interesante escribir un script para identificar y compilar objetos descompilados Un ejemplo para PACKAGE y PACKAGE BODY seria

SET SERVEROUTPUT ON SIZE 1000000BEGIN FOR cur_rec IN (SELECT owner object_name object_type DECODE(object_type PACKAGE 1 PACKAGE BODY 2 2) AS recompile_order FROM dba_objects

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

WHERE object_type IN (PACKAGE PACKAGE BODY) AND status = VALID ORDER BY 4) LOOP BEGIN IF cur_recobject_type = PACKAGE THEN EXECUTE IMMEDIATE ALTER || cur_recobject_type || || cur_recowner || || cur_recobject_name || COMPILE ElSE EXECUTE IMMEDIATE ALTER PACKAGE || cur_recowner || || cur_recobject_name || COMPILE BODY END IF EXCEPTION WHEN OTHERS THEN DBMS_OUTPUTput_line(cur_recobject_type || || cur_recowner || || cur_recobject_name) END END LOOPEND

Con este meacutetodo hay que tener mucho cuidado ya que puedes acabar recompilando algunos objetos varias veces dependiendo del orden de ejecucioacuten de la recompilacioacuten Los procedimientos que nos da oracle y que estaacuten explicados a continuacioacuten si que tiene en cuenta el orden adecuado

DBMS_UTILITYcompile_schemaEl procedimiento COMPILE_SCHEMA del paquete DBMS_UTILITY compila todos los procedures functions packages y triggers de un esquema Hay que tener muy en cuenta que tampoco recompila las vistas EXEC DBMS_UTILITYcompile_schema(schema =gt SCOTT)

UTL_RECOMPEl paquete UTL_RECOMP contiene 2 procedimientos para recompilar objetos descompilados

RECOMP_SERIAL recompila los objetos uno por uno mientras que

RECOMP_PARALLEL recompila en paralelo usando el numero de threads especificado

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL flags IN PLS_INTEGER DEFAULT 0)

PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL schema IN VARCHAR2 DEFAULT NULL flags IN PLS_INTEGER DEFAULT 0)los parametros son

- schema - El esquema a recompilar si es nulo se recompilan todos los objetos descompilados de la base de datos

- threads - El numero de threads a utilizar si es nulo se usa el valor de job_queue_processes Un valor adecuado es el numero de CPUs disponibles

- flags - Se usa para pruebas y diagnostico interno -

EXEC UTL_RECOMPrecomp_serial(SCOTT)EXEC UTL_RECOMPrecomp_parallel(4 SCOTT)Database levelEXEC UTL_RECOMPrecomp_serial()EXEC UTL_RECOMPrecomp_parallel(4)Using job_queue_processes valueEXEC UTL_RECOMPrecomp_parallel()EXEC UTL_RECOMPrecomp_parallel(NULL SCOTT)

Restricciones del paquete -La ejecucion en paralelo utiliza la cola de trabajos Todos los trabajos se dehabilitan hasta que la operacioacuten termina -Debe ejecutarse con el usuario SYS u otros con permisos de SYSDBA ndash

Deber existir STANDARD DBMS_STANDARD DBMS_JOB and DBMS_RANDOM -El ejecutar comandos DDL a la vez puede provocar interbloqueos

Explicacioacuten de la sintaxis utilizada para los comandos- Las palabras en mayusculas son comandos de oracle- Las palabras en minusculas son opiones modificables- Las partes enmarcadas con [] son opcionales- Las palabras en negrita son las opciones por defecto- Las partes enmarcadas con son alternativas (una u otra)- El simbolo | indica OR

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

ANEXO 1

Tipos de Datos y Conversiones

Cada constante y variable posee un tipo de dato el cual especifica su forma de almacenamiento restricciones y rango de valores vaacutelidos Con PLSQL se proveen diferentes tipos de datos predefinidos Un tipo escalar no tiene componentes internas un tipo compuesto tiene otras componentes internas que pueden ser manipuladas individualmente Un tipo de referencia almacena valores llamados punteros que designan a otros elementos de programa Un tipo lob (large object) especifica la ubicacioacuten de un tipo especial de datos que se almacenan de manera diferente

En la figura 3-1 se muestran los diferentes tipos de datos predefinidos y disponibles para ser utilizados

Figura 3-1 Tipos de datos de PLSQL

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

Conversiones

Algunas veces se hace necesario convertir un valor desde un tipo de dato a otro En PLSQL se aceptan las conversiones de datos impliacutecitas y expliacutecitas

Una conversioacuten expliacutecita es aquella que se efectuacutea utilizando las funciones predefinidas Por ejemplo para convertir un valor de caraacutecter a fecha o nuacutemero se utiliza TO_DATE o TO_NUMBER

Existe una cantidad limitada de funciones de conversioacuten que implementan esta caracteriacutestica de conversioacuten expliacutecita

Cuando se hace necesario PLSQL puede convertir un tipo de dato a otro en forma impliacutecita Esto significa que la interpretacioacuten que se daraacute a alguacuten dato seraacute el que mejor se adecue dependiendo del contexto en que se encuentre Tampoco significa que todas las conversiones son permitidas Algunos ejemplos de conversioacuten impliacutecita maacutes comunes se dan cuando variables de tipo char se operan matemaacuteticamente para obtener un resultado numeacuterico

Si PLSQL no puede decidir a queacute tipos de dato de destino puede convertir una variable se generaraacute un error de compilacioacuten

Tabla de conversiones impliacutecitas

Hasta BIN_INT

CHAR

DATE

LONG

NUMBER

PLS_INT

RAW

ROWID

VARCHAR2

Desde

BIN_INT

X X X X X

CHAR X X X X X X X X

DATE X X X

LONG X X X

NUMBER

X X X X X

PLS_INT

X X X X X

RAW X X X

Fundacioacuten Coacutedigo Libre wwwcodigolibreorg Lic Alfredo Leger

ROWID X X

VARCHAR2

X X X X X X X X