Oracle存储过程笔记1：动态SQL（建表）

 

 

 

CREATE OR REPLACE PROCEDURE P_CONSTRUCT_DATA AUTHID CURRENT_USER AS

 

/*---------------------------------------------------------------------------+

 

 |程序目的：动态建表，随机生成测试数据

 

 |创建日期：2010-11-23

 

 |备注：oracle给我们提供了在存储过程中使用role权限的方法：

 

           修改存储过程，加入Authid Current_User时存储过程可以使用role权限

 

 +--------------------------------------------------------------------------*/

 

--DECLARE

 

  V_LOOP1 INT;

 

  V_LOOP2 INT;

 

  V_NUM NUMBER;

 

  V_TNUM NUMBER;

 

  V_STARTIME NUMBER;

 

  V_SQL_MEG VARCHAR(4000);

 

  BEGIN

 

    SELECT MAX(PROD_ID) INTO V_NUM FROM SCOTT.T_PRODUCT_BASE ;

 

    SELECT TO_CHAR(TO_DATE('2010-10-1','YYYY-MM-DD'),'J')INTO V_STARTIME FROM DUAL;

 

    FOR V_LOOP1 IN 1..V_NUM LOOP

 

            SELECT COUNT(1) INTO V_TNUM FROM USER_TABLES WHERE TABLE_NAME='T_PROD_SALE'||V_LOOP1||'';

 

            IF V_TNUM>0 THEN 

 

                EXECUTE IMMEDIATE 'DROP TABLE T_PROD_SALE'||V_LOOP1||'';

 

            END IF;

 

            EXECUTE IMMEDIATE 'CREATE TABLE T_PROD_SALE'||V_LOOP1||'

 

                              (PROD_CNNAME VARCHAR2(50),CUSTOMER_ID NUMBER,SDATE DATE,QUANTITY NUMBER,FEES NUMBER)';

 

            FOR V_LOOP2 IN 1..1500 LOOP

 

                V_SQL_MEG:='INSERT INTO T_PROD_SALE'||V_LOOP1||' VALUES((SELECT PROD_CNNAME FROM

 

                SCOTT.T_PRODUCT_BASE WHERE PROD_ID='||V_LOOP1||'),

 

                ROUND(DBMS_RANDOM.VALUE(2,5000)),

 

                TO_DATE(TRUNC(DBMS_RANDOM.VALUE('''||V_STARTIME||''','''||V_STARTIME||'''+60)),''J''),

 

                ROUND(DBMS_RANDOM.VALUE(2,20)),

 

                ROUND(DBMS_RANDOM.VALUE(2,20)*ROUND(DBMS_RANDOM.VALUE(100,500)),1))';  

 

                EXECUTE IMMEDIATE V_SQL_MEG; 

 

            END LOOP;

 

            COMMIT WORK;

 

    END LOOP;

 

  END P_CONSTRUCT_DATA;

 

  --EXECUTE SCOTT.P_CONSTRUCT_DATE;