DB2 9.7: Usando bloques anónimos PL/SQL en DB2 9.7

Aprenda cómo usar bloques anónimos PL/SQL en un entorno DB2

IBM DB2® for Linux®, UNIX® y Windows® 9.7 introduce soporte para bloques PL/SQL anónimos: un recurso que permite que los desarrolladores de aplicaciones PL/SQL prueben, solucionen problemas, hagan prototipos de nuevo código de procedimiento, simulen la ejecución de aplicaciones y construyan de forma dinámica consultas e informes complejos yad-hoc. Este artículo describe el concepto de los bloques anónimos en DB2 9.7 e ilustra el uso de este recurso usando escenarios comunes de base de datos.

Maksym Petrenko, DB2 Open Database Technologies, IBM  

Maksym Petrenko photoMaksym Petrenko es parte del Equipo DB2 Beta Enablement en el laboratorio IBM de Toronto. Él asiste a quienes adoptan tecnologías de forma temprana a mover sus aplicaciones hacia la más reciente y mejor base de código DB2. Maksym ha trabajado con DB2 desde el 2001 como desarrollador, analista de soporte técnico y consultor de servicios de laboratorio. Su experiencia incluye dar soporte a clientes con la instalación, configuración, desarrollo de aplicaciones y problemas de desempeño relacionados con bases de datos DB2 en plataformas Windows, Linux y UNIX. Maksym es DB2 Advanced Database Administrator certificado y DB2 Application Developer certificado.



Maria Schwenger, DB2 Open Database Technologies, IBM

Photo of Maria SchwengerMaria Schwenger se unió a IBM en 2005 como parte del Entity Analytic Solutions, y cuenta con más de 10 años de experiencia en ingeniería de desempeño, arquitectura de base de datos, administración y desarrollo de bases de datos en Oracle y MS SQL server, así como una extensa experiencia en migración desde bases de datos legadas a relacionales. Actualmente, Maria trabaja en un modelo de servicio automatizado, con participantes en el release piloto para promover la adopción temprana de la tecnología DB2 Open Database.



24-02-2012

Introducción

Este artículo proporciona una guía para el uso de bloques anónimos en DB2 9.7 en los siguientes escenarios:

  • Pruebas, solución de problemas y desarrollo de nuevos procedimientos almacenados PL/SQL
  • Simulación de ejecuciones de aplicación con PL/SQL
  • Construcción de consultas e informes complejos y ad-hoc al vuelo con PL/SQL

Revisando los requisitos previos y los requerimientos de sistema

Este artículo está escrito para desarrolladores de aplicaciones PL/SQL y administradores de bases de datos que estén pasando de Oracle a DB2. Usted debe entender el concepto de lenguaje de procedimientos PL/SQL. Los desarrolladores SQL PL deben usar la función correspondiente proporcionada por los enunciados SQL compuestos nativos en DB2.

Para usar lo ejemplos de este artículo usted debe tener instalado DB2 9.7 Workgroup o Enterprise Edition for Linux, UNIX, and Windows. Consulte la documentación Recursos sección para descargar una versión gratuita de DB2 9.7 for Linux, UNIX, and Windows.

Usando los ejemplos

Usted puede ejecutar los ejemplos usando varias herramientas, incluyendo el procesador de línea de comandos (CLP) DB2 y las herramientas de comandos (CLPPLUS), o las herramientas visuales como el Optim Development Studio. Si usted planea ejecutar los ejemplos desde el CLP, necesitará ejecutar el comando SET SQLCOMPAT PLSQL para activar el reconocimiento del caracter barra inclinada (/) en una nueva línea como un caracter de terminación de enunciado PL/SQL.

Para activar el soporte en DB2 para tipos de datos PL/SQL y Oracle, su base de datos debe ser creada con la variable de registro DB2_COMPATIBILITY_VECTOR configurada en ORA, como se muestra en el Listado 1.

Listado 1. Configurando la variable de registro DB2_COMPATIBILITY_VECTOR
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test

Para este artículo, el Listado 2 ofrece el código para crear una aplicación simple de e-commerce PL/SQL que administra órdenes on-line después de que usted crea una base de datos. Usted también llenará las tablas con algunos datos de muestra.

Listado 2. El código de ejemplo

Entendiendo los bloques anónimos

Los bloques anónimos son estructuras PQ/SQL que ofrecen la capacidad para crear y ejecutar código de procedimiento 'al vuelo', sin almacenar el código persistentemente como objetos de base de datos en catálogos de sistema. El concepto de bloques anónimos es similar a los scripts shell UNIX, los cuales activan diversos comandos ingresados manualmente a ser agrupados y ejecutados como un solo paso. Como su nombre lo indica, los bloques anónimos no tienen nombre y por esta razón no pueden ser referenciados por otros objetos. Aunque se construyen dinámicamente, los bloques anónimos se pueden almacenar fácilmente como scripts en los archivos del sistema operativo para ejecución repetitiva.

Los bloques anónimos son bloques estándar PL/SQL. Estos llevan la sintaxis y obedecen reglas que se aplican a todos los bloques PL/SQL, incluyendo la declaración y el alcance de variables, ejecución, manejo de excepciones y uso de SQL y PL/SQL.

La compilación y ejecución de bloques anónimos se combina en un paso, mientras que un procedimiento almacenado PL/SQL necesita ser redefinido antes de usar cada vez sus cambios de definición. Esta es una de las ventajas significativas de los bloques anónimos sobre los objetos de base de datos con nombre persistido, como los procedimientos almacenados y funciones definidas por usuario, porque esto reduce el tiempo entre la implementación de los cambios y la ejecución actual. Esto hace a los bloques anónimos bastante útiles cuando se están resolviendo problemas, en prototipos y en código de procedimientos de pruebe, porque normalmente estas tareas requieren de múltiples ejecuciones de cambiar y ejecutar.

Otro beneficio de los bloques anónimos es que estos no crean ninguna dependencia, y no requieren ningún privilegio especial para la creación de objetos, lo cual puede evitar complicaciones en un ambiente de producción. Los bloques anónimos proporcionan la flexibilidad para ejecutar cualquier secuencia de procedimiento de acciones basadas en privilegios simples y seleccionados, y hacen posible que usted haga pruebas sin crear ni implicar objetos de bases de datos existentes.

Usted puede crear bloques anónimos de:

  • SQL (por ejemplo, dentro de enunciados EXECUTE IMMEDIATE)
  • APIs DB2 como JDBC y ODBC
  • Varias herramientas DB2, incluyendo CLP, CLPPlus, Optim Database Administrator y Optim Development Studio

Prototipos de código PL/SQL con bloques anónimos

En el Listado 3, un desarrollador de aplicaciones anticipa la necesidad de negocios de un mecanismo para comunicarse (vía e-mail) con los clientes definidos en la tabla CUSTOMER. Para satisfacer esta necesidad de forma proactiva, él decide escribir un bloque anónimo de prototipo PL/SQL simple que envía e-mail que contiene algún mensaje, al cliente de la tabla CUSTOMER. Más adelante, en cuanto se ha terminado la necesidad de negocios, el bloque anónimo de prototipo puede mejorarse y transformarse fácilmente en un nuevo procedimiento almacenado PL/SQL. Note que este bloque anónimo usa nuevos paquetes integrados, incluyendo UTL_SMTP (el paquete para enviar e-mails) y DBMS_OUTPUT (el paquete para escribir mensajes hacia el resultado estándar), que hacen parte del DB2 9.7.

Listado 3. Un bloque anónimo de prototipo PL/SQL simple que envía e-mail que contiene algún mensaje, al cliente de la tabla CUSTOMER.
SET SERVEROUTPUT ON
/

DECLARE
   conn UTL_SMTP.connection;
   reply UTL_SMTP.reply;
   msg VARCHAR2(1024);
   sender VARCHAR2(255) DEFAULT 'demo\@ca.ibm.com';
   recipients VARCHAR2(255);
   subject VARCHAR2(255) DEFAULT 'Quick notification';
   crlf VARCHAR2(2);

BEGIN
      
  crlf := UTL_TCP.CRLF;
  FOR row IN (SELECT first_name, email FROM customer) LOOP
      DBMS_OUTPUT.PUT_LINE('Sending test email to customer ' || row.first_name || '...');
      recipients := row.email;
      msg := 'FROM: ' || sender || crlf ||
                'TO: ' || recipients || crlf ||
                'SUBJECT: ' || subject || crlf ||
                crlf ||
                'Hi ' || row.first_name || ', this is a test notification.';

      UTL_SMTP.OPEN_CONNECTION('smtp_server.ibm.com', 25, conn, 10, reply );
      UTL_SMTP.HELO(conn, 'localhost');
      UTL_SMTP.MAIL(conn, sender);
      UTL_SMTP.RCPT(conn, recipients);
      UTL_SMTP.DATA(conn, msg);
      UTL_SMTP.QUIT(conn); 
   END LOOP;
END;
/

Output:
Sending test email to customer Mike...
Sending test email to customer Joan...
Sending test email to customer Colin...
Sending test email to customer Graham...
Sending test email to customer Patsy...

Simulación de ejecuciones de aplicación con bloques anónimos

Como se mencionó, uno de los usos más comunes de los bloques anónimos es invocar objetos de lenguaje de procedimiento, normalmente para propósitos de prueba. El Listado 4 demuestra cómo simular una ejecución de aplicación con la ayuda de un bloque anónimo PL/SQL. El código simula una aplicación mientras captura mediciones de desempeño. El bloque anónimo simula la creación de 10 órdenes al azar para clientes al azar, desde la tabla CUSTOMER existente. Este también imprime tiempos de inicio y de parada, junto con los detalles de la orden para cada ejecución. Es fácil cambiar el número de órdenes de 10 a 20 y luego volver a ejecutar este bloque anónimo sin re-compilación. Usted también puede añadir más mediciones de desempeño para pruebas adicionales.

Listado 4. Ejecución de una aplicación con la ayuda de un bloque anónimo PL/SQL
SET SERVEROUTPUT ON
/   

DECLARE
   v_customer_id customer.customer_id%TYPE; 
   product_id product.product_id%TYPE:=1;
   o_order_id orders.order_id%TYPE;
   v_test_start TIMESTAMP;
BEGIN
  SELECT CURRENT TIMESTAMP INTO v_test_start FROM dual;   
  FOR k IN 1..10 LOOP 
  	SELECT customer_id INTO v_customer_id FROM customer ORDER BY RAND() FETCH FIRST 1 
		ROW ONLY;                                                             
  	FOR i IN (
                  SELECT product_id, CAST(RAND()*50 as integer)+1 as quantity 
                  FROM product 
                  WHERE ROWNUM < CAST(RAND()*10 as integer)) 
        LOOP                 
      	      add_item_to_shopping_cart(i.product_id, i.quantity); 
        END LOOP;
        create_order(v_customer_id, o_order_id);  
        DBMS_OUTPUT.PUT_LINE('--------------------------------------------');  
  END LOOP; 
  DBMS_OUTPUT.PUT_LINE('Test start: ' || v_test_start);     
  DBMS_OUTPUT.PUT_LINE('Test end  : ' || CURRENT TIMESTAMP);

END;
/  


Output:

Customer           : Mike, Smith
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 150,615.44
--------------------------------------------
Customer           : Joan, Jett
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 159,445.77
...
...
...
Customer           : Colin, Taylor
Order creation     : 07-06-2009
Estimated Delivery : 07-09-2009
Status             : Shipped  
Total price        : $ 266,242.78
--------------------------------------------
Test start: 2009-07-06-11.10.11.500000
Test end  : 2009-07-06-11.10.11.546000

Generando informes ad-hoc con bloques anónimos

Un requerimiento común de informe es concatenar datos e más de una columna en una sola cadena de caracteres. Es posible escribir enunciados pureSQL con recursos complejos para esto. No obstante, en lugar de ello usted puede usar bloques anónimos para hacer esto rápidamente con opciones de formateo dinámico y flujo lógico simple.

El Listado 5 muestra cómo crear un reporte ad-hoc con la ayuda de bloques anónimos. El código recupera la lista de todos los clientes que ordenaron productos de la tienda y el valor total de todas las órdenes del último mes. Los nombres se presentan en una línea y están separados por comas.

Listado 5. Creando un informe ad-hoc con la ayuda de bloques anónimos
SET SERVEROUTPUT ON
/

DECLARE 
     v_customer_names VARCHAR2(4000);
     v_total_sales NUMBER(19,2);
BEGIN
     DBMS_OUTPUT.PUT_LINE('           Last Month Sales Report     ');

     DBMS_OUTPUT.PUT_LINE('---------------------------------------');
     DBMS_OUTPUT.PUT('Customer List: ');
     FOR row IN 
       (SELECT distinct(a.customer_id),first_name, last_name FROM customer a, orders b 
		WHERE a.customer_id=b.order_id AND b.creation_time>CURRENT DATE -1 month)
     LOOP
      v_customer_names := v_customer_names || '"' || row.first_name || ' ' || 
		row.last_name || '", '; 
     END LOOP;
     IF(LENGTH(v_customer_names) > 0) THEN
        v_customer_names := SUBSTR(v_customer_names,1, LENGTH(v_customer_names)-2);
     ELSE
        v_customer_names := 'None';
     END IF; 
     DBMS_OUTPUT.PUT_LINE(v_customer_names); 
     SELECT NVL(SUM(total_price),0) INTO v_total_sales FROM orders WHERE creation_time>
	 CURRENT DATE - 1 month;
     DBMS_OUTPUT.PUT_LINE('---------------------------------------');
     DBMS_OUTPUT.PUT_LINE('Total Sales: ' || TO_CHAR(v_total_sales, '$99,999,999.99')); 
END;
/

Output:

          Last Month Sales Report     ---------------------------------------
Customer List: "Mike Smith", "Joan Jett", "Colin Taylor", "Graham Norton", "Patsy Stone"
---------------------------------------
Total Sales: $ 49,772.56

Conclusión

Este artículo presentó lo siguiente:

  • El recurso de bloques anónimos PL/SQL introducido en el DB2 9.7.
  • El concepto de bloques anónimos.
  • Cómo pueden facilitar los bloques anónimos el proceso de prueba, prototipo y solución de problemas de código de procedimiento.
  • Cómo pueden simular los bloques anónimos ejecuciones de aplicación.
  • Cómo usar los bloques anónimos para potentes informes ad-hoc.

Con el soporte para bloques anónimos PL/SQL, usted puede activar rápidamente soluciones PL/SQL en el entorno DB2, usando scripts PL/SQL existentes o mediante el uso de enunciados PL/SQL y SQL individuales que funcionen con otros sistemas de administración de bases de datos.

Recursos

Aprender

Obtener los productos y tecnologías

Comentar

Comentarios

developerWorks: Ingrese

Los campos obligatorios están marcados con un asterisco (*).


¿Necesita un IBM ID?
¿Olvidó su IBM ID?


¿Olvidó su Password?
Cambie su Password

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


La primera vez que inicie sesión en developerWorks, se creará un perfil para usted. La información en su propio perfil (nombre, país/región y nombre de la empresa) se muestra al público y acompañará a cualquier contenido que publique, a menos que opte por la opción de ocultar el nombre de su empresa. Puede actualizar su cuenta de IBM en cualquier momento.

Toda la información enviada es segura.

Elija su nombre para mostrar



La primera vez que inicia sesión en developerWorks se crea un perfil para usted, teniendo que elegir un nombre para mostrar en el mismo. Este nombre acompañará el contenido que usted publique en developerWorks.

Por favor elija un nombre de 3 - 31 caracteres. Su nombre de usuario debe ser único en la comunidad developerWorks y debe ser distinto a su dirección de email por motivos de privacidad.

Los campos obligatorios están marcados con un asterisco (*).

(Por favor elija un nombre de 3 - 31 caracteres.)

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


Toda la información enviada es segura.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=90
Zone=Information mgmt
ArticleID=795373
ArticleTitle=DB2 9.7: Usando bloques anónimos PL/SQL en DB2 9.7
publish-date=02242012