Viernes, 12 Junio 2020 02:38

Interactuando con el Servidor ORACLE

Rate this item
(0 votes)

Objetivos:

  • Determinar las sentencias SQL que se pueden incluir directamente en un bloque ejecutable de PL/SQL.
  • Manipular los datos con instrucciones DML en PL/SQL.
  • Utilizar sentencias de control de transacciones en PL/SQL.
  • Hacer uso de la cláusula INTO para contener los valores devueltos por una instrucción SQL.
  • Diferenciar entre cursores implícitos y explícitos cursores.
  • Usar atributos de cursor SQL.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.


Sentencias SQL en programas PL/SQL

En un bloque PL/SQL, se utiliza sentencias SQL para recuperar y modificar los datos de tablas de Base de Datos. PL/SQL soporta el Lenguaje de Manipulación de Datos (DML) y los comandos de control de transacciones. Puede utilizar los comandos DML para modificar los datos de una tabla de Base de Datos. Sin embargo, tenga presente los siguientes puntos cuando use sentencias DML y comandos de control de transacciones en bloques de PL/SQL:

La palabra clave END indica el final de un bloque PL/SQL, no el final de una transacción. Del mismo modo que un bloque puede abarcar varias transacciones, una transacción puede abarcar varios bloques.

PL/SQL no soporta directamente el Lenguaje de Definición de Datos (DDL) como CREATE TABLEALTER TABLEDROP TABLE, ETC... PL/SQL admite el enlace temprano o early binding, Lo cual no puede suceder si las aplicaciones tienen que crear objetos de Base de Datos en tiempo de ejecución mediante el paso valores. Las sentencias DDL no pueden ser ejecutadas directamente. Estas instrucciones son sentencias dinámicas de SQL. Las sentencias dinámicas de SQL se construyen como cadenas de caracteres en tiempo de ejecución y pueden contener marcadores de posición(placeholders) para parámetros. Por lo tanto, puede utilizar SQL dinámico para ejecutar sus sentencias DDL en PL/SQL.

PL/SQL no soporta directamente las sentencias de Lenguaje de Control de Datos (DCL), tales como GRANT REVOKE. Puede utilizar SQL dinámico para ejecutarlos.

 

Utilice una instrucción SELECT para recuperar datos de la Base de Datos.

Sintaxis:

SELECT  select_list
INTO  {variable_name[, variable_name]...
 | record_name}
FROM  table
[WHERE  condition];

En la Sintaxis:

select_list

Lista de al menos una columna; puede incluir expresiones SQL, funciones de fila, o funciones de grupo.

variable_name

variable escalar que sostendrá el valor recuperado.

record_name

Record de PL/SQL que sostendrá los valores recuperados.

table

Es el nombre de la tabla.

condition

Se compone de nombres de columnas, expresiones y operadores de comparación, incluyendo las variables y constantes de PL/SQL.

Directrices para la Recuperación de datos en PL/SQL

  • Termine cada sentencia SQL con un punto y coma (;).
  • Cada valor recuperado debe ser almacenado en una variable utilizando la cláusula INTO.
  • La cláusula WHERE es opcional y se puede utilizar para especificar las variables de entrada, constantes, literales, y expresiones PL/SQL. Sin embargo, cuando se utiliza la cláusula INTO, deberías tomar sólo una fila; por lo cual la cláusula WHERE es requerida en estos casos.
  • Especificar el mismo número de variables en la cláusulaINTO que el número de columnas en la cláusula SELECT. Asegúrese de que correspondan posicionalmente y que sus tipos de datos sean compatibles.
  • Utilice las funciones de grupo, tales como SUM, en una sentencia SQL, ya que las funciones de grupo aplican a grupos de filas de una tabla.

 

Detalles:

 

Cláusula INTO.

La cláusula INTO es obligatoria y se especifica entre el SELECT y cláusula FROM. Se utiliza para especificar los nombres de las variables que alojaran los valores retornados por las columnas en la cláusula SELECT. Debe especificar una variable para cada elemento seleccionado, y el orden de las variables deben corresponder con los elementos seleccionados.

Utilice la cláusula INTO para rellenar tanto variables PL/SQL como las variables de entorno(Bind Variables).

Las consultas deben retornar sólo una fila

Las instrucciones SELECT dentro de un bloque PL/SQL caen dentro de la clasificación ANSI de SQL incorporado, para el que se aplica la siguiente regla: Las consultas deben devolver una sola fila. Una consulta que devuelve más de una fila o ninguna fila genera un error.

PL/SQL gestiona estos errores al elevar excepciones estándar, que se pueden controlar en la sección EXCEPTION del bloque PL/SQL con las excepciones: NO_DATA_FOUND TOO_MANY_ROWS. Debe incluir una condición WHERE en la instrucción SQL para que la instrucción retorne una sola fila.

Ejemplos:

SET SERVEROUTPUT ON

DECLARE
  v_nombre VARCHAR2(25);
BEGIN
  SELECT first_name INTO v_nombre
  FROM employees
  WHERE employee_id = 200;
  DBMS_OUTPUT.PUT_LINE('El nombre es: '||v_nombre);
END;

/*El ejemplo consulta el nombre del empleado numero 200 y almacena el valor en la variable v_nombre, posteriormente se imprime el valor.*/

 

DECLARE
    v_fecha_contrato   employees.hire_date%TYPE;
    v_salario     employees.salary%TYPE;
BEGIN
    SELECT   hire_date, salary
    INTO     v_fecha_contrato, v_salario
    FROM     employees
    WHERE    employee_id = 100;
    DBMS_OUTPUT.PUT_LINE ('Fecha de Contratación :'|| v_fecha_contrato);
    DBMS_OUTPUT.PUT_LINE ('Salario :'|| v_salario);
END;

/*El ejemplo extrae la fecha de contratación y salario del empleado numero 100 y luego muestra sus respectivos valores mediante el uso de variables..*/

 

DECLARE
   v_sum_sal   NUMBER(10,2);
   v_deptno    NUMBER NOT NULL := 60;
BEGIN
  SELECT SUM(salary)  -- Función de grupo
  INTO v_sum_sal
  FROM employees
  WHERE     department_id = v_deptno;
  DBMS_OUTPUT.PUT_LINE ('La suma total de los salarios del departamento '||v_deptno||' es: '|| v_sum_sal);
END;

/*El ejemplo muestra como usar una función grupal de SQL dentro de un bloque PL/SQL.*/

 

Cómo recuperar varias filas de una tabla y operar sobre los Datos

Una instrucción SELECT con la cláusula INTO puede recuperar sólo una fila a la vez. Si sue requiere recuperar varias filas y operar sobre los datos, puede hacer uso de cursores explícitos. Los Cursores Explícitos serán presentados en futuras publicaciones.


Ambigüedades en la Nomenclatura

En las sentencias SQL potencialmente ambiguas, los nombres de las columnas de Base de Datos tienen prioridad sobre los nombres de las variables locales.

  • Utilice una buena convención de nombres para evitar ambigüedades en la cláusula WHERE.
  • Evite el uso de nombres de columna de Base de Datos como identificadores.
  • Errores de sintaxis pueden surgir ya que PL/SQL primero busca nombres de columnas de Base de Datos.
  • Los nombres de las variables locales y parámetros formales tienen prioridad sobre los nombres de las tablas de Base de Datos.
  • Los nombres de las columnas en tablas de Bases de Datos tienen prioridad sobre los nombres de las variables locales.
  • Los nombres de las variables tienen prioridad sobre los nombres de las funciones.

Evite ambigüedad en la cláusula WHERE apegándose a un convenio de nomenclatura que distinga los nombres de columnas de Base de Datos de nombres de variables de PL/SQL.

Nota: No hay posibilidad de ambigüedad en la cláusula SELECT, ya que cualquier identificador en la cláusula SELECT debe ser un nombre de columna de Base de Datos. No hay posibilidad de ambigüedad en la cláusula INTO porque los identificadores en la cláusula INTO deben ser variables de PL/SQL. La posibilidad de confusión está presente sólo en la cláusula WHERE.

Ejemplo:

DECLARE
    last_name VARCHAR2(25) := 'OConnell';
BEGIN
    DELETE FROM employees
    WHERE last_name = last_name;
END;

/*Este SCRIPT trata de eliminar los empleados de la tabla employees que tengan OConnell como apellido, sin embargo al ejecutar este SCRIPT, todos los registros serian eliminados debido a que existe Ambigüedad en la cláusula WHERE; en lugar de comparar cada registro con la cadena OConnell, se compararía cada registro con sigo mismo. Nota: Si ejecuta el SCRIPT fallaría debido a un CONSTRAINT de integridad en la tabla employees.*/


Manipulando Datos en PL/SQL

Para manipular los datos en la Base de Datos usamos comandos DML. Puede emitir comandos DML como INSERTUPDATEDELETE MERGE sin restricciones en PL/SQL. Los bloqueos de fila (y los bloqueos de tabla) se liberan mediante la inclusión de las sentencias COMMIT ROLLBACK en el código PL/SQL.

  • La sentencia INSERT añade nuevas filas a la tabla.
  • La instrucción UPDATE modifica filas existentes en la tabla.
  • La instrucción DELETE elimina filas de la tabla.
  • La instrucción MERGE selecciona filas de una tabla para actualizarla o insertarla en otra tabla. La decisión de actualizar o insertar en la tabla destino se basa en una condición en la cláusula ON.

NotaMERGE es una sentencia determinista. Es decir, no se puede actualizar la misma fila de la tabla destino varias veces en la misma instrucción MERGE. Debe tener los privilegios INSERT UPDATE en la tabla destino y el privilegio SELECT en la tabla origen.

Ejemplos:

BEGIN
    INSERT INTO employees
                    (
                      employee_id,
                      first_name,
                      last_name,
                      email,
                      hire_date,
                      salary
                    )
    VALUES
        (
          employees_seq.NEXTVAL,
          'Ruth',
          'Torres',
          'RTORRES@';,
          CURRENT_DATE,
          4000
        );
END;

/*Este ejemplo nos muestra como ejecutar una sentencia INSERT en un bloque PL/SQL.*/

 

DECLARE
  sal_increase   employees.salary%TYPE := 800;
BEGIN
  UPDATE employees
  SET salary = salary + sal_increase
  WHERE employee_id =
                    (
                     SELECT MAX(employee_id)
                     FROM employees
                    );
END;

/*Ahora en este ejemplo usamos una sentencia UPDATE en un bloque PL/SQL para actualizar el salario de la empleada insertada en el ejemplo anterior a este.*/

 

DECLARE
    v_nombre   VARCHAR2(35) :=  'Ruth Torres';
BEGIN
  DELETE FROM   employees
  WHERE  first_name||' '||last_name = v_nombre;
END;

/*Este ejemplo nos muestra como usar una sentencias DELETE en un bloque PL/SQL; Acá eliminamos el registro que anteriormente insertamos y luego actualizamos.*/

 

CREATE TABLE emp_copia
  AS
    SELECT *
    FROM employees
    WHERE department_id = 80;

/*Creamos una copia de la tabla employees solo con los empleados del departamento 80, a continuación usaremos esta tabla para mostrar un ejemplo con la sentencia MERGE.

Nota: la anterior sentencia no esta en un bloque PL/SQL, es una sentencia dinámica de SQL*/

 

BEGIN
    MERGE INTO emp_copia c
         USING employees e
         ON (e.employee_id = c.employee_id)
       WHEN MATCHED THEN
         UPDATE SET
           c.first_name     = e.first_name,
           c.last_name      = e.last_name,
           c.email          = e.email,
           c.phone_number   = e.phone_number,
           c.hire_date      = e.hire_date,
           c.job_id         = e.job_id,
           c.salary         = e.salary,
           c.commission_pct = e.commission_pct,
           c.manager_id     = e.manager_id,
           c.department_id  = e.department_id
       WHEN NOT MATCHED THEN
         INSERT VALUES
                    (
                      e.employee_id,
                      e.first_name,
                      e.last_name,
                      e.email,
                      e.phone_number,
                      e.hire_date,
                      e.job_id,
                      e.salary,
                      e.commission_pct,
                      e.manager_id,
                      e.department_id
                    );
END;

/*Finalmente este es un ejemplo de una sentencia MERGE dentro de una bloque PL/SQL.*/


Cursores SQL

Un cursor es un puntero a la zona privada de memoria asignada por el servidor Oracle. Se utiliza para manejar el conjunto de resultados de una instrucción SELECT.

Hay dos tipos de cursores:

  • Implícito: Creado y gestionado internamente por el servidor Oracle para procesar instrucciones SQL.
  • Explícito: Declarado explícitamente por el programador.

Dónde procesa el Servidor Oracle las sentencias SQL?

El Servidor Oracle asigna un área de memoria privada llamada el área de contexto para el procesamiento de instrucciones SQL. La instrucción SQL se analiza y procesa en esta área. Tanto la información requerida para el procesamiento como la información recuperada después de su transformación se almacenan en esta área. Usted no tiene control sobre esta zona, ya que es gestionada internamente por el servidor Oracle.


Atributos SQL para Cursores Implícitos.

Los atributos SQL para cursores permiten evaluar que ocurrió la ultima vez que un cursor implícito fue utilizado. Es posible utilizar estos atributos en sentencias de PL/SQL, no sentencias SQL.

Es común el uso de SQL%ROWCOUNTSQL%FOUND, y SQL%NOTFOUND en la sección ejecutable de un bloque PL/SQL para recoger información después que un comando DML es ejecutado. PL/SQL no retorna error si una instrucción DML no afecta filas en una tabla. Sin embargo, si una instrucción SELECT no recupera ninguna fila, PL/SQL devuelve una excepción.

Observe que los atributos tienen el prefijo SQL. Estos atributos se utilizan con cursores implícitos creados automáticamente por el Servidor Oracle, por lo cual sus nombres son desconocidos. Es por ello, que se utiliza SQL en lugar del nombre del cursor.

El atributo SQL%NOTFOUND es opuesto a SQL%FOUND. Estos atributos pueden ser utilizados como la condición de salida en un bucle. Son útiles en UPDATE DELETE cuando ninguna fila es afectada, ya que no se levantan excepciónes en estos casos.

SQL%FOUND

Atributo Booleano que evalúa como TRUE si la última sentencia SQL afectó al menos una fila.

SQL%NOTFOUND

Atributo Booleano que evalúa como TRUE si la última sentencia SQL no afectó incluso una fila.

SQL%ROWCOUNT

Un valor entero que representa el número de filas afectadas por la última sentencia SQL.

Ejemplos:

DECLARE
    v_cantidad    NUMBER(3);
    v_dept        employees.department_id%TYPE := 80;
BEGIN
    UPDATE  emp_copia
    SET salary = 6954
    WHERE department_id = v_dept;
    v_cantidad := SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE ('Registros Actualizados: '||v_cantidad);
    COMMIT;
END;

/*El ejemplo muestra como determinar la cantidad de registros actualizados luego de ejecutar la sentencia UPDATE, para ello usamos el atributo SQL%ROWCOUNT.*/

---OUTPUT:

 

DECLARE
    v_cantidad    NUMBER(3);
BEGIN
    DELETE FROM  emp_copia;
    v_cantidad := SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE ('Registros Eliminados: '||v_cantidad);
    COMMIT;
END;

/*El ejemplo muestra como determinar la cantidad de registros eliminados luego de ejecutar la sentencia DELETE.*/

---OUTPUT:


Fuente: Oracle Database: PL/SQL Fundamentals

Read 251 times Last modified on Viernes, 10 Julio 2020 18:24

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.