PL/SQL执行动态SQL

PL/SQL动态SQL 

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

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

DECLARE
  get_count_sql varchar2(100);
  row_count INT;
BEGIN
    get_count_sql:='select count(*) from bizbusinessbaseinfo';
    EXECUTE IMMEDIATE get_count_sql INTO row_count;
    dbms_output.put_line(row_count);
END;

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

DECLARE
  delete_sql varchar2(100);
BEGIN
    delete_sql:='delete from bizbusinessbaseinfo where bizname=''1''';
    execute immediate delete_sql;
END;

3.执行动态DDL语句 

DECLARE
  drop_sql varchar2(100);
BEGIN
    drop_sql:='DROP TABLE logrec_action';
    execute immediate drop_sql;
END;

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

DECLARE
  query_sql varchar2(100);
  queryresult varchar2(50);
  bizcursor SYS_REFCURSOR;
BEGIN
    query_sql:='select bizname from bizbusinessbaseinfo';
    OPEN bizcursor FOR query_sql;
    loop
      fetch bizcursor into queryresult;
      --判读是否提取到值，没取到值就退出
      --取到值c_job%notfound 是false
      --取不到值c_job%notfound 是true
      exit when bizcursor%notfound;
      dbms_output.put_line(queryresult);
    end loop;
    --关闭游标
    close bizcursor;
END;

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

DECLARE
  query_sql varchar2(100);
  queryresult varchar2(50);
  bizcursor SYS_REFCURSOR;
  type my_record is record(
       bizname varchar2(64),
       bizstatus char(1));
  my_rec my_record;
BEGIN
    query_sql:='select bizname,bizstatus from bizbusinessbaseinfo';
    OPEN bizcursor FOR query_sql;
    loop
      fetch bizcursor into my_rec;
      --判读是否提取到值，没取到值就退出
      --取到值c_job%notfound 是false
      --取不到值c_job%notfound 是true
      exit when bizcursor%notfound;
      dbms_output.put_line(my_rec.bizname|| '    ' ||my_rec.bizstatus);
    end loop;
    --关闭游标
    close bizcursor;
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; 

 

•