在pl/sql中,我们的表名称或者参数是不固定的,经常用到动态sql;
一,几种常用的动态sql的用法
1,Execute Immediate ‘执行的sql’;
2,dbms_sql 包
Declare
curid Number;
l_sql Varchar2(1000);
l_cnt Number(5);
l_ret Number(5);
lv_deptno Number(20);
Type curtype Is Ref Cursor;
l_curtype curtype;
Type type_dname Is Table Of dept.Dname%Type Index By Binary_Integer;
l_type_dname type_dname;
Begin
lv_deptno:=20;
--打开cursor
curid :=Dbms_Sql.open_cursor;
--sql语句
l_sql := 'select dname from dept where Deptno>=:l_deptno';
--sql解析
dbms_sql.parse(curid, l_sql, dbms_sql.native);
--绑定变量
dbms_sql.bind_variable(curid,':l_deptno',lv_deptno);
--执行sql
l_ret := dbms_sql.execute(curid);
--转化为ref cursor
l_curtype := dbms_sql.to_refcursor(curid);
Fetch l_curtype Bulk Collect Into l_type_dname;
For i In 1..l_type_dname.count Loop
dbms_output.put_line(l_type_dname(i));
End Loop;
Close l_curtype;
End;
二,游标的几种用法
1,显示游标
declare
cursor c_xx is select xx from table_name;
begin
--打开游标
open c_xx;
--取数据
fetch c_xx into xx
--close 游标
close c_xx;
end;
2, ref cursor的用法
Declare
l_sql Varchar(1000);
Type cur_typ Is Ref Cursor;
c cur_typ;
Begin
For i In (Select t.partition_name
From dba_segments t
Where t.segment_name = 'TMP'
And t.owner = 'SCOTT') Loop
l_sql := 'select a from tmp Partition(' || i.partition_name || ')';
Dbms_output.put_line(l_sql);
Open c For l_sql;
End Loop;
End;