Archivo
PL/SQL – Paquetes ORACLE
Uno de los elementos más importantes son los paquetes («packages»). En general, es recomendable empaquetar el código PL/SQL, evitando el uso de funciones y procedimientos aislados.
Aqui las principales ventajas que propociona el uso de paquetes :
• Agrupa elementos relacionados, haciéndolos mucho más manejables.
• Define una parte pública (especificación) y otra privada (variables, funciones y procedimientos del cuerpo que no se han indicado en la especificación); ocultando complejidad, abstrayendo al desarrollador que lo usa de los detalles innecesarios, e incorporando seguridad.
• Las variables a nivel de paquete, son persistentes durante cada sesión (sólo para dicha sesión); incluso cuando el código que las usa termina su ejecución. Por tanto, podemos declarar una variable en la especificación de un paquete; de modo que un usuario autorizado se conecte y consulte su valor desde un procedimiento; y en la misma sesión, ejecute otro procedimiento que consulte la misma variable, observando el valor que quedó anteriormente, y pudiendo cambiarlo, claro. Dicho valor no lo verán otras sesiones.
• Un paquete, en su implementación, además de variables, funciones y procedimientos, puede incluir un cuerpo (begin … exception … end), que sirva como código de inicialilzación del mismo (interesante para asignar un valor inicial a las variables del paquete). Es recomendable incluir dicho código en un procedimiento privado del paquete (por ejemplo llamado «inicializar«), y llamarlo en el cuerpo; facilitando así la depuración de errores.
El único caso en que puede suponer un problema usar paquetes, es cuando tenemos una función/procedimiento cuya lógica no cambia, que se usa en muchas partes de mi aplicación y, está ubicada dentro de un paquete que contiene otros elementos cuya lógica cambia con frecuencia. Esto hace que cada cambio en la implementación del paquete suponga recompilar todos aquellos elementos de mi aplicación que usan la función (aunque el cuerpo de la misma realmente no haya cambiado).
Para evitar ésto:
• Aislaremos y separaremos aquellas funcionalidades que raramente sufran cambios, de aquellas partes de mi aplicación que se modifiquen con frecuencia. Esto lo podemos hacer con un conjunto pequeño de funciones/procedimientos aislados (no empaquetados), o con un paquete que los agrupe.
• Si nuestros paquetes incluyen constantes en la especificación, que pueden cambiar con cierta frecuencia, y queremos evitar recompilaciones de los paquetes que las usan, podemos sustituir cada constante por una función que nos devuelva el valor.
El siguiente fragmento de código se muestra la creacion de un package con un unico procedimiento. Puede tener muchas variables globales definidas y múltiples procedimientos o funciones dentro de un paquete.
CREATE PACKAGE CLIENTES_SALARIO AS
PROCEDURE BUSCAR_SALARIO(c_id CLIENTES.ID%type);
END CLIENTES_SALARIO;
/
Package Body – Cuerpo del paquete
CREATE OR REPLACE PACKAGE BODY CLIENTES_SALARIO AS
PROCEDURE BUSCAR_SALARIO(c_id CLIENTES.ID%TYPE) IS
c_sal CLIENTES.SALARIO%TYPE;
BEGIN
SELECT SALARIO INTO c_sal
FROM CLIENTES
WHERE ID = c_id;
dbms_output.put_line(‘SALARIO: ‘|| c_sal);
END BUSCAR_SALARIO;
END CLIENTES_SALARIO;
/
Utilizando los elementos del paquete :
Los elementos del paquete (variables, procedimientos o funciones) se accede con la siguiente sintaxis:
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
code CLIENTES.ID%type := &cc_id;
BEGIN
CLIENTES_SALARIO.BUSCAR_SALARIO(code);
END;
/
Ejemplo:
El siguiente programa ofrece un paquete más completo. Usaremos la tabla CLIENTES almacenada en nuestra base de datos con los siguientes registros:
Creamos el siguiente paquete :
CREATE OR REPLACE PACKAGE CLIENTES_Package AS
— Agregar Cliente
PROCEDURE NuevoCliente(c_id CLIENTES.ID%type,
c_nombre CLIENTES.NOMBRE%type,
c_edad CLIENTES.EDAD%type,
c_direccion CLIENTES.DIRECCION%type,
c_salario CLIENTES.SALARIO%type);
— Eliminar Cliente
PROCEDURE EliminarCliente(c_id CLIENTES.ID%TYPE);
— Listar Cliente
PROCEDURE ListarClientes;
END CLIENTES_Package;
/
CREACIÓN DEL CUERPO DEL PAQUETE:
CREATE OR REPLACE PACKAGE BODY CLIENTES_Package AS
PROCEDURE NuevoCliente(c_id CLIENTES.ID%type,
c_nombre CLIENTES.NOMBRE%type,
c_edad CLIENTES.EDAD%type,
c_direccion CLIENTES.DIRECCION%type,
c_salario CLIENTES.SALARIO%type)
IS
BEGIN
INSERT INTO CLIENTES (ID,NOMBRE,EDAD,DIRECCION,SALARIO)
VALUES(c_id, c_nombre, c_edad, c_direccion, c_salario);
END NuevoCliente;
PROCEDURE EliminarCliente(c_id CLIENTES.ID%type) IS
BEGIN
DELETE FROM CLIENTES
WHERE ID = c_id;
END EliminarCliente;
PROCEDURE ListarClientes IS
CURSOR c_Clientes IS
SELECT NOMBRE FROM CLIENTES;
TYPE c_List IS TABLE OF CLIENTES.NOMBRE%type;
lista_nombres c_List := c_List();
contador integer :=0;
BEGIN
FOR n IN c_Clientes LOOP
contador := contador +1;
lista_nombres.extend;
lista_nombres(contador) := n.nombre;
dbms_output.put_line(‘Cliente (‘ ||contador|| ‘)’||lista_nombres(contador));
END LOOP;
END ListarClientes;
END CLIENTES_Package;
/
USO DEL PAQUETE:
El siguiente programa utiliza los métodos declarados y definidos en CLIENTES_Package
DECLARE
code CLIENTES.ID%type:= 8;
BEGIN
CLIENTES_Package.NuevoCliente(7, ‘Liliana‘, 25, ‘Aquino‘, 3500);
CLIENTES_Package.NuevoCliente(8, ‘Victor‘, 32, ‘Huaman‘, 7500);
CLIENTES_Package.ListarClientes ;
CLIENTES_Package.EliminarCliente(code);
CLIENTES_Package.ListarClientes ;
END;
/
PL/SQL – Exceptions
Un error durante la ejecución de un programa llama a una excepción en PL/SQL. PL/SQL soporta atrapar las excepción en el programa y tomar una acción apropiada desacuerdo a la condición de error.
Hay dos tipos de excepciones:
Excepciones definidas por el sistema
Excepciones definidas por el usuario
En el siguiente ejemplo muestro el uso de los tipos de excepciones :
DECLARE
c_id CLIENTES.ID%type := &cc_id;
c_nombre CLIENTES.NOMBRE%type;
c_direccion CLIENTES.DIRECCION%type;
— Excepcion definida por el usuario
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id; — Excepcion definida por el usuario
ELSE
SELECT nombre, direccion INTO c_nombre, c_direccion
FROM Clientes
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE (‘Nombre: ‘|| c_nombre);
DBMS_OUTPUT.PUT_LINE (‘Direccion: ‘ || c_direccion);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line(‘ID debe ser mayor que cero!’);
WHEN no_data_found THEN
dbms_output.put_line(‘No existe el cliente!’);
WHEN OTHERS THEN
dbms_output.put_line(‘Error!’);
END;
/
Creacion Tabla Clientes para que puedas probar el codigo.
|
Excepcion |
Se ejecuta … |
SQLCODE |
| ACCESS_INTO_NULL | El programa intentó asignar valores a los atributos de un objeto no inicializado | -6530 |
| COLLECTION_IS_NULL | El programa intentó asignar valores a una tabla anidada aún no inicializada | -6531 |
| CURSOR_ALREADY_OPEN | El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia OPEN | -6511 |
| DUP_VAL_ON_INDEX | El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index) | -1 |
| INVALID_CURSOR | El programa intentó efectuar una operación no válida sobre un cursor | -1001 |
| INVALID_NUMBER | En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena no representa un número válido | -1722 |
| LOGIN_DENIED | El programa intentó conectarse a Oracle con un nombre de usuario o password inválido | -1017 |
| NO_DATA_FOUND | Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tabla indexada | 100 |
| NOT_LOGGED_ON | El programa efectuó una llamada a Oracle sin estar conectado | -1012 |
| PROGRAM_ERROR | PL/SQL tiene un problema interno | -6501 |
| ROWTYPE_MISMATCH | Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado | -6504 |
| SELF_IS_NULL | El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo | -30625 |
| STORAGE_ERROR | La memoria se terminó o está corrupta | -6500 |
| SUBSCRIPT_BEYOND_COUNT | El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección | -6533 |
| SUBSCRIPT_OUTSIDE_LIMIT | El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “-1”) | -6532 |
| SYS_INVALID_ROWID | La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número | -1410 |
| TIMEOUT_ON_RESOURCE | Se excedió el tiempo máximo de espera por un recurso en Oracle | -51 |
| TOO_MANY_ROWS | Una sentencia SELECT INTO devuelve más de una fila | -1422 |
| VALUE_ERROR | Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña | -6502 |
| ZERO_DIVIDE | El programa intentó efectuar una división por cero | -1476 |
PL/SQL – Variables de tipo Registros
PL / SQL Un registro es una estructura de datos que puede contener elementos de datos de diferentes tipos. Los registros consisten en diferentes campos , de forma similar a una fila de una tabla de base de datos.
ID INT NOT NULL,
Nombre VARCHAR (20) NOT NULL,
Edad INT NOT NULL,
Direccion CHAR (25),
Salario DECIMAL (18, 2),
PRIMARY KEY (ID)
);
VALUES (1, ‘Ricardo’, 32, ‘Huaman’, 2000.00 );INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (2, ‘Juan’, 25, ‘Avila’, 1500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (3, ‘Aldo’, 23, ‘Laderas’, 2000.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (4, ‘Victor’, 25, ‘Moreno’, 6500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (5, ‘Miguel’, 27, ‘Huaman’, 8500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (6, ‘Carlos’, 22, ‘Campos’, 4500.00 );
Registros Basados en Tabla – El atributo %ROWTYPE permite a un programador crear variables basadosen tablas.
DECLARE
Clientes_Reg Clientes%rowtype;
BEGIN
SELECT * INTO Clientes_Reg
FROM Clientes
WHERE id = 5;
dbms_output.put_line(‘Cliente ID: ‘ || Clientes_Reg.ID);
dbms_output.put_line(‘Cliente Nombre: ‘ || Clientes_Reg.NOMBRE);
dbms_output.put_line(‘Cliente Direccion: ‘ || Clientes_Reg.DIRECCION);
dbms_output.put_line(‘Cliente Salario: ‘ || Clientes_Reg.SALARIO);
END;
/
Cursor Basado en Registros – El siguiente ejemplo muestra el concepto de cursores basados en registros. Nosotros vamos a usar la tabla CLIENTES que habíamos creado anteriormente :
DECLARE
CURSOR Clientes_Cursor is
SELECT id, Nombre, Direccion
FROM Clientes;
Clientes_Reg Clientes_Cursor%rowtype;
BEGIN
OPEN Clientes_Cursor;
LOOP
FETCH Clientes_Cursor INTO Clientes_Reg;
EXIT WHEN Clientes_Cursor%notfound;
DBMS_OUTPUT.put_line(Clientes_Reg.id || ‘ ‘ || Clientes_Reg.Nombre);
END LOOP;
END;
/
PL/SQL proporciona un tipo de registro definido por el usuario que le permite definir diferentes estructuras de registro . Los registros consisten en diferentes campos. Supongamos que desea realizar un seguimiento de sus libros en una biblioteca. Es posible que desee realizar un seguimiento de los siguientes atributos acerca de cada libro :
DECLARE
type Libros is record
(Titulo varchar(50),
Autor varchar(50),
Asunto varchar(100),
ID number);
Libro1 Libros;
Libro2 Libros;
BEGIN
— Primer Libro
Libro1.Titulo := ‘Pl/Sql’;
Libro1.Autor := ‘Nuha Ali ‘;
Libro1.Asunto := ‘C Programming Tutorial’;
Libro1.ID := 6495407;
— Segundo Libro
Libro2.Titulo := ‘SQL Server 2014’;
Libro2.Autor := ‘Zara Ali’;
Libro2.Asunto := ‘Telecom Billing Tutorial’;
Libro2.ID := 6495700;
— Imprimir el primer registro
dbms_output.put_line(‘Libro 1 Titulo : ‘|| Libro1.Titulo);
dbms_output.put_line(‘Libro 1 author : ‘|| Libro1.Autor);
dbms_output.put_line(‘Libro 1 subject : ‘|| Libro1.Asunto);
dbms_output.put_line(‘Libro 1 book_id : ‘ || Libro1.ID);
— Imprimir el segundo registro
dbms_output.put_line(‘Libro 2 Titulo : ‘|| Libro2.Titulo);
dbms_output.put_line(‘Libro 2 author : ‘|| Libro2.Autor);
dbms_output.put_line(‘Libro 2 subject : ‘|| Libro2.Asunto);
dbms_output.put_line(‘Libro 2 book_id : ‘ || Libro2.ID);
END;
/
Puede pasar un registro como parámetro a un subprograma en forma muy similar a como se pasa cualquier otra variable . Se podría acceder a los campos de forma similar a la que ha accedido en el ejemplo anterior :
DECLARE
TYPE Libros is record
(Titulo varchar(50),
Autor varchar(50),
Asunto varchar(100),
ID number);
Libro1 Libros;
Libro2 Libros;
PROCEDURE imprimirbook (Libro Libros) IS
BEGIN
dbms_output.put_line(‘Libro Titulo : ‘|| Libro.Titulo);
dbms_output.put_line(‘Libro Author : ‘|| Libro.Autor);
dbms_output.put_line(‘Libro Asunto : ‘|| Libro.Asunto);
dbms_output.put_line(‘Libro ID : ‘ || Libro.ID);
dbms_output.put_line(RPAD(‘-‘, 50, ‘-‘));
END;
BEGIN
— Primer Libro
Libro1.Titulo := ‘Pl/Sql’;
Libro1.Autor := ‘Nuha Ali ‘;
Libro1.Asunto := ‘C Programming Tutorial’;
Libro1.ID := 6495407;
— Segundo Libro
Libro2.Titulo := ‘SQL Server 2014’;
Libro2.Autor := ‘Zara Ali’;
Libro2.Asunto := ‘Telecom Billing Tutorial’;
Libro2.ID := 6495700;
— Imprimir
imprimirbook (Libro1);
imprimirbook (Libro2);
END;
/
PL / SQL Declaración de constantes:
En este ejemplo , vamos a guardar el PI que es constante número real , el radio y el área que son números reales ,
DECLARE
PI CONSTANT REAL := 3.14159;
Radio REAL := 3;
Area REAL := (PI * Radio**2);
BEGIN
dbms_output.put_line(‘ PI: ‘ || PI );
dbms_output.put_line(‘ Radio: ‘ || Radio);
dbms_output.put_line(‘ Area: ‘ || Area);
END;
/
Sintaxis de declaración de variables
- nombre_variable es el nombre predefinido de la variable.
- El tipo de datos es un tipo de datos PL / SQL válida .
- El tamaño es una especificación opcional del tamaño de tipo de datos para mantener el valor de tamaño máximo.
- NOT NULL es una especificación opcional del valor de la variable no se puede aceptar NULL .
- value es también una especificación opcional , donde se puede inicializar el valor de la variable. 6. Cada declaración de variable se termina con un punto y coma.
DECLARE
lnNumEmpleado number(5) NOT NULL := 2; — NOT NULL ( valor no puede estar en blanco ) , Asignar valor inicial
lcNombreEmpleado varchar2(15) := ‘Branson Devs’; — intialize valor en el momento de la declaración
BEGIN
dbms_output.put_line(‘Declaracion de Valores:’);
dbms_output.put_line(‘Numero Empleados: ‘ || lnNumEmpleado || ‘ Nombre Empleado: ‘ || lcNombreEmpleado);
END;
/
Alcance de las Variables
PL / SQL identifica el rango de región que puede hacer referencia una variable. PL / SQL tiene dos tipos de ámbitos ámbito local y alcance global,
Las variables locales – Variables declaradas en el bloque interior y no pueden ser referenciados por los bloques externos.
Las variables globales – Son variables declaradas en un bloque exterior y puede estar haciendo referencia en los bloques interiores.
DECLARE
num1 number := 10;
num2 number := 20;
BEGIN
DECLARE
num_adicional number;
BEGIN
num_adicional := num1 + num2;
dbms_output.put_line(‘La suma es: ‘ || num_adicional);
END; — Fin de acceso a variables num_adicional
END;
/
Este ejemplo muestra una diferencia entre el bloque interior y bloque exterior para el alcance de una variable. Se puede utilizar la palabra clave OUTER accediendo a la variable de bloque exterior dentro del bloque interior .
num number := 11;
BEGIN
DECLARE
num number := 10;
BEGIN
IF num = outer.num THEN
DBMS_OUTPUT.PUT_LINE(‘Ambas variables tiene el mismo valor’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Diferentes valores’);
END IF;
END; — Fin de alcance para la variable num
END;
/
ID INT NOT NULL,
Nombre VARCHAR (20) NOT NULL,
Edad INT NOT NULL,
Direccion CHAR (25),
Salario DECIMAL (18, 2),
PRIMARY KEY (ID)
);
VALUES (1, ‘Ricardo’, 32, ‘Huaman’, 2000.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (2, ‘Juan’, 25, ‘Avila’, 1500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (3, ‘Aldo’, 23, ‘Laderas’, 2000.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (4, ‘Victor’, 25, ‘Moreno’, 6500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (5, ‘Miguel’, 27, ‘Huaman’, 8500.00 );
INSERT INTO Clientes (ID,nombre,edad,direccion,salario)
VALUES (6, ‘Carlos’, 22, ‘Campos’, 4500.00 );
c_id CLIENTES.ID%type := 1;
c_nombre CLIENTES.NOMBRE%type;
c_direcc CLIENTES.DIRECCION%type;
c_salario CLIENTES.SALARIO%type;
BEGIN
SELECT NOMBRE, DIRECCION, SALARIO INTO c_nombre, c_direcc, c_salario
FROM CLIENTES
WHERE id = c_id;
dbms_output.put_line(‘Cliente : ‘ ||c_nombre || ‘ de ‘ || c_direcc || ‘ con salario ‘ || c_salario);
END;
/
PL SQL – ¿Por qué anidar bloques?
Podemos poner BEGIN antes de cualquier conjunto de una o más sentencias ejecutables seguidas de un END, creando un bloque anidado con esas sentencias.
Existen 2 ventajas principales para hacer esto:
(1) posponer la asignación de memoria para variables que se necesitan únicamente en el bloque anidado, y
(2) limitar la propagación de una excepción lanzada por una de las sentencias del bloque anidado.
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
lcMensaje1 VARCHAR2(100) := ‘Hola’;
lcMensaje2 VARCHAR2(100) := ‘ RAHSUAREZ!’;
BEGIN
IF SYSDATE >= TO_DATE(’01/01/2015′)
THEN
lcMensaje2 := lcMensaje1 || lcMensaje2;
DBMS_OUTPUT.put_line(lcMensaje2);
ELSE
DBMS_OUTPUT.put_line(lcMensaje1);
END IF;
END;
El bloque despliega “¡Hola RAHSUAREZ!” cuando la fecha de hoy (retornada por SYSDATE) es por lo menos el primer día de 2015; en otro caso, únicamente despliega el mensaje “Hola”. Aunque este bloque se ejecute en 2011, asigna memoria para la variable lcMensaje2.
Si reestructuramos este bloque, la memoria para lcMensaje2 será asignada únicamente después del 2015:
DECLARE
lcMensaje1 VARCHAR2(100) := ‘Hola’;
BEGIN
IF SYSDATE > TO_DATE(’01/01/2015′)
THEN
DECLARE
lcMensaje2 VARCHAR2(100) := ‘ RAHSUAREZ!’;
BEGIN
lcMensaje2 := lcMensaje1 || lcMensaje2;
DBMS_OUTPUT.put_line(lcMensaje2);
END;
ELSE
DBMS_OUTPUT.put_line(lcMensaje1);
END IF;
END;
De forma similar, podemos agregar una sección de excepciones a este bloque anidado, atrapando errores y permitiendo que el bloque exterior continúe con su ejecución:
DECLARE
lcMensaje1 VARCHAR2(100) := ‘Hola’;
BEGIN
IF SYSDATE > TO_DATE(’01/01/2015′)
THEN
DECLARE
lcMensaje2 VARCHAR2(100) := ‘ RAHSUAREZ!’;
BEGIN
lcMensaje2 := lcMensaje1 || lcMensaje2;
DBMS_OUTPUT.put_line(lcMensaje2);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
ELSE
DBMS_OUTPUT.put_line(lcMensaje1);
END IF;
END;
Ejecutando SQL dentro de bloques PL/SQL
PL/SQL es un lenguaje de programación de base de datos. Casi todos los programas que escribiremos en PL/SQL leerán desde, o escribirán en, una base de datos Oracle utilizando SQL. Aunque estas series asumen que se conoce SQL, debemos estar conscientes de la forma en que llamamos a las sentencias desde un bloque PL/SQL.
Oracle hace que sea muy fácil escribir y ejecutar sentencias SQL en PL/SQL. La mayor parte de las veces, simplemente escribiremos las sentencias SQL directamente en nuestro bloque PL/SQL y después agregaremos el código necesario para la interfaz entre las sentencias SQL y el código PL/SQL.
Supongamos, por ejemplo, que tenemos una tabla llamada ESTUDIANTES, con una columna clave primaria id, y una columna apellido. Podemos ver el apellido del empleado con ID 2, como sigue:
DROP TABLE Estudiantes CASCADE CONSTRAINTS;
CREATE TABLE Estudiantes (
id NUMBER(5) PRIMARY KEY,
nombre VARCHAR2(20),
apellido VARCHAR2(20),
pension FLOAT,
creditos NUMBER(3)
);
DROP SEQUENCE Estudiantes_Secuencia;
CREATE SEQUENCE Estudiantes_Secuencia
START WITH 10000
INCREMENT BY 1;
INSERT INTO Estudiantes (id, nombre, apellido, pension, creditos)
VALUES (Estudiantes_Secuencia.NEXTVAL, ‘Ricardo’, ‘Huaman’, 100, 11);
INSERT INTO Estudiantes (id, nombre, apellido, pension, creditos)
VALUES (Estudiantes_Secuencia.NEXTVAL, ‘Juan’, ‘Victorio’,120, 11);
SELECT * FROM Estudiantes;
Ahora vamos a ejecutar esta una consulta dentro de nuestro bloque PL/SQL y desplegar el nombre. Para hacer esto, necesitaremos “copiar” el apellido desde la tabla a una variable local, lo cual podemos hacer con la cláusula INTO:
DECLARE
lcApellido Estudiantes.apellido%TYPE;
BEGIN
SELECT apellido
INTO lcApellido
FROM Estudiantes
WHERE id = 10000;
DBMS_OUTPUT.put_line(lcApellido);
END;
Primero declaramos una variable local, y haciendo esto introducimos otra característica de PL/SQL: la capacidad de fijar el tipo de datos de nuestra variable en función del tipo de datos de una columna en una tabla
Después ejecutamos una consulta contra la base, obteniendo el apellido del estudiante y asignándolo directamente en la variable lcApellido.
Otro ejemplo ahora ejecutamos la sentencias SELECT en PL/SQL, también querremos insertar, modificar y eliminar datos desde PL/SQL. Aquí hay ejemplos de cada uno de esos tipos de sentencias DML:
• Eliminamos todos los estudiantes cuya pension es igual a 100 y mostramos cuántas tuplas fueron eliminadas:
DECLARE
lnpension Estudiantes.pension%TYPE := 100;
BEGIN
DELETE FROM Estudiantes
WHERE Pension = lnpension;
DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;
SELECT * FROM Estudiantes;
Sobre los nombres en una base Oracle
Ahora que ya se aprecia la importancia de asignar nombres a la lógica, es tiempo de hablar sobre las reglas para los nombres (identificadores) tanto en PL/SQL como, de forma más general, en una base Oracle.
Estas son las reglas para construir identificadores válidos en una base Oracle:
• El largo máximo es de 30 caracteres.
• El primer caracter debe ser una letra, pero cada caracter después del primero puede ser una letra, un número (0 a 9), un signo de pesos ($), un guión bajo (_), o un numeral (#).
Ejemplos de identificadores válidos:
hola_RAHSUAREZ
hola$RAHSUAREZ
hola#RAHSUAREZ
pero estos son inválidos:
1hola_RAHSUAREZ
hola%RAHSUAREZ
• PL/SQL es case-insensitive (no es sensitivo a mayúsculas y minúsculas) con respecto a los identificadores. PL/SQL trata todos los siguientes como el mismo identificador:
hola_rahsuarez
Hola_Rahsuarez
HOLA_RAHSUAREZ
Para ofrecer más flexibilidad, Oracle permite evitar las restricciones de la segunda y tercera regla, encerrando al identificador entre comillas dobles. El identificador encerrado entre comillas puede contener cualquier secuencia de caracteres imprimibles excluyendo las comillas dobles; las diferencias entre mayúsculas y minúsculas serán además preservadas. Así, todos los siguientes identificadores son válidos y distintos:
«Abc»
«ABC»
«a b c»
Es muy raro encontrar identificadores entre comillas en código PL/SQL.
Estas mismas reglas aplican a los nombres de los objetos de base de datos como tablas, vistas y procedimientos, con una regla adicional: a menos que se encierren entre comillas los nombres de estos objetos, Oracle los mantendrá en mayúsculas. Ejemplo:
CREATE OR REPLACE PROCEDURE hola_rahsuarez IS
BEGIN
DBMS_OUTPUT.put_line(‘¡Hola RAHSUAREZ!’);
END hola_rahsuarez;
Construyendo con 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.
SET SERVEROUTPUT ON
PL/SQL en Oracle requiere para obtener el resultado en pantalla SERVEROUTPUT .
Un bloque es en sí mismo una sentencia ejecutable, por lo que se pueden anidar los bloques unos dentro de otros.
Aquí hay algunos ejemplos:
El clásico “¡Hola RAHSUAREZ!” 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 RAHSUAREZ!‘);
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 RAHSUAREZ!’.
Después, el procedimiento DBMS_OUTPUT.PUT_LINE acepta la variable, en lugar del literal, para desplegarlo:
DECLARE
lmensaje VARCHAR2(100) := ‘¡Hola RAHSUAREZ!’;
BEGIN
DBMS_OUTPUT.put_line(lmensaje);
END;
Create Sequence – Generador de indentificadores unicos que no bloquea transacciones
Crea un objeto capaz de darnos numeros consecutivos unicos.
CREATE SEQUENCE secuencia
INCREMENT BY n
START WITH n
{MAX VALUE n | NOMAXVALUE}
{MIN VALUE N | NOMINVALUE}
{CYCLE | NOCYCLE}
{CACHE N | NOCACHE}
{ORDER | NOORDER};
En realida es un generador de indentificadores unicos que no bloquea transacciones.
Es muy util para generar primary keys.
DROP TABLE students CASCADE CONSTRAINTS;
CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3)
);
CREATE SEQUENCE student_sequence
START WITH 10000
INCREMENT BY 1;
INSERT INTO students (id, first_name, last_name, major,
current_credits)
VALUES (student_sequence.NEXTVAL, ‘Scott’, ‘Smith’,
‘Computer Science’, 11);
INSERT INTO students (id, first_name, last_name, major,
current_credits)
VALUES (student_sequence.NEXTVAL, ‘Margaret’, ‘Mason’,
‘History’, 4);
SELECT * FROM students;
Asi obtenemos el siguiente valor:
SELECT student_sequence.NEXTVAL FROM DUAL;
Tambien podemos obtener el valor actual:
SELECT student_sequence.CURRVAL FROM DUAL;
RAHSuarez's Blog 