Visi

1
Visión General de PL/SQL

2
Visión Global
Datos
Sistema Operativo
Oracle8
SQL, PL/SQL
Herramientas
Aplicaciones
Servidor
Cliente
3
Estructura de Bloque en Bloques Anónimos PL/SQL

• DECLARE (opcional)
• Define objetos PL/SQL que serán utilizados dentro
  del mismo bloque
• BEGIN (obligatorio)
• Sentencias Ejecutables
• EXCEPTION (opcional)
• Qué hacer si la acción ejecutada causa un error
• END (obligatorio )

4
Estructura de Bloque en Subprogramas PL/SQL

• Cabecera
• IS
• Sección Declarativa
• BEGIN
• Sección Ejecutable
• EXCEPTION
• Sección de Excepciones
• END

5
Tipos de Bloques

• Anónimo DECLARE
• BEGIN
• sentencias ejecutables
• EXCEPTION
• END
• Procedimiento PROCEDURE nombre_procedimiento
• IS
• declaración - opcional
• BEGIN
• sentencias ejecutables
• EXCEPTION
• END
• Función FUNCTION nombre_funcion
• RETURN tipo de dato a devolver
• IS declaración - opcional
• BEGIN
• sentencias ejecutables
• RETURN value
• EXCEPTION

6
Entornos de Desarrollo

• SQLPlus
• Utiliza el motor PL/SQL en el servidor Oracle
• Procedure Builder
• Utiliza el motor PL/SQL en la herramienta
  cliente o en el servidor Oracle

7
Resumen

• Los bloques anónimos, son bloques PL/SQL sin
  nombre.
• Los subprogramas son bloques PL/SQL con nombre,
  declarados como funciones o procedimientos.
• Hay dos entornos de desarrollo PL/SQL
  principales
• SQLPlus
• Procedure Builder

8
VARIABLES en PL/SQL
9
Uso de las variables

• Las variables se usan para
• - Almacenamiento temporal de los datos
• - Manipulación de valores almacenados,
• - Reusabilidad (se pueden utilizar repetidas
  veces dentro de una aplicación)

10
Gestión de Variables en PL/SQLL

• Declarar e inicializar las variables dentro de
  la sección declaradora
• Asignar nuevos valores a las variables dentro de
  la sección de código
• Pasar valores a los bloques PL/SQL a través de
  los parámetros
• Ver los resultados a través de variables de
  salida.

11
Declaración de Variables PL/SQL
Sintaxis Ejemplos
12
Recomendaciones Declaración Variables

• Seguir las convenciones de nombres
• Inicializar las ctes. y variables designadas
  como NOT NULL
• Inicializar usando el parámetro de asignación
  o la palabra DEFAULT
• Declarar como máximo un identificador por línea

13
Principales variables escalares

• VARCHAR2 (maximum_length)
• NUMBER (precision, scale)
• DATE
• CHAR (maximum_length)
• LONG
• LONG RAW
• BOOLEAN (true,false or NULL)
• BINARY_INTEGER

14
Inicialización de variables

• USO
• Operador de asignación
• DEFAULT
• NOT NULL

15
Declaración de variables escalares

• Ejemplos

DECLARE v_job VARCHAR2(9)
v_count BINARY_INTEGER 0
v_total_sal NUMBER(9,2) 0
v_orderdate DATE SYSDATE 7
c_tax_rate CONSTANT NUMBER(3,2) 8.25
v_valid BOOLEAN NOT NULL TRUE ...
16
El atributo TYPE

• Declarar una variable basada en
• Otras variables previamente declaradas
• La definición de una columna de la bbdd
• Preceder TYPE por
• La tabla y la columna de la bbdd
• El nombre de la variable definia con anterioridad

17
Declaración de variables con el Atributo TYPE

• Ejemplos

... v_name employees.last_nameTYPE
v_balance NUMBER(7,2) v_min_balance
v_balanceTYPE 10 ...
18
Interación con elServidor Oracle 8
19
Sentencias SQL en PL/SQL

• PL/SQL soporta
• Sentencias SQL, para extraer información o
  aplicar cambios a la bbdd
• PL/SQL no soporta
• - el lenguaje de definición de datos (DDL), como
  CREATE TABLE, ALTER TABLE o DROP TABLE
• - Lenguaje de control de datos (DCL), como GRANT
  y REVOKE

20
Sentencias SQL en PL/SQL

• Extraer una fila de datos de la bbdd utilizando
  el comando SELECT. Sólo puede ser devuelta una
  fila
• Realizar cambios a las filas de una tabla de
  bbdd, utilizando comandos DML ( INSERT, UPDATE,
  DELETE)

21
Sentencias SELECT de PL/SQL

• Recuperar datos de tablas de una bbdd mediante
  SELECT, y con la clausula INTO obligatoria
• Sintaxis

SELECT select_list INTO variable_name,
variable_name... record_name FROM
table WHERE condition
22
Sentencias SELECT de PL/SQL

• Ejemplo

DECLARE v_deptno NUMBER(4)
v_location_id NUMBER(4) BEGIN
SELECT department_id, location_id
INTO v_deptno, v_location_id
FROM departments WHERE department_name
'Sales' ... END /
23
Sentencias SELECT de PL/SQL

• Ejemplo

DECLARE v_hire_date employees.hire_dateTYPE
v_salary employees.salaryTYPE BEGIN
SELECT hire_date, salary INTO
v_hire_date, v_salary FROM employees
WHERE employee_id 100 ... END /
24
Manipulación de Datos Utilizando PL/SQL

• Modificar las tablas de la bbdd utilizando
  comandos DML
• INSERT
• UPDATE
• DELETE

25
Inserción de Datos

• Añadir nuevos registros a tabla de bbdd
• Ejemplo

BEGIN INSERT INTO employees (employee_id,
first_name, last_name, email, hire_date,
job_id, salary) VALUES (employees_seq.NEXTVAL
, 'Ruth', 'Cores', 'RCORES', sysdate,
'AD_ASST', 4000) END /
26
Actualización de datos

• Ejemplo

DECLARE v_sal_increase
employees.salaryTYPE 800 BEGIN
UPDATE employees SET salary salary
v_sal_increase WHERE job_id 'ST_CLERK' END /
27
Supresión de datos

• Ejemplo

DECLARE v_deptno employees.department_idTYPE
10 BEGIN DELETE FROM
employees WHERE department_id
v_deptno END /
28
Creación de Estructuras de Control

29
Control del Flujo de Ejecución PL/SQL

• Se puede modificar el flujo lógico de sentencias
  utilizando sentencias IF condicionales y
  estructuras de control de bucles
• Sentencias IF condicionales
• IF-THEN-END IF
• IF-THEN-ELSE-END IF
• IF-THEN-ELSIF-END IF

30
Sentencias IF
Sintaxis Asignar el ID de director 22 si el
nombre del empleado es Osborne
IF condition THEN statements ELSIF condition
THEN statements ELSE statements END IF
IF v_last_name 'OSBORNE' THEN v_mgr
22 END IF
31
Flujo de ejecución de la Sentencia IF-THEN-ELSE
TRUE
FALSE
32
IF-THEN-ELSIF Statement Execution Flow
IF condition
FALSE
TRUE
TRUE
FALSE
33
Bucle básico

• Syntax

LOOP statement1 . . .
EXIT WHEN condition END LOOP
-- delimiter -- statements -- EXIT statement --
delimiter
condition is a Boolean variable or
expression (TRUE, FALSE,
or NULL)
34
Bucle Básico

• Ejemplo

DECLARE v_country_id locations.country_idTYP
E 'CA' v_location_id locations.location_id
TYPE v_counter NUMBER(2) 1 v_city
locations.cityTYPE 'Montreal' BEGIN
SELECT MAX(location_id) INTO v_location_id FROM
locations WHERE country_id v_country_id
LOOP INSERT INTO locations(location_id, city,
country_id) VALUES((v_location_id
v_counter),v_city, v_country_id ) v_counter
v_counter 1 EXIT WHEN v_counter gt 3
END LOOP END /
35
bucle WHILE

• Sintáxis
• Usar el bucle WHILE para repetir sentencias
  mientras una condición sea TRUE

WHILE condition LOOP statement1 statement2
. . . END LOOP
Condition is evaluated at the beginning of each
iteration.
36
bucle WHILE

• Ejemplo

DECLARE v_country_id locations.country_idT
YPE 'CA' v_location_id
locations.location_idTYPE v_city
locations.cityTYPE 'Montreal' v_counter
NUMBER 1 BEGIN SELECT
MAX(location_id) INTO v_location_id FROM
locations WHERE country_id v_country_id
WHILE v_counter lt 3 LOOP INSERT INTO
locations(location_id, city, country_id)
VALUES((v_location_id v_counter), v_city,
v_country_id ) v_counter v_counter 1
END LOOP END /
37
Bucle FOR

• Sintaxis
• Utilizar un bucle FOR para reducir el num de
  repeticiones
• No declarar el contador, se declara implícitamente

FOR counter in REVERSE lower_bound..upper_b
ound LOOP statement1 statement2 . .
. END LOOP
38
Bucle FOR

• Insertar las 10 primeras líneas del pedido del
  num 101

. . . v_ordid item.ordidTYPE101 BEGIN . .
. FOR i IN 1..10 LOOP INSERT INTO
item(ordid,itemid) VALUES(v_ordid, i)
END LOOP END /
39
Cursores explícitos
40
Acerca de cursores explícitos

• Declarados y nombrados por el programador
• Manipulados por medio de sentencias específicas
  en las acciones ejecutables del bloque.
• Utilizarlos para procesar individualmente las
  filas devueltas por una sentencia SELECT de
  varias filas

41
Control de cursores Explícitos
42
Control de cursores Explícitos
Abrir cursor





Recuperar fila del cursor





Continuar hasta que quede vacío

Cerrar el cursor
43
Declaración del Cursor

• Sintaxis
• No incluir la cláusula INTO en la declaración
  del cursor
• Si es necesario procesar filas en una secuencia,
  utilizar la cláusula ORDER BY en la consulta

CURSOR cursor_name IS select_statement
44
Declaración del cursor

• Ejemplo

DECLARE CURSOR emp_cursor IS SELECT
employee_id, last_name FROM employees
CURSOR dept_cursor IS SELECT FROM
departments WHERE department_id 10 BEGIN
...
45
Apertura del cursor

• Sintaxis

OPEN cursor_name
46
Recuperación de Datos del Cursor

• Sintaxis
• Recupera las filas del juego de resultados, una a
  una
• Después de cada recuperación, el cursor avanza a
  la siguiente fila

FETCH cursor_name INTO variable1, variable2,
47
Recuperación de Datos del Cursor

• Ejemplos

... OPEN defined_cursor LOOP FETCH
defined_cursor INTO defined_variables EXIT WHEN
... ... -- Process the retrieved data
END LOOP END
FETCH emp_cursor INTO v_empno, v_ename
48
Cierre del cursor

• Sintaxis
• Cerrar el cursor una vez completado el
  procesamiento de las filas
• Se puede volver a abrir el cursor si es necesario

CLOSE cursor_name
49
Bucles FOR de cursor

• Sintaxis
• Facilita el procesamiento de cursores explícitos
• Apertura, recuperación y cierre implícitos
• No declarar la variable registro, se declara
  implícitamente

FOR record_name IN cursor_name LOOP
statement1 statement2 . . . END LOOP
50
Bucles FOR de Cursor

• ejemplo

DECLARE CURSOR emp_cursor IS SELECT
last_name, department_id FROM
employees BEGIN FOR emp_i IN emp_cursor LOOP
-- implicit open and implicit fetch occur
IF emp_i.department_id 80 THEN ...
END LOOP -- implicit close occurs END /