oracle存储过程中容易地调用另一个有返回值的存储过程
oracle存储过程中简单地调用另一个有返回值的存储过程
我的异常网推荐解决方案:oracle存储过程,http://www..net/oracle-develop/177537.html
先建一张测试表:
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) is begin outchar:=inchar||'hello procedure'; end;
创建调用被调用存储过程的存储过程:
create or replace procedure wwt_test1 is myString varchar(1000); tempString varchar(1000); sqlText varchar(1000); record_number number; logId NUMBER;--记录log_pro的id begin --select cooper.SEQ_LOG_PRO.NEXTVAL into logId from dual; begin tempString:='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 then update wwt_table_test set name ='更新失败',age=23; end if; if record_number = 67 then update 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..net/oracle-develop/177537.html