PL/SQL执行动态SQL

PL/SQL动态SQL 

依据： 
1.使用EXECUTE IMMEDIATE可以来执行动态SQL 
2.open cursor可以打开一个sql的查询，fetch cursor可以挨个获取查询记录 

示例 
1.使用EXECUTE IMMEDIATE查询一张数据表的记录数 

Sql代码  

1. DECLARE   
2.   get_count_sql varchar2(100);  
3.   row_count INT;  
4. BEGIN  
5.     get_count_sql:='select count(*) from bizbusinessbaseinfo';  
6.     EXECUTE IMMEDIATE get_count_sql INTO row_count;  
7.     dbms_output.put_line(row_count);  
8. END;  

2.使用EXECUTE IMMEDIATE执行动态删除语句 

Sql代码  

1. DECLARE   
2.   delete_sql varchar2(100);  
3. BEGIN  
4.     delete_sql:='delete from bizbusinessbaseinfo where bizname=''1''';  
5.     execute immediate delete_sql;  
6. END;  

3.执行动态DDL语句 

Sql代码  

1. DECLARE   
2.   drop_sql varchar2(100);  
3. BEGIN  
4.     drop_sql:='DROP TABLE logrec_action';  
5.     execute immediate drop_sql;  
6. END;  

4.使用open cursor进行简单的动态查询 

Sql代码  

1. DECLARE   
2.   query_sql varchar2(100);  
3.   queryresult varchar2(50);  
4.   bizcursor SYS_REFCURSOR;  
5. BEGIN  
6.     query_sql:='select bizname from bizbusinessbaseinfo';  
7.     OPEN bizcursor FOR query_sql;  
8.     loop  
9.       fetch bizcursor into queryresult;  
10.       --判读是否提取到值，没取到值就退出  
11.       --取到值c_job%notfound 是false   
12.       --取不到值c_job%notfound 是true  
13.       exit when bizcursor%notfound;  
14.       dbms_output.put_line(queryresult);  
15.     end loop;  
16.     --关闭游标  
17.     close bizcursor;  
18. END;  

5.动态查询出多个字段(需要先定义好输出结果的类型，不方便用在动态查询中) 

Sql代码  

1. DECLARE   
2.   query_sql varchar2(100);  
3.   queryresult varchar2(50);  
4.   bizcursor SYS_REFCURSOR;  
5.   type my_record is record(  
6.        bizname varchar2(64),  
7.        bizstatus char(1));  
8.   my_rec my_record;  
9. BEGIN  
10.     query_sql:='select bizname,bizstatus from bizbusinessbaseinfo';  
11.     OPEN bizcursor FOR query_sql;  
12.     loop  
13.       fetch bizcursor into my_rec;  
14.       --判读是否提取到值，没取到值就退出  
15.       --取到值c_job%notfound 是false   
16.       --取不到值c_job%notfound 是true  
17.       exit when bizcursor%notfound;  
18.       dbms_output.put_line(my_rec.bizname|| '    ' ||my_rec.bizstatus);  
19.     end loop;  
20.     --关闭游标  
21.     close bizcursor;  
22. END;  

 

 

查询count

 

         --查询理赔次数

       

get_count_sql:=  'select  count(case  g.status when ''A'' then ''A'' end ) as countA ,
		count(case g.status when ''P'' then ''P'' end ) as countP  
		from gcAdjustmentPersonFee g ,GcAdjustmentPerson p
		where  g.lossNo=p.lossno and g.lossseqno=p.lossseqno and  g.paidserialno=p.paidserialno
		and  g.policyno='''||rec_selectInfoArgs.Policyno||''' and g.clientcode='''||rec_selectInfoArgs.Clientcode||'''
		and g.liabcode in (select * from table(str_split('''||rec_selectInfoArgs.Liabcode||''','',''))) and g.kindcode='''||rec_selectInfoArgs.Kindcode ||''''|| n_sql;

EXECUTE IMMEDIATE get_count_sql INTO n_countA,n_countP;  

n_remainlimit:=  rec_findGcPolicyLimitTemp.Shortlimit-n_countA;
n_predictlimit:=  rec_findGcPolicyLimitTemp.Shortlimit-n_countA-n_countP;

 

 

 

--查询多次条记录

 --记录表into类型

   

   bizcursor SYS_REFCURSOR; 
type my_record is record(  
      n_lossno gcpolicypaidhistorytemp.lossno%type,
      n_lossseqno gcpolicypaidhistorytemp.lossseqno%type,
      n_upload gcpolicypaidhistorytemp.upload%type); 
my_rec my_record;
n_historyId gcpolicypaidhistorytemp.historyid%type:=1;
 
 
get_detail_sql:='select  g.lossNo,g.lossSeqNo,m.dataUploadFlag
    from gcAdjustmentPersonFee g ,GcAdjustmentPerson p,gcAdjustmentMain m
     where  g.lossNo=p.lossno and g.lossseqno=p.lossseqno and  g.paidserialno=p.paidserialno and p.lossno=m.lossno and g.lossseqno=m.lossseqno
      and  g.policyno='''||rec_selectInfoArgs.Policyno||''' and g.clientcode='''||rec_selectInfoArgs.Clientcode||'''
      and g.liabcode in (select * from table(str_split('''||rec_selectInfoArgs.Liabcode||''','',''))) and g.kindcode='''||rec_selectInfoArgs.Kindcode ||''''|| n_sql;

OPEN bizcursor FOR get_detail_sql; 
loop fetch bizcursor into my_rec;  
 EXIT WHEN bizcursor%NOTFOUND; 
     insert into gcpolicypaidhistorytemp (historyId,limitId,policyno,endorseqno,endorno,lossNo,lossSeqNo,upload) 
	    values (n_historyId,rec_findGcPolicyLimitTemp.Limitid,rec_findGcPolicyLimitTemp.Policyno,rec_findGcPolicyLimitTemp.Endorseqno,rec_findGcPolicyLimitTemp.Endorno,
		   my_rec.n_lossno, my_rec.n_lossseqno, my_rec.n_upload);
	   n_historyId:=n_historyId+1;        
end loop;  
--关闭游标  
close bizcursor; 

 

•