各位大侠,能不能帮忙解释下这个储存过程?(红字部分不懂)另外推荐1个有关储存过程,触发器之类的教程,感激不尽!一个统计表汇总统计数据,统计数据的产生需要根据一个基本表,动态执行sql语句.在存储过程中,动态生成sql语句然后执行,需要用到Execute Immediate命令.create or replace procedure P_INSERT_XT_TJ_MX(sDate in varchar2) is
declare
sXh xt_tj.xh%type;
sDwmc xt_tj.dwmc%type;
sDw xt_tj.dw%type;
sDwzd xt_tj.dwzd%type;
sTable xt_tj.hzbmc%type;
sSjzd xt_tj.sjzd%type;
sSqlStr varchar2(300);
iCount int;
cursor curSort is
select xh,dwmc,hzbmc,sjzd,dwzd,dw from xt_tj ;--这一句没有from,也没有where条件
curSortRec curSort%rowtype;
begin
open curSort;
loop
fetch curSort into curSortRec;
exit when curSort%notfound;
sSqlStr := 'select count(*) from ' sTable ' where to_char('sSjzd',''''YYYYMM'')=:sDate and 'sDwzd
' in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =:sDw)';
Execute Immediate sSqlStr into iCount using sDate,sDw;
delete from xt_tj_mx where fxh=sXh and sjz=sDate;
insert into xt_tj_mx(xh,fxh,hzsm,sjz)values(SEQ_XT_TJ_MX.NEXTVAL,sXh,iCount,sDate);
commit;
end loop;
end P_INSERT_XT_TJ_MX;
declare
sXh xt_tj.xh%type;
sDwmc xt_tj.dwmc%type;
sDw xt_tj.dw%type;
sDwzd xt_tj.dwzd%type;
sTable xt_tj.hzbmc%type;
sSjzd xt_tj.sjzd%type;
sSqlStr varchar2(300);
iCount int;
cursor curSort is
select xh,dwmc,hzbmc,sjzd,dwzd,dw from xt_tj ;--这一句没有from,也没有where条件
curSortRec curSort%rowtype;
begin
open curSort;
loop
fetch curSort into curSortRec;
exit when curSort%notfound;
sSqlStr := 'select count(*) from ' sTable ' where to_char('sSjzd',''''YYYYMM'')=:sDate and 'sDwzd
' in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =:sDw)';
Execute Immediate sSqlStr into iCount using sDate,sDw;
delete from xt_tj_mx where fxh=sXh and sjz=sDate;
insert into xt_tj_mx(xh,fxh,hzsm,sjz)values(SEQ_XT_TJ_MX.NEXTVAL,sXh,iCount,sDate);
commit;
end loop;
end P_INSERT_XT_TJ_MX;
while driverCursor%found loop
第二个:loop也要解释?就是循环,不知楼主是啥意思
第三个:当cursort已经到末尾了,就结束循环
第四个:就是用来执行动态SQL,动态SQL就是根据不同的内容拼接起来,不是固定的一条SQL
sSqlStr := 'select count(*) from ' sTable ' where to_char('sSjzd',''''YYYYMM'')=:sDate and 'sDwzd
' in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =:sDw)';
为啥查询语句和表名也要加引号,:sDate代表什么,要是new:sDate还能理解,再就是Execute Immediate sSqlStr into iCount using sDate,sDw;
sSqlStr,iCount已经声明为varchar2(300)和 int,这个语句啥意思?
Execute Immediate sSqlStr into iCount using sDate,sDw;
相当于
select count(*) into iCount from sTable表名 where ... ...=sDate and ... ...=sDw;
详细的用法楼主去搜索一下Execute Immediate就都有了单引号用法不对吧
sSqlStr := 'select count(*) from '||sTable||' where to_char('||sSjzd||',''YYYYMM'')=:sDate and '||sDwzd||
' in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =:sDw)';
两个单引号在字符串中表示一个,字符串连接是||