DB2 9.7: 在 DB2 9.7 中使用 PL/SQL 匿名块

				
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start
db2 create db test

				
访问示例代码
			

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

				
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

				
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