建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
淺析Java JDBC調(diào)用Oracle存儲過程
Java JDBC調(diào)用Oracle存儲過程一般有3種：
創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、清江浦網(wǎng)絡(luò)推廣、成都小程序開發(fā)、清江浦網(wǎng)絡(luò)營銷、清江浦企業(yè)策劃、清江浦品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供清江浦建站搭建服務(wù)，24小時服務(wù)熱線：18980820575，官方網(wǎng)址：www.cdcxhl.com
1.無返回值
2.有一個返回值
3.返回一個數(shù)據(jù)集，就是游標！
關(guān)鍵字：call 語法格式{call 存儲過程名(參數(shù)列表)}
廢話不說，見代碼！
Java JDBC調(diào)用Oracle存儲過程業(yè)務(wù)實例：
1.添加員工,如果指定部門不存在,則先添加部門信息,再添加員工（無返回值）
--創(chuàng)建存儲過程如下
- CREATE OR REPLACE PROCEDURE sp_add_emp1(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)?。?！');
- END IF;
- commit;
- END;
- CREATE OR REPLACE PROCEDURE sp_add_emp1(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)！??！');
- END IF;
- commit;
- END;
Java中調(diào)用代碼1：
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
- conn.setAutoCommit(false);
- conn=DBConnection.getDBConnection().getConnection();
- String spName="{call sp_add_emp1(?,?,?,?)}";
- CallableStatement cstmt=conn.prepareCall(spName);
- cstmt.setInt(1, 2);
- cstmt.setString(2, "wwww");
- cstmt.setInt(3, 1);
- cstmt.setString(4, "qwqwq");
- cstmt.executeUpdate();
- conn.close();
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
- conn.setAutoCommit(false);
- conn=DBConnection.getDBConnection().getConnection();
- String spName="{call sp_add_emp1(?,?,?,?)}";
- CallableStatement cstmt=conn.prepareCall(spName);
- cstmt.setInt(1, 2);
- cstmt.setString(2, "wwww");
- cstmt.setInt(3, 1);
- cstmt.setString(4, "qwqwq");
- cstmt.executeUpdate();
- conn.close();
2.需求同上, 只是返回該部門的員工總數(shù)。（有一個返回值）
--創(chuàng)建存儲過程如下
- CREATE OR REPLACE PROCEDURE sp_add_emp2(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
- num out number
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)?。?！');
- END IF;
- num:=num1;
- commit;
- END;
- CREATE OR REPLACE PROCEDURE sp_add_emp2(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
- num out number
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)?。?！');
- END IF;
- num:=num1;
- commit;
- END;
Java中調(diào)用代碼2：
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
- conn.setAutoCommit(false);
- conn=DBConnection.getDBConnection().getConnection();
- String spName="{call sp_add_emp2(?,?,?,?,?)}";
- CallableStatement cstmt=conn.prepareCall(spName);
- cstmt.setInt(1,1111);
- cstmt.setString(2, "qqqq");
- cstmt.setInt(3, 50);
- cstmt.setString(4, "pppp");
- cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
- cstmt.executeUpdate();
- int i = cstmt.getInt(5);
- System.out.println(i);
- cstmt.close();
- conn.close();
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
- conn.setAutoCommit(false);
- conn=DBConnection.getDBConnection().getConnection();
- String spName="{call sp_add_emp2(?,?,?,?,?)}";
- CallableStatement cstmt=conn.prepareCall(spName);
- cstmt.setInt(1,1111);
- cstmt.setString(2, "qqqq");
- cstmt.setInt(3, 50);
- cstmt.setString(4, "pppp");
- cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
- cstmt.executeUpdate();
- int i = cstmt.getInt(5);
- System.out.println(i);
- cstmt.close();
- conn.close();
3.需求同上, 并返回該部門的員工信息(工號和姓名)。（返回一個游標）
--創(chuàng)建存儲過程如下
Sql代碼
--1.建包
- CREATE OR REPLACE PACKAGE my_pak AS
- TYPE my_cus IS REF CURSOR ;
- END my_pak;
--2.寫存儲返回過程
- CREATE OR REPLACE PROCEDURE sp_add_emp3(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
- p_cus OUT my_pak.my_cus
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- OPEN p_cus FOR select empno,ename into v_empno,v_ename from emp where deptno =v_deptno
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)?。。?);
- END IF;
- commit;
- END;
--1.建包
- CREATE OR REPLACE PACKAGE my_pak AS
- TYPE my_cus IS REF CURSOR ;
- END my_pak;
--2.寫存儲返回過程
- CREATE OR REPLACE PROCEDURE sp_add_emp3(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
- p_cus OUT my_pak.my_cus
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- OPEN p_cus FOR select empno,ename into v_empno,v_ename from emp where deptno =v_deptno
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
- ELSE
- raise_application_error(-202021,'員工id 重復(fù)?。。?);
- END IF;
- commit;
- END;
Java JDBC調(diào)用Oracle，Java中調(diào)用代碼3：
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
- conn.setAutoCommit(false);
- conn=DBConnection.getDBConnection().getConnection();
- String spName="{call sp_add_emp2(?,?,?,?,?)}";
- CallableStatement cstmt=conn.prepareCall(spName);
- cstmt.setInt(1,1111);
- cstmt.setString(2, "qqqq");
- cstmt.setInt(3, 50);
- cstmt.setString(4, "pppp");
- cstmt.registerOutParameter(5, java.sql.Types.ORACLETYPE);
- cstmt.executeUpdate();
- int i = cstmt.getInt(5);
- System.out.println(i);
- cstmt.close();
- conn.close();
【編輯推薦】
- 在Weblogic中實現(xiàn)JDBC的功能
- 詳解JDBC與Hibernate區(qū)別
- JDBC連接MySQL數(shù)據(jù)庫關(guān)鍵四步
- 五步精通SQL Server 2000 JDBC驅(qū)動安裝與測試
- 詳解JDBC驅(qū)動的四種類型
- JDBC存儲過程在Oracle中的獲取結(jié)果集
新聞標題：淺析Java JDBC調(diào)用Oracle存儲過程
標題URL：http://www.dlmjj.cn/article/cdicphh.html
