Introdução

Este artigo fornece orientação para o uso de blocos anônimos no DB2 9.7 nos seguintes cenários:

• Teste, resolução de problemas e desenvolvimento de novos procedimentos armazenados de PL/SQL
• Simulação de execuções de aplicativos em PL/SQL
• Desenvolvimento de consultas e relatórios complexos ad hoc durante a execução com o PL/SQL

Revisão de pré-requisitos e requisitos do sistema

Este artigo foi escrito para desenvolvedores de aplicativos PL/SQL e administradores de banco de dados que estão migrando de Oracle para DB2. É necessário entender o conceito de linguagem processual de PL/SQL. Os desenvolvedores de SQL PL devem usar a função correspondente fornecida pelas instruções SQL compostas nativas do DB2.

Para usar os exemplos deste artigo, é preciso ter instalado o DB2 9.7 Workgroup ou Enterprise Edition para Linux, UNIX e Windows. Consulte a seção Recursos para fazer o download de uma versão de avaliação gratuita do DB2 9.7 para Linux, UNIX e Windows.

Uso dos exemplos

Pode-se executar os exemplos usando várias ferramentas, incluindo o processador da linha de comando (CLP) do DB2 e os utilitários de comando (CLPPLUS), ou ferramentas visuais, como o Optim Development Studio. Se planeja executar os exemplos no CLP, será preciso executar o comando SET SQLCOMPAT PLSQL para ativar o reconhecimento do caractere de barra (/) em uma nova linha como caractere de encerramento de instrução PL/SQL.

Para ativar o suporte a tipos de dados de PL/SQL e Oracle, seu banco de dados deve ser criado com a variável de registro DB2_COMPATIBILITY_VECTOR configurada para ORA, como mostra a Listagem 1.

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test

Para este artigo, a Listagem 2 oferece um código para criar um aplicativo PL/SQL simples de e-commerce que gerencia os pedidos on-line após a criação de um banco de dados. Também preencheremos as tabelas com alguns dados de amostra.

Acessar o código de exemplo
    

Entendendo blocos anônimos

Os blocos anônimos são estruturas de PL/SQL que fornecem o recurso de criar e executar código processual durante a execução sem o armazenamento persistente do código como objeto de banco de dados nos catálogos do sistema. O conceito de blocos anônimos é semelhante aos shell scripts do UNIX, que permitem que vários comandos inseridos manualmente sejam agrupados e executados como uma etapa. Como o nome indica, blocos anônimos não têm nome, e, por essa razão, não podem ser referenciados a partir de outros objetos. Embora sejam criados dinamicamente, os blocos anônimos podem ser facilmente armazenados como scripts nos arquivos do sistema operacional para execução repetitiva.

Os blocos anônimos são os blocos padrão de PL/SQL. Eles transportam a sintaxe e obedecem às regras que se aplicam a todos os blocos de PL/SQL, incluindo declaração e escopo de variáveis, execução, manipulação de exceção e uso de SQL e PL/SQL.

A compilação e execução de blocos anônimos são combinadas em uma única etapa, enquanto um procedimento armazenado de PL/SQL precisa ser redefinido antes do uso cada vez que sua definição é alterada. Essa é uma das vantagens significativas dos blocos anônimos sobre os objetos de banco de dados persistentemente nomeados, como procedimentos armazenados e funções definidas pelo usuário, pois reduzem o tempo entre a implementação das mudanças no código e a execução real. Isso torna os blocos anônimos muito úteis na solução de problemas, prototipagem e teste de código processual, porque essas são tarefas que normalmente requerem diversas execuções de mudança e execução.

Outro benefício dos blocos anônimos é que eles não criam dependências e não exigem privilégios especiais para criação de objeto, o que pode evitar complicações em um ambiente de produção. Os blocos anônimos oferecem a flexibilidade de executar qualquer sequência processual de ações com base em privilégios simples e selecionados, e permitem testar sem criar ou implicar objetos existentes do banco de dados.

Pode-se executar os blocos anônimos a partir de:

• SQL (por exemplo, dentro de instruções EXECUTE IMMEDIATE)
• APIs de DB2, como JDBC e ODBC
• Várias ferramentas do DB2, incluindo CLP, CLPPlus, Optim Database Administrator e Optim Development Studio

Protótipos de código PL/SQL com blocos anônimos

Na Listagem 3, um desenvolvedor de aplicativos antecipa as necessidades de negócios de um mecanismo para se comunicar (por email) com os clientes definidos na tabela CUSTOMER. Para atender a essa necessidade de forma proativa, ele decide escrever um simples bloco anônimo PL/SQL de protótipo que envia um email com uma mensagem para os clientes na tabela CUSTOMER. Mais tarde, depois que a necessidade de negócios é finalizada, o bloco anônimo com protótipo pode ser aprimorado e facilmente transformado em um novo procedimento armazenado de PL/SQL. Observe que esse bloco anônimo usa novos pacotes integrados, incluindo UTL_SMTP (pacote para envio de emails) e DBMS_OUTPUT (pacote para escrever mensagens para a saída padrão), que fazem parte do DB2 9.7.

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...

Voltar para parte superior

Simulando execuções de aplicativos com blocos anônimos

Como mencionado, um dos usos mais comuns dos blocos anônimos é chamar objetos de linguagem processual, em geral, para fins de teste. A Listagem 4 demonstra como simular uma execução de aplicativo com a ajuda de um bloco anônimo de PL/SQL. O código simula uma execução de aplicativo durante a captura de métricas de desempenho. O bloco anônimo simula a criação de 10 pedidos aleatórios para clientes aleatórios a partir da tabela CUSTOMER existente. Ele também imprime os horários de início e encerramento do teste, juntamente com os detalhes do pedido para cada execução. É fácil alterar o número de pedidos de 10 para 20 e então executar novamente esse bloco anônimo sem recompilar. Também é possível incluir mais métrica de desempenho para teste adicional.

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

Voltar para parte superior

Gerando relatórios ad hoc com blocos anônimos

Um requisito do relatório comum é concatenar dados de mais de uma coluna em uma única cadeia de caractere. É possível escrever instruções SQL puras com recursão complexa para fazer isso. Em vez disso, porém, é possível usar blocos anônimos para concluir rapidamente essa tarefa com opções de formatação dinâmica e fluxo de lógica simples.

A Listagem 5 mostra como criar um relatório ad hoc com a ajuda de blocos anônimos. O código recupera a lista de todos os clientes que fizeram pedidos de produtos da loja e o valor total de todos os pedidos do mês passado. Os nomes são exibidos em uma linha e são separados por vírgulas.

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

Voltar para parte superior

Conclusão

Este artigo apresentou o seguinte:

• O recurso de blocos anônimos de PL/SQL apresentado no DB2 9.7.
  
  
• O conceito de blocos anônimos.
  
  
• Como os blocos anônimos podem facilitar o processo de teste, prototipagem e resolução de problemas de código processual.
  
  
• Como os blocos anônimos podem simular execuções de aplicativos.
  
  
• Como usar os blocos anônimos para geração de relatórios ad hoc eficientes.
  
  

Com o suporte a blocos anônimos do PL/SQL, é possível ativar rapidamente soluções de PL/SQL no ambiente DB2 usando os scripts de PL/SQL existentes ou usando instruções individuais de PL/SQL e SQL que funcionam com outros sistemas de gerenciamento de banco de dados.

Recursos

Aprender

• Use um feed RSS para solicitar a notificação com relação aos artigos futuros desta série. (Descubra mais sobre feeds RSS do conteúdo do developerWorks.)
  
  
• Saiba mais sobre o suporte pronto para os dialetos Oracle SQL e PL/SQL no DB2 9.7 em "DB2 9.7: Execute aplicativos Oracle em DB2 9.7 para Linux, UNIX e Windows" (developerWorks, julho de 2009).
  
  
• Saiba mais sobre SQL composto no Centro de Informações do DB2.
  
  
• Saiba mais sobre ferramentas de gerenciamento de banco de dados e desenvolvimento de aplicativo no Centro de Informações do DB2.
  
  
• Saiba mais sobre sintaxe de bloco anônimo no Centro de Informações do DB2.
  
  
• Saiba mais sobre módulos definidos pelo sistema no Centro de Informações do DB2.
  
  
• Saiba mais sobre Information Management na zona do Information Management no developerWorks. Encontre documentação técnica, artigos de instruções, treinamento, downloads, informações de produtos, e muito mais.
  
  
• Fique por dentro doseventos técnicos e webcasts do developerWorks .
  
  

Obter produtos e tecnologias

• Faça o download do DB2 Express-C 9.7, uma versão gratuita do servidor de banco de dados DB2 Express para a comunidade que inclui pureXML.
  
  
• Faça download de uma versão de teste gratuita do DB2 9.7 para Linux, UNIX e Windows..
  
  
• Crie seu próximo projeto de desenvolvimento com o software de avaliação da IBM, disponível para download diretamente do developerWorks.
  
  

Discutir

• Participar do fórum de discussão.
  
  
• Confira os blogs do developerWorks e participe da Comunidade do developerWorks.
  
  

Sobre os autores

Maksym Petrenko faz parte da equipe DB2 Beta Enablement no Laboratório da IBM em Toronto. Ele ajuda os primeiros usuários a mover seus aplicativos para o código base mais recente e superior do DB2. Maksym trabalha com o DB2 desde 2001 como desenvolvedor, analista de suporte técnico e consultor de serviços de laboratório. Sua experiência inclui suporte a clientes com problemas de instalação, configuração, desenvolvimento de aplicativo e desempenho relacionados a bancos de dados DB2 em plataformas Windows, Linux e UNIX. Maksym é DB2 Advanced Database Administrator e DB2 Application Developer certificado.

Maria Schwenger passou a fazer parte da IBM em 2005 na equipe Entity Analytic Solutions quando já tinha mais de 10 anos de experiência em engenharia de desempenho, arquitetura de banco de dados, administração e desenvolvimento de banco de dados em servidor Oracle e MS SQL, além de ampla experiência em migração de bancos de dados de legado para relacionais. Atualmente, Maria trabalha em um modelo de envolvimento humano com participantes de release inicial para promover a adoção antecipada da DB2 Open Database Technology.

Classificar este artigo

Comentários

Voltar para parte superior

Índice

• Introdução
• Revisão de pré-requisitos e requisitos do sistema
• Uso dos exemplos
• Entendendo blocos anônimos
• Protótipos de código PL/SQL com blocos anônimos
• Simulando execuções de aplicativos com blocos anônimos
• Gerando relatórios ad hoc com blocos anônimos
• Conclusão
• Recursos
• Sobre os autores
• Comentários

Conheça a IBM da sua cidade

A IBM está mais perto do que você imagina!

---

Saiba mais