Oracle預存程序及調用

Oracle的預存程序及調用

Oracle預存程序文法

Oracle的預存程序文法如下：

create procedure 預存程序名稱（隨便取） is       在這裡可以定義常量、變數、遊標、複雜資料類型這裡可以定義變數、常量begin       執行部分end;（2）帶參數的預存程序文法：create procedure 預存程序名稱（隨便取） (變數1 資料類型,變數2 資料類型,...,變數n 資料類型)is       在這裡可以定義常量、變數、遊標、複雜資料類型這裡可以定義變數、常量begin       執行部分end;（3）帶輸入、輸出參數的預存程序文法：create procedure 預存程序名稱（隨便取） (變數1 in(或out) 資料類型,變數2 in(或out) 資料類型,...,變數n in(或out) 資料類型)is       在這裡可以定義常量、變數、遊標、複雜資料類型這裡可以定義變數、常量begin       執行部分end;

注意：用上面的文法建立預存程序時可能會碰到資料庫中已經有了同名的預存程序，這樣Oracle就會彈框報錯，說名字已被現有對象使用。解決方案有兩種：

　　　方法一：換個預存程序名

　　　方法二：在最開頭的create procedure 之間加上 or replace 關鍵字，例如：create or replace procedure 預存程序名稱。但是這種方法不建議使用，因為這種方法會把之前同名的預存程序替換為你當前寫的這個

預存程序案例一：沒參數的預存程序

create replace procedure procedure_1isbegin       dbms_output.put_line('procedure_1.......');end;

預存程序案例二：帶參數的的預存程序

create procedure procedure_2(v_i number,v_j number)is       v_m number(5);begin       dbms_output.put_line('procedure_2.......');       v_m := v_i + v_j;       dbms_output.put_line(v_i||' + '||v_j||' = '||v_m);end;

預存程序案例三：帶輸入、輸出參數的預存程序
預存程序的參數分為輸入參數和輸出參數，
輸入參數：輸入參數一般會在變數名和資料類型之間加in來表示該參數是輸入參數
輸出參數：輸出參數一般會在變數名和資料類型之間加out來表示該變數是輸出參數
不寫in和out的話，預設為輸入參數

create procedure procedure_3(v_i in number,v_j in number ,v_m out number)isbegin       dbms_output.put_line('procedure_3.......');       v_m:=v_i - v_j;       dbms_output.put_line(v_i||' - '||v_j||' = '||v_m);end;

PL/SQL塊中調用預存程序

下面以調用上面三個預存程序為例

declare       v_param1 number(5):=2;       v_param2 number(5):=8;       v_result number(5);begin        --調用上面案例一的預存程序       procedure_1();        --調用上面案例二的預存程序       procedure_2(v_param1,v_param2);        --調用上面案例三的預存程序       procedure_3(v_param1,v_param2,v_result);       dbms_output.put_line(v_result);end;/*執行結果：*/procedure_1.......procedure_2.......2 + 8 = 10procedure_3.......2 - 8 = -610

java調用預存程序

案例一：java調用沒有傳回值的預存程序

要求：編寫一個像資料庫emp表插入一條編號為6666，姓名為張三，職位為MANAGER的記錄

/*預存程序*/create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )isbegin       insert into emp (empno,ename,job) values (v_empno,v_ename,v_job);end;

//java調用預存程序public static void main(String[] args) {    Connection conn=null;    CallableStatement cs=null;    ResultSet rs=null;    //java調用預存程序    try {        Class.forName("oracle.jdbc.OracleDriver");        conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");        cs=conn.prepareCall("{call procedure_4(?,?,?)}");        //給輸入參數賦值        cs.setInt(1, 6666);        cs.setString(2, "張三");        cs.setString(3, "MANAGER");        cs.execute();//執行    } catch (Exception e) {        e.printStackTrace();    }finally{        closeResource(conn,cs,rs);//關閉資源    }   }//執行後就會向資料庫的emp表中插入一條編號為6666，姓名為張三，職位為MANAGER的記錄

案例二：java調用返回單列單行的預存程序

要求：編寫一個根據員工編號尋找員工姓名的預存程序，並用java調用該預存程序

/*預存程序*/create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)isbegin       select ename into v_ename from emp where empno=v_empno;end;

//java調用預存程序public static void main(String[] args) {    Connection conn=null;    CallableStatement cs=null;    ResultSet rs=null;    try {        Class.forName("oracle.jdbc.OracleDriver");        conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");        cs=conn.prepareCall("{call procedure_5(?,?)}");        cs.setInt(1, 6666);//給輸入參數賦值        /*指定輸出參數的資料類型        文法：oracle.jdbc.OracleTypes.輸出參數的資料類型        此例輸出參數的資料類型是varchar，所以是oracle.jdbc.OracleTypes.VARCHAR*/        cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);        cs.execute();//執行        //擷取輸出參數的值，位置要和輸出參數對應。的位置對應起來，該例輸出參數對應第2個問號，而且輸出參數的資料類型為字元型，所以是cs.getString(2)        String a=cs.getString(2);        System.out.println("員工姓名："+a);    } catch (Exception e) {        e.printStackTrace();    }finally{        closeResource(conn,cs,rs);//關閉資源    }   }/*執行結果,控制台列印：*/結果：員工姓名：張三

案例三：java調用返回單行多列的預存程序

要求：編寫一個根據員工編號尋找員工姓名、職位和工資的預存程序，並用java調用該預存程序

/*預存程序*/create procedure procedure_6(v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type)isbegin       select ename,job,sal into v_ename,v_job,v_sal from emp where empno=v_empno;end;

//java調用預存程序public static void main(String[] args) {    Connection conn=null;    CallableStatement cs=null;    ResultSet rs=null;    try {        Class.forName("oracle.jdbc.OracleDriver");        conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");        cs=conn.prepareCall("{call procedure_6(?,?,?,?)}");        cs.setInt(1, 7788);        //指定輸出參數的資料類型，注意：順序要對應起來        cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);        cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);        cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);        cs.execute();//執行        //擷取傳回值        String ename=cs.getString(2);//擷取姓名        String job=cs.getString(3);//擷取職位        double sal=cs.getDouble(4);//擷取薪水        System.out.println("員工編號為7788的姓名為："+ename+"  職位是："+job+"  薪水是："+sal);    } catch (Exception e) {        e.printStackTrace();    }finally{        closeResource(conn,cs,rs);//關閉資源    }}/*執行結果，控制台列印：*/員工編號為7788的姓名為：SCOTT  職位是：ANALYST  薪水是：3000.0

案例四：java調用返回多行多列（返回列表）的預存程序

要求：編寫一個根據部門編號尋找部門所有員工資訊的預存程序，並用java調用該預存程序

/*定義遊標*/create package my_package astype emp_cursor is ref cursor;end my_package;/*預存程序*/create procedure procedure_7(v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor)isbegin       open emp_cursor for select * from emp where deptno=v_deptno;end;

//java調用預存程序public static void main(String[] args) {    Connection conn=null;    CallableStatement cs=null;    ResultSet rs=null;    try {        Class.forName("oracle.jdbc.OracleDriver");        conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");        cs=conn.prepareCall("{call procedure_7(?,?)}");        cs.setInt(1, 20);//給輸入參數賦值        cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定輸出參數的資料類型        cs.execute();        rs=(ResultSet) cs.getObject(2);//擷取輸出參數的值        while(rs.next()){            //順序為資料庫中欄位前後順序，例如資料庫emp表中第5列為hiredate，資料類型為Date，所以擷取第5列值時就應該用rs.getDate(5)            System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getDate(5));        }    } catch (Exception e) {        e.printStackTrace();    }finally{        closeResource(conn,cs,rs);//關閉資源    }   }/*以下就是20號部門所有員工的資訊，這裡為方便我們只列印了編號、姓名和入職時間運行結果，控制台列印：*/7369 SMITH 1980-12-177566 JONES 1981-04-027788 SCOTT 1987-04-197876 ADAMS 1987-05-237902 FORD 1981-12-03

這是上面java調用預存程序代碼中關閉資源方法的代碼

public static void closeResource(Connection conn,CallableStatement cs,ResultSet rs){        if(rs!=null){            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(cs!=null){            try {                cs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(conn!=null){            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }

最後給個應用，分頁的預存程序

分頁預存程序：

/*定義遊標*/create package page_package astype page_cursor is ref cursor;end page_package;/*預存程序*/create procedure pro_paging (       v_page_size in number,--每頁顯示多少條       v_page_count out number,--總頁數       v_current_page in number,--當前頁       v_total_count out number,--記錄總條數       emp_cursor out page_package.page_cursor--返回查詢結果集的遊標       )is       v_begin number(5):=v_page_size*(v_current_page-1)+1;--查詢起始位置       v_end number(5):=v_page_size*v_current_page;--查詢結束位置       v_sql varchar2(1000):='select empno,ename from             (select a.empno,a.ename,rownum rn from                      (select empno,ename from emp) a              where rownum<='|| v_end ||') b        where b.rn>='||v_begin;       v_ename  varchar2(10);       v_empno number(4);begin       open emp_cursor for v_sql;       loop         fetch emp_cursor into v_empno,v_ename;         exit when emp_cursor%notfound;         dbms_output.put_line(v_empno||'  '||v_ename);       end loop;       v_sql:='select count(empno) from emp';       execute immediate v_sql into v_total_count;       if(mod(v_total_count,v_page_size)=0) then             v_page_count:=v_total_count/v_page_size;       else             v_page_count:=trunc(v_total_count/v_page_size)+1;       end if;       dbms_output.put_line('共 '||v_total_count||' 條記錄');       dbms_output.put_line('共 '||v_page_count||' 頁');       dbms_output.put_line('當前頁：  '||v_current_page);       dbms_output.put_line('每頁顯示  '||v_page_size||' 條');end;

Java調用的話和上面java調用預存程序的例子一樣。這裡為了方便 ，就直接在pl/sql中調用了

/*調用分頁預存程序*/declare      v_page_count number(5);      v_cursor page_package.page_cursor;      v_total_count number(5);begin      dbms_output.put_line('第一頁資料。。。。。。。。。');      pro_paging(5,--每頁顯示5條      v_page_count,--總頁數      1,--當前頁      v_total_count,--記錄總條數      v_cursor--遊標      );      dbms_output.put_line('--------------------------');      dbms_output.put_line('第二頁資料。。。。。。。。。');      --顯示第二頁資料      pro_paging(5,--每頁顯示5條      v_page_count,--總頁數      2,--當前頁      v_total_count,--記錄總條數      v_cursor--遊標      );end;/*運行結果：*/第一頁資料。。。。。。。。。6666  張三20  empSu219  empSave27369  SMITH7499  ALLEN共 17 條記錄共 4 頁當前頁：  1每頁顯示  5 條--------------------------第二頁資料。。。。。。。。。7521  WARD7566  JONES7654  MARTIN7698  BLAKE7782  CLARK共 17 條記錄共 4 頁當前頁：  2每頁顯示  5 條