Java调用Oracle存储过程和函数

1、Java调用Oracle存储过程

存储过程：

create or replace PROCEDURE CREATE_TABLE
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE tb_user( 
id  NUMBER PRIMARY KEY, 
name  VARCHAR2(20)
)';
END CREATE_TABLE;

 

String url="jdbc:oracle:thin:@localhost:1521:orcl";

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn=DriverManager.getConnection(url, user, password);

CallableStatement cs=conn.prepareCall("{CALL user.CREATE_TABLE}");

cs.execute();

 

2、Java调用Oracle函数

函数：

create or replace FUNCTION F_CREATE_TABLE
    RETURN NUMBER
  AS
  BEGIN
    EXECUTE IMMEDIATE 'create table tb_test(id number,name varchar2(20))';
    RETURN 0;
  END F_CREATE_TABLE;

 

cs.prepareCall("?=call user.F_CREATE_TABLE");//此处call必需小写

cs.registerOutParameter(1, Types.NUMERIC); 

cs.execute();

int result=cs.getInt(1);//获取函数返回结果