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與原生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;