plsql动态的sql
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
---- ------ -------- ------- --------
COLNUMBER
改写为存储过程：
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('uk',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('uk',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 retcd0 then
exit;
end if;
create_dyn_table2(idx.region_name,retcd,errmsg);
if retcd0 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 isnull then
v_dyn_type:=i_type||'('||i_length||')';
elsif i_length isnull and i_pre isnull 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 isnull then
v_dyn_type:=i_type||'('||i_length||')';
elsif i_length isnull and i_pre isnull 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 retcd0 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 retcd0 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方式
###########################################################################################
页:
[1]