Testing/Running the stored procedure(has OUT params) in Oracle sqldeveloper or sqlplus

sql>var v_result number

sql>exec sp_isauthorized('a', 'b', :v_result)

PL/SQL procedure successfully completed.

sql>print v_result  -- or turn autoprint on

===================================================

To test the stored procedure, we can either execute it from within an environment that allows you to execute stored procedures and supply their parameters, such as SQL Navigator or Toad, or we can run it from within sqlplus.

Executing a stored procedure from within sqlplus is straightforward once you know how.

Firstly, start up sqlplus and declare a sqlplus variable of type refcursor. This declares a sqlplus variable of type refcursor (a sqlplus type), called "results":

SQL> var results refcursor

Next, execute the procedure, supplying a number as a parameter, and assigning the result into our variable, "results". Note the unusual syntax.

SQL> exec :results := sp_get_stocks(20.0)

PL/SQL procedure successfully completed.

Finally, use the sqlplus print statement to print out the result set

SQL> print results

RIC PRICE UPDATED

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

AMZN 15.5 21-OCT-01

SUNW 16.25 21-OCT-01

ORCL 14.5 21-OCT-01

If this works successfully, similar to the above, the stored procedure (well, function) is working correctly.

 

 

Java client 调用：

if(cs==null)
cs=m_Conn.prepareCall("{call p(?,?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.setString(2,column);
cs.execute();
rs=(ResultSet)cs.getObject(1);
while(rs.next()){
    System.out.println(column+"="+rs.getString(1));
}
rs.close();
rs=null;