怎么調用Oracle存儲過程

這篇文章給大家介紹怎么調用Oracle存儲過程，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

Oracle的存儲過程語法如下：

create procedure 存儲過程名稱（隨便?。?nbsp;
is
    在這里可以定義常量、變量、游標、復雜數(shù)據(jù)類型這里可以定義變量、常量
begin
    執(zhí)行部分
end;

（2）帶參數(shù)的存儲過程語法：

create procedure 存儲過程名稱（隨便?。?nbsp;(變量1 數(shù)據(jù)類型,變量2 數(shù)據(jù)類型,...,變量n 數(shù)據(jù)類型)
is
    在這里可以定義常量、變量、游標、復雜數(shù)據(jù)類型這里可以定義變量、常量
begin
    執(zhí)行部分
end;

（3）帶輸入、輸出參數(shù)的存儲過程語法：

create procedure 存儲過程名稱（隨便?。?nbsp;(變量1 in(或out) 數(shù)據(jù)類型,變量2 in(或out) 數(shù)據(jù)類型,...,變量n in(或out) 數(shù)據(jù)類型)
is
    在這里可以定義常量、變量、游標、復雜數(shù)據(jù)類型這里可以定義變量、常量
begin
    執(zhí)行部分
end;

注意：用上面的語法創(chuàng)建存儲過程時可能會碰到數(shù)據(jù)庫中已經有了同名的存儲過程，這樣Oracle就會彈框報錯，說名字已被現(xiàn)有對象使用。解決方法有兩種：

　　　方法一：換個存儲過程名

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

存儲過程案例一：沒參數(shù)的存儲過程

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

存儲過程案例二：帶參數(shù)的的存儲過程

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;

存儲過程案例三：帶輸入、輸出參數(shù)的存儲過程

存儲過程的參數(shù)分為輸入參數(shù)和輸出參數(shù)，

輸入參數(shù)：輸入參數(shù)一般會在變量名和數(shù)據(jù)類型之間加in來表示該參數(shù)是輸入參數(shù)

輸出參數(shù)：輸出參數(shù)一般會在變量名和數(shù)據(jù)類型之間加out來表示該變量是輸出參數(shù)

不寫in和out的話，默認為輸入參數(shù)

create procedure procedure_3(v_i in number,v_j in number ,v_m out number)
is
begin
    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;
/*執(zhí)行結果：*/
procedure_1.......
procedure_2.......
2 + 8 = 10
procedure_3.......
2 - 8 = -6
10

java調用存儲過程

案例一：java調用沒有返回值的存儲過程

要求：編寫一個像數(shù)據(jù)庫emp表插入一條編號為6666，姓名為張三，職位為MANAGER的記錄

/*存儲過程*/
create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )
is
begin
    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(?,?,?)}");
    //給輸入參數(shù)賦值
    cs.setInt(1, 6666);
    cs.setString(2, "張三");
    cs.setString(3, "MANAGER");
    cs.execute();//執(zhí)行
  } catch (Exception e) {
    e.printStackTrace();
  }finally{
    closeResource(conn,cs,rs);//關閉資源
  }  
}
//執(zhí)行后就會向數(shù)據(jù)庫的emp表中插入一條編號為6666，姓名為張三，職位為MANAGER的記錄

案例二：java調用返回單列單行的存儲過程

要求：編寫一個根據(jù)員工編號查找員工姓名的存儲過程，并用java調用該存儲過程

/*存儲過程*/
create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)
is
begin
    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);//給輸入參數(shù)賦值
    /*指定輸出參數(shù)的數(shù)據(jù)類型
    語法：oracle.jdbc.OracleTypes.輸出參數(shù)的數(shù)據(jù)類型
    此例輸出參數(shù)的數(shù)據(jù)類型是varchar，所以是oracle.jdbc.OracleTypes.VARCHAR*/
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
    cs.execute();//執(zhí)行
    //獲取輸出參數(shù)的值，位置要和輸出參數(shù)對應？的位置對應起來，該例輸出參數(shù)對應第2個問號，而且輸出參數(shù)的數(shù)據(jù)類型為字符型，所以是cs.getString(2)
    String a=cs.getString(2);
    System.out.println("員工姓名："+a);
  } catch (Exception e) {
    e.printStackTrace();
  }finally{
    closeResource(conn,cs,rs);//關閉資源
  }  
}
 
/*執(zhí)行結果,控制臺打?。?/
結果：員工姓名：張三

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

要求：編寫一個根據(jù)員工編號查找員工姓名、職位和工資的存儲過程，并用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)
is
begin
    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);
    //指定輸出參數(shù)的數(shù)據(jù)類型，注意：順序要對應起來
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
    cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);
    cs.execute();//執(zhí)行
    //獲取返回值
    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);//關閉資源
  }
}
/*執(zhí)行結果，控制臺打?。?/
員工編號為7788的姓名為：SCOTT 職位是：ANALYST 薪水是：3000.0

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

要求：編寫一個根據(jù)部門編號查找部門所有員工信息的存儲過程，并用java調用該存儲過程

/*定義游標*/
create package my_package as
type 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)
is
begin
    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);//給輸入參數(shù)賦值
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定輸出參數(shù)的數(shù)據(jù)類型
    cs.execute();
    rs=(ResultSet) cs.getObject(2);//獲取輸出參數(shù)的值
    while(rs.next()){
      //順序為數(shù)據(jù)庫中字段前后順序，例如數(shù)據(jù)庫emp表中第5列為hiredate，數(shù)據(jù)類型為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-17
7566 JONES 1981-04-02
7788 SCOTT 1987-04-19
7876 ADAMS 1987-05-23
7902 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 as
type page_cursor is ref cursor;
end page_package;
/*存儲過程*/
create procedure pro_paging (
    v_page_size in number,--每頁顯示多少條
    v_page_count out number,--總頁數(shù)
    v_current_page in number,--當前頁
    v_total_count out number,--記錄總條數(shù)
    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;
    /*不能像下面這么寫，不然調用該存儲過程時會報類型不一致的錯，因為最里面查的只有empno,ename，因此外面也要和里面保持一致
    v_sql varchar2(1000):=\'select * from
       (select a.*,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('第一頁數(shù)據(jù)。。。。。。。。。');
   pro_paging(5,--每頁顯示5條
   v_page_count,--總頁數(shù)
   1,--當前頁
   v_total_count,--記錄總條數(shù)
   v_cursor--游標
   );
   dbms_output.put_line('--------------------------');
   dbms_output.put_line('第二頁數(shù)據(jù)。。。。。。。。。');
   --顯示第二頁數(shù)據(jù)
   pro_paging(5,--每頁顯示5條
   v_page_count,--總頁數(shù)
   2,--當前頁
   v_total_count,--記錄總條數(shù)
   v_cursor--游標
   );
end;
/*運行結果：*/
第一頁數(shù)據(jù)。。。。。。。。。
6666 張三
20 empSu2
19 empSave2
7369 SMITH
7499 ALLEN
共 17 條記錄
共 4 頁
當前頁： 1
每頁顯示 5 條
--------------------------
第二頁數(shù)據(jù)。。。。。。。。。
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
共 17 條記錄
共 4 頁
當前頁： 2
每頁顯示 5 條

關于怎么調用Oracle存儲過程就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。