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

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

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

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;

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;

declare    v_param1 number(5):=2;    v_param2 number(5):=8;    v_result number(5);begin    --調(diào)用上面案例一的存儲(chǔ)過程    procedure_1();     --調(diào)用上面案例二的存儲(chǔ)過程    procedure_2(v_param1,v_param2);     --調(diào)用上面案例三的存儲(chǔ)過程    procedure_3(v_param1,v_param2,v_result);    dbms_output.put_line(v_result);end;/*執(zhí)行結(jié)果：*/procedure_1.......procedure_2.......2 + 8 = 10procedure_3.......2 - 8 = -610

/*存儲(chǔ)過程*/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調(diào)用存儲(chǔ)過程public static void main(String[] args) {  Connection conn=null;  CallableStatement cs=null;  ResultSet rs=null;  //java調(diào)用存儲(chǔ)過程  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(?,?,?)}");    //給輸入?yún)?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);//關(guān)閉資源  }  }//執(zhí)行后就會(huì)向數(shù)據(jù)庫的emp表中插入一條編號(hào)為6666，姓名為張三，職位為MANAGER的記錄

/*存儲(chǔ)過程*/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調(diào)用存儲(chǔ)過程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);//給輸入?yún)?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ù)對(duì)應(yīng)？的位置對(duì)應(yīng)起來，該例輸出參數(shù)對(duì)應(yīng)第2個(gè)問號(hào)，而且輸出參數(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);//關(guān)閉資源  }  } /*執(zhí)行結(jié)果,控制臺(tái)打印：*/結(jié)果：?jiǎn)T工姓名：張三

/*存儲(chǔ)過程*/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調(diào)用存儲(chǔ)過程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ù)類型，注意：順序要對(duì)應(yīng)起來    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("員工編號(hào)為7788的姓名為："+ename+" 職位是："+job+" 薪水是："+sal);  } catch (Exception e) {    e.printStackTrace();  }finally{    closeResource(conn,cs,rs);//關(guān)閉資源  }}/*執(zhí)行結(jié)果，控制臺(tái)打印：*/員工編號(hào)為7788的姓名為：SCOTT 職位是：ANALYST 薪水是：3000.0

/*定義游標(biāo)*/create package my_package astype emp_cursor is ref cursor;end my_package;/*存儲(chǔ)過程*/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調(diào)用存儲(chǔ)過程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);//給輸入?yún)?shù)賦值    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定輸出參數(shù)的數(shù)據(jù)類型    cs.execute();    rs=(ResultSet) cs.getObject(2);//獲取輸出參數(shù)的值    while(rs.next()){      //順序?yàn)閿?shù)據(jù)庫中字段前后順序，例如數(shù)據(jù)庫emp表中第5列為hiredate，數(shù)據(jù)類型為Date，所以獲取第5列值時(shí)就應(yīng)該用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);//關(guān)閉資源  }  }

7369 SMITH 1980-12-177566 JONES 1981-04-027788 SCOTT 1987-04-197876 ADAMS 1987-05-237902 FORD 1981-12-03 

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();      }    }  }

/*定義游標(biāo)*/create package page_package astype page_cursor is ref cursor;end page_package;/*存儲(chǔ)過程*/create procedure pro_paging (    v_page_size in number,--每頁顯示多少條    v_page_count out number,--總頁數(shù)    v_current_page in number,--當(dāng)前頁    v_total_count out number,--記錄總條數(shù)    emp_cursor out page_package.page_cursor--返回查詢結(jié)果集的游標(biāo)    )is    v_begin number(5):=v_page_size*(v_current_page-1)+1;--查詢起始位置    v_end number(5):=v_page_size*v_current_page;--查詢結(jié)束位置    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;    /*不能像下面這么寫，不然調(diào)用該存儲(chǔ)過程時(shí)會(huì)報(bào)類型不一致的錯(cuò)，因?yàn)樽罾锩娌榈闹挥衑mpno,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('當(dāng)前頁： '||v_current_page);    dbms_output.put_line('每頁顯示 '||v_page_size||' 條');end;

/*調(diào)用分頁存儲(chǔ)過程*/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,--當(dāng)前頁   v_total_count,--記錄總條數(shù)   v_cursor--游標(biāo)   );   dbms_output.put_line('--------------------------');   dbms_output.put_line('第二頁數(shù)據(jù)。。。。。。。。。');   --顯示第二頁數(shù)據(jù)   pro_paging(5,--每頁顯示5條   v_page_count,--總頁數(shù)   2,--當(dāng)前頁   v_total_count,--記錄總條數(shù)   v_cursor--游標(biāo)   );end;/*運(yùn)行結(jié)果：*/第一頁數(shù)據(jù)。。。。。。。。。6666 張三20 empSu219 empSave27369 SMITH7499 ALLEN共 17 條記錄共 4 頁當(dāng)前頁： 1每頁顯示 5 條--------------------------第二頁數(shù)據(jù)。。。。。。。。。7521 WARD7566 JONES7654 MARTIN7698 BLAKE7782 CLARK共 17 條記錄共 4 頁當(dāng)前頁： 2每頁顯示 5 條