plsql动态的sql

sunbird

　　12 动态SQL语句和动态PLSQL语句
　　SQL语句 --  静态的SQL语句、动态的SQL语句
　　静态的SQL语句  --  在编译的时候已经确定的SQL，语法和语义引用也是在编译的时候确定下来的
　　动态的SQL语句  --  由字符串组成，在运行的时候编译和执行
　　12.1 动态SQL语句
　　(1) 动态SQL的简单案例
　　案例1： 动态创建表示例
　　declare
　　dyn_tab_name varchar2(30):='temp';
　　dyn_string varchar2(150);
　　begin
　　dyn_string:='create table '||dyn_tab_name||' (col number not null)';
　　execute immediate dyn_string;                    --动态运行，在匿名块里面调用DDL，要加execute immediate
　　end;
　　SQL> desc temp;
　　Name Type   Nullable Default Comments
　　---- ------ -------- ------- --------
　　COL  NUMBER
　　改写为存储过程：
　　create or replace procedure p21 is
　　dyn_tab_name varchar2(30):='temp';
　　dyn_string varchar2(150);
　　begin
　　dyn_string:='create taeble '||dyn_tab_name||' (col number not null)'; --有语法错误的，但是编译的时候不会报错
　　execute immediate dyn_string;
　　end;
　　SQL> exec p21;              --在执行的时候报错
　　BEGIN p21; END;
　　*
　　ERROR at line 1:
　　ORA-00901: invalid CREATE command
　　ORA-06512: at "PLSQL.P21", line 6
　　ORA-06512: at line 1
　　create or replace procedure p21 is
　　dyn_tab_name varchar2(30):='temp';
　　dyn_string varchar2(150);
　　begin
　　dyn_string:='create table '||dyn_tab_name||' (col number not null)';
　　execute immediate dyn_string;
　　end;
　　SQL> exec p21;
　　BEGIN p21; END;
　　*
　　ERROR at line 1:
　　ORA-01031: insufficient privileges              --权限不足
　　ORA-06512: at "PLSQL.P21", line 6
　　ORA-06512: at line 1
　　create or replace procedure p21 authid current_user is          --正确的写法
　　--在存储过程中出现动态SQL的时候，需要在SP的头部加上authid current_user显式授权语句，将执行的权限授给当前的用户
　　dyn_tab_name varchar2(30):='temp1';
　　dyn_string varchar2(150);
　　begin
　　dyn_string:='create table '||dyn_tab_name||' (col number not null)';
　　execute immediate dyn_string;
　　end;
　　SQL> exec p21;
　　PL/SQL procedure successfully completed.
　　SQL> desc temp1;
　　Name                       Null?    Type
　　----------------------------------------- -------- ----------------------------
　　COL                       NOT NULL NUMBER
　　总结：在存储过程中使用动态SQL语句，如果SQL语句语法有问题，在编译的时候不会报错，只有在调用SP的时候才会报错
　　在存储过程中出现动态SQL的时候，需要在SP的头部加上authid current_user显式授权语句，将执行的权限授给当前的用户
　　案例2：动态创建表
　　create or replace procedure create_dyn_table(i_region_name in varchar2,retcd out number,errmsg out varchar2) authid current_user is
　　dyn_tab_name varchar2(200);
　　dyn_string varchar2(2000);
　　begin
　　dyn_string:='';
　　dyn_tab_name:='ORDERS_FOR_'||replace(i_region_name,' ','');   --去掉空格
　　dyn_string:='create table '||dyn_tab_name||' (order_id number(10) primary key,order_date date not null,total_qty number,total_price number(15,2))';
　　execute immediate dyn_string;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(100);
　　begin
　　create_dyn_table('  u  k  ',v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　select * from orders_for_uk;
　　案例3：通过动态SQL给表添加外键
　　create or replace procedure create_dyn_table2(i_region_name in varchar2,retcd out number,errmsg out varchar2) authid current_user is
　　dyn_tab_name varchar2(200);
　　dyn_string varchar2(2000);
　　begin
　　dyn_tab_name:='ORDERS_ITEMS_FOR_'||replace(i_region_name,' ','');
　　dyn_string:='create table '||dyn_tab_name||' (order_id number(10) not null,item_id varchar2(20) not null,unit_pirce number(15,2),quantity number)';
　　execute immediate dyn_string;
　　dyn_string:='alter table '||dyn_tab_name||' add constraint '||' FK_OIFOR_'||replace(i_region_name,' ','')||' foreign key(order_id) references orders_for_'||replace(i_region_name,' ','')||'(order_id)';
　　execute immediate dyn_string;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(100);
　　begin
　　create_dyn_table2('  u  k  ',v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　select * from orders_items_for_uk;
　　select * from user_constraints where table_name='ORDERS_ITEMS_FOR_UK';
　　案例4：按照地区配置表，创建每个销售区域订单表跟订单明细表
　　地区配置表：
　　insert into region_tab values(1,'region1');
　　insert into region_tab values(2,'region2');
　　insert into region_tab values(3,'region3');
　　insert into region_tab values(4,'region4');
　　commit;
　　select * from region_tab;
　　region_tab  ->  orders_for_
　　->  orders_items_for_
　　有四个销售区域，每个销售区域需要有订单表跟订单明细表，表的结构都是一样的
　　create or replace procedure create_dyn_for_all(retcd out number,errmsg out varchar2) authid current_user is
　　cursor csr_region is select * from region_tab;
　　begin

　　for>　　create_dyn_table(idx.region_name,retcd,errmsg);
　　if retcd  0 then
　　exit;
　　end if;
　　create_dyn_table2(idx.region_name,retcd,errmsg);
　　if retcd  0 then
　　exit;
　　end if;
　　end loop;
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(100);
　　begin
　　create_dyn_for_all(v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　select * from user_tables where table_name like 'ORDERS%';      --查看到新创建了8个表
　　(2) 动态创建表
　　设计方式--根据配置表动态实现功能，只需要维护外面的配置表的内容，将功能封装
　　需求：字段名和表名都用配置表来配置，实现动态创建表的过程
　　步骤：
　　A 创建表1：配置表名
　　create table table_config(
　　tab_id number primary key,
　　table_name varchar2(20) not null,
　　crt_flag varchar2(2)
　　);
　　B 插入初始化数据：
　　insert into table_config values(1,'A','0');
　　insert into table_config values(2,'B','0');
　　commit;
　　C 创建表2：配置字段的表
　　create table column_config(
　　col_id number not null,
　　tab_id number not null,
　　col_name varchar2(20),
　　col_type varchar2(20),
　　col_length number,
　　col_pre number
　　);
　　添加主外键：
　　alter table column_config add constraint PK_COL_CFG primary key(col_id,tab_id);
　　alter table column_config add constraint FK_COL_CFG foreign key(tab_id) references table_config(tab_id);
　　D 初始化字段表
　　insert into column_config values(1,1,'A1','varchar2',20,null);
　　insert into column_config values(2,1,'A2','char',3,null);
　　insert into column_config values(3,1,'A3','number',12,null);
　　insert into column_config values(4,1,'A4','number',11,4);
　　insert into column_config values(5,1,'A5','date',null,null);
　　insert into column_config values(6,2,'B1','varchar2',22,null);
　　insert into column_config values(7,2,'B2','char',13,null);
　　insert into column_config values(8,2,'B3','number',20,null);
　　insert into column_config values(9,2,'B4','date',null,null);
　　insert into column_config values(10,2,'B5','number',12,2);
　　commit;
　　E 编写功能模块
　　模块1：读取字段配置表进行字段类型串的拼接
　　create or replace function dyn_type(i_type in varchar2,i_length in number,i_pre in number) return varchar2 is
　　v_dyn_type varchar2(1000);
　　begin
　　if i_type='varchar2' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='char' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='date' then
　　v_dyn_type:=i_type;
　　elsif i_type='number' then
　　if i_length is not null and i_pre is not null then
　　v_dyn_type:=i_type||'('||i_length||','||i_pre||')';
　　elsif i_length is not null and i_pre is null then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_length is null and i_pre is null then
　　v_dyn_type:=i_type;
　　end if;
　　end if;
　　return v_dyn_type;
　　exception when others then
　　return '';
　　end;
　　模块2：拼接创建表语句中类型模块
　　create or replace procedure dyn_crt_tab(i_tab_name in varchar2,i_crt_string out varchar2,retcd out number,errmsg out varchar2) authid current_user is
　　cursor csr_tab is select h.tab_id,h.table_name,o.col_type,o.col_name,o.col_length,o.col_pre
　　from column_config o,table_config h
　　where o.tab_id=h.tab_id
　　and h.table_name=i_tab_name;
　　csr_rec csr_tab%rowtype;
　　dyn_col_type varchar2(100);
　　dyn_tab_p varchar2(4000);
　　begin
　　open csr_tab;
　　dyn_tab_p:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when (csr_tab%notfound);
　　dyn_col_type:=dyn_type(csr_rec.col_type,csr_rec.col_length,csr_rec.col_pre)||',';   --调用拼接字段的函数
　　dyn_tab_p:=dyn_tab_p||csr_rec.col_name||' '||dyn_col_type;
　　end loop;
　　dyn_tab_p:=substr(dyn_tab_p,1,instr(dyn_tab_p,',',-1,1)-1);  --将最后一个','去掉
　　i_crt_string:=dyn_tab_p;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　模块3：执行模块
　　create or replace procedure dyn_crt(retcd out number,errmsg out varchar2) authid current_user is
　　cursor csr_tab is select h.tab_id,h.table_name
　　from table_config h
　　where h.crt_flag='0';
　　csr_rec csr_tab%rowtype;
　　dyn_string varchar2(4000);
　　v_crt_table varchar2(4000);
　　v_sqlcode number;
　　v_sqlerrm varchar2(200);
　　begin
　　open csr_tab;
　　dyn_string:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when(csr_tab%notfound);
　　dyn_crt_tab(csr_rec.table_name,v_crt_table,v_sqlcode,v_sqlerrm);                --调用表拼接的存储过程
　　dyn_string:='create table '||csr_rec.table_name||'('||v_crt_table||')';
　　execute immediate dyn_string;
　　update table_config set crt_flag='1' where tab_id=csr_rec.tab_id;
　　commit;
　　end loop;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(200);
　　begin
　　dyn_crt(v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　SQL> desc a
　　Name                       Null?    Type
　　----------------------------------------- -------- ----------------------------
　　A1                            VARCHAR2(20)
　　A2                            CHAR(3)
　　A3                            NUMBER(12)
　　A4                            NUMBER(11,4)
　　A5                            DATE
　　SQL> desc b
　　Name                       Null?    Type
　　----------------------------------------- -------- ----------------------------
　　B1                            VARCHAR2(22)
　　B2                            CHAR(13)
　　B3                            NUMBER(20)
　　B4                            DATE
　　B5                            NUMBER(12,2)
　　练习：
　　A 将上面3个子程序封装在一个package中存储，其中函数还有另外一个存储作为私有对象。
　　在包头加上authid current_user
　　存储过程中authid current_user去掉
　　B 开发一个动态drop表的SP封装在上面创建的包中，要求，在table_config表上添加一个字段，drop_flag varchar2(2)
　　删除标记，如果为1，表示要删除，如果为0表示保留，将所有标记为1的表删除掉，如果删除的时候发现已经被删除了，则要捕获异常将数据插入到我们之前创建异常监控表中，与此同时创建标记crt_flag置为0

　　>　　C 开发动态删除和添加字段的模块，需要在字段配置表上添加删除和添加的标记字段。

　　>
　　>　　包头：
　　create or replace package dyn_pkg authid current_user is
　　procedure dyn_crt(retcd out number,errmsg out varchar2);
　　procedure dyn_drop(retcd out number, errmsg out varchar2);
　　procedure dyn_add(retcd out number, errmsg out varchar2);
　　procedure dyn_del(retcd out number, errmsg out varchar2);
　　end dyn_pkg;
　　包体：
　　create or replace package body dyn_pkg is
　　function dyn_type(i_type in varchar2,i_length in number,i_pre in number)
　　return varchar2 is
　　v_dyn_type varchar2(1000);
　　begin
　　if i_type='VARCHAR2' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='CHAR' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='DATE' then
　　v_dyn_type:=i_type;
　　elsif i_type='NUMBER' then
　　if i_length is not null and i_pre is not null then
　　v_dyn_type:=i_type||'('||i_length||','||i_pre||')';
　　elsif i_length is not null and i_pre is  null then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_length is  null and i_pre is  null then
　　v_dyn_type:=i_type;
　　end if;
　　end if;
　　return v_dyn_type;
　　exception when others then
　　return '';
　　end;
　　procedure dyn_crt_tab(i_tab_name in varchar2,i_crt_string out varchar2,retcd out number,errmsg out varchar2) is
　　cursor csr_tab is select h.tab_id,h.table_name,o.col_type,o.col_name,o.col_length,o.col_pre
　　from column_config o,table_config h
　　where o.tab_id=h.tab_id
　　and h.table_name=i_tab_name;
　　csr_rec csr_tab%rowtype;
　　dyn_col_type varchar2(100);
　　dyn_tab_p varchar2(4000);
　　begin
　　open csr_tab;
　　dyn_tab_p:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when(csr_tab%notfound);
　　dyn_col_type:=dyn_type(csr_rec.col_type,csr_rec.col_length,csr_rec.col_pre)||',';   --调用拼接字段的函数
　　dyn_tab_p:=dyn_tab_p||csr_rec.col_name||' '||dyn_col_type;
　　end loop;
　　dyn_tab_p:=substr(dyn_tab_p,1,instr(dyn_tab_p,',',-1,1)-1);    --将最后一个‘,’去掉
　　i_crt_string:=dyn_tab_p;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　procedure dyn_crt(retcd out number,errmsg out varchar2) is
　　cursor csr_tab is select h.tab_id,h.table_name
　　from table_config h
　　where h.crt_flag='0';
　　csr_rec csr_tab%rowtype;
　　dyn_string varchar2(4000);
　　v_crt_table varchar2(4000);
　　v_retcd number;
　　v_errmsg varchar2(150);
　　begin
　　open csr_tab;
　　dyn_string:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when(csr_tab%notfound);
　　dyn_crt_tab(csr_rec.table_name,v_crt_table,v_retcd,v_errmsg);    --调用表拼接的函数
　　dyn_string:='create table '||csr_rec.table_name||'('||v_crt_table||')';
　　execute immediate dyn_string;
　　update table_config set crt_flag='1' where tab_id=csr_rec.tab_id;
　　commit;
　　end loop;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　procedure dyn_drop(retcd out number, errmsg out varchar2) is
　　cursor csr_drop is
　　select table_name, tab_id
　　from table_config
　　where drop_flag = '1';
　　csr_rec csr_drop%rowtype;
　　dyn_drop_config varchar2(100);
　　sqlnotfound exception;
　　pragma exception_init(sqlnotfound,-942);
　　v_sqlcode number;
　　v_sqlerrm varchar2(100);
　　begin
　　open csr_drop;
　　dyn_drop_config := '';
　　loop
　　fetch csr_drop
　　into csr_rec;
　　exit when(csr_drop%notfound);
　　dyn_drop_config := 'drop table ' || csr_rec.table_name;
　　execute immediate dyn_drop_config;
　　end loop;
　　close csr_drop;
　　retcd  := 0;
　　errmsg := 'successful';
　　exception
　　when sqlnotfound then
　　v_sqlcode:=sqlcode;
　　v_sqlerrm:=sqlerrm;
　　insert into exception_monitor            values
　　(csr_rec.table_name,
　　csr_rec.tab_id,
　　upper('dyn_drop'),
　　upper('sqlnotfound'),
　　v_sqlcode,
　　v_sqlerrm,
　　sysdate);
　　update table_config
　　set drop_flag = '0'
　　where tab_id = csr_rec.tab_id;
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　procedure dyn_add(retcd out number, errmsg out varchar2) is
　　cursor csr_add is
　　select t.table_name,
　　t.tab_id,
　　c.col_id,
　　c.col_name,
　　c.col_type,
　　c.col_length,
　　c.col_pre
　　包头：
　　create or replace package dyn_pkg authid current_user is
　　procedure dyn_crt(retcd out number,errmsg out varchar2);
　　procedure dyn_drop(retcd out number, errmsg out varchar2);
　　procedure dyn_add(retcd out number, errmsg out varchar2);
　　procedure dyn_del(retcd out number, errmsg out varchar2);
　　end dyn_pkg;
　　包体：
　　create or replace package body dyn_pkg is
　　function dyn_type(i_type in varchar2,i_length in number,i_pre in number)
　　return varchar2 is
　　v_dyn_type varchar2(1000);
　　begin
　　if i_type='VARCHAR2' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='CHAR' then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_type='DATE' then
　　v_dyn_type:=i_type;
　　elsif i_type='NUMBER' then
　　if i_length is not null and i_pre is not null then
　　v_dyn_type:=i_type||'('||i_length||','||i_pre||')';
　　elsif i_length is not null and i_pre is  null then
　　v_dyn_type:=i_type||'('||i_length||')';
　　elsif i_length is  null and i_pre is  null then
　　v_dyn_type:=i_type;
　　end if;
　　end if;
　　return v_dyn_type;
　　exception when others then
　　return '';
　　end;
　　procedure dyn_crt_tab(i_tab_name in varchar2,i_crt_string out varchar2,retcd out number,errmsg out varchar2) is
　　cursor csr_tab is select h.tab_id,h.table_name,o.col_type,o.col_name,o.col_length,o.col_pre
　　from column_config o,table_config h
　　where o.tab_id=h.tab_id
　　and h.table_name=i_tab_name;
　　csr_rec csr_tab%rowtype;
　　dyn_col_type varchar2(100);
　　dyn_tab_p varchar2(4000);
　　begin
　　open csr_tab;
　　dyn_tab_p:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when(csr_tab%notfound);
　　dyn_col_type:=dyn_type(csr_rec.col_type,csr_rec.col_length,csr_rec.col_pre)||',';   --调用拼接字段的函数
　　dyn_tab_p:=dyn_tab_p||csr_rec.col_name||' '||dyn_col_type;
　　end loop;
　　dyn_tab_p:=substr(dyn_tab_p,1,instr(dyn_tab_p,',',-1,1)-1);    --将最后一个‘,’去掉
　　i_crt_string:=dyn_tab_p;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　procedure dyn_crt(retcd out number,errmsg out varchar2) is
　　cursor csr_tab is select h.tab_id,h.table_name
　　from table_config h
　　where h.crt_flag='0';
　　csr_rec csr_tab%rowtype;
　　dyn_string varchar2(4000);
　　v_crt_table varchar2(4000);
　　v_retcd number;
　　v_errmsg varchar2(150);
　　begin
　　open csr_tab;
　　dyn_string:='';
　　loop
　　fetch csr_tab into csr_rec;
　　exit when(csr_tab%notfound);
　　dyn_crt_tab(csr_rec.table_name,v_crt_table,v_retcd,v_errmsg);    --调用表拼接的函数
　　dyn_string:='create table '||csr_rec.table_name||'('||v_crt_table||')';
　　execute immediate dyn_string;
　　update table_config set crt_flag='1' where tab_id=csr_rec.tab_id;
　　commit;
　　end loop;
　　close csr_tab;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　procedure dyn_drop(retcd out number, errmsg out varchar2) is
　　cursor csr_drop is
　　select table_name, tab_id
　　from table_config
　　where drop_flag = '1';
　　csr_rec csr_drop%rowtype;
　　dyn_drop_config varchar2(100);
　　sqlnotfound exception;
　　pragma exception_init(sqlnotfound,-942);
　　v_sqlcode number;
　　v_sqlerrm varchar2(100);
　　begin
　　open csr_drop;
　　dyn_drop_config := '';
　　loop
　　fetch csr_drop
　　into csr_rec;
　　exit when(csr_drop%notfound);
　　dyn_drop_config := 'drop table ' || csr_rec.table_name;
　　execute immediate dyn_drop_config;
　　end loop;
　　close csr_drop;
　　retcd  := 0;
　　errmsg := 'successful';
　　exception
　　when sqlnotfound then
　　v_sqlcode:=sqlcode;
　　v_sqlerrm:=sqlerrm;
　　insert into exception_monitor            values
　　(csr_rec.table_name,
　　csr_rec.tab_id,
　　upper('dyn_drop'),
　　upper('sqlnotfound'),
　　v_sqlcode,
　　v_sqlerrm,
　　sysdate);
　　update table_config
　　set drop_flag = '0'
　　where tab_id = csr_rec.tab_id;
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　procedure dyn_add(retcd out number, errmsg out varchar2) is
　　cursor csr_add is
　　select t.table_name,
　　t.tab_id,
　　c.col_id,
　　c.col_name,
　　c.col_type,
　　c.col_length,
　　c.col_pre
　　from table_config t, column_config c
　　where t.tab_id = c.tab_id
　　and c.add_flag = '1';
　　csr_rec csr_add%rowtype;
　　dyn_col_type   varchar2(200);
　　dyn_add_string varchar2(4000);
　　begin
　　open csr_add;
　　dyn_add_string := '';
　　loop
　　fetch csr_add
　　into csr_rec;
　　exit when(csr_add%notfound);
　　dyn_col_type   := dyn_type(csr_rec.col_type,
　　csr_rec.col_length,
　　csr_rec.col_pre);
　　dyn_add_string := 'alter table ' || csr_rec.table_name ||
　　' add ' || csr_rec.col_name || ' ' ||
　　dyn_col_type;
　　execute immediate dyn_add_string;
　　update column_config set add_flag='0' where tab_id=csr_rec.tab_id and col_id=csr_rec.col_id;
　　end loop;
　　close csr_add;
　　retcd  := 0;
　　errmsg := 'successful!';
　　exception
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　procedure dyn_del(retcd out number, errmsg out varchar2) is
　　cursor csr_del is
　　select t.table_name, c.col_name ,t.tab_id , c.col_id
　　from table_config t, column_config c
　　where t.tab_id = c.tab_id
　　and c.del_flag = '1';
　　dyn_del_string varchar2(4000);
　　csr_rec csr_del%rowtype;
　　begin
　　open csr_del;
　　dyn_del_string := '';
　　loop
　　fetch csr_del
　　into csr_rec;
　　exit when(csr_del%notfound);
　　dyn_del_string := 'alter table ' || csr_rec.table_name ||
　　' drop column ' || csr_rec.col_name;
　　execute immediate dyn_del_string;
　　update column_config set del_flag='0' where tab_id=csr_rec.tab_id and col_id=csr_rec.col_id;
　　end loop;
　　close csr_del;
　　retcd  := 0;
　　errmsg := 'successful!';
　　exception
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　end dyn_pkg;
　　from table_config t, column_config c
　　where t.tab_id = c.tab_id
　　and c.add_flag = '1';
　　csr_rec csr_add%rowtype;
　　dyn_col_type   varchar2(200);
　　dyn_add_string varchar2(4000);
　　begin
　　open csr_add;
　　dyn_add_string := '';
　　loop
　　fetch csr_add
　　into csr_rec;
　　exit when(csr_add%notfound);
　　dyn_col_type   := dyn_type(csr_rec.col_type,
　　csr_rec.col_length,
　　csr_rec.col_pre);
　　dyn_add_string := 'alter table ' || csr_rec.table_name ||
　　' add ' || csr_rec.col_name || ' ' ||
　　dyn_col_type;
　　execute immediate dyn_add_string;
　　update column_config set add_flag='0' where tab_id=csr_rec.tab_id and col_id=csr_rec.col_id;
　　end loop;
　　close csr_add;
　　retcd  := 0;
　　errmsg := 'successful!';
　　exception
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　procedure dyn_del(retcd out number, errmsg out varchar2) is
　　cursor csr_del is
　　select t.table_name, c.col_name ,t.tab_id , c.col_id
　　from table_config t, column_config c
　　where t.tab_id = c.tab_id
　　and c.del_flag = '1';
　　dyn_del_string varchar2(4000);
　　csr_rec csr_del%rowtype;
　　begin
　　open csr_del;
　　dyn_del_string := '';
　　loop
　　fetch csr_del
　　into csr_rec;
　　exit when(csr_del%notfound);
　　dyn_del_string := 'alter table ' || csr_rec.table_name ||
　　' drop column ' || csr_rec.col_name;
　　execute immediate dyn_del_string;
　　update column_config set del_flag='0' where tab_id=csr_rec.tab_id and col_id=csr_rec.col_id;
　　end loop;
　　close csr_del;
　　retcd  := 0;
　　errmsg := 'successful!';
　　exception
　　when others then
　　retcd  := sqlcode;
　　errmsg := sqlerrm;
　　end;
　　end dyn_pkg;
　　###########################################################################################
　　12.2 动态PLSQL语句
　　(1) 动态单行select语句
　　A 录入订单表和订单明细表的初始化数据
　　insert into orders_for_region1 values(1,sysdate,null,null);
　　insert into orders_items_for_region1 values(1,'A001',20,13);
　　insert into orders_items_for_region1 values(1,'A001',23,10);
　　insert into orders_items_for_region1 values(1,'A001',11,5);
　　commit;
　　B 创建SP，更新订单表的值
　　create or replace procedure update_dyn_table(i_region_name in varchar2,i_order_id in number,retcd out number,errmsg out varchar2) authid current_user is
　　dyn_upd_string varchar2(2000);
　　dyn_query_string varchar2(1000);
　　dyn_tab_name1 varchar2(30);
　　dyn_tab_name2 varchar2(30);
　　v_total_price number;
　　v_total_quantity number;
　　begin
　　dyn_tab_name1:='ORDERS_FOR_'||replace(i_region_name,' ','');
　　dyn_tab_name2:='ORDERS_ITEMS_FOR_'||replace(i_region_name,' ','');
　　dyn_query_string:='select sum(quantity),sum(quantity*unit_pirce) from '||dyn_tab_name2||' where order_id=:input_order_id';   --:input_order_id表示绑定变量
　　execute immediate dyn_query_string into v_total_quantity,v_total_price using i_order_id;
　　dyn_upd_string:='update '||dyn_tab_name1||' set total_qty=:v_total_qty,total_price=:v_total_price where order_id=:input_order_id';
　　execute immediate dyn_upd_string using v_total_quantity,v_total_price,i_order_id;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(200);
　　begin
　　update_dyn_table('region1',1,v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　查询：
　　select * from orders_for_region1;
　　注意：
　　A 动态PLSQL语句中，select语句需要用into来接收参数值
　　B 绑定变量在动态PLSQL中，用冒号前置的方式来定义，不需要声明
　　C 执行的时候，绑定变量需要用using关键字来赋值
　　(2) 动态多行的select语句
　　A 初始化数据
　　insert into orders_for_region1 values(2,sysdate,null,null);
　　insert into orders_items_for_region1 values(2,'A001',30,13);
　　insert into orders_items_for_region1 values(2,'A001',33,10);
　　insert into orders_items_for_region1 values(2,'A001',11,7);
　　insert into orders_for_region1 values(3,sysdate,null,null);
　　insert into orders_items_for_region1 values(3,'A001',50,13);
　　insert into orders_items_for_region1 values(3,'A001',63,7);
　　insert into orders_items_for_region1 values(3,'A001',21,5);
　　insert into orders_for_region1 values(4,sysdate,null,null);
　　insert into orders_items_for_region1 values(4,'A001',30,13);
　　insert into orders_items_for_region1 values(4,'A001',35,10);
　　insert into orders_items_for_region1 values(4,'A001',12,5);
　　commit;
　　B 编写SP实现多行的更新
　　create or replace procedure update_dyn_all_table(i_region_name in varchar2,retcd out number,errmsg out varchar2) authid current_user is
　　dyn_upd_string varchar2(1000);
　　dyn_query_string varchar2(1000);
　　dyn_tab_name varchar2(30);
　　v_total_price number;
　　v_total_quantity number;
　　type csr_dyn is ref cursor;
　　csr_dyn1 csr_dyn;
　　v_order_id number;
　　begin
　　dyn_tab_name:='ORDERS_ITEMS_FOR_'||replace(i_region_name,' ','');
　　dyn_query_string:='select distinct order_id from '||dyn_tab_name;
　　open csr_dyn1 for dyn_query_string;   --动态的方式打开游标
　　loop
　　fetch csr_dyn1 into v_order_id;
　　exit when(csr_dyn1%notfound);
　　update_dyn_table(i_region_name,v_order_id,retcd,errmsg);
　　if retcd  0 then
　　exit;
　　end if;
　　end loop;
　　close csr_dyn1;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(200);
　　begin
　　update_dyn_all_table('region1',v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　验证：
　　select * from orders_for_region1;
　　目前为止只是处理了一个销售区域，总共有四个销售区域，怎样处理四个销售区域用一个SP
　　A 初始化
　　insert into orders_for_region1 values(1,sysdate,null,null);
　　insert into orders_items_for_region1 values(1,'A001',30,13);
　　insert into orders_items_for_region1 values(1,'A001',33,10);
　　insert into orders_items_for_region1 values(1,'A001',11,7);
　　insert into orders_for_region1 values(2,sysdate,null,null);
　　insert into orders_items_for_region1 values(2,'A001',30,13);
　　insert into orders_items_for_region1 values(2,'A001',33,10);
　　insert into orders_items_for_region1 values(2,'A001',11,7);
　　insert into orders_for_region1 values(3,sysdate,null,null);
　　insert into orders_items_for_region1 values(3,'A001',50,13);
　　insert into orders_items_for_region1 values(3,'A001',63,7);
　　insert into orders_items_for_region1 values(3,'A001',21,5);
　　insert into orders_for_region1 values(4,sysdate,null,null);
　　insert into orders_items_for_region1 values(4,'A001',30,13);
　　insert into orders_items_for_region1 values(4,'A001',35,10);
　　insert into orders_items_for_region1 values(4,'A001',12,5);
　　commit;
　　insert into orders_for_region2 values(1,sysdate,null,null);
　　insert into orders_items_for_region2 values(1,'A001',30,13);
　　insert into orders_items_for_region2 values(1,'A001',33,10);
　　insert into orders_items_for_region2 values(1,'A001',11,7);
　　insert into orders_for_region2 values(2,sysdate,null,null);
　　insert into orders_items_for_region2 values(2,'A001',30,13);
　　insert into orders_items_for_region2 values(2,'A001',33,10);
　　insert into orders_items_for_region2 values(2,'A001',11,7);
　　insert into orders_for_region2 values(3,sysdate,null,null);
　　insert into orders_items_for_region2 values(3,'A001',50,13);
　　insert into orders_items_for_region2 values(3,'A001',63,7);
　　insert into orders_items_for_region2 values(3,'A001',21,5);
　　insert into orders_for_region2 values(4,sysdate,null,null);
　　insert into orders_items_for_region2 values(4,'A001',30,13);
　　insert into orders_items_for_region2 values(4,'A001',35,10);
　　insert into orders_items_for_region2 values(4,'A001',12,5);
　　commit;
　　insert into orders_for_region3 values(1,sysdate,null,null);
　　insert into orders_items_for_region3 values(1,'A001',30,13);
　　insert into orders_items_for_region3 values(1,'A001',33,10);
　　insert into orders_items_for_region3 values(1,'A001',11,7);
　　insert into orders_for_region3 values(2,sysdate,null,null);
　　insert into orders_items_for_region3 values(2,'A001',30,13);
　　insert into orders_items_for_region3 values(2,'A001',33,10);
　　insert into orders_items_for_region3 values(2,'A001',11,7);
　　insert into orders_for_region3 values(3,sysdate,null,null);
　　insert into orders_items_for_region3 values(3,'A001',50,13);
　　insert into orders_items_for_region3 values(3,'A001',63,7);
　　insert into orders_items_for_region3 values(3,'A001',21,5);
　　insert into orders_for_region3 values(4,sysdate,null,null);
　　insert into orders_items_for_region3 values(4,'A001',30,13);
　　insert into orders_items_for_region3 values(4,'A001',35,10);
　　insert into orders_items_for_region3 values(4,'A001',12,5);
　　commit;
　　insert into orders_for_region4 values(1,sysdate,null,null);
　　insert into orders_items_for_region4 values(1,'A001',30,13);
　　insert into orders_items_for_region4 values(1,'A001',33,10);
　　insert into orders_items_for_region4 values(1,'A001',11,7);
　　insert into orders_for_region4 values(2,sysdate,null,null);
　　insert into orders_items_for_region4 values(2,'A001',30,13);
　　insert into orders_items_for_region4 values(2,'A001',33,10);
　　insert into orders_items_for_region4 values(2,'A001',11,7);
　　insert into orders_for_region4 values(3,sysdate,null,null);
　　insert into orders_items_for_region4 values(3,'A001',50,13);
　　insert into orders_items_for_region4 values(3,'A001',63,7);
　　insert into orders_items_for_region4 values(3,'A001',21,5);
　　insert into orders_for_region4 values(4,sysdate,null,null);
　　insert into orders_items_for_region4 values(4,'A001',30,13);
　　insert into orders_items_for_region4 values(4,'A001',35,10);
　　insert into orders_items_for_region4 values(4,'A001',12,5);
　　commit;
　　B 创建SP实现多个区域的销售数据的计算。region_tab
　　create or replace procedure update_dyn_global(retcd out number,errmsg out varchar2) authid current_user is
　　cursor csr_region is select region_name from region_tab;
　　begin

　　for>　　update_dyn_all_table(idx.region_name,retcd,errmsg);
　　if retcd  0 then
　　exit;
　　end if;
　　end loop;
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　测试：
　　declare
　　v_sqlcode number;
　　v_sqlerrm varchar2(200);
　　begin
　　update_dyn_global(v_sqlcode,v_sqlerrm);
　　dbms_output.put_line(v_sqlerrm);
　　end;
　　验证：
　　select * from orders_for_region1;
　　select * from orders_for_region2;
　　select * from orders_for_region3;
　　select * from orders_for_region4;
　　在存储过程中使用动态PLSQL调用存储过程，封装在SP中
　　create or replace procedure update_dyn_global2(retcd out number,errmsg out varchar2) authid current_user is
　　begin
　　execute immediate 'begin update_dyn_global(:1,:2);end;' using out retcd,out errmsg;
　　--动态PLSQL语句中调用存储过程
　　retcd:=0;
　　errmsg:='successful!';
　　exception when others then
　　retcd:=sqlcode;
　　errmsg:=sqlerrm;
　　end;
　　练习：
　　将上面的三个SP封装在一个package中，全部做成公有对象
　　SCOTT用户下
　　A 创建一个表emp_salary_grd ： 三个列：员工号 empno 、工资等级 sal_level、年薪 sal_anl  (sal*12+comm)
　　B 创建一个SP，用动态PLSQL的方式将这个表的数据装载
　　C 用动态PSLQL方式调用上面创建的SP
　　设计一个SP,将PLSQL用户下所有的触发器禁用掉，用动态SQL方式
　　###########################################################################################