hope this helps.
test code:
conn scott
create or replace procedure p_test
(p_empno innumber,
p_ename out varchar2,
p_sal out number)
as
begin
select ename,sal into p_ename,p_sal from emp where empno = p_empno;
exception
when no_data_found then
p_ename := 'No Body';
p_sal := 0;
end;
/
set serverout on
declare
l_empno number;
l_ename varchar2(20);
l_sal number;
begin
l_empno := 7876;
execute immediate 'call p_test(:l_empno,:l_ename,:l_sal)'
--execute immediate 'begin p_test(:l_empno,:l_ename,:l_sal); end;' -- this is also OK.
using l_empno,out l_ename,out l_sal;
--using l_empno,out l_ename,out l_sal;
dbms_output.put_line('Empno: ' || l_empno);
dbms_output.put_line('EName: ' || l_ename);
dbms_output.put_line('Sal: ' || l_sal);
end;
/
declare
l_empno number;
l_ename varchar2(20);
l_sal number;
l_cur integer;
l_ret integer;
begin
l_empno := 7876;
l_cur := dbms_sql.open_cursor;
dbms_sql.parse(l_cur,'call p_test(:l_empno,:l_ename,:l_sal)',dbms_sql.native);
dbms_sql.bind_variable(l_cur,'l_empno',l_empno);
dbms_sql.bind_variable(l_cur,'l_ename',l_empno,20); -- here 20 is out parameter's maxsize
dbms_sql.bind_variable(l_cur,'l_sal',l_sal);
l_ret := dbms_sql.execute(l_cur);
dbms_sql.variable_value(l_cur,'l_ename',l_ename);
dbms_sql.variable_value(l_cur,'l_sal',l_sal);
dbms_sql.close_cursor(l_cur);
dbms_output.put_line('Empno: ' || l_empno);
dbms_output.put_line('EName: ' || l_ename);
dbms_output.put_line('Sal: ' || l_sal);
end;
/
-- drop procedure p_test;
-- more detail about DBMS_SQL package
set pagesize 10000
spool c:\dbms_sql.txt
select text from all_source where name='DBMS_SQL' and type ='PACKAGE';
spool off
复制代码
|
