plsql与java_Oracle之PLSQL与Java应用

增加--只有IN没有OUT

存储过程语句：

--插入语句，没有输出参数，只有输入参数

CREATE OR REPLACE PROCEDURE ADD_DEPT (

P_DEPTNO IN NUMBER ,

P_DNAME IN VARCHAR2 ,

P_DEPTLOC IN VARCHAR2

)

AS BEGIN

INSERT INTO DEPT VALUES(P_DEPTNO,P_DNAME,P_DEPTLOC) ;

COMMIT ;

END ;

/

在Java中调用该存储过程：

Class.forName(DBDRIVER) ;

conn = DriverManager.getConnection(URL, USERNAME, PASSWORD) ;

callstmt = conn.prepareCall("{call ADD_DEPT(?,?,?)}") ;

callstmt.setInt(1, 87) ;

callstmt.setString(2, "HR") ;

callstmt.setString(3, "MIAMI") ;

callstmt.executeUpdate();

conn.commit();

删除-只有IN没有OUT

存储过程语句：

--根据ID号删除部门信息

CREATE OR REPLACE PROCEDURE DELETE_DEPT_BY_ID

(

P_DEPTNO IN NUMBER

)

AS BEGIN

DELETE FROM DEPT WHERE DEPTNO=P_DEPTNO ;

END ;

/

在Java中调用该存储过程：

Class.forName(DBDRIVER) ;

conn = DriverManager.getConnection(URL, USERNAME, PASSWORD) ;

callstmt = conn.prepareCall("{call DELETE_DEPT_BY_ID(?)}") ;

callstmt.setInt(1, 87) ;

callstmt.executeUpdate();

conn.commit();

修改-只有IN没有OUT

存储过程语句：

--根据部门号更新部门信息

CREATE OR REPLACE PROCEDURE UPDATE_DEPT_BY_ID

(

P_DEPTNO IN NUMBER ,

P_DNAME IN VARCHAR2 ,

P_LOC IN VARCHAR2

)

AS BEGIN

UPDATE DEPT SET DNAME=P_DNAME,LOC=P_LOC WHERE DEPTNO=P_DEPTNO ;

END ;

/

在Java中调用该存储过程：

Class.forName(DBDRIVER) ;

conn = DriverManager.getConnection(URL, USERNAME, PASSWORD) ;

callstmt = conn.prepareCall("{call UPDATE_DEPT_BY_ID(?,?,?)}") ;

callstmt.setInt(1, 80) ;

callstmt.setString(2, "THE_HR") ;

callstmt.setString(3, "MIAMI,USA") ;

callstmt.executeQuery();

conn.commit();

查询-有IN有OUT:

存储过程语句：

--根据员工编号查询员工的基本信息

CREATE OR REPLACE PROCEDURE QUERY_EMP_BY_ID

(

P_EMPNO IN OUT NUMBER ,

P_ENAME OUT VARCHAR2 ,

P_JOB OUT VARCHAR2 ,

P_MGR OUT VARCHAR2 ,

P_HIREDATE OUT DATE ,

P_SAL OUT NUMBER ,

P_COMM OUT NUMBER ,

P_DNAME OUT VARCHAR2 ,

P_LOC OUT VARCHAR2

)

IS

BEGIN

SELECT  E.EMPNO, E.ENAME ,E.JOB ,E.MGR ,E.HIREDATE,E.SAL ,E.COMM ,D.DNAME ,D.LOC

INTO    P_EMPNO ,P_ENAME ,P_JOB ,P_MGR ,P_HIREDATE,P_SAL ,P_COMM ,P_DNAME ,P_LOC

FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.EMPNO=P_EMPNO ;

IF P_COMM IS NULL THEN

P_COMM:=100 ;

END IF ;

END QUERY_EMP_BY_ID ;

/

在Java中调用存储过程：

Class.forName(DBDRIVER) ;

conn = DriverManager.getConnection(URL, USERNAME, PASSWORD) ;

callstmt = conn.prepareCall(callSql) ;

callstmt.setInt("P_EMPNO", 7521) ;

callstmt.registerOutParameter("P_ENAME", oracle.jdbc.OracleTypes.VARCHAR) ;

callstmt.registerOutParameter("P_JOB", oracle.jdbc.OracleTypes.VARCHAR) ;

callstmt.registerOutParameter("P_MGR", oracle.jdbc.OracleTypes.NUMBER) ;

callstmt.registerOutParameter("P_HIREDATE", oracle.jdbc.OracleTypes.DATE) ;

callstmt.registerOutParameter("P_SAL", oracle.jdbc.OracleTypes.NUMBER) ;

callstmt.registerOutParameter("P_COMM", oracle.jdbc.OracleTypes.NUMBER) ;

callstmt.registerOutParameter("P_DNAME", oracle.jdbc.OracleTypes.VARCHAR) ;

callstmt.registerOutParameter("P_LOC", oracle.jdbc.OracleTypes.VARCHAR) ;

callstmt.executeQuery();

System.out.println( "姓名 : "+callstmt.getString("P_ENAME") ) ;

System.out.println( "工作 : "+callstmt.getString("P_JOB") ) ;

System.out.println( "入职 : "+callstmt.getDate("P_HIREDATE") ) ;

System.out.println( "薪水 : "+callstmt.getFloat("P_SAL") ) ;

System.out.println( "奖金 : "+callstmt.getFloat("P_COMM") ) ;

System.out.println( "部门 : "+callstmt.getString("P_DNAME") ) ;

System.out.println( "地址 : "+callstmt.getString("P_LOC") ) ;

存储过程语句：

--查询某职位的所有员工，自定义类型EMPS_CUR

CREATE OR REPLACE PACKAGE PKG_EMPS

AS

TYPE ALL_EMPS IS REF CURSOR ;

END ;

/

CREATE OR REPLACE PROCEDURE GET_EMPS

(

P_JOB IN VARCHAR2 ,

P_EMPS_CUR OUT PKG_EMPS.ALL_EMPS  --引用自定义类型

)

IS BEGIN

OPEN P_EMPS_CUR FOR

SELECT E.EMPNO,E.ENAME,E.JOB,E.HIREDATE,E.SAL,E.COMM,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND JOB=P_JOB ;

END GET_EMPS;

/

在Java中调用存储过程：

Class.forName(DBDRIVER) ;    conn = DriverManager.getConnection(URL, USERNAME, PASSWORD) ;    callstmt = conn.prepareCall("{call GET_EMPS(?,?)}") ;        callstmt.setString(1, "SALESMAN") ;    callstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR) ;    callstmt.execute();    rs = (ResultSet) callstmt.getObject(2) ;    while(rs.next())    {     StringBuilder oneRowResult = new StringBuilder() ;     for(int i = 1 ; i<=8 ; i++)     {      oneRowResult.append(rs.getString(i)).append(" ") ;     }     System.out.println(oneRowResult.toString());    }    conn.commit();