oracle存储过程中简单地调用另一个有返回值的存储过程

先建一张测试表：

create table WWT_TABLE_TEST(? NAME???? VARCHAR2(2000),? AGE????? NUMBER,? SEX????? VARCHAR2(20),? BIRTHDAY DATE)

?创建被调用的存储过程：

create or replace procedure wwt_test2(inchar in varchar2,outchar out varchar2) isbeginoutchar:=inchar||'hello procedure';end;

?

创建调用被调用存储过程的存储过程：

create or replace procedure wwt_test1  ismyString varchar(1000);tempString varchar(1000);sqlText varchar(1000);record_number number;logId NUMBER;--记录log_pro的idbegin--select cooper.SEQ_LOG_PRO.NEXTVAL into logId from dual;begintempString:='already';wwt_test2('wwt',tempString);myString:=tempString;sqlText:='insert into wwt_table_test  values('''||myString||''')';dbms_output.put_line('==>'||sqlText);execute immediate 'insert into wwt_table_test(name)  values('''||myString||''')';execute immediate 'update wwt_table_test set name = name||''hello''';commit;end;--以下为其它测试之用，可忽略select count(*) into record_number from tbl_importdata_log t where t.STATE=1 and t.begintime like to_char(sysdate-2,'yyyy-mm-dd')||'%';Dbms_Output.put_line('==>'||record_number);if record_number<67 thenupdate wwt_table_test set name ='更新失败',age=23;end if;if record_number = 67 thenupdate wwt_table_test set name ='更新成功',age=24,birthday=sysdate;end if;commit;end;

???

创建完后，在命令窗口中：call wwt_test1();执行完毕后，在表wwt_table_test中就会有一条记录了。

也可以在java类中调用，如：

package procudure;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class ProcedureTest {public static void main(String[] args) {try {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:JULY", "scott","snaillocke");CallableStatement cs = conn.prepareCall("{call wwt_test1()}");/* * 如果有参数可以在此设置 *///cs.setString(1, "SCOTT");//cs.setDouble(2, 666.66);//如果有是回值：call.registerOutParameter(2, java.sql.Types.VARCHAR);call.execute(); ????????//String testPrint = call.getString(2);cs.execute();cs.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}}

?

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html