DECLARE
sql_stmt VARCHAR2(100);
plsql_block VARCHAR2(200);
my_deptno NUMBER(2) := 50;
my_dname VARCHAR2(15) := 'PERSONNEL';
my_loc VARCHAR2(15) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc; sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788; EXECUTE IMMEDIATE 'DELETE FROM dept
WHERE deptno = :n' USING my_deptno; plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE sql_stmt;
END;
sql_stmt VARCHAR2(100);
plsql_block VARCHAR2(200);
my_deptno NUMBER(2) := 50;
my_dname VARCHAR2(15) := 'PERSONNEL';
my_loc VARCHAR2(15) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc; sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788; EXECUTE IMMEDIATE 'DELETE FROM dept
WHERE deptno = :n' USING my_deptno; plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE sql_stmt;
END;
The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, identifies the result set, positions the cursor on the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT. Unlike the static form of OPEN-FOR, the dynamic form has an optional USING clause. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement. The syntax is OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
[USING bind_argument[, bind_argument]...];where cursor_variable is a weakly typed cursor variable (one without a return type), host_cursor_variable is a cursor variable declared in a PL/SQL host environment such as an OCI program, and dynamic_string is a string expression that represents a multi-row query. In the following example, you declare a cursor variable, then associate it with a dynamic SELECT statement that returns rows from the emp table: DECLARE
TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
emp_cv EmpCurTyp; -- declare cursor variable
my_ename VARCHAR2(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR -- open cursor variable
'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
...
END;Any bind arguments in the query are evaluated only when the cursor variable is opened. So, to fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.
使用EXECUTE IMMEDIATE 万事大吉。
TYPE FLIGHTRCTYPE IS REF CURSOR ;--RETURN FLIGHTRESULT;
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2) RETURN FLIGHTRCTYPE;
END FLIGHTRESULT;
CREATE OR REPLACE PACKAGE BODY FLIGHTRESULT AS
FUNCTION SELECTCOMPARE (TNAME IN VARCHAR2)
--TNAME:IN OR OUT
RETURN FLIGHTRCTYPE IS
RC FLIGHTRCTYPE; --返回结果集游标
EXCSQLSTR1 VARCHAR2(3000):='';
BEGIN
EXCSQLSTR1:='SELECT * FROM '||TNAME;
OPEN RC FOR EXCSQLSTR1;
RETURN RC;
EXCEPTION --异常处理
WHEN NO_DATA_FOUND THEN
OPEN RC FOR SELECT -2 SEATS FROM DUAL;
RETURN RC; --数据没有找到
WHEN OTHERS THEN
OPEN RC FOR SELECT -3 SEATS FROM DUAL;
RETURN RC; --运行时发生意外错误
END SELECTCOMPARE;
END FLIGHTRESULT;