动态Sql

create or replace procedure ly_pkgl_zza_test(
                                      i_xh in varchar2,
                                      i_nj in varchar2,
                                      i_bm in varchar2,
                                      i_sql in varchar,
                                      o_errMsg out varchar2
                                      ) is

/*
  test

*/
 var_number integer default 0;
 var_text varchar2(50);
 v_sql varchar2(200);
 v_cursor number;
 v_mynum integer;
 v_numrows integer;
 my_exception exception;
 
 mul_PRIMARYKEY exception;
 pragma exception_init(mul_PRIMARYKEY,-00001);
 
 
 invalid_err exception;
 
 type list_type is table of xsxxgl_xsjbxx.xm%type index by binary_integer;  
  xm_list list_type;  
  type list_type1 is table of xsxxgl_xsjbxx.nj%type index by binary_integer;  
  nj_list list_type1; 
 
begin
    --method1
    /*v_sql:='update pkgl_ttkxx ttk set ttk.sqxxyy='''||'开会'||''' where ttk.zj='||'123';
    dbms_output.put_line(v_sql);
    execute immediate  v_sql; */
        
    
    /*--method2
    v_sql:='update pkgl_ttkxx ttk set ttk.sqxxyy=:v_sqyy where ttk.zj=:v_zj';
    dbms_output.put_line(v_sql);
    execute immediate  v_sql using '开会1','123'; */ 
    
    
   /* --method3 xh=01021804001,xm=李俊泽 单行查询
    v_sql:='select a.xm from xsxxgl_xsjbxx a where  a.xh=:xh';
   --注意：v_sql中不能以分号结束
    execute immediate v_sql into var_text using i_xh;
    dbms_output.put_line(i_xh||' 的姓名是：'||var_text);*/       
    
    
    /*--method3 多行查询
    v_sql:='select a.nj,a.xm from xsxxgl_xsjbxx a where  a.nj=:nj';
   --注意：v_sql中不能以分号结束
    execute immediate v_sql bulk collect into nj_list, xm_list using i_nj;
    for i in xm_list.first .. xm_list.last loop
    dbms_output.put_line('年级: '||nj_list(i)||'  姓名: '||xm_list(i));
    end loop; */ 
    
    
       
    /*--method3 多行查询 false
    v_sql:='select a.nj,a.xm from :table a where  a.nj=:nj';
   --注意：v_sql中不能以分号结束
    execute immediate v_sql bulk collect into nj_list, xm_list using 'xsxxgl_xsjbxx', i_nj;
    for i in xm_list.first .. xm_list.last loop
    dbms_output.put_line('年级: '||nj_list(i)||'  姓名: '||xm_list(i));
    end loop; 
    */
    
    
   /* --method3 多行查询   如果更改表名或者改列名，拼SQL，把v_sql 作为参数。
    v_sql:='select a.nj,a.xm from '||i_bm|| ' a where  a.nj=:nj';
    dbms_output.put_line(v_sql);
   --注意：v_sql中不能以分号结束
    execute immediate v_sql bulk collect into nj_list, xm_list using i_nj;
    for i in xm_list.first .. xm_list.last loop
    dbms_output.put_line('年级: '||nj_list(i)||'  姓名: '||xm_list(i));
    end loop;*/
     
    
       
       /*--method4, dbms包（表可变，查询列可变）  select xm from xsxxgl_xsjbxx  where  nj='2018' 
        v_cursor:=dbms_sql.open_cursor; --为处理打开光标 
          --打印语句
          dbms_output.put_line('v_sql: '||i_sql);          
          --分析语句 
          dbms_sql.parse(v_cursor,i_sql,dbms_sql.native);
          --定义查询列
          dbms_sql.define_column(v_cursor,1,var_text,50);
          --执行语句,返回的结果行数为：v_numrows
          v_numrows :=dbms_sql.execute(v_cursor);
          --打印结果  
   
          loop
          if dbms_sql.fetch_rows(v_cursor)=0 then exit;
          end if;
           dbms_sql.column_value(v_cursor,1,var_text);
           dbms_output.put_line(var_text);
          end loop;                        
         dbms_sql.close_cursor(v_cursor); --关闭光标 */
         
          
   --1. 以上的动态SQL语句也可以用动态Plsql改写
         
   --2. 以上只是动态SQL在存储过程里的应用，那么动态SQL在View应用是怎么样呢？(包内的方法进行传参数)
   
   
   
   -------------------------异常------------------
   
  /* --预定义的异常 no_data_found
   begin
      select a.xm into var_text from xsxxgl_xsjbxx a where  a.xh=i_xh;
    
    exception
       when no_data_found  or too_many_rows then
                dbms_output.put_line(i_xh||' 的姓名是：不存在的');
    end ;
   
   --预定义的异常 too_many_rows
   begin
      select a.xm into var_text from xsxxgl_xsjbxx a;    
    exception
       when too_many_rows then
                dbms_output.put_line('查询得到的姓名是很多的');
         end ;*/
   
/*   
   --非预定义的异常
   begin
     insert into xsxxgl_xsjbxx (xsid,xh,xm,nj,sfyxj,xbdm) values('1','1','1','1','1','1');
     insert into xsxxgl_xsjbxx (xsid,xh,xm,nj,sfyxj,xbdm) values('1','1','1','1','1','1');     
     exception 
       when mul_PRIMARYKEY then 
       dbms_output.put_line('主键不唯一了！');
       end;*/
       
      
      ---自定义异常
       update xsxxgl_xsjbxx a set a.xm='张三' where a.xh='123'; 
       if sql%notfound then
         raise invalid_err;
         end if;
       
       exception 
         when invalid_err then
          dbms_output.put_line('学号为：'||i_xh||' 的姓名是：是不存在滴！');          
       rollback;  
         
/*       
exception
    when others then
       o_errMsg:=o_errMsg||' 程序运行出现内部错误! '||dbms_utility.format_error_backtrace()||SQLCODE||'---'||SQLERRM;
    rollback; */   
end ly_pkgl_zza_test;

 --摘自二期培训