shell&procedure variable in/out

时间：2008-07-03 来源：aqcjsy1

将shell变量传递到oracle procedure并将procedure结果输出到shell变量。
$ sqlplus scott/tiger SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 7月 3 15:07:21 2008 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR:
ORA-28000: ??????
请输入用户名: system
请输入口令: 连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options SQL> create or replace procedure test_sh_in(var_in varchar2)
2 as
3 begin
4 dbms_output.put_line(var_in);
5 end;
6 / 过程已创建。 SQL> exit;
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开 806803@CNHZPD-6VRWY1X ~/new_folder
$ str="hello"
806803@CNHZPD-6VRWY1X ~/new_folder
$ result=`sqlplus -s system/oracle << eof
set serveroutput on
exec test_sh_in('$str');
exit;
eof` 806803@CNHZPD-6VRWY1X ~/new_folder
$ echo $result
hello PL/SQL 过程已成功完成。 806803@CNHZPD-6VRWY1X ~/new_folder
$ result=`sqlplus -s system/oracle << eof
set serveroutput on
set feedback off # remove the feedback "PL/SQL 过程已成功完成。"
> exec test_sh_in('$str');
> exit;
> eof` 806803@CNHZPD-6VRWY1X ~/new_folder
$ echo $result
hello 806803@CNHZPD-6VRWY1X ~/new_folder
$ ###########################################################
#about out parameter to shell
SQL> ed
已写入 file afiedt.buf 1 create or replace procedure test_out_var(var_in varchar2,var_out out varcha
r2)
2 as
3 begin
4 var_out:=var_in;
5* end;
SQL> /
SQL> declare
2 var_out varchar2(100);
3 begin
4 test_out_var('china',var_out);
5 end;
6 /
SQL> set serveroutput on
SQL> /
SQL> ed
已写入 file afiedt.buf 1 declare
2 var_out varchar2(100);
3 begin
4 test_out_var('china',var_out);
5 dbms_output.put_line(var_out);
6* end;
SQL> /
china 806803@CNHZPD-6VRWY1X ~/new_folder
$ str="china" 806803@CNHZPD-6VRWY1X ~/new_folder
$ result=`sqlplus -s system/oracle@orcl << eof
set serveroutput on
set feedback off
declare
var_out varchar2(100);
begin
test_out_var('$str',var_out);
dbms_output.put_line(var_out);
end;
/
exit;
eof` 806803@CNHZPD-6VRWY1X ~/new_folder
$ echo $result
china

相关阅读更多 +