执行Stroredprocedure时出错 [英] Getting error while executing Stroredprocedure

问题描述

CREATE OR REPLACE
PROCEDURE javao
  (
    numberw IN number,
    emp_name OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN emp_name FOR SELECT * FROM employees WHERE emp_no = numberw;
END;
  EXEC javao(1)

表结构

Name                         Type                                  
-------------                -------- 
EMP_NO                       NUMBER                      
EMP_NAME                    VARCHAR2(30 BYTE)    
ADDRESS                      VARCHAR2(15 BYTE)     
PH_NO                         NUMBER(10)                  

在执行上述存储过程时遇到错误.该错误是

Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'JAVAO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

请尝试帮助解决我的代码中的错误.

解决方案

正如Nicholas Karasn​​ov所说，您需要在调用中使用参数以匹配过程声明.在这种情况下，您有一个OUT参数，因此您需要在某个地方存储要选择的数据-与sys_refcursor参数类型相对应.

如果您使用SQL * Plus进行测试，则可以声明

...但是以伪代码为界，您需要阅读文档以填补空白(并纠正所有错误；例如，您可能需要特定于Oracle的语句类型)，并添加对象关闭和错误处理所有其他好东西.

CREATE OR REPLACE
PROCEDURE javao
  (
    numberw IN number,
    emp_name OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN emp_name FOR SELECT * FROM employees WHERE emp_no = numberw;
END;
  EXEC javao(1)

Table structure

Name                         Type                                  
-------------                -------- 
EMP_NO                       NUMBER                      
EMP_NAME                    VARCHAR2(30 BYTE)    
ADDRESS                      VARCHAR2(15 BYTE)     
PH_NO                         NUMBER(10)                  

While executing above stored procedure am getting error.That error is

Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'JAVAO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

please try to help what is the wrong in my code.

解决方案

As Nicholas Karasnov says, you need the arguments in the call to match the procedure declaration. In this case you have an OUT parameter, so you need somewhere for the data you're selecting to go - something corresponding to the sys_refcursor parameter type.

If you're using SQL*Plus to test this you can declare a SQL*Plus variable for this and then pass that as a bind variable, and then use the print command to display the cursor contents:

variable rc refcursor
exec javao(1, :rc);
print rc

This works in SQL Developer too. When you call the procedure from other code, you'll have a variable declared in a PL/SQL block, or in an external application (e.g. via jdbc), but the details of those depend on what you're doing.

To call from Java, you'd do something like:

// get connection
CallableStatement cStmt = conn.prepareCall("{ call javao(?, ?) }");
cStmt.setInt(1, 1);
cStmt.registerOutParameter(2, OracleTypes.cursor);
cStmt.execute();
ResultSet rSet = ((OracleCallableStatement) cStmt).getCursor(2);
while (rSet.next())
{
    // get columns from result set row and do something with the data
}

... but that's bordering in pseudocode, and you'll need to read the documentation to fill in the blanks (and correct any errors; you probably need Oracle-specific statement types for example), and add object closing and error handling all all that other good stuff.

这篇关于执行Stroredprocedure时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！