一、無返回值的存儲過程

古人云：欲速則不達，為了讓大傢伙比較容易接受分頁過程編寫，我還是從簡單到複雜，循序漸進的給大家講解。首先是掌握最簡單的存儲過程，無返回值的存儲過程。 案例：現有一張表book，表結構如下：書號、書名、出版社。

CREATE TABLE book(
 ID NUMBER(4),
 book_name VARCHAR2(30),
 publishing VARCHAR2(30)
);

請寫一個過程，可以向book表添加書，要求通過java程序調用該過程。

--注意：in->表示這是一個輸入參數，默認為in --out->表示一個輸出參數

CREATE OR REPLACE PROCEDURE ADD_BOOK(ID IN NUMBER,

NAME IN VARCHAR2,

PUBLISHING IN VARCHAR2) IS

BEGIN

INSERT INTO BOOK VALUES (ID, NAME, PUBLISHING);

COMMIT;

END;

java程序調用該存儲過程的代碼

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager; 

/**
 * 調用一個無返回值的存儲過程
 * 
 * @author jiqinlin
 *
 */
public class ProcedureTest {
 public static void main(String[] args) {
 try {
 // 1.加載驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 // 2.得到連接
 Connection ct = DriverManager.getConnection(
 "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
 // 3.創建CallableStatement
 CallableStatement cs = ct.prepareCall("call ADD_BOOK(?,?,?)");
 //給?賦值
 cs.setInt(1, 1);
 cs.setString(2, "java");
 cs.setString(3, "java出版社");
 // 4.執行
 cs.execute();
 //5、關閉
 cs.close();
 ct.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
}

二、有返回值的存儲過程(非列表)

案例：編寫一個存儲過程，可以輸入僱員的編號，返回該僱員的姓名。

--輸入和輸出的存儲過程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, SPNAME OUT VARCHAR2) IS
BEGIN
 SELECT ENAME INTO SPNAME FROM EMP WHERE EMPNO = SPNO; 

END;
/

java程序調用該存儲過程的代碼

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
 * 調用一個無返回值的存儲過程
 * 
 * @author jiqinlin
 *
 */
public class ProcedureTest {
 public static void main(String[] args) {
 try {
 // 1.加載驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 // 2.得到連接
 Connection ct = DriverManager.getConnection(
 "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
 // 3.創建CallableStatement
 CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
 //給第一個?賦值
 cs.setInt(1,7788);
 //給第二個?賦值
 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
 //4、執行
 cs.execute();
 //取出返回值,要注意？的順序
 String name=cs.getString(2);
 System.out.println("編號7788的名字："+name);
 //5、關閉
 cs.close();
 ct.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
} 

案例擴張：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名、工資和崗位。

--輸入和輸出的存儲過程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
 SPNAME OUT VARCHAR2,
 SPSAL OUT NUMBER,
 SPJOB OUT VARCHAR2) IS
BEGIN
 SELECT ENAME, SAL, JOB INTO SPNAME, SPSAL, SPJOB FROM EMP WHERE EMPNO = SPNO;
END;
/

java程序調用該存儲過程的代碼

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
 * 調用一個無返回值的存儲過程
 * 
 * @author jiqinlin
 *
 */
public class ProcedureTest {
 public static void main(String[] args) {
 try {
 // 1.加載驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 // 2.得到連接
 Connection ct = DriverManager.getConnection(
 "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
 // 3.創建CallableStatement
 CallableStatement cs = ct.prepareCall("{call sp_proc(?,?,?,?)}");
 //給第一個?賦值
 cs.setInt(1,7788);
 //給第二個?賦值
 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); 

 //給第三個？賦值
 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
 //給第四個？賦值
 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
 //4、執行
 cs.execute();
 //取出返回值,要注意？的順序
 String name=cs.getString(2);
 double sal=cs.getDouble(3);
 String job=cs.getString(4);
 System.out.println("編號7788的名字："+name+"，職位："+job+"，薪水："+sal+"");
 //5、關閉
 cs.close();
 ct.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
}

三、有返回值的存儲過程(列表[結果集])

案例：編寫一個存儲過程，輸入部門號，返回該部門所有僱員信息。

該題分析如下：由於oracle存儲過程沒有返回值，它的所有返回值都是通過out參數來替代的，列表同樣也不例外，但由於是集合，所以不能用一般的參數，必須要用pagkage了。所以要分兩部分：

1)、建立一個包，在該包中我們定義類型test_cursor，它是個遊標。

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
 TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/

2)、建立存儲過程。

CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,

 P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS
BEGIN
 OPEN P_CURSOR FOR
 SELECT * FROM EMP WHERE DEPTNO = SPNO;
END SP_PROC;
/

3)、如何在java 程序中調用該過程

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
 * 調用一個無返回值的存儲過程
 * 
 * @author jiqinlin
 *
 */
public class ProcedureTest {
 public static void main(String[] args) {
 try {
 // 1.加載驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 // 2.得到連接
 Connection ct = DriverManager.getConnection(
 "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
 // 3.創建CallableStatement
 CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}");
 //給第一個?賦值
 cs.setInt(1,10);
 //給第二個?賦值
 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); 

 //4、執行
 cs.execute();
 //得到結果集
 ResultSet rs = (ResultSet) cs.getObject(2);
 while (rs.next()) {
 System.out.println(rs.getInt(1) + " " + rs.getString(2));
 }
 //5、關閉
 rs.close();
 cs.close();
 ct.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
}

四、編寫分頁過程

有了上面的基礎，相信大家可以完成分頁存儲過程了。

要求，請大家編寫一個存儲過程，要求可以輸入表名、每頁顯示記錄數、當前頁。返回總記錄數，總頁數，和返回的結果集。

--ROWNUM用法
SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10;
----oracle分頁sql語句；在分頁時，大家可以把下面的sql語句當做一個模板使用
SELECT *
FROM (SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10)
WHERE RN >= 6;

1)、開發一個包

建立一個包，在該包中定義類型為test_cursor的遊標。

--建立一個包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
 TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/
--開始編寫分頁的過程
CREATE OR REPLACE PROCEDURE FENYE(TABLENAME IN VARCHAR2, 
 PAGESIZE IN NUMBER, --每頁顯示記錄數
 PAGENOW IN NUMBER, --頁數
 MYROWS OUT NUMBER, --總記錄數
 MYPAGECOUNT OUT NUMBER, --總頁數
 P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS --返回的記錄集 
--定義部分
--定義sql語句字符串
V_SQL VARCHAR2(1000);
--定義兩個整數
V_BEGIN NUMBER := (PAGENOW - 1) * PAGESIZE + 1; 
V_END NUMBER := PAGENOW * PAGESIZE;
BEGIN
--執行部分
V_SQL := 'select * from (select t1.*, rownum rn from (select * from ' || TABLENAME || ') t1 where rownum<=' || V_END || ') where rn>=' || V_BEGIN;
--把遊標和sql關聯
OPEN P_CURSOR FOR V_SQL;
--計算myrows和myPageCount
--組織一個sql語句
V_SQL := 'select count(*) from ' || TABLENAME;
--執行sql,並把返回的值，賦給myrows；
EXECUTE ImMEDIATE V_SQL INTO MYROWS; --它解析並馬上執行動態的SQL語句或非運行時創建的PL/SQL塊.動態創建和執行SQL語句性能超前，
 --EXECUTE IMMEDIATE的目標在於減小企業費用並獲得較高的性能，較之以前它相當容易編碼.
 --儘管DBMS_SQL仍然可用，但是推薦使用EXECUTE IMMEDIATE,因為它獲的收益在包之上。 
--計算myPageCount
--if myrows%Pagesize=0 then 這樣寫是錯的 

IF MOD(MYROWS, PAGESIZE) = 0 THEN 
 MYPAGECOUNT := MYROWS/PAGESIZE; 
ELSE 
 MYPAGECOUNT := MYROWS/PAGESIZE + 1;
END IF;
--關閉遊標
--CLOSE P_CURSOR; --不要關閉，否則java調用該存儲過程會報錯
END;
/

java調用分頁代碼

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
/**
 * 調用一個無返回值的存儲過程
 * 
 * @author jiqinlin
 * 
 */
public class ProcedureTest {
 public static void main(String[] args) {
 try {
 // 1.加載驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 // 2.得到連接
 Connection ct = DriverManager.getConnection(
 "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
 // 3.創建CallableStatement
 CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
 cs.setString(1, "emp"); //表名
 cs.setInt(2, 5); //每頁顯示記錄數
 cs.setInt(3, 1);//頁數
 // 註冊總記錄數
 cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); //總記錄數
 // 註冊總頁數
 cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); //總頁數
 // 註冊返回的結果集 

 cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //返回的記錄集
 // 4、執行
 cs.execute();
 // 得到結果集
 // 取出總記錄數 /這裡要注意，getInt(4)中4，是由該參數的位置決定的
 int rowNum = cs.getInt(4);
 int pageCount = cs.getInt(5);
 ResultSet rs = (ResultSet) cs.getObject(6);
 // 顯示一下，看看對不對
 System.out.println("rowNum=" + rowNum);
 System.out.println("總頁數=" + pageCount);
 while (rs.next()) {
 System.out.println("編號：" + rs.getInt(1) + 
 " 名字：" + rs.getString(2) + 
 " 工資：" + rs.getFloat(6));
 }
 // 5、關閉
 //rs.close();
 cs.close();
 ct.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
}

RNG輸了，青春已不在~

閱讀更多 波波說運維 的文章

關鍵字: SQL 技術 分頁