是用单引号。 CREATE OR REPLACE PROCEDURE kg_ti_interface (pckgda in varchar2,out_result IN OUT gaps_pk_wyhl.wyhl_lzpzpr_set) AS BEGIN execute immediate ' gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result)' using in pckgda ,out out_result; END kg_ti_interface;执行的时候报:invalid sql statement
SQL> CREATE OR REPLACE PROCEDURE a_test 2 AS 3 v_num number(10); 4 BEGIN 5 select count(*) into v_num from emp; 6 END; 7 /
Procedure created
SQL> SQL> CREATE OR REPLACE PROCEDURE b_test 2 AS 3 BEGIN 4 execute immediate 'a_test' ; 5 END; 6 /
Procedure created
SQL> exec b_test;
begin b_test; end;
ORA-00900: invalid SQL statement ORA-06512: at "SCOTT.B_TEST", line 4 ORA-06512: at line 2
SQL>
execute immediate 'begin gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result); end;' using in pckgda, out out_result; 試一下吧。
请问minitoy,那要怎样动态调用子过程?
execute immediate 'begin gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result); end;' using in pckgda, out out_result; 这个还是报错:OUT bind variable bound to an IN position
execute immediate 'begin gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result); end;' using in pckgda, in out out_result;
CREATE OR REPLACE PROCEDURE kg_ti_interface (pckgda in varchar2,out_result IN OUT gaps_pk_wyhl.wyhl_lzpzpr_set) AS BEGIN execute immediate 'begin gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result); end;' --关键在这里!过程中动态执行PRO要这样用吧 using in pckgda, out out_result; END kg_ti_interface;
SQL> CREATE OR REPLACE PROCEDURE a_test(o_rn out number) 2 AS 3 4 BEGIN 5 select count(*) into o_rn from emp; 6 END; 7 /
Procedure created
SQL> SQL> CREATE OR REPLACE PROCEDURE b_test 2 3 AS 4 v_num number(10); 5 BEGIN 6 execute immediate 'begin a_test(:v_num); end;' using out v_num; 7 dbms_output.put_line(v_num); 8 END; 9 /
Procedure created
SQL> set serveroutput on SQL> exec b_test;
14
PL/SQL procedure successfully completed
SQL>
这是execute的语法图 execute_immediate_statement ::= EXECUTE_IMMEDIATE dynamic_string { INTO { define_variable [, define_variable ...] | record_name } | BULK COLLECT INTO { collection_name [, collection_name ...] | :host_array_name } } [ USING [ IN | OUT | IN OUT ] bind_argument [, [ IN | OUT | IN OUT ] bind_argument] ... ] [ returning_clause ] ;dynamic_string有两种形式:1.sql语句 2.匿名块 begin .... end; 以前只知道怎么执行sql,今天学习了怎么执行匿名块.嘿嘿.
execute immediate 'gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result)'
用的是單引號吧.
CREATE OR REPLACE PROCEDURE kg_ti_interface (pckgda in varchar2,out_result IN OUT gaps_pk_wyhl.wyhl_lzpzpr_set)
AS
BEGIN
execute immediate ' gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result)'
using in pckgda ,out out_result;
END kg_ti_interface;执行的时候报:invalid sql statement
2 AS
3 v_num number(10);
4 BEGIN
5 select count(*) into v_num from emp;
6 END;
7 /
Procedure created
SQL>
SQL> CREATE OR REPLACE PROCEDURE b_test
2 AS
3 BEGIN
4 execute immediate 'a_test' ;
5 END;
6 /
Procedure created
SQL> exec b_test;
begin b_test; end;
ORA-00900: invalid SQL statement
ORA-06512: at "SCOTT.B_TEST", line 4
ORA-06512: at line 2
SQL>
using in pckgda, out out_result;
試一下吧。
using in pckgda, out out_result;
这个还是报错:OUT bind variable bound to an IN position
using in pckgda, in out out_result;
CREATE OR REPLACE PROCEDURE kg_ti_interface (pckgda in varchar2,out_result IN OUT gaps_pk_wyhl.wyhl_lzpzpr_set)
AS
BEGIN
execute immediate 'begin gaps32.gaps_rp_wyhl_lzpzpr(:pckgda,:out_result); end;' --关键在这里!过程中动态执行PRO要这样用吧
using in pckgda, out out_result;
END kg_ti_interface;
2 AS
3
4 BEGIN
5 select count(*) into o_rn from emp;
6 END;
7 /
Procedure created
SQL>
SQL> CREATE OR REPLACE PROCEDURE b_test
2
3 AS
4 v_num number(10);
5 BEGIN
6 execute immediate 'begin a_test(:v_num); end;' using out v_num;
7 dbms_output.put_line(v_num);
8 END;
9 /
Procedure created
SQL> set serveroutput on
SQL> exec b_test;
14
PL/SQL procedure successfully completed
SQL>
execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
{
INTO { define_variable [, define_variable ...] | record_name }
| BULK COLLECT INTO { collection_name [, collection_name ...] | :host_array_name }
}
[ USING [ IN | OUT | IN OUT ] bind_argument
[, [ IN | OUT | IN OUT ] bind_argument] ... ] [ returning_clause ] ;dynamic_string有两种形式:1.sql语句 2.匿名块 begin .... end;
以前只知道怎么执行sql,今天学习了怎么执行匿名块.嘿嘿.