oracle 创建存储,ORACLE存储过程的创建

1.无返回值

CREATE OR REPLACE PROCEDURE TESTA(paramA IN VARCHAR2,paramB in VARCHAR2)AS

BEGIN

INSERT INTO TNAME (T_id,T_name)values(paramA,paramB);

END TESTA;

java调用代码：

CallableStatement proc = null;

proc = conn.prepareCall("{ call TESTA(?,?) }");

proc.setString(1, "100");

proc.setString(2, "TestOne");

proc.execute();

2.有返回值

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS

BEGIN

SELECT INTO PARA2 FROM TNAME WHERE I_ID= PARA1;

END TESTB；

java调用代码：

CallableStatement proc = null;

proc = conn.prepareCall("{ call TESTB(?,?) }");

proc.setString(1, "100");

proc.registerOutParameter(2, Types.VARCHAR);

proc.execute();

String testPrint = proc.getString(2);

3.返回列表

--建一个程序包

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

--建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) AS

BEGIN

OPEN p_CURSOR FOR SELECT * FROM TNAME;

END TESTC;

java调用代码：

CallableStatement proc = null;

proc = conn.prepareCall("{ call testc(?) }");

proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

proc.execute();

rs = (ResultSet)proc.getObject(1);