Que es SQL

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. 

Que mas funcionalidades nos permite trabajar el PL/SQL

Además PL/SQL nos permite realizar programas sobre las siguientes herramientas de ORACLE:

• Oracle Forms
• Oracle Reports
• Oracle Graphics
• Oracle Aplication Server

Fundamentos de PL/SQL

Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.

• PL/SQL no es CASE-SENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros lenguajes de programación como C o Java. Sin embargo debemos recordar que ORACLE es CASE-SENSITIVE en la búsqueda de texto.

 Es un símbolo simple o compuesto que tiene una función especial en PL/SQL. Estos pueden ser:

• Operadores Aritméticos
• Operadores Lógicos
• Operadores Relacionales

UL - DELIMITADOR

UL- IDENTIFICADOR

Son empleados para nombrar objetos de programas en PL/SQL asi como a unidades dentro del mismo, estas unidades y objetos incluyen:

• ​Constantes
• Cursores
• Variables
• Subprogramas
• Excepciones
• Triggers
• ETC.

UL- LITERAL

Es un valor de tipo numérico, carácter, cadena o lógico no representado por un identificador (es un valor explícito).

UL- COMENTARIO

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
*/

Ejemplo

UL- COMENTARIO

-- 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

Operadores en PL/SQL

La siguiente tabla ilustra los operadores de PL/SQL.

BLOQUES EN 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.

Bloques PL/SQL

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;

Ejemplos PL/SQL

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:

Concatenación

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;

¡Póngale nombre a los bloques!

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.

¡Póngale nombre a los bloques!

Ejemplo

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.

QUE HICIMOS...

COMO SE LE LLAMA A ESTO?

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.

Ejercicio practico 

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

Como lo hacemos eficiente?

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.

Entonces como lo hacemos?

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.

Utilicemos:

Y ahora podemos saludar a nuestro mundo y a nuestro universo como sigue:

BEGIN
   hola_lugar('Mundo!');
   hola_lugar('Universo!');
END;

Funciones en PL/SQL

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:

Ejemplo creación de funciones

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):

• Como columna de un SELECT.
• Condiciones en cláusulas WHERE y HAVING.
• Cláusulas ORDER BY y GROUP BY.
• Cláusula VALUES de un comando INSERT.
• Cláusula SET de un comando UPDATE.

Restricciones en Funciones

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

BORRADO DE FUNCIONES

Para quitar una función de la parte del servidor, ejecutar el comando DROP FUNCTION.

DROP FUNCTION fn_Obtener_Salario

DROP PROCEDURE hola_mundo

Triggers

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:

SINTAXIS TRIGGER

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

Para su mayor economía, leer esto por si la duda...

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.

Utilización de :OLD y :NEW

La siguiente tabla muestra los valores de OLD y NEW.

Orden de ejecución

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: