Oracle 动态sql(二)

-- Created on 2019/4/10 by IRIS.ZHOU 
--使用dbms_sql實現動態sql

/**
TYPE binary_double_table IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;
TYPE binary_float_table IS TABLE OF BINARY_FLOAT   INDEX BY BINARY_INTEGER;
TYPE bfile_table    IS TABLE OF BFILE          INDEX BY BINARY_INTEGER;
TYPE blob_table     IS TABLE OF BLOB           INDEX BY BINARY_INTEGER;
TYPE clob_table     IS TABLE OF CLOB           INDEX BY BINARY_INTEGER;
TYPE date_table     IS TABLE OF DATE           INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_Table IS TABLE OF dsinterval_unconstrained INDEX BY BINARY_INTEGER;
TYPE interval_year_to_MONTH_Table IS TABLE OF yminterval_unconstrained  INDEX BY BINARY_INTEGER;
TYPE number_table   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;
TYPE time_table     IS TABLE OF time_unconstrained INDEX BY BINARY_INTEGER;
TYPE time_with_time_zone_table IS TABLE OF time_tz_unconstrained INDEX BY BINARY_INTEGER;
TYPE timestamp_table  IS TABLE OF timestamp_unconstrained INDEX BY BINARY_INTEGER;
TYPE timestamp_with_ltz_Table  IS TABLE OF timestamp_ltz_unconstrained INDEX BY BINARY_INTEGER;
TYPE timestamp_with_time_zone_Table  IS TABLE OF timestamp_tz_unconstrained  INDEX BY BINARY_INTEGER;
TYPE urowid_table   IS TABLE OF UROWID         INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
**/

/**
declare 
  mysqlsel varchar2(200);
  mysqlins varchar2(200);
  sar number(10):=2000;
  num integer;
  c_name emp1.name%type;
  c_did emp1.did%type;
  c integer;
  e_num integer;
begin
 
  mysqlsel:='select e.name,e.did from emp1 e where e.sar>:sar';
  c:=dbms_sql.open_cursor;
  dbms_sql.parse(c,mysqlsel,dbms_sql.native);
  
  -- dbms_sql.define_column(c,position,column in <datatype>);
  -- dbms_sql.define_column(c,position,column in char/varchar2  character set any_cs);
  dbms_sql.define_column(c,1,c_name,20);--varchar2類型需要column_size
  dbms_sql.define_column(c,2,c_did);--number類型不需要column_size
  
  dbms_sql.bind_variable(c,':sar',sar);--mysqlsel中無參數可以省略
  
  --必須定義一個參數接受結果
  e_num:=dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c)>0 loop--獲取一行
  
  
  --將前面定義的字段返回給變量       
  dbms_sql.column_value(c,1,c_name);--dbms_sql.column_value(c IN INTEGER,position IN INTEGER,value OUT <datatype>);
  dbms_sql.column_value(c,2,c_did);
  
  dbms_output.put_line(c_name||' '||c_did);
       
  end loop; 
  
  dbms_sql.close_cursor(c);
end;
**/

/**
--使用 define_array 方法得到查询结果 
declare 
  mysqlsel varchar2(200);
  mysqlins varchar2(200);
  sar number(10):=2000;
  num integer;
  c_name dbms_sql.Number_Table;
  c_did dbms_sql.Number_Table;
  c integer;
  e_num integer;
  indx number:=1;
begin
 
  mysqlsel:='select e.did from emp1 e where e.sar>:sar';
  c:=dbms_sql.open_cursor;
  dbms_sql.parse(c,mysqlsel,dbms_sql.native);
  
 --DBMS_SQL.DEFINE_ARRAY (c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype>, cnt IN INTEGER, lower_bnd IN INTEGER);
  dbms_sql.define_array(c,1,c_did,9,indx);
  
  dbms_sql.bind_variable(c,':sar',sar);--mysqlsel中無參數可以省略
  
  --必須定義一個參數接受結果
  e_num:=dbms_sql.execute(c);

   e_num:=dbms_sql.fetch_rows(c);
   dbms_output.put_line('fetch rows is ' || e_num);
    
   if e_num<9 then
   dbms_sql.column_value(c,1, c_did);
 
   for i in 1..e_num loop
      dbms_output.put_line(c_did(i));    
   end loop; 
   end if;

  dbms_sql.close_cursor(c);
  
  exception
    when others then 
      dbms_output.put_line('errors1:'||sqlerrm);
      dbms_output.put_line('errors1:'||sqlcode||' and '||dbms_utility.format_error_stack);
      dbms_output.put_line('errors2:'||sqlcode||' and '||dbms_utility.format_call_stack);
      dbms_output.put_line('errors3:'||sqlcode||' and '||dbms_utility.format_error_backtrace);
end;
**/

/**
--使用 variable_value
declare 
  a emp1.name%type:='name10';   
  b emp1.did%type:=6;   
  r emp1.did%type;   

  num integer;
  c integer:=dbms_sql.open_cursor;

begin

  dbms_sql.parse(c
           ,'insert into emp1(name,did) values(:a,:b) return :b*:b into :r'
           ,dbms_sql.native);
  
  dbms_sql.bind_variable(c,':a',a);
  dbms_sql.bind_variable(c,':b',b);
  dbms_sql.bind_variable(c,':r',r);
  
  num:=dbms_sql.execute(c);
  --使用variable_value函数得到DML操作returning的结果集 
  dbms_sql.variable_value(c,'r',r);
  dbms_output.put_line(r);

  dbms_sql.close_cursor(c);
  
  exception
    when others then 
      dbms_output.put_line('errors1:'||sqlerrm);
      dbms_output.put_line('errors1:'||sqlcode||' and '||dbms_utility.format_error_stack);
      dbms_output.put_line('errors2:'||sqlcode||' and '||dbms_utility.format_call_stack);
      dbms_output.put_line('errors3:'||sqlcode||' and '||dbms_utility.format_error_backtrace);
end;
**/

--dbms_sql與原生sql的切換
--to_cursor_number();
--to_refcursor();
declare 
  ref_c sys_refcursor; 
  myname emp1.name%type;
  num integer;
  c integer:=dbms_sql.open_cursor;

begin

  dbms_sql.parse(c
           ,'select name from emp1'
           ,dbms_sql.native);
  
  num:=dbms_sql.execute(c);
 
  ref_c:=dbms_sql.to_refcursor(c);

  loop
    fetch ref_c into myname;
    if ref_c%found then
    dbms_output.put_line(myname);
    else
     exit; 
    end if;
  end loop;
  
  exception
    when others then 
      dbms_output.put_line('errors1:'||sqlerrm);
      dbms_output.put_line('errors1:'||sqlcode||' and '||dbms_utility.format_error_stack);
      dbms_output.put_line('errors2:'||sqlcode||' and '||dbms_utility.format_call_stack);
      dbms_output.put_line('errors3:'||sqlcode||' and '||dbms_utility.format_error_backtrace);
end;