10. ARQUITECTURA DE SQL SERVER

Los componentes de SQL se dividen en dos amplios grupos: el motor relacional y las utilidades externas.

SQL Server Database Engine

Es el núcleo del servidor, se encarga de la gestión del trabajo de las bases de datos. Incluye los siguientes componentes:

·        Algebrizer: comprueba los comandos SQL y los transforma para ser procesados internamente. Query optimizer: determina la forma óptima de procesar los comandos SQL.

·        Query Engine: ejecuta finalmente los comandos de acuerdo con el plan generado por el optimizador. Motor de almacenamiento: procesa las operaciones de E/S a disco.

·        Buffer Manager: optimiza el uso  dde memoria reucieno la dependencia de acceso a disco. Checkpoint: realiza operaciones de escritura de memoria a los ficheros.

·        Resource monitor: optimiza la caché de consultas con relación a la carga actual de memoria. Lock manager: gestiona los bloqueos sobre distintos objetos de la base de datos.

·        SQLOS: es una capa añadida que funciona como un pequeño sistema operativo capaz de gestionar recursos propios del servidor tajes como memoria, hilos, peticiones de E/S, etc.

Transact·SQL

Del mismo modo que la mayoría de los sistemas gestores, SQL Server implementa su propia versión de SQL que incorpora él estándar además de extensiones y mejoras incluidas por SQL Server. Todos los comandos enviados al servidor deben ser comandos T·SQL válidos distinguiendo entre los DML (comandos de modificación), DDL (comandos de definición) y DCL(comandos de control).

Policy based manamement

O PBM es el conjunto de declaraciones administrativas que afectan al servidor, a las bases de datos y a cualquier objeto de estas.

Es un componente que centraliza las tareas de administración.

NET Common Language Runtime

Es un componente que permite la integración de Visual Sturlio de Microsoft con SQL Server de forma que se pueda ampliar la funcionalidad de T-SQL tanto para crear prutinas almacenadas como tablas o bases de datos desde el p ropio Visual Studio.

Service Broker

Es un componente que permite balancear la carga de proceso de forma dinámica mediante el uso de buffering, colas e instancias para cada petición.

Replication services

Estos servicios sirven para poder hacer réplicas de datos en diferentes ubicaciones de manera dinámica así como poder realizar transacciones que impliquen datos distribuidos.

Búsquedas Full-text

Estas búsquedas, realizadas sobre todas las palabras de un texto se optimizan enormemente con índices full-text ya que en estos se index cada palabra del campo correpondiente.

Server Management Object (SMO)

Son un conjunto de objetos que porporcionan la configuración con el objeto de facilitar la programación y el desarrollode scripts en el servidor.

Servicios de SQL Server

Estos componentes son de tipo cliente y sirven para controlar o comunicarse con el servidor.

·        Agente SQL Server

Realiza trabajos automáticos del servidor. Base de datos de correo

·        Permite envío de correo

      Permite envío de correo vía smtp. Este correo puede provenir del  propio dservidor, código T-SQL, tareas programadas, alertas, etc.

·        Distributed Transaction Coordinator (DTC)

Proceso que gestiona transacciones que afectan a varios servidores.

Business lntelligence (BI)

Es el conjunto de herramientas que permiten la gestión de datos con el objeto de reaHzar análisis, informes, data mining y visualización. En este sentido incluye tres herramientas que pueden ser utilizadas con el -Busin.ess inteligence Development Studio (BIDS) una versión de Visual Sturuo para el soporte de BI compatible con Visual Studio.

• Integration Services (18) permite la obtención y transformación de datos de toda clase de fuentes para ser integrados con la herramienta extract-transform-loo.d (ETL) del servidor.

• Analysis Services para el análisis online de bases de datos multidimensionales mediante OLAP (On.line Analytical Processing) nsí como métodos de búsqueda de patrones y tendencias en datos mediante Data- Mining.

• Reporling Services (RS) para la visualziación vía web y obtención de informes de datos y su exportación a distintos formatos como pdfo Excel.

Interfaces de usuario (UI) y otras herramientas

• SQL Server Management Studio 
  Es la herramienta más potente y útil para el administrador y desaroolador de bases de datos. Porporciona una interfaz gráfica para la gestión de la mayoría de caracterfsticas de SQL Server tanto de administración como de desarrollo incluyendo el Solution Explorer para la gestión de proyectos.
•   SQL Server Configuration Manager
  Herramienta utilizada para iniciar y detener el servidor así como para establecer parámetros de configuración y conectividad
• SQL Profilertrrace
  Permite depurar aplicaciones y hacer seguimiento de ciertas trazas de eventos u operaciones sobre datos en tiempo real para facilitar la optimi zación del servidor,
• Performance Monitor
  Es una herramienta visual para monitorizar el estado de] servidor y todos sus procesos y operaciones en un momento dado.

Diccionario de datos en SQL Server

Como en la mayoría de gestores de datos, SQL Server incorpora un diccionario de datos en el que se registran toda la información relativa a los distintos objetos de las bases de datos y del servidor,

Esta puede ser consultado con los comandos T-SQL o a través del el Object Explorer de Managemen.t Studio. El diccionario se divide básicamente en dos partes.

·        Bases de datos del sistema

o   Master: con información de las bases de datos del servidor.

o   Msdb: mantiene un listado de actividades de backup y tareas ojobs para cada usuario.

o   Tempdb: espacio temporal para creación de tablas, bases, rutinas almacenadas o para espacio temporal en el procesamiento de consultas.

·        Vistas

o   Es donde se almacena la información realmente 'tuil del diccionario se compone de las siguientes vistas: Catalogo: contiene información de datos estáticos acerca de tablas, seguridad y configuración del servidor.

o   Dyn.amic management uiew (DMW): almacena información de estado del servidor en cuanto a recursos en uso y onexiones activas.

o   Funciones de sistema y  variables globales: información de estado del servidor, de las bases de datos y variables de configuración.

o   Information schema: con información sobre objetos de cada base de datos siguiendo el estándar ISO de SQL,

Ediciones de SQL Server

Existen di versas ediciones del Servidor SQL a continuación veremos un listado de las mi smas con una brve descripción. Finalmente mostraremos el proceso de instalación de la edición libre express.

• Enterprise CDeueloper) edition.: es la más avanzada e incluye todas las posibilidades del servidor. Estaá oritentada a grandes sistemas de muchas bases de datos. La versión developer es igual pero solo liceniada para pruebas y desarrollo.
• Standardedition: es la edición para sistemas medios y recomendable para probar antes de pasar a la Enterprise Edition,
• Workgroup edition: edición más ligera ya que no incluye el componente Integration Services. Está orientada para pequeños negocios.
• Edición Web : sirve para alojar aplicaciones de sitios web. En partículas la SQL Server Express Edition es una versión limitada a 4GB de espacio para bases de datos y no más de 25 usuarios. Recomendable para pequeñas aplicaciones .Net que utilicen SQL server como base de datos.
• Edición compacta o Compact Edition (CE): es un servidor compacto más limitado que el anterior y cuyo uso está orientado para aplicaciones de móviles.
• SQL Data Seruices (SDS): proporciona características de alta disponibilidad y es la base de datos de Microsoft Azure.

Cuentas de usuario y permisos

La arquitectura de seguridad de SQL server pude representarse mediante el siguiente esquema:

La autenticación en SQL server es similar a MySQL, una vez autenticado el usuario se le otorgan los permisos correspondientes. La información de login se almacena en la base de datos master.

Un usuario en general se identifica mediante tres métodos posibles:

• Como usuario de Windows con su login correspondiente.
• Como miembro de un grupo de usuarios Windows.
• Como usuario de SQL Server con el login específico del servidor.

Una vez autenticado el usuario tiene los permisos que se le hayan concedido via mediante grant I incluyendo los correspondientes a los roles que tenga asignados.

A nivel de sistema operativo debe notarse que aunque lo susurios no tienen porque interaccionar con el sistema de archivos (salvo a través del servidor) si debemos considerar que el proceso SQL Server tiene que hacerlo por lo cual req uiere una cuenta de tres posibles:

·        Local: para cuando no es necesario trabajar en red.

·        Local de sistema: es buena para un solo servidor peor falla cuando hay procesamiento distribuido en red.

·        Cuenta de dominio: es la opción recomendada ya que es una cuenta creada ad-hoc para el servidor a la que se pueden otorgar distintos privilegios.

Vamos a ver ahora las opciones más interesantes desde el punto de vista de T-SQL:

Gestion de cuentas

Eliminar una cuenta

Denegar una cuenta de Windows

Modificar cuentas

De manera similar a la creación de cuentas podemos modificarlas con alter login:

A nivel de base de datos,los usuarios son independientes de las cuentas, así podemos crear y eliminar usarios a partir de cuentas o logins para cada base de datos.

Crear usuarios

Borrar usuarios

Modificar usuarios

SQL Server utiliza el paradigma de roles para englobar un conjunto de permisos bajo un mismo nombre. Distinguimos roles de sistema y de bases de datos. Del primero existen algunos predefinidos, por ejemplo Dbcrealor que permite crear, modificar, eliminar y restaurm' cualquier base de datos o el rol Processadmin para crear y eliminar procesos del servidor. Aunque sin duda es el Sysadmin el usuario que tiene todo el control sobre todos los objetos del sistema.

En el caso de roles de bases de datos existe el db_owrter con todos los permisos sobre la base de datos o db_ ddladmin que puede ejecutar comandos ddi como u·eate. drop o alter.

Para añadir un usuario a un rol se usa un procedimiento alamcenado llamado sp_addsrutolemember..

Para eliminarlo sp_dropsruroiemember.

♠

Para crear/eliminar un rol usarnos create role.

♠

Para crear/eliminar un rol usarnos create role.

♠

Para eliminar un miembro de un rol:

♠

Gestion de permisos y privilegios

En SQL Server los permisos se otorgan sobre los distintos objetos usando los comandos DeL (GRANT, REVOKE y DENY) y procedimientos almacenados predefinidos.

Se distinguen permisos sobre objetos y en forma de sentencia.

Son los stalement permissions que incluyen alter table, alter datahase o create function y se otorgan con la siguiente sintaxis:

♠

Donde security account puede ser un rol, un usuario de SQL Server o un usuario o grupo de Windows.

Los pennisos posibles sobre objetos induyen seLect, insert, update, deleu, dri (para claves ajenas) y execute para ejecución de procedimientos almacenados.

Para conceder permisos se usa el comando grant:

♠

Del mismo modo para revocar permisos usamos:

Encriptación

SQL server incropora la funcionalidad de encriptación de datos usaodo cuatro métodos:

Frase de paso

Es como usar una contraseña pero con más restricciones.

En el ejemplo se usa la función EncryptByPassPhrase con la frase de paso seguida de los datos bancarios a encriptar para lo cual el campo número de cuenta debe ser de tipo uarbinary:

Para desencriptar la información puede usarse la función DecryptByPassPhrase.

Las frases de paso son texto claro y poco seguras así que es mejor usar encriptación simétrica con el comando CREATE SIMETRIC KEY, como en el ejemplo:

♠

Donde el algoritmo usado es típicamente triple_des o aes~50,  aunque hay algunos más.

Para  usar la clave debe ser abierta en primer lugar con el comando OPEN SIMETRIC KEY ccardkey DECRIPTION BY PASSWORD= P@s$wOrD              '                  '.

Después hacemos las operaciones normales como inserción de datos:

♠

Después para desencriptar usamos decriptbykey y finalmente la cerramos con:

♠

9.TRIGGERS

 

 Triggers en Transact SQL

SQL Server proporciona dos tipos de triggers:

Trigger DML:

se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML).

Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.

Trigger DDL:

se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DOL.

Trigger DML

Los trigger OML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.

La sintaxis general de un trigger es la siguiente:

Las instrucciones de triggers DML utilizan dos tablas especiales denominadas inserted y deleted. SQL Server crea y administra automáticamente ambas tablas. La estructura de las tablas inserted y deleted es la misma que tiene la tabla que ha desencadenado la ejecución del trigger.

La primera tabla (inserted) solo está disponible en las operaciones lNSERT y UPDATE y en ella están los valores resultantes después de la inserción o actualización. Es decir, los datos insertados. lnserted estará vacía en una operación DELETE.    

En la segunda (deleted), disponible en las operaciones UPDATE y DELETE, están los valores anteriores a la ejecución de la actualización o borrado. Es decir, los datos que serán borrados. Deleted estará vacía en una operación INSERT.

¿No existe una tabla UPDATED?

No, hacer una actualización es lo mismo que borrar(deleted)e insertarlos nuevos (inserted ). La sentencia UPDATE es la única en la que inserted y deleted tienen datos simultáneamente.

No se puede modificar directamente los datos de estas tablas.

El siguiente ejemplo graba un histórico de saldos

Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instrucción DML (UPDATE, INSERT o DELETE) no haya afectado a ninguna fila. En este caso inserted y deleted devolverán un conjunto de datos vacío.

TriggerDDL

Los trigger DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos CDOL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de T-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.

La sintaxis general de un trigger es la siguiente:

8. PROCEDIMIENTOS CON Y SIN PARAMETROS

 

 PROCEDIMIENTOS CON Y SIN PARAMETROS

Como en el caso de MySQL son bloques PL/SQL que no pueden devolaver ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.

Los parámetros pueden ser de entrada (1N), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es

INy se toma ese valor en caso de que no especifiquemos nada. En el siguiente ejemplo se actualiza el saldo de una cuenta.

7. CURSORES EN PL/SQL

 

 CURSORES EN PL/SQL

Un cursor es un conjunto de registros devuelto por una instrucci6n SQL. 

Son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT.  

Ejemplo

CURSOR C1 IS

SELECT ENAME,JOB,HIREDATE

PUSQL distingue entre cursos implícitos que devuelven cero o una sola fila y los explícitos que pueden devolver varias.

Declarar el cursor 

DECLARE CURSOR cursor name [(parameterl , parameter] ... )] 

{RETURN return_typel 1S select_statement ; 

Donde el tipo devuelto representa una fi la de una tabla

 cursor-parameter_name [IN} datatype [{ :- I DEFAULT) e xpression] 

Ejemplo

DECLARE

V_ENAME EMP.ENAME%TYPE;

V_JOB  EMP.JOB%TYPE;

V_HIREDATE EMP.HIREDATE%TYPE;

CURSOR C1 IS

SELECT ENAME,JOB,HIREDATE

Abrir el cursor 

Usamos la instrucción OPEN. 

Ejemplo

BEGIN

OPEN C1;

Leer los datos del cursor

Con la instrucción FETCH.

Ejemplo

FETCH C1 INTO V_DEPTNO,V_DNAME,V_LOC; 

Lo procesamos con un loop.  

Ejemplo

WHILE C1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(V_DEPTNO||' '||V_DNAME||' '||V_LOC);

FETCH C1 INTO V_DEPTNO,V_DNAME,V_LOC;

END LOOP;

END;

/

Cerrar el cursor

 Para liberar los recursos y cerrar el cursor usamos la instrucción CLOSE.  

Ejemplo 

END;

/

Declaración y utilización de cursores de actualización 

Ejemplo

CURSOR nombre cursor 1S instrucción 

SELECT FOR UPDATE 

Para actualizar los datos del cursor hay que ejecutar una sentencia UPDATE especificando la cláusula WHERE CURRENT OF . 

UPDATE emp SET

EMP.DEPTNO=DEPT.DEPTNO  and ENAME LIKE '%A%'

WHERE CURRENT OF c1 

MANEJO DE ERRORES EN PUSQL

En PllSQL una advertencia o error es considera una excepción. Las excepciones se controlan dentro de su propio bloque.

DECLARE

 - - Declaraciones BEGIN 

-- Ejecucion EXCEPTION 

-- Excepción END;  

 Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION.

Ejemplo

Provocar y atrapar un error del tipo INVALID CURSOR, y sacar en la pantalla el siguiente mensaje (con DBMS OUTPUT) "CURSOR NO VÁLIDO”.

 

Si existe un bloque de excepción apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo.

• PUSQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales.
• Las excepciones predefinidas no necesitan ser declaradas.
• PUSQL permite al usuario definir sus propias excepciones, utilizando la sentencia RAISE.
• La sentencia RAlSE permite lanzar una excepción en forma explícita. 

A continuación prese sentaremos varios ejemplos  de lo ya mencionado 

Ejmeplo

Desplegar nombre del empleado y el nombre del departamento para todos aquellos empleados que tengan una A en su nombre y que además trabajen en el departamento RESEARCH. Usar REGISTROS PL/SQL ( a lo más 1 registro) con atributo %ROWTYPE

Crear un cursor con nombre CI para obtener nombre, ocupación y de contratación de todos los empleados que fueron contratados entre 20 de febrero de 1981 y I de mayo de 1981. Sacar los datos del cursor usando un ciclo básico. Para la condición de salida del ciclo utilizar los atributos del cursor explícito. Con un IF 'verificar si el cursor está abierto, si es así cerrarlo. Imprimir los datos en pantalla con el paquete DBMS OUTPUT

En ocasiones queremos enviar un mensaje de error personalizado al producirse una excepción PUSQL. Para ello es necesario utilizar la instrucción RAlSE...APPLlCATlON_ERROR. 

Ejemplo

Provocar y atrapar un error del tipo ZERO DIVIDE, y sacar en la pantalla el siguiente .mensaje (con DBMS OUTPUT) "NO SE PUEDE DIVIDIR ENTRE CERO".

6.ESTRUCTURAS DE CONTROL DE FLUJO EN PL/SQL

 Existe un número de estructuras de control las cuales nos permiten cambiar el flujo lógico de sentencias dentro de un bloque PL/SQL. A continuación abordamos cuatro de ellas: La instrucción IF, Las expresiones CASE, Los bucles(LOOP, FOR, WHILE), y la sentencia CONTINUE.

____________________________________________________________________________________
Sentencias IF.

En PL/SQL la estructura de la sentencia IF es similar a la estructura de la misma en otros lenguajes de programación. Esta permite a PL/SQL para realizar acciones selectivas basadas en condiciones.

Sintaxis:
IF condition THEN
    statements;
[ELSIF condition THEN
    statements;]
[ELSE 
    statements;]
END IF;

En la sintaxis:

condition	Es una expresión o variable Booleana que devuelve TRUE, FALSE o NULL.
THEN	Introduce una cláusula que asocia la expresión Booleana con la secuencia de instrucciones que le sigue.
statements	Pueden ser una o más instrucciones de PL/SQL o SQL. (Estas instrucciones pueden incluir sentencias IF adicionales y estas contener varios IF, ELSE, y ELSIF anidados.) Las sentencias de la cláusula THEN se ejecutan sólo si la condición en la cláusula IF asociada evalúa TRUE.
ELSIF	Es una palabra clave que introduce una expresión Booleana (Si la primera condición es FALSE o NULL, la palabra clave ELSIF introduce condiciones adicionales).
ELSE	Introduce la cláusula por defecto que se ejecuta si y sólo si ninguno de los predicados anteriores (introducidos por IF y ELSIF) resulta TRUE. Las pruebas se ejecutan en secuencia, de manera que los primeros predicados tienen prioridad sobre los posteriores (si existen dos predicados Verdaderos, solo el primero de ellos se ejecutara.)
END IF	Marca el final de la sentencia IF.

ELSIF y ELSE son opcionales en una instrucción IF. Es posible tener cualquier número  de cláusulas ELSIF pero sólo una cláusula ELSE. END IF marca el final de una instrucción IF y debe terminar con un punto y coma.

 

Una instrucción IF puede tener múltiples expresiones condicionales relacionadas con operadores lógicos como AND, OR y NOT. No hay limitación en el número de las expresiones condicionales. Sin embargo, estas sentencias deben estar relacionadas con los operadores lógicos adecuados.

Directrices:

• Puede realizar acciones selectivas basadas en condiciones encontradas.

• Al escribir código, recuerda la ortografía de las palabras clave:

--ELSIF es una palabra.

--END IF es de dos palabras.

• Si la condición de control Booleana es TRUE, se ejecuta la secuencia de sentencias asociada; Si la condición de control Booleana es FALSE o NULL, se omite la secuencia de sentencias asociada. Es permitido cualquier número de cláusulas ELSIF.

• Es recomendable indentar las sentencias condicionales a ejecutar, ya que esto da mayor claridad y facilita el mantenimiento.

Ejemplos:
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
    v_nombre    employees.last_name%TYPE;
    v_tiempo    NUMBER(2);
    v_codigo    employees.employee_id%TYPE  :=  &codigo;
BEGIN
    SELECT
            last_name,
            TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)/12)
    INTO    v_nombre,
            v_tiempo
    FROM    employees
    WHERE   employee_id = v_codigo;
--
    IF (v_nombre = 'King' AND v_tiempo > 8) OR v_nombre    = 'Moquete' THEN
        DBMS_OUTPUT.PUT_LINE('Este es un manda mas!!!');
    END IF;
END;
/*Este es un simple  ejemplo del uso de la estructura condicional IF; el SCRIPT pide al usuario introducir un código de empleado para así recuperar su apellido y tiempo en la empresa, luego valida los datos extraídos, si el apellido es King y tiene mas de 8 Años o si el apellido es Moquete(sin importar el tiempo), un mensaje se mostrara.*/
---OUTPUT:

SET SERVEROUTPUT ON

DECLARE

    v_edad  NUMBER := 15;

BEGIN

    IF v_edad > 17 THEN

        DBMS_OUTPUT.PUT_LINE('Edad: '||v_edad||' Años'||CHR(10)||'El individuo es Mayor de edad!!!');

    ELSE

        DBMS_OUTPUT.PUT_LINE('Edad: '||v_edad||' Años'||CHR(10)||'El individuo es Menor de edad!!!');

    END IF;

END;

/*En el ejemplo vemos el uso de la cláusula ELSE; como el valor que le pasamos a la variable v_edad no es mayor que 17, la sentencia ejecutada fue la contenida en el ELSE.*/
---OUTPUT:

SET SERVEROUTPUT ON

SET VERIFY OFF

DECLARE

    v_edad  NUMBER;

BEGIN

    v_edad :=  &Edad;

    

    IF v_edad BETWEEN 1 AND 17 THEN

        DBMS_OUTPUT.PUT_LINE('Edad: '||v_edad||' Años'||CHR(10)||'El individuo es Menor de edad!!!');

    ELSIF v_edad BETWEEN 18 AND 90 THEN

        DBMS_OUTPUT.PUT_LINE('Edad: '||v_edad||' Años'||CHR(10)||'El individuo es Mayor de edad!!!');

    ELSIF v_edad BETWEEN 91 AND 115 THEN

        DBMS_OUTPUT.PUT_LINE('Edad: '||v_edad||' Años'||CHR(10)||'WOW, El individuo es Mayor de edad!'

                                                                 ||CHR(10)||'No dejes que se vaya sin que te diga la formula');

    ELSE

        DBMS_OUTPUT.PUT_LINE(v_edad||' Es una Edad Invalida!!!');

    END IF;

END;

/*Este ejemplo muestra el uso de ELSIF; el cual nos ofrece la posibilidad de dar una validación mas especifica.*/

---OUTPUT:

____________________________________________________________________________________
Expresiones CASE.

Una expresión CASE devuelve un resultado basado en una o más alternativas. Para devolver el resultado, la expresión CASE utiliza un selector, que es una expresión cuyo valor se utiliza para retornar una de varias alternativas. El selector es seguido por una o más cláusulas WHEN que se comprueban secuencialmente. El valor del selector determina el resultado a retornar. Si el valor del selector es igual al valor de una cláusula WHEN, el resultado de esa cláusula WHEN es retornado.

Sintaxis:
CASE selector
   WHEN expression1 THEN result1
   [WHEN expression2 THEN result2
   ...
   WHEN expressionN THEN resultN]
  [ELSE resultN+1]
END;

Otra modalidad de expresión CASE es la expresión CASE buscada, la cual no tiene un selector. Mas bien, las cláusulas WHEN en las expresiones CASE contienen las condiciones de búsqueda que generan un valor Booleano en lugar de expresiones que pueden contener un valor de cualquier tipo.

Sintaxis:
CASE
   WHEN search_condition1 THEN result1
   [WHEN search_condition2 THEN result2
   ...
   WHEN search_conditionN THEN resultN]
  [ELSE resultN+1]
END;

Ejemplos:
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
    v_selector      VARCHAR2(15) := '&VALOR';
    v_resultado     VARCHAR2(35);
BEGIN 
    v_resultado := CASE v_selector
       WHEN '2' THEN 'La Primera cláusula WHEN.'
       WHEN 'A' THEN 'La Segunda cláusula WHEN.'
       WHEN '5' THEN 'La Tercera cláusula WHEN.'
      ELSE 'La cláusula ELSE.'
    END;
--
    DBMS_OUTPUT.PUT_LINE('El valor introducido es: '||v_selector||CHR(10)||
                             'Por esta razón entró a '||v_resultado);
END;
/*Este ejemplo muestra el uso de la expresión CASE con un selector; dependiendo del valor introducido por el usuario un mensaje diferente se mostrara.*/
---OUTPUT:

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
    v_valor         VARCHAR2(35) := '&VALOR';
    v_longitud      NUMBER(2)   := LENGTH(v_valor);
    v_resultado     VARCHAR2(39);
BEGIN 
    v_resultado := CASE 
        WHEN v_longitud < 5 THEN 'Se considera Corta.'
        WHEN v_longitud < 15 THEN 'No es Corta pero tampoco Larga.'
        WHEN v_longitud < 25 THEN 'Se puede considerar Larga.'
        ELSE 'Es Larguísima.'
    END;
--
    DBMS_OUTPUT.PUT_LINE('Se introdujo la Cadena: '||v_valor||CHR(10)||
                             'Con: '||v_longitud||' caracter/es, '||v_resultado);
END;
/*Para ver un ejemplo de una expresión CASE buscada, creamos este SCRIPT que evalúa la cantidad de caracteres de una cadena introducida por el usuario y de acuerdo a su longitud nuestra un mensaje.*/
---OUTPUT:

____________________________________________________________________________________
Sentencias CASE.

Recordemos el uso de la sentencia IF. Puede incluir n número de instrucciones PL/SQL en la cláusula THEN así como también en la cláusula ELSE. Del mismo modo, se pueden incluir en la sentencia CASE, lo cual resulta más fácil de leer en comparación con varias sentencias IF y ELSIF.

 

Cómo difiere una Expresión CASE de una Sentencia CASE?

Una expresión CASE evalúa la condición y devuelve un valor, mientras que una sentencia CASE evalúa la condición y lleva a cabo una acción. Una sentencia CASE puede ser un bloque completo de PL/SQL.

• Las sentencias CASE terminan con END CASE ;

• Las expresiones CASE terminan con END;
• El resultado de retorno en las expresiones CASE no termina con punto y coma (;), mientras que las instrucciones contenidas en una cláusula THEN de una sentencia CASE deben terminar con punto y coma.

Sintaxis:
CASE selector
   WHEN expression1 THEN
        statemens;
   [WHEN expression2 THEN
        statemens;
   ...
   WHEN expressionN THEN
        statemens;]
  [ELSE
        statemens;]
END CASE;
---
CASE
   WHEN search_condition1 THEN
        statements;
   [WHEN search_condition2 THEN
        statements;
   ...
   WHEN search_conditionN THEN
        statements;]
  [ELSE
        statements;]
END CASE;

Nota: Mientras que una instrucción IF es capaz de no realizar ninguna acción (las condiciones podrían ser todas falsas y la cláusula ELSE no es obligatoria), una sentencia CASE debe ejecutar alguna sentencia PL/SQL, de no hacerlo se levantaría un ERROR.

Ejemplos:
SET SERVEROUTPUT ON
DECLARE
    v_dia           VARCHAR2(9);
BEGIN
    SELECT --Puede cambiar la linea comentada para ver distintos resultados(-5 = 5 días atrás)
            --TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = SPANISH') 
            TO_CHAR(SYSDATE-5, 'DAY', 'NLS_DATE_LANGUAGE = SPANISH')
    INTO    v_dia
    FROM dual;
--
    CASE SUBSTR(v_dia, 1,2)
       WHEN 'LU' THEN
            DBMS_OUTPUT.PUT_LINE('Iniciando la semana laboral, que flojera.');
       WHEN 'MA' THEN
            DBMS_OUTPUT.PUT_LINE('Fin de semana lejos, sigue la flojera.');
       WHEN 'MI' THEN
            DBMS_OUTPUT.PUT_LINE('Ombligo de la semana, no se reportan mejoras de animo.');
       WHEN 'JU' THEN
            DBMS_OUTPUT.PUT_LINE('Llegó algo de esperanza al cuerpo, el futuro se pinta mejor.');
      ELSE
            DBMS_OUTPUT.PUT_LINE('Fiesta Carajo....');
    END CASE;
END;
/*Este ejemplo evalúa el Día de la semana y de acuerdo a el muestra un estado de animo.*/
---OUTPUT:

SET SERVEROUTPUT ON

SET VERIFY OFF

DECLARE

    v_caracter      CHAR(1);

    v_tipo          VARCHAR2(10);

BEGIN 

    v_caracter := '&Carácter'; ---Debe introducir solo un carácter, de lo contrario una exception se levantará.

--

    CASE

       WHEN REGEXP_LIKE(v_caracter, '[[:digit:]]') THEN

            v_tipo := 'Numérico';

       WHEN REGEXP_LIKE(v_caracter, '[[:punct:]]') OR REGEXP_LIKE(v_caracter, '[[:space:]]') THEN

            v_tipo := 'Especial';

      ELSE

            CASE 

                WHEN 'AEIOU' LIKE '%'||UPPER(v_caracter)||'%' THEN

                    v_tipo := 'Vocal';

                ELSE

                    v_tipo := 'Consonante';

            END CASE;

   END CASE;

--

    DBMS_OUTPUT.PUT_LINE(q'[El carácter introducido: ']'||v_caracter||q'[', es ]'||v_tipo);

END;

/*Este ejemplo pide al usuario introducir un carácter y luego evalúa que tipo de carácter es.*/
---OUTPUT:

____________________________________________________________________________________
Manejando Valores NULL.

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

• Comparaciones simples que involucran valores nulos siempre producen NULL.

• Si aplica el operador lógico NOT a un valor nulo, obtiene NULL.

• Si en una sentencia de control condicionar la condición resulta NULL, su secuencia de sentencias asociada  no se ejecuta.

 

Consideremos el siguiente ejemplo:

    a := 5;

    b := NULL;

    ...

    IF a != b THEN  -- = NULL, no TRUE

            sequence_of_statements;   --  Secuencia de instrucciones que no se ejecuta.

    END IF;

 

Se puede esperar que la secuencia de sentencias se ejecute debido a que a y b parecen desigual. Pero como los valores nulos son indeterminados, se desconoce si a y b son iguales. Por esta razón,  la condición IF resulta NULL y la secuencia de sentencias se pasa por alto.

    a := NULL;

    b := NULL;

    ...

    IF a = b THEN  -- = NULL, no TRUE

            sequence_of_statements;   --  Secuencia de instrucciones que no se ejecuta.

    END IF;

En el último ejemplo vemos un escenario parecido, a y b parecen iguales. Pero, de nuevo, su igualdad es desconocida, por lo que la condición IF es NULL y la secuencia de sentencias no se ejecuta.

____________________________________________________________________________________

Control Iterativo: Sentencias de Bucle(LOOP).

PL/SQL proporciona varias estructuras de Bucles útiles para repetir una instrucción o secuencia de instrucciones varias veces. Los bucles se utilizan principalmente para ejecutar sentencias varias veces hasta que se alcanza una condición de salida. Es obligatorio tener una condición de salida en un bucle; de lo contrario, sería infinito.

Los bucles son el tercer tipo de estructuras de control. PL/SQL proporciona los siguientes tipos de bucles:
• Bucle Básico(Basic LOOP) que realiza acciones repetitivas sin condiciones generales.
• Bucle FOR(FOR LOOP)que realiza acciones iterativas en base a un conteneo.
• Bucle WHILE que realiza acciones iterativas sobre la base de una condición.

Nota: Una sentencia de salida(EXIT) puede ser utilizado para terminar bucles. Un bucle básico debe tener un EXIT.

Basic LOOP.
La forma más simple de una sentencia LOOP es el bucle básico, que encierra una secuencia de sentencias entre las palabras claves LOOP y END LOOP. Cada vez que el flujo de ejecución alcanza la cláusula END LOOP, el control retorna a la cláusula LOOP inicial para repetir la instrucción. Un bucle básico permite la ejecución de sus sentencias al menos una vez, incluso cuando la condición de salida se cumple antes de entrar en el bucle. Sin la sentencia EXIT, el bucle sería infinito.

Sintaxis:
LOOP
  statement1;
  . . .
  EXIT [WHEN condition];
END LOOP;

La Sentencia EXIT.

Puede utilizar la instrucción EXIT para terminar un bucle. Al hacerlo el control pasa a la siguiente instrucción después de la sentencia END LOOP. Puede emitir EXIT como una acción dentro de una sentencia IF o como una sentencia independiente dentro del bucle. La sentencia EXIT debe ser colocada dentro de un bucle. Adicionalmente, puede usar la cláusula WHEN para habilitar la terminación condicional del bucle. Cuando se encuentra la instrucción EXIT, se evalúa la condición de la cláusula WHEN. Si la condición da TRUE, el bucle finaliza y el control pasa a la siguiente sentencia después del bucle.
Un bucle básico puede contener varias sentencias EXIT, pero se recomienda tener sólo un punto de salida.

Ejemplo:
DECLARE
    v_countryid    locations.country_id%TYPE := 'CA';
    v_loc_id       locations.location_id%TYPE;
    v_counter      NUMBER(2) := 1;
    v_new_city     locations.city%TYPE := 'Montreal';
BEGIN
    SELECT MAX(location_id)
    INTO v_loc_id
    FROM locations
    WHERE country_id = v_countryid;
---
    LOOP
        INSERT INTO locations(
                                location_id,
                                city,
                                country_id
                             )   
        VALUES(
                (v_loc_id + v_counter),
                v_new_city,
                v_countryid
              );
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 3;
    END LOOP;
END;
/*El ejemplo anterior consulta el código de locación mas alto de la tabla locations, luego utiliza un bucle básico para insertar 3 registros nuevos en misma tabla.*/
---
WHILE LOOP.
Puede utilizar el bucle WHILE para repetir una secuencia de instrucciones hasta que la condición de control ya no sea TRUE. La condición se evalúa al inicio de cada iteración. El ciclo termina cuando la condición es FALSE o NULL. Si al inicio del bucle la condición es FALSE o NULL, no se realizan más iteraciones. Por lo tanto, es posible que ninguna de las instrucciones dentro del bucle se ejecutan.

Si las variables que intervienen en las condiciones de salida no cambian durante el cuerpo del bucle, la condición permanece TRUE y el bucle no termina.

Nota: Si la condición da NULL, el bucle se pasa por alto y el control pasa a la siguiente instrucción.

Ejemplo:
DECLARE
    v_countryid   locations.country_id%TYPE := 'CA';
    v_loc_id      locations.location_id%TYPE;
    v_new_city    locations.city%TYPE := 'Montreal';
    v_counter     NUMBER := 1;
BEGIN
    SELECT MAX(location_id)
    INTO v_loc_id
    FROM locations
    WHERE country_id = v_countryid;
---
    WHILE v_counter <= 3 LOOP
        INSERT INTO locations(
                                location_id,
                                city,
                                country_id
                             )   
        VALUES(
                (v_loc_id + v_counter),
                v_new_city,
                v_countryid
              );
        v_counter := v_counter + 1;
    END LOOP;
END;
/*Vemos ahora el mismo ejemplo de la sentencia LOOP(básico), ahora con WHILE LOOP.*/
---
FOR LOOP.
Los bucles FOR tienen la misma estructura general que el bucle básico. Adicionalmente, tienen una instrucción de control antes de la palabra clave LOOP la cual establece el número de iteraciones a ejecutar.

Sintaxis:

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP

    statement1;

    statement2;

    . . .

END LOOP;

En la sintaxis:

counter	Es un número entero declarado implícitamente cuyo valor aumenta o disminuye automáticamente (disminuye si se utiliza la palabra clave REVERSE) por 1 en cada iteración del bucle hasta que el límite superior o límite inferior se alcanza.
REVERSE	Hace que el contador disminuya con cada iteración desde el límite superior hasta el límite inferior. Nota: El límite inferior siempre es referenciado en primer lugar.
lower_bound upper_bound	Especifica el límite inferior. Especifica el límite superior.

No declare el contador(counter), ya que este se declara implícitamente como un entero.

Nota: La secuencia de instrucciones se ejecuta cada vez que se incrementa o disminuye el contador, de acuerdo a los dos límites. El límite inferior y superior del bucle pueden ser literales, variables o expresiones, pero deben evaluar a enteros. Los límites se redondean a números enteros; es decir, 11/3 y 8/5 son límites válidos. El rango límite inferior-superior es inclusive. Si el límite inferior es un número entero mayor que el límite superior, la secuencia de instrucciones no se ejecuta.

 

Directrices de los LOOP FOR.

• Sólo hacer referencia al contador(counter) dentro del bucle; ya que el mismo no está definido fuera.

• No es posible asignar un valor al contador.

• Ninguno de los Límites debe ser NULL.

 

Nota: Los límites inferior y superior de una sentencia LOOP no necesitan ser literales numéricos. Pueden ser expresiones compatibles con valores numéricos.

 

Ejemplo:

SET SERVEROUTPUT ON

BEGIN

    DBMS_OUTPUT.PUT_LINE('Este LOOP es creciente!!!');

    FOR I IN 1..7 LOOP

        DBMS_OUTPUT.PUT_LINE(I);

    END LOOP;

--

    DBMS_OUTPUT.PUT_LINE('Este LOOP es decreciente!!!');

    FOR I IN REVERSE 1..7 LOOP

        DBMS_OUTPUT.PUT_LINE(I);

    END LOOP;

END;

/*Este ejemplo muestra el uso de los FOR LOOP.*/
---OUTPUT:

____________________________________________________________________________________

Los Bucles y sus Usos.

• Utilice el bucle básico(LOOP) cuando se espera ejecutar las sentencias por lo menos una vez.

• Utilice el bucle WHILE si la condición debe ser evaluada al inicio de cada iteración.

• Utilice el bucle FOR si se conoce el número de iteraciones.

 

Un bucle básico permite la ejecución de sus sentencias al menos una vez, incluso si la condición de salida se cumple al entrar en el bucle. Sin la instrucción EXIT, el bucle sería infinito.

 

Puede utilizar el bucle WHILE para repetir una secuencia de instrucciones hasta que la condición de control ya no esa TRUE. La condición se evalúa al inicio de cada iteración. El ciclo termina cuando la condición es FALSE o NULL. No se realizan iteraciones si la condición es FALSE al del inicio del bucle.

 

Los bucles FOR tienen una sentencia de control antes de la palabra clave LOOP la cual determina el número de iteraciones a realizar. Utilice un bucle FOR si se conoce el número de iteraciones a realizar.

____________________________________________________________________________________

Los Bucles Anidados y Las Etiquetas.

Puede anidar los bucles FOR, WHILE, y básicos(LOOP) dentro de otros. La terminación de un bucle anidado no termina el bucle exterior a no ser que se produzca una excepción. Sin embargo, puede etiquetar bucles y salir del bucle exterior con la instrucción EXIT.

 

Los nombres de etiqueta siguen las mismas reglas que los otros identificadores. Una etiqueta se coloca antes de una sentencia, ya sea en la misma línea o en una línea separada. Etiquete bucles básicos colocando el nombre antes de la palabra LOOP dentro de los delimitadores de etiqueta (<<etiqueta>>). En bucles FOR y WHILE, coloque la etiqueta antes FOR o WHILE.

 

Si el bucle está etiquetado, el nombre de etiqueta puede ser incluido (opcionalmente) después de la instrucción END LOOP para mayor claridad.

 

Ejemplo:

SET SERVEROUTPUT ON

DECLARE

    v_contador1  NUMBER;

    v_contador2  NUMBER;

BEGIN

    <<Bucle_LOOP>>

    LOOP

        v_contador1 := NVL(v_contador1,0)+1;

        v_contador2 := NULL;

--

        DBMS_OUTPUT.PUT_LINE('-Iteracion: '||v_contador1||' del Bucle_LOOP-');

--

        <<Bucle_WHILELOOP>>

        WHILE NVL(v_contador2,0) < 2 LOOP

            v_contador2 := NVL(v_contador2,0)+1;

--

            DBMS_OUTPUT.PUT_LINE('--Iteracion: '||v_contador2||' del Bucle_WHILELOOP--');

--

            <<Bucle_FORLOOP>>

            FOR I IN 1..2 LOOP

                DBMS_OUTPUT.PUT_LINE('---Iteracion: '||I||' del Bucle_FORLOOP---');

            END LOOP Bucle_FORLOOP;

        

        END LOOP Bucle_WHILELOOP;

--

        EXIT WHEN v_contador1 > 1;

    END LOOP Bucle_LOOP;

END;  

/*En el anterior ejemplo tenemos algunos bucles anidados, en los cuales se muestra en pantalla sus iteraciones en forma de nivel.*/
---OUTPUT:

___________________________________________________________________________________

La Sentencia CONTINUE.

La sentencia CONTINUE le permite transferir el flujo de control dentro de un bucle a una nueva iteración. A partir de Oracle 11g, PL/SQL ofrece esta funcionalidad. Antes de esa version, se podía codificar una solución mediante el uso de variables Booleanas e instrucciones condicionales que simulaban la misma funcionalidad de CONTINUE. En algunos casos, dichas soluciones resultaban menos eficientes.

 

La sentencia CONTINUE le ofrece un medio sencillo para el control de iteraciones dentro del ciclo. En el mayor de los casos CONTINUE  resulta más eficiente que las soluciones usadas anteriormente.

 

La sentencia CONTINUE se utiliza comúnmente para filtrar datos dentro del bucle antes de iniciar el procesamiento principal.

 

Ejemplos:

SET SERVEROUTPUT ON

DECLARE

    v_total SIMPLE_INTEGER := 0;

BEGIN

    FOR i IN 1..10 LOOP

        v_total := v_total + i;

        DBMS_OUTPUT.PUT_LINE('Iteracion: '|| i||', El Total es: '|| v_total);

        CONTINUE WHEN i > 5;

        v_total := v_total + i;

        DBMS_OUTPUT.PUT_LINE('Fuera del LOOP, el Total es:'|| v_total);    

    END LOOP;

END;

/*E aquí un ejemplo de la sentencia CONTINUE dentro de un LOOP; notar que en este SCRIPT, las sentencias debajo de CONTINUE solo se ejecutan en las primeras 5 iteraciones.*/

---OUTPUT:

SET SERVEROUTPUT ON

DECLARE 

    v_total NUMBER := 0;

BEGIN

    <<Top_LOOP>>

    FOR i IN 1..10 LOOP

        v_total := v_total + 1;

        DBMS_OUTPUT.PUT_LINE('El Total es: ' || v_total);

        FOR j IN 1..10 LOOP

            CONTINUE Top_LOOP WHEN i + j > 5;

            v_total := v_total + 1;

        END LOOP;

    END LOOP;

END;

/*En este ejemplo vemos como la sentencia CONTINUE nos permite salir de un LOOP anidado a otro mas externo.*/

---OUTPUT:

_____________________________________________________________________________________