Oracle动态游标PLSQL动态SQL语句openfor[using]语句PL/SQL:open for [using] 语句
2017年07⽉19⽇ 09:52:55  阅读数：681 标签：  更多
个⼈分类：
※ OPEN FOR [USING] 语句※
⽬的:
和ref cursor配合使⽤, 可以将游标变量分配给不同的SQL (⽽不是在declare中把游标给定死), 增加处理游标的灵活性
语法：
declare
type type_cursor  is ref cursor [return 记录类型];  --使⽤ ref cursor 才能把游标分配给不同的SQL,return不能⽤在动态SQL中    v_cursor type_cursor ;
begin
OPEN v_cursor FOR select first_name, last_name from student;
OPEN v_cursor FOR ' select first_name,last_name  from student where zip = :1 '
USING 绑定变量1;
open静态SQL cursor cursor c1 is <；静态SQL⽂本>
open c1; fetch ... into ... ; close c1;
open for    静态SQL
ref cursor type t_1 is ref cursor;
c2  t_1 ;
open c2 for <；静态SQL语句>;
open for using动态SQL type t_1 is ref cursor;
c2  t_1 ;
open c2 for <；动态SQL语句> using ... ;
例⼦1:
declare
type student_cur_type is ref CURSOR RETURN test_stu%ROWTYPE;  --声明ref cursor类型, return类型固定    v_first_name test_stu.first_name%TYPE;
v_last_name test_stu.last_name%TYPE;
cur_stud student_cur_type;
begin
open cur_stud for select first_name,last_name from student ;  --带return的ref cursor只能⽤在静态sql中 loop
fetch cur_stud into v_first_name, v_last_name;
exit when cur_stud%NOTFOUND;
dbms_output.put_line(v_first_name || ' ' || v_last_name);
end loop;
close cur_stud;
end;
例⼦2:
declare
v_zip        varchar2(5) := '&sv_zip';
v_first_name varchar2(25);
v_last_name  varchar2(25);
type student_cur_type is ref cursor;  --声明ref cursor类型
student_cur student_cur_type;  --student_cur是游标变量 / student_cur_type 是引⽤游标类型
begin
--2打开游标变量，让它指向⼀个动态select查询的结果集 ; 就是使⽤open for语句代替⼀般的open语句代开游标  open student_cur for 'select first_name,last_name ' from student where zip = :1'
using v_zip;
loop
fetch student_cur into v_first_name, v_last_name;
exit when student_cur%NOTFOUND;
dbms_output.put_line(v_first_name || ' ' || v_last_name);
end loop;
close student_cur;
end;
动态SQL中使⽤Open for语句
2017年09⽉06⽇ 19:35:55  阅读数：3199 标签：  更多
个⼈分类：
Open for本是为了⽀持游标变量，现在⽤它实现多⾏动态查询。OPEN FOR的语法如下：
OPEN{cursor_variable | :host_cursor_viable}FOR SQL_string
[USING bind_argument [, bind_argument]…];
解释：
Cursor_variable是⼀种弱类型的游标变量。
:host_cursor_variable是在PL/SQL宿主环境下声明的游标变量，如Oracle调⽤接⼝程序。
SQL_string包含动态执⾏的SELECT语句。
USING⼦句与EXECUTE IMMEDIATE语句遵循相同的规则。
使⽤OPEN FOR语句打开动态查询的例⼦：
PROCEDURE show_parts_inventory(
parts_table IN VARCHAR2, where_in IN VARCHAR2)
IS
TYPE query_curtype IS REF CURSOR;
dyncur query_curtype;
BEGIN
OPEN dyncur FOR
'SELECT * FROM' || parts_table || 'WHERE' || where_in;
。。。
执⾏OPEN FOR语句时，PL/SQL运⾏引擎操作如下：
1、  将游标变量与在查询字符串到的查询相关联。
2、  对绑定参数求值，并⽤这些值替换查询字符串内的占位符。
3、  执⾏查询。
4、  识别结果集。
5、  将游标放在结果集第⼀⾏。
6、  把由%ROWCOUNT返回的⾏计数值归零。
注意，查询中任何绑定参数（由USING⼦句提供），仅当游标变量打开时才能求值。这意味着如果我们想对相同的动态查询使⽤不同的绑定参数值，
就必须使⽤该参数再执⾏⼀次OPEN FOR语句。
执⾏多⾏查询需遵循以下步骤：
1、  声明⼀个REFCURSOR类型（或使⽤Oracle定义的SYS_REFCURSOR弱CURSOR类型）。
2、  基于这个REF CURSOR类型声明⼀个游标变量。
3、  ⽤这个游标变量打开查询字符串。
4、  使⽤FETCH语句提供查询确认的⼀⾏或多⾏结果集。
5、  必要时检查游标属性（%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN）。
6、  使⽤标准CLOSE语句关闭游标变量。
/*显⽰任何⼀个表中由WHERE⼦句所选出的⾏的指定列的内容（对数字、⽇期和字符串列有效）*/ /*参数说明：tab：表名、col：列名、whr：条件*/
PROCEDURE showcol(tab IN VARCHAR2,
col IN Varchar2,
whr IN VARCHAR2:=NULL)
IS
cv SYS_REFCURSOR;
val VARCHAR2(32767);
BEGIN
OPEN cv FOR
--注意字符串之间的空格
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || NVL(whr, '1 = 1');
LOOP
--取cv的值给val，如果不到就退出，和显⽰游标相同
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
--如果取到第⼀⾏，显⽰头部的信息
IFcv%ROWCOUNT = 1
THEN
Dbms_Output.put_line(RPAD('_',60,'_'));
Dbms_Output.put_line(
'Contents of ' || UPPER(tab)|| '.' || UPPER(col));
Dbms_Output.put_line(RPAD('_',60,'_'));
END IF;
Dbms_Output.put_line(val);
END LOOP;
--记得关闭游标
CLOSE cv;
END;
sql语句查询不包含/
*升级版showcol程序，显⽰带有⼀个时间列，并且时间列在⼀定范围数值内的所有列的信息*/ PROCEDURE showcol(tab VARCHAR2,
col VARCHAR2,
dtcol VARCHAR2,
dt1 DATE,
dt2 DATE := NULL)
IS
cvSYS_REFCURSOR;
val VARCHAR2(32767);
BEGIN
OPEN cv FOR
--注意空格
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || dtcol ||
' BETWEEN TRUNC (:startdt) AND TRUNC (:enddt)'
USING dt1, NVL (dt2, dt1+1);
LOOP
--取cv的值给val，如果不到就退出
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
--如果取到第⼀⾏，显⽰信息
IF cv%ROWCOUNT = 1
THEN
Dbms_Output.put_line(
'Contents of ' || UPPER(tab)|| '.' || UPPER(col) ||
' for ' || UPPER(dtcol) ||
' between ' || dt1 || ' AND' || NVL (dt2, dt1+1));
END IF;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。