用动态sql;SQL> CREATE OR REPLACE PROCEDURE P AS
2 v_String1 VARCHAR2(200);
3 BEGIN
4 v_String1:='create view v_1(a,b) as select a,b from tmp';
5 execute immediate v_String1;
6 end P;
7 /过程已创建。
SQL> exec p;PL/SQL 过程已成功完成
SQL> select * from cat where table_type='VIEW';TABLE_NAME TABLE_TYPE
------------------------------ -----------
V_1 VIEW
2 v_String1 VARCHAR2(200);
3 BEGIN
4 v_String1:='create view v_1(a,b) as select a,b from tmp';
5 execute immediate v_String1;
6 end P;
7 /过程已创建。
SQL> exec p;PL/SQL 过程已成功完成
SQL> select * from cat where table_type='VIEW';TABLE_NAME TABLE_TYPE
------------------------------ -----------
V_1 VIEW
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,取出
你所需的数据。