九、PL/SQL之本地动态SQL
文章目录
- 前言
- 一、EXECUTE IMMEDIATE
- 1.1 EXECUTE IMMEDIATE语句
- 二、OPEN-FOR、FETCH和CLOSE语句
- 2.1 打开游标
- 2.2 游标检索
- 2.3 关闭游标
前言
程序运行时才能确定的SQL语句如SQL语句所使用的表和数据列也不是预先知道的或者用户所需的数据排序和分组方式，以构建各种报告…,由于SQL语句是动态产生的，所以被称为动态的。
一、EXECUTE IMMEDIATE
动态SQL语句：
‘SELECT first_name, last_name FROM stu WHERE stuid = :stu_id’
无法预先知道需要查询学生ID的值，需要使用绑定参数 :stu_id来实现指定。绑定参数是未声明标识符的占位符，名称的前缀是冒号。PL/SQL不会区分如下两个语句
‘SELECT first_name, last_name FROM stu WHERE stuid = :stu_id’
‘SELECT first_name, last_name FROM stu WHERE stuid = :id’
为处理动态SQL语句，需要使用EXECUTE IMMEDIATE，或者OPEN-FOR、FETCH和CLOSE语句。EXECUTE IMMEDIATE被用于单行SELECT语句、所有的DML语句和DDL语句。OPEN-FOR、FETCH和CLOSE语句用于多行SELECT语句以及引用游标。
1.1 EXECUTE IMMEDIATE语句
语法如下：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO defined_variable1, defined_variable2, …]
[USING [in|out|in out] bind_argument1, bind_argument2,…]
[{RETURNING|RETURN} field1, field2, … INTO bind_argument1, bind_argument2, …]
DECLAREsql_stmt VARCHAR2(1000);plsql_block VARCHAR2(1000);v_ooag011 ooag_t.ooag011%TYPE;v_total_ooag NUMBER;v_new_ooag011 ooag_t.ooag011%TYPE;v_ooag001 ooag_t.ooag001%TYPE := '00261' ;
BEGIN--用法一：sql_stmt := 'CREATE TABLE ooag_t_bak' ||' AS SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = 70 ' ||' AND ooag001 = ' || v_ooag001;EXECUTE IMMEDIATE sql_stmt;--用法二:EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ooag_t_bak'INTO v_total_ooag;DBMS_OUTPUT.PUT_LINE(v_total_ooag);--用法三:plsql_block := 'DECLARE ' ||' v_date DATE; ' ||'BEGIN' ||' SELECT SYSDATE INTO v_date FROM DUAL; ' ||' DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date,''YYYY-MM-DD''));' ||'END;';EXECUTE IMMEDIATE plsql_block;-- 下面这句里的:1表一需要一个绑定变量-- 字符串中需要'就用双引号如下面的赵老四sql_stmt := ' UPDATE ooag_t_bak SET ooag011 = ''赵老四'' WHERE ooag001 = :1 ' ||-- :2表示需要别一个变量来接收' RETURNING ooag011 INTO :2';EXECUTE IMMEDIATE sql_stmt USING v_ooag001 RETURNING INTO v_new_ooag011;DBMS_OUTPUT.PUT_LINE('NEW ooag011 IS:'||v_new_ooag011);
END;
二、OPEN-FOR、FETCH和CLOSE语句
OPEN_FOR、FETCH和CLOSE语句被用于处理多行查询或者游标。
2.1 打开游标
对于动态SQL，OPEN-FOR语句有个可选的USING子句，使用它可以在运行时向绑定参数传值。
语法：
OPEN cursor_variable FOR dynamic_sql_string
[USING bind_argument1, bind_argument2, …]
cursor_variable是一种弱类型的REF CURSOR变量，并且dynamic_SQL_string是包含多行查询的字符串
DECLARETYPE ooag_cur_type IS REF CURSOR;-- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联ooag_cur ooag_cur_type;v_ooagent ooag_t.ooagent%TYPE := &ooagent;v_ooag001 ooag_t.ooag001%TYPE;v_ooag011 ooag_t.ooag011%TYPE;
BEGIN-- 打开游标OPEN ooag_cur FOR ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 'USING v_ooagent;......
END
2.2 游标检索
DECLARETYPE ooag_cur_type IS REF CURSOR;-- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联ooag_cur ooag_cur_type;v_ooagent ooag_t.ooagent%TYPE := &ooagent;v_ooag001 ooag_t.ooag001%TYPE;v_ooag011 ooag_t.ooag011%TYPE;
BEGINOPEN ooag_cur FOR ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 'USING v_ooagent;--游标检索LOOP -- INTO子句中的变量数量必须对应到游标所返回的列数，INTO子句中变量与游标列必须是类型兼容的。FETCH ooag_cur INTO v_ooag001,v_ooag011;EXIT WHERE ooag_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE('v_ooag001='||v_ooag001||',v_ooag011='||v_ooag011);END LOOP;
END
2.3 关闭游标
DECLARETYPE ooag_cur_type IS REF CURSOR;-- 定义游标变量，在运行时,ooag_cur变量与SELECT语句建立关联ooag_cur ooag_cur_type;v_ooagent ooag_t.ooagent%TYPE := &ooagent;v_ooag001 ooag_t.ooag001%TYPE;v_ooag011 ooag_t.ooag011%TYPE;
BEGINOPEN ooag_cur FOR ' SELECT ooag001,ooag011 FROM ooag_t WHERE ooagent = :1 'USING v_ooagent;-- 游标检索LOOP -- INTO子句中的变量数量必须对应到游标所返回的列数，INTO子句中变量与游标列必须是类型兼容的。FETCH ooag_cur INTO v_ooag001,v_ooag011;EXIT WHEN ooag_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE('v_ooag001='||v_ooag001||',v_ooag011='||v_ooag011);END LOOP;-- 关闭游标CLOSE ooag_cur;
EXCEPTIONWHEN OTHERS THENIF ooag_cur%ISOPEN THENCLOSE ooag_cur;END IF;
END;
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！