PL/SQL ( Procedural Language for SQL ) es la extensión procedimental de Oracle Corporation para SQL y la base de datos relacional Oracle . PL/SQL está disponible en Oracle Database (desde la versión 6; procedimientos/funciones/paquetes/activadores PL/SQL almacenados desde la versión 7), la base de datos en memoria TimesTen (desde la versión 11.2.1) e IBM Db2 (desde la versión 9.7). [1] Oracle Corporation generalmente extiende la funcionalidad de PL/SQL con cada versión sucesiva de Oracle Database.
PL/SQL incluye elementos de lenguaje procedimental como condiciones y bucles , y puede manejar excepciones (errores en tiempo de ejecución). Permite la declaración de constantes y variables , procedimientos, funciones, paquetes, tipos y variables de esos tipos, y activadores. Se admiten matrices que impliquen el uso de colecciones PL/SQL. Las implementaciones a partir de la versión 8 de Oracle Database han incluido funciones asociadas con la orientación a objetos . Se pueden crear unidades PL/SQL como procedimientos, funciones, paquetes, tipos y activadores, que se almacenan en la base de datos para su reutilización por parte de aplicaciones que utilicen cualquiera de las interfaces programáticas de Oracle Database.
La primera versión pública de la definición PL/SQL [2] fue en 1995. Implementa el estándar ISO SQL/PSM . [3]
La característica principal de SQL (no procedimental) es también su desventaja: no se pueden utilizar instrucciones de control ( toma de decisiones o control iterativo ) si solo se va a utilizar SQL. PL/SQL proporciona la funcionalidad de otros lenguajes de programación procedimental, como toma de decisiones, iteración, etc. Una unidad de programa PL/SQL es una de las siguientes: bloque anónimo PL/SQL, procedimiento , función , especificación de paquete , cuerpo de paquete, disparador, especificación de tipo, cuerpo de tipo, biblioteca. Las unidades de programa son el código fuente PL/SQL que se desarrolla, compila y, en última instancia, se ejecuta en la base de datos. [4]
La unidad básica de un programa fuente PL/SQL es el bloque, que agrupa declaraciones y sentencias relacionadas. Un bloque PL/SQL se define con las palabras clave DECLARE, BEGIN, EXCEPTION y END. Estas palabras clave dividen el bloque en una parte declarativa, una parte ejecutable y una parte de manejo de excepciones. La sección de declaración es opcional y se puede utilizar para definir e inicializar constantes y variables. Si una variable no se inicializa, se toma el valor NULL de forma predeterminada . La parte opcional de manejo de excepciones se utiliza para manejar errores en tiempo de ejecución. Solo se requiere la parte ejecutable. Un bloque puede tener una etiqueta. [5]
Por ejemplo:
<<etiqueta>> -- esto es opcional DECLARE -- esta sección es opcional number1 NUMBER ( 2 ); number2 number1 %TYPE := 17 ; -- valor predeterminado text1 VARCHAR2 ( 12 ) := ' Hola mundo ' ; text2 DATE := SYSDATE ; -- fecha y hora actuales BEGIN -- esta sección es obligatoria, debe contener al menos una declaración ejecutable SELECT street_number INTO number1 FROM address WHERE name = 'INU' ; EXCEPTION -- esta sección es opcional WHEN OTHERS THEN DBMS_OUTPUT . PUT_LINE ( 'El código de error es ' || TO_CHAR ( sqlcode )); DBMS_OUTPUT . PUT_LINE ( 'El mensaje de error es ' || sqlerrm ); END ; El símbolo :=funciona como un operador de asignación para almacenar un valor en una variable.
Los bloques se pueden anidar, es decir, como un bloque es una sentencia ejecutable, puede aparecer en otro bloque siempre que se permita una sentencia ejecutable. Un bloque se puede enviar a una herramienta interactiva (como SQL*Plus) o se puede incrustar en un programa OCI o Oracle Precompiler . La herramienta o el programa interactivo ejecuta el bloque una vez. El bloque no se almacena en la base de datos y, por ese motivo, se lo denomina bloque anónimo (aunque tenga una etiqueta).
El propósito de una función PL/SQL es generalmente calcular y devolver un único valor. Este valor devuelto puede ser un único valor escalar (como un número, una fecha o una cadena de caracteres) o una única colección (como una tabla anidada o una matriz). Las funciones definidas por el usuario complementan las funciones integradas proporcionadas por Oracle Corporation. [6]
La función PL/SQL tiene la forma:
CREAR O REEMPLAZAR FUNCIÓN < nombre_función > [( declaraciones de variables de entrada / salida )] RETURN tipo_retorno [ AUTHID < USUARIO_ACTUAL | DEFINIDOR > ] < IS | AS > -- parte del encabezado cantidad número ; -- bloque de declaración BEGIN -- parte ejecutable < bloque PL / SQL con declaración de retorno > RETURN < valor_retorno > ; [ Excepción ninguna ] RETURN < valor_retorno > ; END ; Las funciones de tabla con canalización devuelven colecciones [7] y toman la forma:
CREAR O REEMPLAZAR FUNCIÓN < nombre_función > [( declaraciones de variables de entrada / salida )] RETURN tipo_retorno [ AUTHID < USUARIO_ACTUAL | DEFINIDOR > ] [ < AGREGAR | PIPELINED > ] < IS | USING > [ bloque de declaración ] BEGIN < bloque PL / SQL con declaración de retorno > PIPE ROW < tipo_retorno > ; RETURN ; [ bloque de excepción de excepción ] PIPE ROW < tipo_retorno > ; RETURN ; END ; Una función solo debe utilizar el tipo de parámetro IN predeterminado. El único valor de salida de la función debe ser el valor que devuelve.
Los procedimientos se parecen a las funciones en que son unidades de programa con nombre que pueden invocarse repetidamente. La principal diferencia es que las funciones se pueden utilizar en una instrucción SQL, mientras que los procedimientos no . Otra diferencia es que el procedimiento puede devolver varios valores, mientras que una función solo debe devolver un único valor. [8]
El procedimiento comienza con una parte de encabezado obligatoria que contiene el nombre del procedimiento y, opcionalmente, la lista de parámetros del procedimiento. A continuación vienen las partes declarativa, ejecutable y de manejo de excepciones, como en el bloque anónimo de PL/SQL. Un procedimiento simple podría verse así:
CREAR PROCEDIMIENTO create_email_address ( -- La parte del encabezado del procedimiento comienza con nombre1 VARCHAR2 , nombre2 VARCHAR2 , empresa VARCHAR2 , correo electrónico OUT VARCHAR2 ) -- La parte del encabezado del procedimiento termina AS -- La parte declarativa comienza (opcional) error_message VARCHAR2 ( 30 ) := 'La dirección de correo electrónico es demasiado larga.' ; BEGIN -- La parte ejecutable comienza (obligatorio) correo electrónico := nombre1 || '.' || nombre2 || '@' || empresa ; EXCEPTION -- La parte de manejo de excepciones comienza (opcional) WHEN VALUE_ERROR THEN DBMS_OUTPUT . PUT_LINE ( error_message ); END create_email_address ; El ejemplo anterior muestra un procedimiento independiente: este tipo de procedimiento se crea y se almacena en un esquema de base de datos mediante la sentencia CREATE PROCEDURE. También se puede crear un procedimiento en un paquete PL/SQL: esto se denomina procedimiento de paquete. Un procedimiento creado en un bloque anónimo PL/SQL se denomina procedimiento anidado. Los procedimientos independientes o de paquete, almacenados en la base de datos, se denominan " procedimientos almacenados ".
Los procedimientos pueden tener tres tipos de parámetros: IN, OUT y IN OUT.
PL/SQL también admite procedimientos externos a través del proceso estándar de la base de datos Oracle ext-proc. [9]
Los paquetes son grupos de funciones, procedimientos, variables, declaraciones de tipo de tabla y registro PL/SQL, constantes, cursores, etc., vinculados conceptualmente. El uso de paquetes promueve la reutilización del código. Los paquetes se componen de la especificación del paquete y un cuerpo de paquete opcional. La especificación es la interfaz con la aplicación; declara los tipos, variables, constantes, excepciones, cursores y subprogramas disponibles. El cuerpo define completamente los cursores y subprogramas y, por lo tanto, implementa la especificación. Dos ventajas de los paquetes son: [10]
Un disparador de base de datos es como un procedimiento almacenado que Oracle Database invoca automáticamente cada vez que se produce un evento específico. Es una unidad PL/SQL con nombre que se almacena en la base de datos y se puede invocar repetidamente. A diferencia de un procedimiento almacenado, puede habilitar y deshabilitar un disparador, pero no puede invocarlo explícitamente. Mientras un disparador está habilitado, la base de datos lo invoca automáticamente (es decir, el disparador se activa) cada vez que se produce el evento que lo activó. Mientras un disparador está deshabilitado, no se activa.
Creas un disparador con la instrucción CREATE TRIGGER. Especificas el evento disparador en términos de instrucciones disparadoras y el elemento sobre el que actúan. Se dice que el disparador se crea o se define en el elemento, que puede ser una tabla, una vista , un esquema o la base de datos. También especificas el punto de tiempo, que determina si el disparador se activa antes o después de que se ejecute la instrucción disparadora y si se activa para cada fila a la que afecta la instrucción disparadora.
Si el disparador se crea en una tabla o vista, el evento de activación se compone de instrucciones DML y el disparador se denomina disparador DML. Si el disparador se crea en un esquema o en la base de datos, el evento de activación se compone de instrucciones DDL o de operaciones de base de datos y el disparador se denomina disparador del sistema.
Un disparador INSTEAD OF es: un disparador DML creado en una vista o un disparador del sistema definido en una declaración CREATE. La base de datos activa el disparador INSTEAD OF en lugar de ejecutar la declaración de activación.
Los activadores se pueden escribir para los siguientes propósitos:
Los principales tipos de datos en PL/SQL incluyen NUMBER, CHAR, VARCHAR2, DATE y TIMESTAMP.
nombre_variable numero ([ P , S ]) := 0 ; Para definir una variable numérica, el programador añade el tipo de variable NUMBER a la definición del nombre. Para especificar la precisión (opcional) (P) y la escala (opcional) (S), se pueden añadir entre paréntesis, separadas por una coma. ("Precisión" en este contexto se refiere a la cantidad de dígitos que puede contener la variable, y "escala" se refiere a la cantidad de dígitos que pueden seguir al punto decimal).
Una selección de otros tipos de datos para variables numéricas incluiría: binary_float, binary_double, dec, decimal, double precision, float, entire, int, numeric, real, small-int, binary_integer.
variable_name varchar2 ( 20 ) := 'Texto' ; -- por ejemplo: dirección varchar2 ( 20 ) := 'camino con vista al lago' ; Para definir una variable de tipo carácter, el programador normalmente añade el tipo de variable VARCHAR2 a la definición del nombre. A continuación, entre paréntesis, se indica el número máximo de caracteres que puede almacenar la variable.
Otros tipos de datos para variables de caracteres incluyen: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob y bfile.
variable_nombre fecha := hasta_fecha ( '01-01-2005 14:20:23' , 'DD-MM-AAAA hh24:mi:ss' ); Las variables de fecha pueden contener fecha y hora. La hora se puede omitir, pero no hay forma de definir una variable que solo contenga la hora. No existe el tipo DATETIME. Y existe el tipo TIME. Pero no existe el tipo TIMESTAMP que pueda contener una marca de tiempo de grano fino de hasta milisegundos o nanosegundos. La TO_DATEfunción se puede utilizar para convertir cadenas en valores de fecha. La función convierte la primera cadena entre comillas en una fecha, utilizando como definición la segunda cadena entre comillas, por ejemplo:
hasta_fecha ( '31-12-2004' , 'dd-mm-aaaa' ) o
to_date ( '31-dic-2004' , 'dd-mon-aaaa' , 'NLS_DATE_LANGUAGE = American' ) Para convertir las fechas en cadenas se utiliza la función TO_CHAR (date_string, format_string).
PL/SQL también admite el uso de literales de fecha e intervalo ANSI. [11] La siguiente cláusula proporciona un rango de 18 meses:
DONDE campoFecha ENTRE FECHA '2004-12-30' - INTERVALO '1-6' AÑO A MES Y FECHA '2004-12-30' Las excepciones (errores durante la ejecución del código) son de dos tipos: definidas por el usuario y predefinidas.
Las excepciones definidas por el usuario siempre son generadas explícitamente por los programadores, utilizando los comandos RAISEo RAISE_APPLICATION_ERROR, en cualquier situación en la que determinen que es imposible continuar con la ejecución normal. El RAISEcomando tiene la sintaxis:
RAISE < nombre de excepción > ; Oracle Corporation ha predefinido varias excepciones como NO_DATA_FOUND, TOO_MANY_ROWS, etc.
Cada excepción tiene un número de error SQL y un mensaje de error SQL asociado. Los programadores pueden acceder a ellos mediante las funciones SQLCODEy .SQLERRM
Nombre_de_variable Nombre_de_tabla Nombre_de_columna %type ;
Esta sintaxis define una variable del tipo de la columna referenciada en las tablas referenciadas.
Los programadores especifican tipos de datos definidos por el usuario con la sintaxis:
tipo tipo_datos es registro ( campo_1 tipo_1 := xyz , campo_2 tipo_2 := xyz , ... , campo_n tipo_n := xyz ); Por ejemplo:
declarar tipo t_direccion es registro ( nombre direccion . nombre %tipo , calle direccion . calle %tipo , calle_numero direccion . calle_numero %tipo , código postal direccion . código postal %tipo ); v_direccion t_direccion ; empezar seleccionar nombre , calle , calle_numero , código postal en v_direccion de direccion donde rownum = 1 ; fin ; Este programa de ejemplo define su propio tipo de datos, llamado t_address , que contiene los campos nombre, calle, número de calle y código postal .
Entonces, según el ejemplo, podemos copiar los datos de la base de datos a los campos del programa.
Utilizando este tipo de datos, el programador ha definido una variable llamada v_address y la ha cargado con datos de la tabla ADDRESS.
Los programadores pueden abordar atributos individuales en dicha estructura mediante la notación de puntos, de este modo:
v_address.street := 'Calle principal';
El siguiente segmento de código muestra la construcción IF-THEN-ELSIF-ELSE. Las partes ELSIF y ELSE son opcionales, por lo que es posible crear construcciones IF-THEN o IF-THEN-ELSE más simples.
SI x = 1 ENTONCES secuencia_de_declaraciones_1 ; ELSIF x = 2 ENTONCES secuencia_de_declaraciones_2 ; ELSIF x = 3 ENTONCES secuencia_de_declaraciones_3 ; ELSIF x = 4 ENTONCES secuencia_de_declaraciones_4 ; ELSIF x = 5 ENTONCES secuencia_de_declaraciones_5 ; SI NO secuencia_de_declaraciones_N ; FIN SI ; La declaración CASE simplifica algunas estructuras grandes IF-THEN-ELSIF-ELSE.
CASO CUANDO x = 1 ENTONCES secuencia_de_declaraciones_1 ; CUANDO x = 2 ENTONCES secuencia_de_declaraciones_2 ; CUANDO x = 3 ENTONCES secuencia_de_declaraciones_3 ; CUANDO x = 4 ENTONCES secuencia_de_declaraciones_4 ; CUANDO x = 5 ENTONCES secuencia_de_declaraciones_5 ; SINO secuencia_de_declaraciones_N ; FIN DEL CASO ; La declaración CASE se puede utilizar con un selector predefinido:
CASO x CUANDO 1 ENTONCES secuencia_de_declaraciones_1 ; CUANDO 2 ENTONCES secuencia_de_declaraciones_2 ; CUANDO 3 ENTONCES secuencia_de_declaraciones_3 ; CUANDO 4 ENTONCES secuencia_de_declaraciones_4 ; CUANDO 5 ENTONCES secuencia_de_declaraciones_5 ; SINO secuencia_de_declaraciones_N ; FIN DEL CASO ; PL/SQL se refiere a las matrices como "colecciones". El lenguaje ofrece tres tipos de colecciones:
Los programadores deben especificar un límite superior para las variables, pero no es necesario para las tablas indexadas o para las tablas anidadas. El lenguaje incluye varios métodos de recopilación que se utilizan para manipular elementos de recopilación: por ejemplo, FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Las tablas indexadas se pueden utilizar para simular matrices asociativas, como en este ejemplo de una función memo para la función de Ackermann en PL/SQL .
Con tablas indexadas, la matriz se puede indexar por números o cadenas. Es similar a un mapa de Java , que incluye pares clave-valor. Solo tiene una dimensión y no tiene límites.
En el caso de las tablas anidadas, el programador debe comprender qué es lo que está anidado. En este caso, se crea un nuevo tipo que puede estar compuesto por varios componentes. Ese tipo se puede utilizar para crear una columna en una tabla y, dentro de esa columna, se encuentran esos componentes anidados.
Con Varrays, debes entender que la palabra "variable" en la frase "matrices de tamaño variable" no se aplica al tamaño de la matriz de la forma en que podrías pensar que lo haría. El tamaño con el que se declara la matriz es, de hecho, fijo. La cantidad de elementos de la matriz es variable hasta el tamaño declarado. Por lo tanto, se podría decir que las matrices de tamaño variable no son tan variables en tamaño.
Un cursor es un puntero a un área SQL privada que almacena información procedente de una instrucción SELECT o de un lenguaje de manipulación de datos (DML) (INSERT, UPDATE, DELETE o MERGE). Un cursor contiene las filas (una o más) devueltas por una instrucción SQL. El conjunto de filas que contiene el cursor se denomina conjunto activo. [12]
Un cursor puede ser explícito o implícito. En un bucle FOR, se debe utilizar un cursor explícito si se va a reutilizar la consulta; de lo contrario, se prefiere un cursor implícito. Si se utiliza un cursor dentro de un bucle, se recomienda utilizar FETCH cuando se necesite realizar una recopilación masiva o cuando se necesite SQL dinámico.
Como lenguaje procedimental por definición, PL/SQL proporciona varias construcciones de iteración , incluidas las sentencias LOOP básicas, los bucles WHILE , los bucles FOR y los bucles Cursor FOR. Desde Oracle 7.3, se introdujo el tipo REF CURSOR para permitir que se devolvieran conjuntos de registros desde funciones y procedimientos almacenados. Oracle 9i introdujo el tipo SYS_REFCURSOR predefinido, lo que significa que ya no tenemos que definir nuestros propios tipos REF CURSOR.
Sentencias LOOP <<parent_loop>>Sentencias de bucle <<child_loop>> exit parent_loop when < condition > ; -- Termina ambos bucles exit when < condition > ; -- Devuelve el control a parent_loop end loop child_loop ; if < condition > then continue ; -- continúa con la siguiente iteración end if ; salir cuando < condición > ; FIN DEL BUCLE parent_loop ; [13]
Los bucles se pueden terminar utilizando la EXIT palabra clave o generando una excepción .
DECLARE var NUMBER ; BEGIN /* NB: las variables de bucle for en PL/SQL son declaraciones nuevas, con alcance solo dentro del bucle */ FOR var IN 0 .. 10 LOOP DBMS_OUTPUT . PUT_LINE ( var ); END LOOP ; SI var ES NULO ENTONCES DBMS_OUTPUT . PUT_LINE ( 'var es nulo' ); DE LO CONTRARIO DBMS_OUTPUT . PUT_LINE ( 'var no es nulo' ); FIN SI ; FIN ; Producción:
0 1 2 3 4 5 6 7 8 9 10 var es nulo
PARA RecordIndex EN ( SELECCIONAR código_persona DE tabla_personas ) BUCLE DBMS_OUTPUT . PUT_LINE ( RecordIndex . código_persona ); FIN DEL BUCLE ; Los bucles cursor-for abren automáticamente un cursor , leen sus datos y cierran el cursor nuevamente.
Como alternativa, el programador PL/SQL puede predefinir la instrucción SELECT del cursor con antelación para (por ejemplo) permitir la reutilización o hacer que el código sea más comprensible (especialmente útil en el caso de consultas largas o complejas).
DECLARAR CURSOR cursor_persona ES SELECCIONAR codigo_persona DE tabla_personas ; COMIENZO PARA RecordIndex EN cursor_persona BUCLE DBMS_OUTPUT . PUT_LINE ( recordIndex . codigo_persona ); FIN BUCLE ; FIN ; El concepto de person_code dentro del bucle FOR se expresa con notación de punto ("."):
RecordIndex . código_personaSi bien los programadores pueden incorporar fácilmente sentencias de lenguaje de manipulación de datos (DML) directamente en el código PL/SQL mediante sentencias SQL sencillas, el lenguaje de definición de datos (DDL) requiere sentencias de "SQL dinámico" más complejas en el código PL/SQL. Sin embargo, las sentencias DML sustentan la mayoría del código PL/SQL en aplicaciones de software típicas.
En el caso del SQL dinámico PL/SQL, las primeras versiones de Oracle Database requerían el uso de una DBMS_SQLbiblioteca de paquetes Oracle compleja. Sin embargo, las versiones más recientes han introducido un "SQL dinámico nativo" más simple, junto con una EXECUTE IMMEDIATEsintaxis asociada.
PL/SQL funciona de manera análoga a los lenguajes procedimentales integrados asociados con otras bases de datos relacionales . Por ejemplo, Sybase ASE y Microsoft SQL Server tienen Transact-SQL , PostgreSQL tiene PL/pgSQL (que emula PL/SQL hasta cierto punto), MariaDB incluye un analizador de compatibilidad PL/SQL, [14] e IBM Db2 incluye SQL Procedural Language, [15] que cumple con el estándar SQL/PSM de ISO SQL .
Los diseñadores de PL/SQL modelaron su sintaxis sobre la de Ada . Tanto Ada como PL/SQL tienen a Pascal como ancestro común, por lo que PL/SQL también se parece a Pascal en la mayoría de los aspectos. Sin embargo, la estructura de un paquete PL/SQL no se parece a la estructura básica de un programa Object Pascal tal como se implementa en una unidad Borland Delphi o Free Pascal . Los programadores pueden definir tipos de datos globales públicos y privados, constantes y variables estáticas en un paquete PL/SQL. [16]
PL/SQL también permite la definición de clases y su instanciación como objetos en código PL/SQL. Esto se asemeja al uso en lenguajes de programación orientados a objetos como Object Pascal , C++ y Java . PL/SQL se refiere a una clase como un "Tipo de datos abstracto" (ADT) o "Tipo definido por el usuario" (UDT), y la define como un tipo de datos Oracle SQL en lugar de un tipo definido por el usuario de PL/SQL, lo que permite su uso tanto en Oracle SQL Engine como en Oracle PL/SQL Engine. El constructor y los métodos de un tipo de datos abstracto se escriben en PL/SQL. El tipo de datos abstracto resultante puede funcionar como una clase de objeto en PL/SQL. Dichos objetos también pueden persistir como valores de columna en las tablas de bases de datos de Oracle.
PL/SQL es fundamentalmente distinto de Transact-SQL , a pesar de las similitudes superficiales. La migración de código de uno a otro generalmente implica un trabajo no trivial, no solo debido a las diferencias en los conjuntos de características de los dos lenguajes, [17] sino también debido a las diferencias muy significativas en la forma en que Oracle y SQL Server tratan la concurrencia y el bloqueo .
El producto StepSqlite es un compilador PL/SQL para la popular base de datos SQLite , que admite un subconjunto de la sintaxis PL/SQL. La versión Berkeley DB 11g R2 de Oracle agregó compatibilidad con SQL basada en la popular API SQLite al incluir una versión de SQLite en Berkeley DB. [18] En consecuencia, StepSqlite también se puede utilizar como una herramienta de terceros para ejecutar código PL/SQL en Berkeley DB. [19]
{{cite web}}: CS1 maint: multiple names: authors list (link)Una función de tabla canalizada [...] devuelve un conjunto de resultados como una colección [...] de manera iterativa. [...] A medida que cada fila está lista para ser asignada a la colección, se "canaliza" fuera de la función.
Siempre que el motor de ejecución de PL/SQL encuentra una llamada a un procedimiento externo, Oracle Database inicia el proceso extproc. La base de datos transmite la información recibida de la especificación de la llamada alextprocproceso, lo que le ayuda a localizar el procedimiento externo dentro de la biblioteca y ejecutarlo utilizando los parámetros suministrados. Elextprocproceso carga la biblioteca vinculada dinámicamente, ejecuta el procedimiento externo y devuelve el resultado a la base de datos.
¿Berkeley DB admite PL/SQL?
