试试 begin execute immediate 'select * from '|| v1; end;
execute immediate 'select re,svt_no,saving_num into '||v1||','|| v2||','||v3||' from '|| in_d ||' where acc='||in_a; 这样写队吗? v1,v2,v3是我在汉数里定义的,in_a是函数输入参数。 函数编译时没问题,可是测试时却不对
sample: DECLARE sql_stmt VARCHAR2(200); plsql_block VARCHAR2(500); emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 7788, 500; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; END;
to Petergepeter: 谢谢,我知道动态sql的大致情况了。 可我上面的程序应该也没问题阿? 编译通过,select ... from dual测试时却出错。 请指点。
CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create function f1(v1 char) return pkg_test.myrctype as p_rc pkg_test.myrctype; begin open p_rc for select * from v1; end; /
因为你在在动态SQL中用了SELECT ...INTO ...。 本身动态SQL是比较独立,所以里面的INTO后的变量,不是外面的变量。 应该 STRSQL := 'select re,svt_no,saving_num from '|| in_d ||' where acc='||in_a; execute immediate STRSQL INTO V1,V2,V3;
begin
execute immediate 'select * from '|| v1;
end;
这样写队吗?
v1,v2,v3是我在汉数里定义的,in_a是函数输入参数。
函数编译时没问题,可是测试时却不对
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONNEL';
location VARCHAR2(13) := 'DALLAS';
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
谢谢,我知道动态sql的大致情况了。
可我上面的程序应该也没问题阿?
编译通过,select ... from dual测试时却出错。
请指点。
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create function f1(v1 char)
return pkg_test.myrctype
as
p_rc pkg_test.myrctype;
begin
open p_rc for select * from v1;
end;
/
本身动态SQL是比较独立,所以里面的INTO后的变量,不是外面的变量。
应该
STRSQL := 'select re,svt_no,saving_num from '|| in_d ||' where acc='||in_a;
execute immediate STRSQL INTO V1,V2,V3;