oracle_plsql_out_parametes

　　前天，调用plsql过程出错，今天测试了一下oracle plsql 的out 参数：

测试用例：

　　CREATE OR REPLACE PROCEDURE zp_for_test(in_month NUMBER,v_err OUT VARCHAR2) IS
　　BEGIN

　　----
　　END  zp_for_test;

测试结果：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as hbkf_crm
 
SQL> exec zp_for_test(201203,'');
 
begin zp_for_test(201203,''); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression '' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,:v_out);
 
begin zp_for_test(201203,:v_out); end;
 
ORA-01008: not all variables bound
 
SQL> exec zp_for_test(201203);
 
begin zp_for_test(201203); end;
 
ORA-06550: line 2, column 7:
PLS-00306: wrong number or types of arguments in call to 'ZP_FOR_TEST'
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,'0');
 
begin zp_for_test(201203,'0'); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,'11');
 
begin zp_for_test(201203,'11'); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression '11' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,'abc');
 
begin zp_for_test(201203,'abc'); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression 'abc' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL>  exec zp_for_test(201203,'abc');
 
begin zp_for_test(201203,'abc'); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression 'abc' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,null);
 
begin zp_for_test(201203,null); end;
 
ORA-06550: line 2, column 26:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL> exec zp_for_test(201203,);
 
begin zp_for_test(201203,); end;
 
ORA-06550: line 2, column 26:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <一个带有字符集说明的可带引号的字符串文字>
   <一个可带引号的 SQL 字符串>
The symbol "null" was
 
SQL> exec zp_for_test(201203);
 
begin zp_for_test(201203); end;
 
ORA-06550: line 2, column 7:
PLS-00306: wrong number or types of arguments in call to 'ZP_FOR_TEST'
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
 
SQL>call
SQL> zp_for_test(in_month => :in_month,
  2                v_err => :v_err);
 
zp_for_test(in_month => :in_month,
              v_err => :v_err)
 
ORA-00900: invalid SQL statement

-------------------------------------------

最后还是没有找到解决的方法，上网google了半天，解决方法都是，将其做为子程序调用。

eg.

You can call your procedure like this

declare
p1 varchar2(20);
p2 varchar2(3);
begin
zp_for_test(p1,p2);
end;

转载于:https://www.cnblogs.com/Alex-Zeng/archive/2012/04/23/2466160.html