Displaying items by tag: Sentencias

Viernes, 12 Junio 2020 03:50

Sentencia Select, SQL

Objetivos: 

  • Enumerar las capacidades de las sentencias SELECT de SQL.
  • Ejecutar una sentencia SELECT básica.

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


SINTAXIS:

SELECT *|{[DISTINCT] column|expression [alias],...} 
FROM table;

En la sintaxis:

  1. SELECTSelecciona la lista de columnas a consultar.
  2. *: Se usa para que en la consulta se muestren todos los campos/columnas de la tabla.
  3. DISTINCT : suprime los duplicados(omite registros duplicado).
  4. column|expression:  Columnas o expresiones a mostrar en la consulta.
  5. alias:  Nombre personalizado que se le quiere asignar  al campo o columna.
  6. FROM: Especifica la o las tablas que contienen las columnas a mostrar en la consulta.
  7. table: Es la tabla que se esta consultando.
  8. |: Es equivalente a 'o', eje: esto aquello = esto | aquello.
  9. {}: Agrupa objetos similares.
  10. []: Todo lo que esta entre corchetes es Opcional.

Ejemplos:

SELECT  *
FROM departments;

 /*se muestran todos los campos o columnas de la tabla en cuestión(departments)*/

 

SELECT  department_id,
        department_name,
        manager_id,
        location_id
FROM departments;

/*Este Query muestra el mismo resultado que el anterior, la diferencia es que aquí yo enumeré todas las columnas de la tabla*/

 

SELECT  department_id, 
        department_name
FROM departments;

/*Aquí solo enumeré 2 columnas de la tabla*/


Directrices de las Sentencias SQL

Mediante las sencillas reglas que se detallan a continuación, puede crear sentencias válidas que resulten fáciles de leer y de editar:

  • Las sentencias SQL no son sensibles a mayúsculas/minúsculas. (a menos que se indique que lo sean).
  • Las sentencias SQL se pueden introducir en una o en varias líneas.
  • Las palabras clave no se pueden dividir en líneas ni se pueden abreviar.
  • Las cláusulas se suelen colocar en líneas aparte para facilitar su lectura y su edición.
  • Se deben utilizar sangrados para facilitar la lectura del código.
  • Las palabras clave se suelen introducir en mayúsculas; el resto de palabras, como nombres y columnas, se introduce en minúsculas.

Expresiones Aritméticas

Todo esto es posible mediante expresiones aritméticas. Una expresión aritmética puede contener nombres de columna, valores numéricos constantes e los operadores aritméticos y son de gran ayuda para modificar la manera en la que se muestran los datos.

Operador

Descripción

+

Suma

-

Resta

*

Multiplicación

/

División

Operadores Aritméticos 

El cuadro muestra los operadores aritméticos disponibles en SQL. Puede utilizar operadores aritméticos en cualquier cláusula de una sentencia SQL (excepto en la cláusula FROM). 

 

Nota: Con los tipos de datos DATE y TIMESTAMP, puede utilizar únicamente los operadores de suma y de resta.

Ejemplos:

SELECT last_name, salary, salary+ 300 
FROM employees;

/*muestra el apellido, salario y el salario más 300*/

 

SELECT last_name, salary, 12*salary+100
FROM   employees;

 /*muestra el apellido, salario y el salario por 12 más 100*/

 

SELECT last_name, salary, 12*(salary+300)
FROM   employees;

/*muestra el apellido, salario y el salario más 300 por 12*/

 

 Nota: Me limité a hacer ejemplos con estos Operadores pero cualquiera de los ya mencionados puede ser usado siempre y cuando la situación lo requiera.

OJO: El uso de paréntesis es muy importante, el siguiente Query lo muestra claramente: 

SELECT last_name, salary, 12*salary+100, 12*(salary+100)
FROM   employees;

Valor Nulo(NULL)

  • Un valor nulo es aquel que no está disponible, no está asignado, es desconocido o no es aplicable. 
  • Un valor nulo no es lo mismo que un cero o un espacio en blanco.
  • Si faltan valores en una fila para una columna, se dice que el valor es nulo.
  • Las columnas de cualquier tipo de datos pueden contener valores nulos. A menos que tengan      restricciones (NOT NULLPRIMARY KEY) los cuales impiden el uso de valores nulos en las  columnas.
  • Las expresiones aritméticas que contienen un valor nulo se evalúan como nulas.

Nota: usaremos como ejemplo la columna: commission_pct ya que la misma contiene valores nulos debido a que solo las posiciones de directores de ventas o representantes de ventas reciben comisiones.

Ejemplos:

SELECT last_name, salary, commission_pct
FROM   employees; 

/*muestra registros con valores nulos en la columna: commission_pct*/

 

SELECT last_name, 12*salary*commission_pct 
FROM   employees; 

/*el cálculo no se realiza en los registros con este campo nulos*/


Alias de la Columna

  • Cambia el nombre de una cabecera de columna.
  • Es útil para los cálculos.
  • Sigue inmediatamente al nombre de columna o puede usarse la palabra clave AS (opcional) entre el nombre de la columna y el alias para más claridad.
  • Requiere comillas dobles: "" si contiene espacios o caracteres especiales, o si es sensible a mayúsculas/minúsculas.

Ejemplos:

SELECT last_name as Nombre, salary salario,
    phone_number "Numero de Telefono"
FROM   employees;
SELECT last_name apellido, salary as "Sal", phone_number as "telefono"
FROM   employees;

/*como muestra el ejemplo, cualquier forma que se use es válida, pero si se quiere mostrar el nombre de columna con un formato especifico o con espacios entonces dicho alias debe estar entre comillas dobles: ""*/


Operador de Concatenación

  • Enlaza columnas o cadenas de caracteres con otras columnas,expresiones aritméticas o valores constantes.
  • Se representa mediante dos barras verticales (||).
  • Las columnas oexpresiones enlazadasse combinan y crean una única columna de salida.
  • Si se concatena una columna no nula con una nula es resultado seria la columna no nula.

Ejemplos:

SELECT first_name,last_name, first_name||last_name
FROM   employees;
SELECT last_name||'hola', first_name||360
FROM   employees;
SELECT hire_Date||commission_pct
FROM   employees;

Cadenas de Literales

  • Un literal es un carácter, un número o una fecha que se ha incluido en la sentencia SELECT.
  • Un literal no es un nombre de columna ni un alias de columna.
  • Los valores de literales de caracteres y fecha deben ir entre comillas simples (' '); los literales de números no es necesario que las tengas.
  • Las cadenas de literales se incluyen en cada fila devuelta.

Ejemplos:

SELECT last_name ||' is a '||job_id 
       AS "Employee Details"
FROM   employees;
SELECT first_name ||' gana '||salary 
       AS "Salary by Employee"
FROM   employees;
SELECT 'El Empleado numero: '||employee_id||' fue contratado el '||hire_date 
       AS "Fecha de Contratación por Emp"
FROM   employees;

Filas Duplicadas

  • Si consultasuna tabla que tiene registros con columnas con el mismo valor(campos sin restriccionescomo UNIQUE PRIMARY KEY) dichos registros se mostraran duplicados. Para evitar esto debes incluir la palabra clave DISTINCT  después de SELECT pero antes de las columnas a mostrar.

Ejemplos:

SELECT department_id
FROM   employees; 
SELECT department_id, job_id
FROM   employees; 

/*En los dos ejemplos anteriores se muestran algunos valores duplicados.*/

 

SELECT DISTINCT department_id 
FROM   employees; 
SELECT DISTINCT department_id, job_id
FROM   employees;

/*En los dos últimos ejemplos vemos registros únicos por pila.*/


Link con un Archivo de ejercicios de práctica!!!


Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1

Published in SQL
Tagged under
Viernes, 12 Junio 2020 02:40

Sentencias Ejecutables

Objetivos:

  • Identificar las unidades léxicas en un bloque PL/SQL.
  • Utilizar funciones de SQL en PL/SQL.
  • Describir cuando usar las conversiones explícitas e implícitas.
  • Escribir bloques anidados y identificar las variables con etiquetas/alias.
  • Utilizar secuencias en expresiones de PL/SQL.
  • Escribir código legible con Indentación apropiada.

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


Unidades Léxicas en Bloques PL/SQL

 

Las unidades léxicas:

  • Son conjuntos de instrucciones en cualquier bloque PL/SQL.
  • Son secuencias de caracteres, incluyendo letras, números, espacios, tabulaciones, retornos y símbolos.
  • Se pueden clasificar como:

--Identificadores: v_fname, c_percent

--Delimitadores:; , +, -

--Los literales: Juan, 428, True

--Comentarios: -, /* */

Las unidades léxicas incluyen letras, números, caracteres especiales, espacios, tabulaciones, retornos y símbolos.

  • Identificadores: identificadores son los nombres dados a los objetos PL/SQL. En publicaciones anteriores vimos los identificadores válidos y no válidos. Recordemos que las palabras clave no se pueden usar como identificadores.
  • Identificadores con Comillas Dobles:

--Son sensibles a mayúsculas y minúsculas.

--Soportan caracteres especiales tales como espacios.

--Pueden contener palabras reservadas.

Ejemplos:

    "Fecha Inicio"             DATE;

    "fecha Fin"                  DATE;

    "Excepción lanzada?"   BOOLEAN DEFAULT TRUE;

Todo el posterior uso de estas variables debe tener comillas dobles. Sin embargo, no se recomienda el uso de identificadores entre comillas.

  • Delimitadores: son símbolos que tienen un significado especial. Ya vimos que el punto y coma (;) se utiliza para terminar una sentencia de SQL PL/SQL. Por lo tanto, el (;) es un ejemplo de un delimitador.

A continuación un subconjunto de delimitadores disponibles en PL/SQL.

Delimitadores Simples:

Signo

Definición

+

Operator de Suma

-

Operator de Sustracción/Negación.

*

Operator de Multiplicación

/

Operator de División

=

Operator de Igualdad

@

Indicador de Acceso Remoto

;

Terminador de Sentencia

Delimitadores Compuestos:

Signo

Definición

<> 

Operator de Desigualdad

!=

Operator de Desigualdad

||

Operator de Concatenación

--

Indicador para Comentario de una Linea

/*

Indicador para Inicio de Comentario(Varias Lineas)

*/

Indicador para Fin de Comentario(Varias Lineas)

:=

Operador de Asignación

  • Los Literales:Es cualquier valor que se asigna a una variable. Cualquier valor de carácter, número, booleano, o una fecha que no es un identificador es un literal. Los literales se clasifican en:

--Literales de Caracteres: Son todas las cadenas con tipo de dato CHAR VARCHAR2 (por ejemplo, 'Juan', y '12C').

--Literales Numéricos: Un literal numérico representa un valor real o entero (por ejemplo, 428 y 1.276).

--Literales Booleanos: Son los valores que se asignan a las variables booleanas. Ejemplo: TRUEFALSO, y NULL.

  • Comentarios: Es una buena práctica de programación para explicar lo que una pieza de código está tratando de lograr. Es bueno aclarar que el compilador no puede interpretar los /*Comentarios*/, ellos solo sirven de referencia para el programador.

--Los dos guiones (--) se utilizan para comentar una sola línea.

--Los delimitadores de comienzo y fin de comentario (/* */) se utilizan para comentar varias líneas.

Uso de Literales

Un literal es un valor numérico explícito, cadena de caracteres, fecha o valor booleano que no está representado por un identificador.

  • Los literales de Carácter y las Fecha deben ir entre comillas simples.
  • Los literales de caracteres incluyen todos los caracteres imprimibles en el juego de caracteres  de PL/SQL: letras, números, espacios y símbolos especiales.
  • Los literales numéricos se pueden representar ya sea por un valor simple (por ejemplo, -32,5) o en notación científica (por ejemplo,2E5 significa  2 * 105 = 200.000).

Funciones de SQL en PL/SQL

SQL provee varias funciones predefinidas que se pueden utilizar en las instrucciones SQL. La mayoría de estas funciones (como funciones de una sola fila(numéricas y de caracteres), funciones de conversión y funciones de fecha) son válidas en expresiones PL/SQL.

Las siguientes funciones no están disponibles en sentencias de PL/SQL:

  • DECODE
  • NVL2
  • Las funciones de grupo:AVGMINMAXCOUNTSUMSTDDEV, y VARIANCE. Las funciones de grupo se aplican a grupos de filas en una tabla y están, por lo tanto, disponible sólo en instrucciones SQL en un bloque PL/SQL. Las funciones mencionadas anteriormente son sólo un subconjunto de la lista completa.

Ejemplo:

SET SERVEROUTPUT ON

DECLARE
    v_fecha_entrada       DATE;
    v_tiempo              NUMBER;
    v_comision            NUMBER(3,2);
    v_nombre              VARCHAR2(50);
    v_longitud_nom        NUMBER(2);
    "Obtiene Comision?"   CHAR(2);
BEGIN
     SELECT
            first_name||' '||last_name,
            commission_pct,
            hire_date
     INTO   v_nombre,
            v_comision,
            v_fecha_entrada
     FROM employees
     WHERE employee_id = 100;

     --Determinamos la longitud del nombre completo del empleado:
     v_longitud_nom := LENGTH(v_nombre);

     --Determinamos si recibe comisión:
     "Obtiene Comision?" := NVL(TO_CHAR(v_comision), 'NO');

     --Determinamos los años que tiene en la empresa:
     v_tiempo := TRUNC(MONTHS_BETWEEN(SYSDATE, v_fecha_entrada)/12);

     --Imprime las Variables:
     DBMS_OUTPUT.PUT_LINE('Nombre: '||v_nombre||', Longitud del Nombre: '||v_longitud_nom||', Comisión: '||"Obtiene Comision?");
     DBMS_OUTPUT.PUT_LINE('Fecha Contratación: '||v_fecha_entrada||', Años en la Empresa: '||v_tiempo);
END;

/*Este ejemplo nos muestra como usar las funciones de SQL en un Bloque de PL/SQL; Luego de extraer los datos necesarios(En el SELECT), procedemos a aplicar las funciones SQL de la siguiente manera: 

  • Usamos LENGTHpara determinar la longitud de la cadena contenida en la variable v_nombre, y el resultado se lo asignamos a la variable v_longitud_nom.
  • Usamos TO_CHARpara convertir a CHAR el valor contenido en v_comision y así hacerlo compatible con la cadena 'NO', esto porque los dos parámetros usados en la función NVL deben ser del mismo tipo; Si la expresión  TO_CHAR(v_comision) resulta nula la función NVL retorna 'NO' y este valor es asignado a la variable: "Obtiene Comision?".
  • Usamos MONTHS_BETWEENpara determinar la cantidad de meses desde la fecha de contratación(hire_date) y la fecha actual(SYSDATE), dividimos la cantidad de meses entre 12 para determinar los años y por último usamos la función TRUNC para eliminar los decimales y ese valor es asignado a la variable: v_tiempo.

*/

---OUTPUT:


Secuencias en PL/SQL

En Oracle Database 11g, es posible utilizar las pseudocolumnas NEXTVAL CURRVAL en cualquier contexto PL/SQL, donde sea necesario una expresión del tipo de dato NUMBER. Aunque aun es posible utilizar un SELECT para consultar una secuencia, el uso directo es mas recomendable.

Antes era necesario escribir una sentencia SQL para poder usar algún valor(NEXTVAL CURRVAL) de una secuencia en un bloque PL/SQL

Como esto creaba un problema de usabilidad, a partir de la version 11g es posible usar una secuencia en cualquier bloque PL/SQL.

Ejemplo:

DECLARE
    v_nuevo_emp     NUMBER(6);
BEGIN
  v_nuevo_emp :=  EMPLOYEES_SEQ.NEXTVAL;
END;

/*Este ejemplo muestra como usar una secuencia en un bloque PL/SQL.*/


Conversiones de Tipo de Dato

En cualquier lenguaje de programación, la conversión de un tipo de dato a otro es un requisito común. Es por ello que PL/SQL ofrece conversiones con los tipos de datos escalares. Las conversiones de tipos de datos pueden ser de dos tipos:

Conversiones ImplícitasPL/SQL intenta convertir tipos de datos dinámicamente si es necesario en una sentencia.

Ejemplo:

DECLARE
      v_salario NUMBER(6):=6000;
      v_aumento VARCHAR2(5):='1000';
      v_salario_total v_salario%TYPE;
BEGIN
      v_salario_total :=  v_salario+v_aumento;
END;

/*En este ejemplo, la variable v_aumento es del tipo VARCHAR2. Cuando se calcula el salario total, PL/SQL primero convierte v_aumento a número, y luego realiza la operación. El resultado es del tipo de número.*/

 

Conversiones Explícitas: Las usamos para convertir valores de un tipo de datos a otro, para ello usamos las funciones incorporadas en SQL. Por ejemplo, para convertir un valor CHAR a un valor de fecha o número, utilice TO_DATE TO_NUMBER, respectivamente.

Funciones:

TO_CHAR

TO_DATE

TO_NUMBER

TO_TIMESTAMP

 

Ejemplo:

DECLARE
      v_fecha_inicio DATE;
BEGIN
      v_fecha_inicio  :=  TO_DATE('February 02,2000','Month DD, YYYY');
END;

/*En este ejemplo, la función TO_DATE se utiliza para convertir de forma explícita la fecha dada en un formato determinado y asignarlo a la variable v_fecha_inicio del tipo de dato DATE.*/


Bloques Anidados

PL/SQL provee la capacidad de anidar bloques. Puede anidar bloques en cualquier lugar que una sentencia ejecutable sea permitida(sección ejecutable, sección de excepciones), por lo cual el bloque anidado se convierte en una sentencia. Si su sección ejecutable contiene código de forma lógica para muchas funcionalidades relacionadas y con ellos respaldar múltiples requerimientos del negocio, entonces puede dividir la sección ejecutable en bloques más pequeños. Como ya apuntamos, la sección de excepciones también puede contener bloques anidados.

Ejemplo:

DECLARE
    v_variable_externa VARCHAR2(20) := 'VARIABLE GLOBAL';
BEGIN
  DECLARE
    v_variable_interna VARCHAR2(20) :=  'VARIABLE LOCAL';
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_variable_interna);
    DBMS_OUTPUT.PUT_LINE(v_variable_externa);
  END;
  DBMS_OUTPUT.PUT_LINE(v_variable_externa);
END;

/*El ejemplo mostrado tiene un bloque externo (padre) y un bloque anidado (hijo). La variable v_variable_externa se declara en el bloque exterior y la variable v_variable_internase declara en el bloque interno.

v_variable_externa es local en el bloque exterior y global al bloque interior. Cuando se accede a esta variable en el bloque interior, PL/SQL primero busca una variable local en el bloque interno con ese nombre. No hay una variable con el mismo nombre en el bloque interior, por lo cual PL/SQL busca la variable en el bloque exterior. Por lo tanto, v_variable_externa se considera como variable global para todos los bloques adjuntos o sub-bloques. Se puede acceder a esta variable en el bloque interno como se muestra en el ejemplo. Las variables declaradas en un bloque PL/SQL se consideran como locales en ese bloque y global a todos sus sub-bloques.

v_variable_interna es local en el bloque interno y no es global, porque el bloque interno no tiene ningún bloque anidado. Esta variable sólo se puede acceder dentro del bloque interior. Si PL/SQL no encuentra la variable definida localmente, busca hacia arriba en la parte declarativa de los bloques padres. PL/SQL no busca hacia abajo en los bloques hijos.*/

---OUTPUT:


Alcance y Visibilidad de las Variables

Ejemplo:

DECLARE
    v_nombre_padre VARCHAR2(20) :=  'Candelario';
    v_fecha_nacimiento DATE :=  '20-Apr-1972';
BEGIN

  DECLARE
    v_nombre_hijo VARCHAR2(20) :=  'Alfonso';
    v_fecha_nacimiento DATE:=  '12-Dec-2002';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Nombre del Hijo: '||v_nombre_hijo);
    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||v_fecha_nacimiento);
    DBMS_OUTPUT.PUT_LINE('Nombre del Padre: '||v_nombre_padre);
  END;

  DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||v_fecha_nacimiento);
END;

/*Notar las fechas de nacimiento que se imprimen para el padre e hijo. La salida proporciona información correcta, porque el alcance y la visibilidad de las variables se aplican correctamente.*/

---OUTPUT:

  • El alcance de una variable es la parte del programa en el que se declara la variable y es accesible.
  • Lavisibilidad de una variable es la parte del programa donde la variable se puede acceder sin necesidad de utilizar un alias.

Alcance

  • La variable v_nombre_padre y la primera aparición de la variable v_fecha_nacimiento se declaran en el bloque exterior. Estas variables tienen el alcance del bloque en el que se declaran y de los sub-bloques.
  • Las variables v_nombre_hijo v_fecha_nacimiento se declaran en el bloque interior o bloque anidado. Estas variables son accesibles solamente dentro del bloque anidado y no son accesibles en el bloque exterior. Cuando una variable está fuera de alcance,PL/SQL libera la memoria utilizada para almacenar la variable; por lo tanto, no puede hacer referencia a estas variables.

Visibilidad

  • La variable v_fecha_nacimiento declarada en el bloque exterior tiene un alcance incluso en el bloque interior. Sin embargo, esta variable no es visible en el bloque interno debido a que el bloque interno tiene una variable local con el mismo nombre.

--Notar que puede imprimir el nombre del padre, el nombre del hijo, y la fecha de nacimiento del hijo en el bloque interno. Mientras que sólo el nombre del padre y su fecha de nacimiento pueden ser impresos en el bloque exterior.

Nota: No se puede tener variables con el mismo nombre en un bloque. Sin embargo, como se muestra en este ejemplo, se puede declarar variables con el mismo nombre en dos bloques diferentes (bloques anidados).


Uso de Alias en Bloques Anidados

Un alias es un nombre dado a un bloque. Puede utilizar un alias para acceder a las variables que tienen alcance pero no son visibles.

Ejemplo:

BEGIN <<externo>>
DECLARE
    v_nombre_padre VARCHAR2(20) :=  'Candelario';
    v_fecha_nacimiento DATE :=  '20-Apr-1972';
BEGIN  <<interno>>
  DECLARE
    v_nombre_hijo VARCHAR2(20) :=  'Alfonso';
    v_fecha_nacimiento DATE :=  '12-Dec-2002';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Nombre del Hijo: '||interno.v_nombre_hijo);
    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||interno.v_fecha_nacimiento);
    DBMS_OUTPUT.PUT_LINE('Nombre del Padre: '||externo.v_nombre_padre);
    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||externo.v_fecha_nacimiento);
  END;
END interno;
END externo;

/*Ahora notamos como con el uso de alias podemos imprimir todas nuestras variables desde el bloque interno, esto a su vez facilita la lectura del código.*/

---OUTPUT:


Uso de Operadores en PL/SQL

Las operaciones en una expresión se realizan en un orden particular en función a su prioridad. La siguiente tabla muestra el orden predeterminado de las operaciones(de alta prioridad a baja prioridad):

Operador

Operación

**

Exponenciación

+, -

Suma/Adición, Resta/Negación

*, /

Multiplicación, División

||

Concatenación

=, <,  >,  <=,  >=,  <>,  !=,  ~=,  ^=, 
IS NULLLIKEBETWEENIN

Comparación

NOT

Negación Lógica

AND

Conjunción

OR

Inclusión

Cuando se trabaja con nulos, puede evitar algunos errores comunes teniendo en cuenta las siguientes reglas:

Las comparaciones con valores nulos siempre producen NULL.

Aplicando el operador lógico NOT a una nula devuelve NULL.

En las sentencias de control condicionales, si la condición contiene NULL, su asociada secuencia no se ejecuta.

Ejemplo:

DECLARE
    contador  NUMBER :=  0;;
    num_emp   employees.employee_id%TYPE  :=  100;
    salario   employees.salary%TYPE;
    buen_salario  BOOLEAN;
    valido    buen_salario%TYPE;
BEGIN
    contador := contador + 1; --Se le suma  al valor actual de la variable: contador.
    buen_salario := salario BETWEEN 50000 AND 150000;  --Como esto es falto se le asigna FALSE a buen_salario.
    valido := (num_emp IS NOT NULL); --Como esto es cierto se le asigna TRUE a la variable: valido.
END;

/*Este ejemplo muestra el uso de algunos operadores en PL/SQL.*/


Directrices de programación

Siga las directrices de programación mostradas a continuación para producir código claro y reducir el mantenimiento al desarrollar un bloque PL/SQL.

  • Documentar el código con comentarios.
  • Usar una nomenclatura constante para los identificadores y otros objetos.
  • Usar buena indentación para facilitar la legibilidad.

La siguiente tabla proporciona pautas para escribir código en mayúscula o minúscula y así ayudar a distinguir las palabras claves de nombres de objetos.

Categoria

Modo

Ejemplos

Sentencias SQL

Mayúsculas

SELECT, INSERT

Palabras Claves de PL/SQL

Mayúsculas

DECLARE, BEGIN, IF

Tipos de Datos

Mayúsculas

VARCHAR2, BOOLEAN

Identificadores y Parametros

Minúsculas

v_sal, emp_cursor, g_sal, p_empno

Tablas de Base de Datos

Minúsculas, Plural

employees, departments

Columnas de Base de Datos

Minúsculas, Singular

employee_id, department_id

La Indentación

Para mayor claridad y legibilidad, es recomendable indentar cada nivel de código. Para mostrar la estructura, se puede dividir el codigo mediante el uso de líneas de retorno de carro y se puede indentar las líneas mediante el uso de los espacios y tabulaciones.

Ejemplos:

--Código no Indentado:
IF x>y THEN max:=x;ELSE max:=y;END IF;

--Código Indentado:
IF x > y THEN
    max := x;
ELSE 
    max := y;
END IF;

/*Los dos Códigos hacen lo mismo pero es indiscutible que el segundo es mucho mas entendible gracias a la Indentacion;*/


Fuente: Oracle Database: PL/SQL Fundamentals

Published in PL/SQL
Tagged under
Domingo, 07 Junio 2020 22:56

SQL Dinámico y Sentencias DDL en PL/SQL

Objetivos:
• Sentencias DDL PL/SQL.
• SQL Dinámico.
  1. SQL Dinámico Nativo.
     -Sentencia EXECUTE IMMEDIATE.
     -Sentencias OPEN-FORFETCH CLOSE
  2. Package DBMS_SQL.
• SQL Dinámico Nativo vs Package DBMS_SQL.

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

SQL Dinámico
SQL Dinámico es una metodología de programación que genera y ejecuta sentencias SQL en tiempo de ejecución. Es útil cuando se escriben programas flexibles y de propósito general como sistemas que usan Consultas Ad-Hoc (Ad-Hoc Queries), cuando se escriben programas que deben ejecutar instrucciones DDL o cuando en tiempo de compilación no se sabe el texto completo de una instrucción SQL o el número o tipos de datos de sus variables de entrada/salida.

Formas de escribir SQL dinámico PL/SQL:
• SQL dinámico nativo, una característica de lenguaje PL/SQL (es decir, nativa) para crear y ejecutar instrucciones SQL dinámicas.
• Package DBMS_SQL, una API para crear, ejecutar y describir sentencias SQL dinámicas.

Un código SQL dinámico nativo resulta más fácil de leer y escribir que su equivalente utilizando el paquete DBMS_SQL, de igual forma el primero se ejecuta notablemente más rápido (especialmente cuando puede ser optimizado por el compilador). Sin embargo, para escribir código SQL dinámico nativo, debe saber en tiempo de compilación el número y los tipos de datos de las variables de entrada y salida de la instrucción SQL dinámica. Si en tiempo de compilación no conoce esta información, debe utilizar el paquete DBMS_SQL.

En caso de necesitar ambos (Paquete DBMS_SQL y SQL dinámico nativo), puede cambiar entre ellos utilizando la función DBMS_SQL.TO_REFCURSOR y la función DBMS_SQL.TO_CURSOR_NUMBER.

Necesidad de SQL Dinámico.
En PL/SQL, se necesita SQL dinámico para ejecutar lo siguiente:
• SQL cuyo texto es desconocido en tiempo de compilación.
Ejemplo: Una instrucción SELECT que incluye un identificador desconocido en tiempo de compilación (como un nombre de tabla) o una cláusula WHERE en la que el número de subcláusulas es desconocido en tiempo de compilación.
• Cualquier construcción SQL que no sea compatible como SQL Estático.
Si no necesita SQL Dinámico, utilice SQL Estático, el cual tiene las siguientes ventajas:
• Una compilación exitosa comprueba que las sentencias SQL estáticas hagan referencia a objetos de base de datos válidos y que el usuario tenga los privilegios necesarios para acceder a esos objetos.
• La compilación exitosa crea dependencias de objetos de esquema.

Uso de SQL Dinámico Nativo
SQL dinámico nativo procesa la mayoría de las sentencias SQL dinámicas por medio de la sentencia EXECUTE IMMEDIATE.

Si la sentencia SQL dinámica es una instrucción SELECT que devuelve varias filas, SQL dinámico nativo le ofrece las siguientes opciones:
• Utilice la sentencias EXECUTE IMMEDIATE con la cláusula BULK COLLECT INTO.
• Utilice las sentencias OPEN-FORFETCH CLOSE.

Puede utilizar variables Bind como parámetros dinámicos en las sentencias EXECUTE IMMEDIATE OPENSQL Dinámico Nativo incluye las siguientes capacidades:
• Define la sentencia SQL dinámica.
• PL/SQL soporta instancias Bind de cualquier tipo de datos SQL.
• Maneja variables Bind con modo: ININ OUT y OUT que están enlazadas por posición, no por nombre.

Nota: Los Atributos de Cursor SQL funcionan de la misma manera con SQL dinámico nativo como con SQL Estático.

Sentencias DDL y PL/SQL
PL/SQL (Procedural Language/Structured Query Language) es un Lenguaje Procedimental o Lenguaje de Procedimiento combinado con SQL que tiene como fin fundamental facilitar el manejo de los datos en una Base de Datos OraclePL/SQL garantiza la correcta y completa consistencia de la información, todo esto, exponiendo la base de datos sólo a través de una interfaz que oculta los detalles de la implementación.

Por la anterior introducción podemos deducir que una de las razones por la cual no es posible introducir sentencias DDL en un bloque de PL/SQL es porque dicho lenguaje está orientado al manejo de los datos. Es bueno recargar que las sentencias DDL cambian la definición del esquema, o sea, crean, modifican o destruyen objetos de base de datos (Tablas, Columnas, Indices y demás) y una operación como tal, por lo general es realizada una sola vez.

Sin embargo, a veces surgen casos en los cuales se requiere realizar operaciones inusuales tales como la necesidad de ejecutar una operación DDL en un bloque de PL/SQL. Para estas excepciones nos valemos del SQL Dinámico (Dynamic SQL) presentado a continuación.

Sentencia EXECUTE IMMEDIATE
La instrucción EXECUTE IMMEDIATE ejecuta una sentencia SQL dinámica o un bloque PL/SQL anónimo. Puede usarla para emitir sentencias SQL que no se pueden incluir directamente en PL/SQL o para generar sentencias en las que no se conocen los nombres de tabla, cláusulas WHERE, etc.

Sintaxis:
execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
   [ INTO { define_variable [, define_variable ...] | record_name } ]
   [ USING [ IN | OUT | IN OUT ] bind_argument
       [, [ IN | OUT | IN OUT ] bind_argument] ... ]
   [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];

Donde:

• bind_argument: Una expresión cuyo valor se pasa a la instrucción SQL dinámica o una variable que almacena un valor devuelto por la instrucción SQL dinámica.
• define_variable: Una variable que almacena el valor de una columna seleccionada.
• dynamic_string: La cadena literal, variable o expresión que representa una sola sentencia SQL o un bloque PL/SQL. Debe ser de tipo CHAR VARCHAR2, no NCHAR NVARCHAR2.
• INTO: Utilizada sólo para consultas de una sola fila, esta cláusula especifica las variables o registros en los que se recuperan los valores de las columnas. Para cada valor recuperado por la consulta, debe haber una variable o campo correspondiente compatible con el tipo en la cláusula INTO.
• record_name: Record %ROWTYPE (o definido por el usuario) que almacena una fila seleccionada.
• RETURNING INTO: Utilizada sólo para sentencias DML que tienen una cláusula RETURNING (sin una cláusula BULK COLLECT), esta cláusula especifica las variables Bind en las que se devuelven los valores de columna. Para cada valor devuelto por la sentencia DML, debe haber una variable correspondiente compatible con el tipo en la cláusula RETURNING INTO.
• USING: Especifica una lista de argumentos bind de entrada y/o salida. El modo de parámetro predeterminado es IN.

Nota: Puede utilizar literales numéricos, de caracteres y cadena como argumentos Bind, pero no puede utilizar literales booleanos (TRUEFALSE NULL).

Ejemplos:
CREATE PROCEDURE proc_create_table(
                                    p_table_name    VARCHAR2,
                                    p_col_specs     VARCHAR2
                                  ) IS
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE '||p_table_name||
                      '('||p_col_specs||')';
END;
/*En el pasado ejemplo creamos el procedimiento: proc_create_table el cual recibe dos parámetros, uno equivalente al nombre de una tabla y el segundo a las especificaciones de dicha tabla.*/
 
BEGIN
    proc_create_table('EMPLOYEE_NAMES',
                       'id NUMBER(4) PRIMARY KEY, name VARCHAR2(40)'
                      );
END;
/*En el bloque anterior vemos como podemos implementar el procedimiento: proc_create_table. El ejemplo crea la tabla: EMPLOYEE_NAMES con dos columnas: id y name*/
 
CREATE PROCEDURE proc_add_col(
                                p_table_name    VARCHAR2,
                                p_col_spec      VARCHAR2
                             ) IS
    v_statement     VARCHAR2(100) := 'ALTER TABLE '||p_table_name||
                                      ' ADD '||p_col_spec;
BEGIN
    EXECUTE IMMEDIATE v_statement;
END;
/*Ahora creamos el procedimiento: proc_add_col que tiene como función agregar una columna o constraint a una tabla previamente creada.*/
 
BEGIN
    proc_add_col('employee_names', 'salary number(8,2)');
END;
/*El pasado ejemplo muestra el uso de proc_add_colNotar como luego de ejecutar el bloque es agregada la columna salary a tabla employee_names.*/
 
DECLARE
    v_table_name    VARCHAR2(11)    :=  'my_table';
BEGIN
    BEGIN
        EXECUTE IMMEDIATE
                        'CREATE TABLE '||v_table_name||'
                        (
                            code   NUMBER,
                            name   VARCHAR2(15)
                        )';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;

    FOR i IN 1..3 LOOP
        EXECUTE IMMEDIATE
                        'INSERT INTO '||v_table_name||' (code, name)
                         VALUES (:text_string1, :text_string2)'
        USING i, 'nombre'||i;
    END LOOP;
END;
/*En el ejemplo se crea un tabla con un nombre X y posteriormente se insertan 3 registros en dicha tabla. Notar el uso de la cláusula USING combinada con las variables Bind: :text_string1 y :text_string2.*/
 
CREATE FUNCTION proc_del_rows(
                                p_table_name    VARCHAR2
                             )  RETURN NUMBER IS
BEGIN
      EXECUTE IMMEDIATE 'DELETE FROM '||p_table_name;
      RETURN SQL%ROWCOUNT;
END;
/*En este ejemplo creamos la función: proc_del_rows que no solo elimina los registros de la tabla que recibe como parámetro, sino que también retorna el numero de registros eliminados.*/
 
SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE(proc_del_rows('my_table')|| ' rows deleted.');
END;
/*Ahora usamos la función proc_del_rows para eliminar todos los registros de la tabla: my_table (creada en uno de los ejemplos anteriores), a continuación vemos el OUTPUT.*/
---OUTPUT:
Dinamic Delete
 
DECLARE
    TYPE    typ_details IS RECORD
    (
        code        NUMBER,
        name        VARCHAR2(50),
        dept_job    VARCHAR2(50)
    );
 
    TYPE    typ_tab IS
                    TABLE OF    typ_details
                        INDEX BY BINARY_INTEGER;
    v_tab   typ_tab;
    PROCEDURE   proc_bring__details
                                    (
                                        p_col_value     VARCHAR2,
                                        p_list_emps     OUT typ_tab
                                    )   IS
        BEGIN
            EXECUTE IMMEDIATE
                            '
                            SELECT
                                    e.employee_id,
                                    e.first_name||'' ''||e.last_name  AS name,
                                    (
                                      SELECT  d.department_name
                                      FROM    hr.departments d
                                      WHERE   d.department_id = e.department_id
                                    )
                            FROM    hr.employees e
                            WHERE   e.job_id  = :v_filter
                            UNION
                            SELECT
                                    e.employee_id,
                                    e.first_name||'' ''||e.last_name  AS name,
                                    (
                                      SELECT  j.job_title
                                      FROM    hr.jobs j
                                      WHERE   j.job_id = e.job_id
                                    )
                            FROM    hr.employees e
                            WHERE   TO_CHAR(e.department_id)  = TO_CHAR(:v_filter)
                            '
             BULK COLLECT INTO p_list_emps USING    p_col_value, p_col_value;
        END proc_bring__details;
BEGIN
    proc_bring__details(30, v_tab);
    
    FOR i   IN NVL(v_tab.FIRST, 1)..NVL(v_tab.LAST,0) LOOP
        DBMS_OUTPUT.PUT_LINE
                          (
                            v_tab(i).code||', '||RPAD(v_tab(i).name, 10, ' ')||', '||v_tab(i).dept_job
                          );
    END LOOP;
END;
/*En este ejemplo se hizo lo siguiente: Un bloque anónimo que contiene un tipo Record de 3 atributos,  una tipo tabla que hace referencia al record anterior y un procedimiento anidado (proc_bring_details) con dos parámetros, un parámetro de entrada tipo carácter y otro de salida tipo tabla (previamente creada)El procedimiento anidado ejecuta una consulta dinámica que filtra por el código de departamento o por el tipo de empleo sin necesidad de que el usuario indique que tipo de filtro desea aplicar; Una vez realizada la consulta los resultados son asignados al parámetro tipo tabla mediante la cláusula BULK COLLECT.  La siguiente imagen muestra los resultados dependiendo del valor recibido.*/
---OUTPUT:
Dinamic Select 1

Sentencias OPEN-FOR, FETCH y CLOSE
La sentencia OPEN-FOR ejecuta la instrucción SELECT asociada a una variable tipo cursor. OPEN-FOR asigna recursos de base de datos para procesar la sentencia, identifica el conjunto de resultados (las filas que cumplen las condiciones) y coloca la variable tipo cursor antes de la primera fila en el conjunto de resultados.

Con la cláusula USING (opcional), OPEN-FOR procesa una sentencia SELECT dinámica que devuelve varias filas: asocia una variable tipo cursor con el SELECT, ejecuta la sentencia, identifica el conjunto de resultados, posiciona el cursor antes de la primera fila del resultado y pone en cero el recuento de filas procesadas (%ROWCOUNT).

Directrices:
• La cláusula USING no puede contener el NULL literal. Para evitar esta restricción, utilice una variable no inicializada donde desea utilizar NULL.
• Utilice la instrucción FETCH para recuperar las filas de conjunto de resultados de una en una, varias a la vez o todas a la vez.
• Utilice la instrucción CLOSE para cerrar la variable tipo cursor.

Sintaxis:
open_for_statement ::=
OPEN { cursor_variable_name | :host_cursor_variable_name }
  FOR select_statement [ using_clause ] ;

using_clause ::=
USING [ IN | OUT | IN OUT ] bind_argument
  [ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...

Ejemplo:
CREATE PROCEDURE list_employees(deptid NUMBER) IS
    TYPE emp_refcsr IS REF CURSOR;
    emp_cv          emp_refcsr;
    emprec          employees%ROWTYPE;
    v_statement     VARCHAR2(200) := 'SELECT * FROM employees';
BEGIN
    IF deptid IS NULL THEN
        OPEN emp_cv FOR v_statement;
    ELSE
        v_statement := v_statement || ' WHERE department_id = :id';
        OPEN emp_cv FOR v_statement USING deptid;
    END IF;
 
    LOOP
        FETCH emp_cv INTO emprec;
        EXIT WHEN emp_cv%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emprec.department_id||
                      ' ' ||emprec.last_name);
    END LOOP;
    CLOSE emp_cv;
END;
/*En el pasado ejemplo se creo el procedimiento list_employees que muestra en pantalla la lista de empleados de algún departamento especificado, en caso de pasar un valor nulo se mostrarían todos los registros de la tabla employees. Notar el uso de los REF CURSOR.*/
---OUTPUT:
OPEN FOR

Paquete DBMS_SQL
Utilizando DBMS_SQL, puede escribir procedimientos almacenados y bloques PL/SQL anónimos que usan SQL Dinámico, como ejecutar sentencias DDL en PL/SQL, por ejemplo, ejecutar una sentencia DROP TABLE. Las operaciones proporcionadas por este paquete se realizan bajo el usuario actual, no bajo el propietario del paquete (SYS). El paquete DBMS_SQL proporciona los siguientes subprogramas para ejecutar SQL dinámico:

• OPEN_CURSOR para abrir un nuevo cursor y devolver un número de ID del mismo.
• PARSE para analizar la instrucción SQL, es decir, comprueba la sintaxis de la sentencia y la asocia con el cursor abierto. Las sentencias DDL se ejecutan inmediatamente cuando se analizan.
• BIND_VARIABLE para enlazar un valor dado a una variable Bind identificada por su nombre en la sentencia analizada. No es necesario si la sentencia no tiene variables Bind.
• EXECUTE para ejecutar la instrucción SQL y devolver el número de filas procesadas.
• FETCH_ROWS para recuperar la siguiente fila de una consulta (se usa en un bucle para varias filas).
• CLOSE_CURSOR para cerrar el cursor especificado.

Nota: El uso del paquete DBMS_SQL para ejecutar instrucciones DDL puede resultar en un interbloqueo. Por ejemplo, al utilizar dicho paquete para eliminar un procedimiento que todavía está utilizando.

Para procesar dinámicamente una sentencia DML, realice los pasos siguientes:
1. Utilice OPEN_CURSOR para establecer un área en la memoria para procesar una instrucción SQL.
2. Utilice PARSE para establecer la validez de la instrucción SQL.
3. Utilice la función EXECUTE para ejecutar la instrucción SQL. Esta función devuelve el número de filas procesadas.
4. Utilice CLOSE_CURSOR para cerrar el cursor.

Los pasos para ejecutar una instrucción DDL son similares; Pero el paso 3 es opcional porque una instrucción DDL se ejecuta inmediatamente cuando el PARSE se realiza con éxito, es decir, la sintaxis de la sentencia y la semántica son correctas. Si utiliza la función EXECUTE con una instrucción DDL , no haría nada y devolvería un valor de 0 para el número de filas procesadas, ya que las instrucciones DDL no procesan filas.

Ejemplo:
CREATE OR REPLACE FUNCTION delete_all_rows
                                        (
                                            table_name VARCHAR2
                                        )   RETURN NUMBER IS
    csr_id INTEGER;
    rows_del    NUMBER;
BEGIN
    csr_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(csr_id,'DELETE FROM '||table_name, DBMS_SQL.NATIVE);
 
    rows_del := DBMS_SQL.EXECUTE (csr_id);
    DBMS_SQL.CLOSE_CURSOR(csr_id);
    RETURN rows_del;
END;
/*En el anterior ejemplo notamos el uso de paquete DBMS_SQL. Notar que es requerido usar sus métodos para así realizar una operación X. En los dos siguientes métodos mostraos la implementación de la función antes creada.*/
 
CREATE TABLE EMP_COPY AS
    SELECT * 
FROM employees;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Rows Deleted: ' ||delete_all_rows('EMP_COPY')); 
END;
---OUTPUT:
DBMS SQL
 
CREATE PROCEDURE insert_row(
                            table_name VARCHAR2,
                            id VARCHAR2,
                            name VARCHAR2,
                            region NUMBER
                           ) IS
    csr_id     INTEGER;
    stmt       VARCHAR2(200);
    rows_added NUMBER;
BEGIN
    stmt := 'INSERT INTO '||table_name||
            ' VALUES (:cid, :cname, :rid)';
    csr_id := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(csr_id, stmt, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(csr_id, ':cid', id);
    DBMS_SQL.BIND_VARIABLE(csr_id, ':cname', name);
    DBMS_SQL.BIND_VARIABLE(csr_id, ':rid', region);
    rows_added := DBMS_SQL.EXECUTE(csr_id);
    DBMS_SQL.CLOSE_CURSOR(csr_id);

    DBMS_OUTPUT.PUT_LINE(rows_added||' row added');
END;
/*En este ejemplo notamos otro uso del paquete DBMS_SQL. Como vemos es requerido un paso adicional para poder asociar una variable Bind a la sentencia dinámica.*/
---OUTPUT:
DBMS SQL 2
Después que la sentencia es analizada (PARSE), debe llamar al procedimiento DBMS_SQL.BIND_VARIABLE para asignar valores para cada variable Bind que existe en la sentencia. La vinculación de valores debe realizarse antes de ejecutar el código.
 
Para procesar dinámicamente una instrucción SELECT, realice los siguientes pasos después de abrir y antes de cerrar el cursor:
1. Ejecute DBMS_SQL.DEFINE_COLUMN para cada columna seleccionada.
2. Ejecute DBMS_SQL.BIND_VARIABLE para cada variable de enlace en la consulta.
3. Para cada fila, haga lo siguiente:
• Ejecute DBMS_SQL.FETCH_ROWS para recuperar una fila y devolver el número de filas obtenidas. Detenga el procesamiento adicional cuando se devuelve un valor cero.
• Ejecute DBMS_SQL.COLUMN_VALUE para recuperar cada valor de columna seleccionado en la variable PL/SQL para su procesamiento.
 
Nota: Aunque el proceso de codificación con DBMS_SQL no es complejo, resulta mas lento y mas propenso a errores en comparación con el uso del enfoque SQL Dinámico Nativo.

Comparación: SQL Dinámico Nativo vs Paquete DBMS_SQL
El SQL Dinámico Nativo proporciona las siguientes ventajas sobre el paquete DBMS_SQL.
• Facilidad de uso: Debido a que el SQL Dinámico Nativo está integrado con SQL, puede utilizarlo de la misma forma en que utiliza SQL Estático dentro del código PL/SQL. El código suele ser más compacto y legible en comparación con el código escrito con el paquete DBMS_SQL.
• Mejora de rendimiento: SQL Dinámico Nativo se desempeña significativamente mejor que DBMS_SQL, en la mayoría de las circunstancias, debido al soporte nativo proporcionado por el intérprete PL/SQL. El enfoque DBMS_SQL utiliza una API de procedimiento y sufre de muchas llamadas a procedimientos y copia de datos de sobrecargas.
• Soporte para tipos definidos por el usuario: SQL Dinámico Nativo soporta todos los tipos soportados por SQL Estático en PL/SQL. Por lo tanto, SQL Dinámico Nativo proporciona soporte para tipos definidos por el usuario, como objetos, colecciones y REFs. El paquete DBMS_SQL no admite tipos definidos por el usuario. Sin embargo, tiene soporte limitado para matrices.
• Soporte para la obtención de registros: con SQL Dinámico Nativo, las filas resultantes de una consulta se pueden obtener directamente en registros PL/SQL. El paquete DBMS_SQL no admite la obtención de estructuras tipo registros.

Published in PL/SQL

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.