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;
解决方案 »
- oracle中用触发器如何获得一个表某字段修改前后的值(回答的马上散分)
- 求助:occi运行出现的getString()问题和getInt()问题
- Oracle 数据导出问题
- 存储过程中select语句问题
- oracle 整列返回空 实际有数据
- 求一个物管程序的设计方法,大家提一些具体的实现方法。
- 我的sql/plus 为什么不能执行 select * from xxx???????
- 这样建数据库连接为什么会出错?
- oracle10g升级以后 503 Service Unavailable
- 關於ORACLE 語句轉換成sql server
- 大侠们,看看还有救吗!
- 谁帮忙解决一下rollback断点设置的问题?
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;