SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los
lenguajes de programación.
SQL es un lenguaje de consulta, no un lenguaje de programación.
Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales.
PL/SQL es el lenguaje de programación que proporciona Oracle para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes de programación .
Con PL/SQL vamos a poder programar las unidades de programa de la base de datos ORACLE, están son:
Además PL/SQL nos permite realizar programas sobre las siguientes herramientas de ORACLE:
Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.
Una linea en PL/SQL contiene grupos de caracteres conocidos como UNIDADES LEXICAS, que pueden ser clasificadas como:
Es un símbolo simple o compuesto que tiene una función especial en PL/SQL. Estos pueden ser:
Son empleados para nombrar objetos de programas en PL/SQL asi como a unidades dentro del mismo, estas unidades y objetos incluyen:
Es un valor de tipo numérico, carácter, cadena o lógico no representado por un identificador (es un valor explícito).
Es una aclaración que el programador incluye en el código. Son soportados 2 estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos caracters especiales como son:
-- Linea simple
/*
Conjunto de Lineas
*/
Cada constante y variable tiene un tipo de dato en el cual se especifica el formato de almacenamiento, restricciones y rango de valores validos.
PL/SQL proporciona una variedad predefinida de tipos de datos . Casi todos los tipos de datos manejados por PL/SQL son similares a los soportados por SQL. A continuación se muestran los TIPOS de DATOS más comunes:
-- NUMBER [(precision, escala)]
saldo NUMBER(16,2);
/*
Indica que puede almacenar un valor
numérico de 16 posiciones, 2 de ellas
decimales. Es decir, 14 enteros y dos
decimales
*/
-- CHAR [(longitud_maxima)]
nombre CHAR(20);
/*
Indica que puede almacenar valores
alfanuméricos de 20
posiciones
*/
-- VARCHAR2 (longitud_maxima)
nombre VARCHAR2(20);
/* Indica que puede almacenar valores
alfanuméricos de hasta 20 posiciones */
/* Cuando la longitud de los datos sea
menor de 20 no se rellena con blancos */
-- BOOLEAN (lógico)
hay_error BOOLEAN;
/* Se emplea para almacenar
valores TRUE o FALSE. */
-- DATE (Fecha)
fecha_ingreso = '10/08/2014';
/* Almacena datos de tipo fecha.
Las fechas se almacenan internamente
como datos numéricos, por lo que es
posible realizar operaciones aritméticas
con ellas.
*/
Los mas conocidos y trabajados son:
Ver los demás tipos de datos en el archivo de Excel enviado al correo
La siguiente tabla ilustra los operadores de PL/SQL.
PL/SQL es un lenguaje estructurado con bloques.
Un bloque PL/SQL es definido por las palabras clave DECLARE, BEGIN, EXCEPTION, y END, que dividen el bloque en tres secciones
1. Declarativa: sentencias que declaran variables, constantes y otros elementos de código, que después pueden ser usados dentro del bloque
2. Ejecutable: sentencias que se ejecutan cuando se ejecuta el bloque
3. Manejo de excepciones: una sección especialmente estructurada para atrapar y manejar cualquier excepción que se produzca durante la ejecución de la sección ejecutable
Sólo la sección ejecutable es obligatoria. No es necesario que usted declare nada en un bloque, ni que maneje las excepciones que se puedan lanzar.
Un bloque es en sí, es una sentencia ejecutable, por lo que se pueden anidar los bloques unos dentro de otros.
El clásico “¡Hola Mundo!” es un bloque con una sección ejecutable que llama al procedimiento DBMS_OUTPUT.PUT_LINE para mostrar texto en pantalla:
BEGIN
DBMS_OUTPUT.put_line('¡Hola Mundo!');
END;
DECLARE
l_mensaje VARCHAR2(100) := '¡Hola Mundo!';
BEGIN
DBMS_OUTPUT.put_line(l_mensaje);
END;
El siguiente bloque declara una variable de tipo VARCHAR2 (un string) con un largo máximo de 100 bytes para contener el string ‘¡Hola Mundo!’. Después, el procedimiento DBMS_OUTPUT.PUT_LINE acepta la variable, en lugar del literal, para desplegarlo:
El siguiente ejemplo de bloque agrega una sección de manejo de excepciones que atrapa cualquier excepción (WHEN OTHERS) que pueda ser lanzada y muestra el mensaje de error, que es retornado por la función SQLERRM (provista por Oracle).
DECLARE
l_mensaje VARCHAR2(100) := '¡Hola Mundo!';
BEGIN
DBMS_OUTPUT.put_line(l_mensaje);
DBMS_OUTPUT.put_line(4/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Ocurrió un Error: ' || SQLERRM);
END;
El siguiente ejemplo de bloque demuestra la habilidad de PL/SQL de anidar bloques dentro de bloques así como el uso del operador de concatenación (||) para unir múltiples strings.
DECLARE
l_mensaje VARCHAR2(100) := '¡Hola';
BEGIN
DECLARE
l_mensaje2 VARCHAR2(100) := l_mensaje || ' Mundo!';
BEGIN
DBMS_OUTPUT.put_line(l_mensaje2);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Ocurrió un Error: ' || SQLERRM);
END;
Todos los bloques que hemos visto hasta el momento son “anónimos”, no tienen nombres. Si los bloques anónimos fueran la única manera de organizar el código, sería muy difícil usar PL/SQL para crear una aplicación grande y compleja. Por esto, PL/SQL soporta la definición de bloques nombrados (named blocks), también conocidos como subprogramas.
Los subprogramas pueden ser procedimientos o funciones. Generalmente, un procedimiento se utiliza para realizar una acción y una función se utiliza para calcular y devolver un valor.
Vamos a asegurarnos de que se comprendan los conceptos básicos detrás de la creación del subprograma.
Supongamos que necesitamos mostrar "¡Hola Mundo!" desde múltiples lugares en nuestra aplicación. Queremos evitar la repetición de la misma lógica en todos esos lugares. Por ejemplo, ¿qué pasa cuando tenemos que cambiar el mensaje, tal vez para "¡Hola Universo!"? Vamos a tener que encontrar todos los lugares en nuestro código donde esta lógica aparece.
CREATE OR REPLACE PROCEDURE hola_mundo IS
l_mensaje VARCHAR2(100) := '¡Hola Mundo!';
BEGIN
DBMS_OUTPUT.put_line(l_mensaje);
END hola_mundo;
-- Llamando al procedimiento antes creado
BEGIN
hola_mundo;
END;
En su lugar, vamos a crear un procedimiento denominado hola_mundo mediante la ejecución de la siguiente sentencia DDL (Data Definition Language):
Hemos escondido todos los detalles de cómo decir hola mundo dentro del cuerpo (body), o implementación, de nuestro procedimiento. Ahora podemos llamar a este procedimiento hola_mundo y mostrar el mensaje deseado sin tener que escribir la llamada a DBMS_OUTPUT.PUT_LINE o averiguar la forma correcta de darle formato al texto.
Podemos llamar a este procedimiento desde cualquier lugar en nuestra aplicación. Así que si alguna vez necesitamos cambiar ese texto, lo vamos a hacer en un solo lugar, el único punto de definición de ese texto.
Ha esto se le llaman procedimientos almacenados simples. Tus procedimientos tendrán mucho más código, y casi siempre también tendrán parámetros.
Los parámetros pasan información a los subprogramas, cuando éstos son llamados, y es lo que permite crear subprogramas más flexibles y genéricos.
Se pueden usar en muchos contextos diferentes.
He mencionado antes que algún día puede ser que desee mostrar "¡Hola Universo!" en lugar de "¡Hola Mundo!".
¿Como lo haría?
CREATE OR REPLACE PROCEDURE hola_universo IS
l_mensaje VARCHAR2(100) := '¡Hola Universo!';
BEGIN
DBMS_OUTPUT.put_line(l_mensaje);
END hola_universo;
Solución
Podríamos, sin embargo, terminar con las decenas de variantes del “mismo” procedimiento hola, que haría muy difícil mantener nuestra aplicación.
Un enfoque mucho mejor es analizar el procedimiento e identificar qué partes se mantienen incambiadas (son estáticas) cuando el mensaje tiene que cambiar y qué partes cambian.
Podemos pasar las partes que cambian como parámetros y tener un procedimiento único que se puede utilizar en diferentes circunstancias.
Así que vamos a cambiar hola_mundo (y hola_universo) a un nuevo procedimiento, hola_lugar:
CREATE OR REPLACE PROCEDURE hola_lugar (lugar_in IN VARCHAR2) IS
l_mensaje VARCHAR2(100);
BEGIN
l_mensaje := '¡Hola ' || lugar_in;
DBMS_OUTPUT.put_line(l_mensaje);
END hola_lugar;
Procedimiento almacenado con parámetros sencillo
Justo después del nombre del procedimiento, añadimos entre paréntesis de apertura y cierre, un único parámetro.
Podemos tener varios parámetros, pero cada parámetro de la misma forma básica:
nombre_de_parametro modo_de_parametro tipo_de_datos
En otras palabras, debemos proveer un nombre para el parámetro, el modo o forma en que éste será usado (IN = sólo lectura), y el tipo de dato que será pasado al subprograma a través de este parámetro.
En este caso, vamos a pasar un texto de sólo lectura al procedimiento hola_lugar.
Y ahora podemos saludar a nuestro mundo y a nuestro universo como sigue:
BEGIN
hola_lugar('Mundo!');
hola_lugar('Universo!');
END;
Una función es un subprograma que devuelve un valor.
Una función es un bloque nombrado PL/SQL que devuelve un valor Y ademas su valores pueden ser llamados y guardados desde un expresión SQL, como Insert, Update y Delete.
-- La sintaxis para construir funciones es la siguiente:
CREATE [OR REPLACE]
FUNCTION <fn_name>[(<param1> IN <type>, <param2> IN <type>, ...)]
RETURN <return_type>
IS
result <return_type>;
BEGIN
return(result);
[EXCEPTION]
-- Sentencias control de excepcion
END [<fn_name>];
Una vez creada y compilada la función podemos ejecutarla de la siguiente forma:
A continuación vamos a crear una función que me retorne un salario para ser insertado en la tabla de my _employees llamando la función desde allí.
CREATE OR REPLACE
FUNCTION fn_Obtener_Salario(e_employee_id NUMBER)
RETURN NUMBER
IS
result NUMBER;
BEGIN
SELECT SALARY INTO result
FROM EMPLOYEES
WHERE EMPLOYEE_ID = e_employee_id;
return(result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
return 0;
END ;
DECLARE
EMPLOYEE_ID NUMBER := 107;
SALARY NUMBER;
BEGIN
SALARY := fn_Obtener_Salario(EMPLOYEE_ID);
DBMS_OUTPUT.put_line(SALARY);
END;
Las funciones pueden utilizarse en sentencias SQL de manipulación de datos (SELECT, UPDATE, INSERT y DELETE):
No se permiten comandos INSERT, UPDATE o DELETE.
La función no puede llamar a otro subprograma que rompa una de las restricciones arriba indicadas.
SELECT
fn_Obtener_Salario(EMPLOYEE_ID) SALARY,
LAST_NAME
FROM EMPLOYEES
Para quitar una función de la parte del servidor, ejecutar el comando DROP FUNCTION.
DROP FUNCTION fn_Obtener_Salario
DROP PROCEDURE hola_mundo
Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción SQL (una operación DML: INSERT, UPDATE o DELETE) sobre dicha tabla.
La sintaxis para crear un trigger es la siguiente:
CREATE [OR REPLACE] TRIGGER <nombre_trigger>
{BEFORE|AFTER}
{DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]
[OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]}
ON <nombre_tabla>
[FOR EACH ROW [WHEN (<condicion>)]]
DECLARE
-- variables locales
BEGIN
-- Sentencias
[EXCEPTION]
-- Sentencias control de excepcion
END <nombre_trigger>;
El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.
Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación. El modificador BEFORE AFTER indica que el trigger se ejecutará antes o despues de ejecutarse la sentencia SQL definida por DELETE INSERT UPDATE.
Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista
FOR EACH ROW
Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocó el disparo.
Los disparadores con nivel de orden se activan sólo una vez, antes o después de la orden. Los disparadores con nivel de fila se identifican por la cláusula FOR EACH ROW en la definición del disparador.
INSERT, DELETE, UPDATE
Define qué tipo de orden DML provoca la activación del disparador.
BEFORE , AFTER
Define si el disparador se activa antes o después de que se ejecute la orden.
create table MY_EMPLOYEES_LOG(
ID NUMBER primary key
LAST_NAME VARCHAR2(25)
)
CREATE OR REPLACE TRIGGER TR_MY_EMPLOYEE_01
AFTER INSERT ON MY_EMPLOYEE
FOR EACH ROW
DECLARE
-- local variables
BEGIN
INSERT INTO MY_EMPLOYEES_LOG
(ID, LAST_NAME)
VALUES
(:NEW.ID,:NEW.LAST_NAME);
END ;
INSERT INTO MY_EMPLOYEE
(ID, LAST_NAME, FIRST_NAME, USERID, SALARY)
VALUES
(1,'Galeano', 'Yhoan', 'gyhoan', 2300);
INSERT INTO MY_EMPLOYEE
(ID, LAST_NAME, FIRST_NAME, USERID, SALARY)
VALUES
(2,'Urrea', 'Andres', 'UAndres', 2500);
SELECT * FROM MY_EMPLOYEES_LOG
La siguiente tabla muestra los valores de OLD y NEW.
Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila.
Se ejecuta la propia orden.
Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior) con nivel de fila.
Se ejecuta, si existe, el disparador de tipo AFTER con nivel de orden.
Una misma tabla puede tener varios triggers. En tal caso es necesario conocer el orden en el que se van a ejecutar.
Los disparadores se activan al ejecutarse la sentencia SQL.
Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo) con nivel de orden.
Para cada fila a la que afecte la orden: