ORACLE編程,存儲過程,自學筆記(備份)轉

          --創建過程名稱
          --create procedure 存儲過程名字 is begin
          --create or replace procedure  如果有就替換掉
          ----------------------------------------------
          案例1:
          --創建一個表
          create table mytest(name varchar2(30),passwd varchar2(30));
          --創建過程
          create procedure sq_pro1 is
          begin
          --執行部分
          insert into mytest values('zgx','888666');
          end;

          -- / 斜線回車


          ----------------------------------------------
          如何查看錯誤信息:
          show error  回車

          --調用存儲過程
          1.exec 過程名(參數1,2....);
          2.call 過程名(參數1,2....);
          ---------------------------------------------------
          set serveroutput on;打開輸出選項
          set serveroutput off;關閉輸出選項
          dbms_  是包名的意思!
          案例2:
          dbms_output.put_line('helloWorld'); 
          -----------------
          declare
           v_ename varchar2(5);--定義字符串變量
          begin
           --into v_ename意思:把查詢出來數據 賦值給 v_ename;&no是執行的時候會彈出輸入框
           select ename into v_ename from emp where empno=&no;
           --||代表 連接符號;
           dbms_output.put_line('用戶名是:'||v_ename);
          end;
          -----------
          案例3:
          declare
           v_ename varchar2(5);--定義字符串變量
           v_sal number(7,2);--定義字符串變量
          begin
           --如果是多個字段,用逗號隔開,順序必須一樣!!
           select ename,sal into v_ename,v_sal from emp where empno=&no;
           --||代表 連接符號;
           dbms_output.put_line('用戶名是:'||v_ename||'工資:'||v_sal);
          end;
          ---------------------
          --異常的捕獲
          exception
          when no_data_found then --如果出現no_data_found異常就執行下一句
          dbms_output.put_line('輸入有誤!');
          end;
          ------------
          過程:
          案例4:
          創建帶輸入參數的過程;
          create procedure sp_pro3(spNma varchar2,newSal number) is
          begin
           update emp set sal=newSal where ename=spName;
          end;
          ------------
          函數:
          函數用于返回特定的數據,當建立函數時,在函數頭部要求有return語句;
          案例5:
          --輸入雇員姓名,返回該雇員的年薪
          --返回一個number類型;返回值名字是yearSal,類型是number(7,2);
          create function sp_fun1(spName varchar2) return number is yearSal number(7,2);
          begin
          --執行部分
          select sal*12+nvl(comm,0)*12 into yearSal from emp where enamee=spName;
          return yearSal;
          end;
          調用函數中
          --隨便定義一個值
          var abc number;
          --掉用函數把結果賦值給 abc
          call sp_fun1()'SCOTT' into:abc;
          -------------

          創建包:
          --創建了一個包 sp_package
          --聲明該包里有一個過程update_sal
          --生命該包里有一個函數annual_income
          create package sp_package is
           procedure update_sal(name,varchar2,newsal number);
           function annual_income(name varchar2, return number;
          end;
          給包sp_package 實現包體--把定義包中的 過程和函數實現;
          create package body sp_package is
          procedure update_sal(name,varchar2,newsal number) is
          begin
           update emp set sal=newsal where ename=name;
          end;
          function annual_income(name varchar2)
          return number isannual_salary number;
          begin
          select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
          return annual_salary;
          end;
          end;
          --------------
          調用包中的過程或函數
          exec sp_package.update_sal('SCOTT','120');
          ---------------------
          觸發器
          觸發器是指隱含的執行的存儲過程。當定義觸發器時,必須要指定觸發的時間和觸發的操作,常用觸發包括insert,pudate,delete語句,而觸發操作實際就是一個pl/sql塊。可以使用create trigger來建立觸發器。
          觸發器是非常有用的,可維護數據庫的安全和一致性。
          ---------
          定義并使用變量
          包括:
          1.標量類型(scalar)
          2.符合類型()

          ---------
          標量(scalar)-常用類型
          語法:
          identifier [constant] datatype [not null] [:=| default expr]
          identifier:名稱
          constant:指定常量。需要指定它的初始值,且其值是不能改變的
          datatype:數據類型
          not null: 指定變量值不能為null
          := 給變量或是常量指定初始值
          default 用于指定初始值
          expr:指定初始值的pl/sql表達式,文本值、其他變量、函數等
          ------------
          標量定義的案例
          1.定義一個變長字符串
          v_ename varchar2(10)
          2.定義一個小數 范圍 -9999.99~9999.99
          v_sal number(6,2)
          3.定義一個小數并給一個初始值為5.4 :=pl/sql的賦值號
          v_sal2 number(6,2):=5.4
          4.定義一個日期類型的數據
          v_hiredate date;
          5.定義一個布爾變量,不能為空,初始值為false
          v_valid boolean not null default false;
          ---------------
          如何使用標量
             定義好變量后,就可以使用這些變量。這里需要說明的是pl/sql塊為變量賦值不同于其他的編程語言,需要在等號前面加冒號(:=)

          下面以輸入員工號,顯示雇員名稱、工資、個人所得稅(稅率為0.03為例)。說明變量的使用,看看如何編寫:

          declare
          c_tax_rate number(3.2):=0.03; --定義賦值
          --用戶名
          v_ename varchar2(5);
          v_sal number(7,2);
          v_tax_sal number()7,2;
          begin
          --執行
          select ename,sal into v_ename,v_sal from emp where empno=$no;
          --計算所得稅
          v_tax_sal:=v_sal*c_tax_rate;
          --輸出
          dbms_output.put_line('姓名是:'||v_ename||'工資:'||v_sal||'所得稅:'||v_tax_sal);
          end;
          -----
          標量(scalar)--使用%type類型
           對于上面的pl/sql塊有一個問題:
           就是如果員工的姓名超過了5字符的話,就會有錯誤,為了降低pl/sql程序的維護工作量,可以使用%type屬性定義變量,這樣他會按照數據庫列來確定你定義的變量的類型和長度。
          看看怎么使用。
          語法: 標識符名 表名.列名%type;

           declare
            v_ename emp.ename%type; --定義變量v_ename 和emp表中列名ename大小類型保持一致;
          ---
          復合變量(composite)
          用于存放多個值的變量。
          包括:
          1.pl/sql記錄
          2.pl/sql表
          ---------------
          復合類型-pl/sql記錄
            類似與高級語言的結構體,需要注意的是,當引用pl/sql記錄成員時,必須要加記錄變量作為前綴(記錄變量.記錄成員)
          如下:
          declare
          --定義一個pl/sql記錄類型是:emp_record_type,類型包括三個數據name,salary,title;該類型中可以存放三個類型的數據;
          type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title  emp.job%type);

          --定義了一個sp_record變量,類型是emp_record_type
          sp_record emp_record_type;

          begin
          select ename,sal,job into sp_record from emp where empno=7788;
          dbms_output.put_line('員工名:'||sp_record.name); --顯示定義emp_record_type類型中 name的值;
          end;
          end;
          ----------------
          復合類型--pl/sql表
           相當于高級語言中的數組。但是需要注意的是在高級語言中數組的下標不能為負數,而pl/sql是可以為負數的,并且表元素的下標沒有限制。實例如下:
          declare
          --定義了一個pl/sql表類型sp_table_type,該類型是用于存放emp.ename%type類型的數組
          --index by binary_integer標識下標是整數
          type sp_table_type is table of emp.ename%type index by binary_integer;
          --定義了一個sp_table變量,變量類型是sp_table_type
          sp_table sp_table_type;
          begin
          --把查詢出來的ename放到 table(0)下標為0的數據
          select ename into sp_table(0) from emp where empno=7788;
          dbms_output.put_lin('員工名:'||sp_table(0)); --要和存放下標一樣
          end;
          說明:
          sp_table_type  是pl/sql表類型
          emp.ename%type 指定了表的元素的類型和長度
          sp_table       為pl/sql表變量
          sp_table(0)    表示下標為0的
          ---------------
          參照變量
          參照變量是指用于存放數值指針的變量。通過使用參照變量,可以使用得應用程序共享相同對象,從而降低占用的空間。在編寫pl/sql程序時,可以使用游標變量和對象類型變量兩種參照變量類型
          游標變量用的最多
          -----------
          參照變量---游標變量
          使用游標時,當定義游標時不需要指定相應的select語句,但是當使用游標時需要指定select語句,這樣一個游標就與一個select語句結合了。
          如下
          1.請使用pl/sql編寫一個塊,可以輸入部門號,并顯示該部門所有員工姓名和他的工資。
          declare
           --定義游標類型
           type sp_emp_cursor is ref cursor;
           --定義一個游標變量
           test_cursor sp_emp_cursor;
           --定義變量
           v_ename emp。ename%type;
           v_sal emp。sal%type;
          begin
          --執行
          --打開一個游標test_cursor和一個select結合
          open test_cursor for select ename,sal from emp where deptno=&no;
          --循環取出
          loop
           --fetch就是取出。取出test_cursor中的數據放到 v_ename,v_sal里面去;
           fetch test_cursor into v_ename,v_sal;
           --判斷是否test_cursor為空
           exit when test_cursor%notfound;
           dbms_output.put_line('名字:'||v_ename||'工資:'||v_sal);
          end loop;
          end;


          2.在1。基礎上,如果某個員工的工資低于200元,就增加100元。
          declare
           --定義游標類型
           type sp_emp_cursor is ref cursor;
           --定義一個游標變量
           test_cursor sp_emp_cursor;
           --定義變量
           v_ename emp。ename%type;
           v_sal emp。sal%type;
          begin
          --執行
          --打開一個游標test_cursor和一個select結合
          open test_cursor for select ename,sal from emp where deptno=&no;

          --循環取出
          loop
           --fetch就是取出。取出test_cursor中的數據放到 v_ename,v_sal里面去;
           fetch test_cursor into v_ename,v_sal;
           if v_sal<200 then
           update emp set sal=sal+100 where ename=v_ename;
           end if;
           --判斷是否test_cursor為空
           exit when test_cursor%notfound;
           dbms_output.put_line('名字:'||v_ename||'工資:'||v_sal);
          end loop;
          end;

           

           

          ----------
          條件分支語句
          if--then,
          if--then--else,
          if--then--elsif--else

          ----
          循環語句
          loop --end loop;至少會執行一次。
          create or replace procedure sp_pro6() is
          --定義賦值
          v_num number:=1;
          begin
          loop
           insert into users1 values(v_num,spName);
           --判斷是否要退出循環
           exit when v_num=10;
           --自增
           v_num:=v_num+1;
          end loop;
          end;


          -------------
          循環語句-while先判斷后執行
          create or replace procedure sp_pro6() is
          --定義賦值
          v_num number:=11;
          begin
          while v_num<=20 loop
           insert into users1 values(v_num,spName);
           v_num:=v_num+1;
          end loop;
          end;
          ------------------
          循環語句--for循環(不建議)
          begin
           for i in reverse 1。。10 loop
           insert into users1 values(i,'aaa');
           end loop;
          end;
          -------
          循環語句--goto,null循環(不建議)
          declare
           i int:=1;
          begin
           loop
           dbms_output.put_line('輸出i='||i);
           if i=10 then
           goto end_loop;
           end if;
           i:=i+1;
           end loop;
           <<end_loop>>  --到i到10后直接跳到該標記
          dbms_output.put_line('循環結束');
          end;
          ---------------------------
          無返回值的存儲過程(有輸入參數)
           
          create table book(
           bookId number;
           bookName varchar2(100);
           publishHouse varchar2(50);
          );
          --編寫過程
          --in表示這是一個輸入參數,不寫默認是in
          --out 表示一個輸出參數
          create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
          begin
           insert into book values(spBookId,spbookName,sppublishHouse);
          end;

          ---------------
          有返回值的存儲過程(有輸入和輸出參數)

          create or replace procedure sp_pro8(ename in number,spName out varchar2) is
          begin
           --spName自動返回 因為他是out
           select ename into spName from emp where empno=spno;
          end;
          ----------------
          有返回值是集合數組的存儲過程(有輸入和輸出參數)
          1.建立一個包
          --創建包 里面定義一個游標類型;
          create or replace package testpackage as
          type test_cursor is ref cursor;
          end testpackage;
          2.建立存儲過程。
          create or replace procedure sp_pro8(spNo in number,p_cursor out testpackage.test_cursor) is
          begin
           --spName自動返回 因為他是out
           open p_cursor for select * from emp where deptno=spNo;
          end;

          ------------
          oracle的分頁  rn是別名
          select t1.*,rownum rn from(select * from emp) t1;//多加一個列記錄個數
          select t1.*,rownum rn from(select * from emp) t1 where rownum<10;
          select * from (select t1.*,rownum rn from(select * from emp) t1 where rownum<10) where rn>=6;

          編寫oracle的分頁
          --建立一個包
          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<10'||?||') where rn>='||?||';';
          --把游標和sql語句關聯起來
          open p_cursor for v_sql;

          --計算myrows
          v_sql:='select count(*) from '||tableName||'';
          --執行sql,并把返回值,賦值給myrows;
          execute immediate v_sql int myrows;
          --計算myPagecount
          if mod(myrows,pageSize)=0 then --mod()取余數
          myPageCount:=myrows/pageSize;
          else
          myPageCount:=myrows/pagesize+1;
          end if;

          --關閉游標
          --close p_cursor;
          end;


          ------------------------
          例外的分類
          1.預定義例外用于處理常見的oracle錯誤
          2.非預定義例外用于處理預定義例外不能處理的例外  6.53
          3.自定義例外用于處理與oracle錯誤無關的其他情況

           

           


          ------------------------------------------------
          -----------------------------------------------
          -------JAVA中-調用無返回值的存儲過程-----------------
          try{
           Class.forName();
           Connection ct=DriverManager.getConnerction();
           //調用無返回值存儲過程
           CallableStatement cs=ct.prepareCall("{call 存儲過程名稱(?,?,?)}") // ?代表存儲過程參數
           cs.setIn(1,10);
           cs.setString(2,'java調用存儲過程');
           cs.setString(3,'人民出版社');
           //執行
           cs.execute();
           
          }catch(Exception e)
          {
           e.printStackTrace();
          }finally{
           cs.close();
           ct.close();
          }

          ------------------------------------------------
          -----------------------------------------------
          ------JAVA中--調用有回值的存儲過程-----------------
          try{
           Class.forName();
           Connection ct=DriverManager.getConnerction();
           //調用有返回值存儲過程
           CallableStatement cs=ct.prepareCall("{call 存儲過程名稱(?,?)}") // ?代表存儲過程參數 第一是輸入,第二是輸出
           //第一個?輸入參數
           cs.setIn(1,10);
           //給第二個?輸出值賦值
           cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //
           //執行
           cs.execute();
           //取出返回值,
           String name=cs。getString(2);
           System.out。println("名稱是:"+name);
          }catch(Exception e)
          {
           e.printStackTrace();
          }finally{
           cs.close();
           ct.close();
          }

          ------------------------------------------------
          -----------------------------------------------
          -------JAVA中-調用有回值是多個 數組2011-12-5的存儲過程-----------------
          try{
           Class.forName();
           Connection ct=DriverManager.getConnerction();
           //調用有返回值存儲過程
           CallableStatement cs=ct.prepareCall("{call 存儲過程名稱(?,?)}") // ?代表存儲過程參數 第一是輸入,第二是輸出
           //第一個?輸入參數
           cs.setIn(1,10);
           //給第二個?輸出值賦值
           cs.registerOutParameter(2,oracle.jdbc.OracleTypes.cursor); //類型是cursor游標
           //執行
           cs.execute();
           //取出返回值(結果集)
           ReaultSet rs=(ResultSet)cs.getObject(2); //2是第二?
           while(rs.next())
           {
            int =rs。getInt(1);
            String name=rs。getString(2);
            System.out。println("名稱是:"+name);
           
           }

          }catch(Exception e)
          {
           e.printStackTrace();
          }finally{
           cs.close();
           ct.close();
          }
          ------------------------------------------------
          -----------------------------------------------
          ------JAVA中--調用有回值的存儲過程-----------------
          try{
           Class.forName();
           Connection ct=DriverManager.getConnerction();
           //調用有返回值存儲過程
           CallableStatement cs=ct.prepareCall("{call 存儲過程名稱(?,?)}") // ?代表存儲過程參數 第一是輸入,第二是輸出
           //第一個?輸入參數
           cs.setIn(1,10);
           //給第二個?輸出值賦值
           cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //
           //執行
           cs.execute();
           //取出返回值,
           String name=cs。getString(2);
           System.out。println("名稱是:"+name);
          }catch(Exception e)
          {
           e.printStackTrace();
          }finally{
           cs.close();
           ct.close();
          }

          ------------------------------------------------
          -----------------------------------------------
          -------JAVA中-測試分頁調用存儲過程-----------------
          try{
           Class.forName();
           Connection ct=DriverManager.getConnerction();
           //調用有返回值存儲過程
           CallableStatement cs=ct.prepareCall("{call 分頁存儲過程名稱(?,?,?,?,?,?)}") // ?代表存儲過程參數 第一是輸入,第二是輸出
           //?輸入參數
           cs.setString(1,'表名'); //表名
           cs.setInt(2,5); //一頁顯示幾條記錄
           cs.setInt(3,1); //顯示第幾頁
           //?輸出參數
           //注冊總記錄數
           cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
           //注冊總頁數
           cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
           //注冊返回的結果集
           cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR); //類型是cursor游標

           
           //執行
           cs.execute();
           //取出總記錄數
           int rowNum=cs.getInt(4);//4表示參數中第四個?
           //總頁數
           int pageCount=cs.getInt(5);
           //返回的記錄結果
           ReaultSet rs=(ResultSet)cs.getObject(6);
           while(rs.next())
           {
            int =rs。getInt(1);
            String name=rs。getString(2);
            System.out。println("名稱是:"+name);
           
           }

          }catch(Exception e)
          {
           e.printStackTrace();
          }finally{
           cs.close();
           ct.close();
          }

          A . 嵌套表

          1. 聲明數組類型
                 create or replace type tab_array is table of varchar2(38);暫時不要在包中聲明該類型

          2. 創建存儲過程
                   -- 該例子存儲過程是在包中創建的,包名 arraydemo
                   procedure testArray(resNumber in tab_array,procResult out tab_array) is
                   begin
                       procResult := new tab_array();
                       for i in 1..resNumber.Count loop
                          procResult.EXTEND;
                          procResult(i) := resNumber(i) || 'lucifer' || i;
                       end loop;
                   end;

          3. Java調用代碼
              //必須使用Oracle的連接和Statement,使用了連接池的必須通過一些方法獲取原始的連接
              OracleConnection conn = null;
              OracleCallableStatement stmt = null;
              String[] param = { "1001", "1002", "1006" };
              stmt =(轉換類型) conn.prepareCall("{call arraydemo.testArray(?,?)}");
              // 類型名必須大寫
              ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TAB_ARRAY", conn);
              stmt.setARRAY(1, new ARRAY(descriptor,conn,param));
              stmt.registerOutParameter(2, OracleTypes.ARRAY, "TAB_ARRAY");
              stmt.execute();
            
              ARRAY array = stmt.getARRAY(2);
              Datum[] data = array.getOracleArray();
              for (int i = 0; i < data.length; i++) {
                  System.out.println(i + " : " + new String(data.shareBytes()));
              }
          4 . 注意的問題及尚未解決的問題
              拋出:Non supported character set: oracle-character-set-852 異常---解決:添加 nls_charset12.jar 到classpath,該包在oracle/ora92/jdbc/lib目錄下
              待解決問題:
              a) 如何調用在包聲明的自定義類型
              b) 比較不同聲明類型的優缺點,及使用場合
              嵌套表其它應用:http://zhouwf0726.itpub.net/post/9689/212253

          B . 索引表
          C . 內置數組
          D . 游標方式

          posted on 2012-11-15 11:28 youngturk 閱讀(853) 評論(0)  編輯  收藏 所屬分類: 數據庫方面Oracle不明白紀錄java連接數據庫解析

          <2012年11月>
          28293031123
          45678910
          11121314151617
          18192021222324
          2526272829301
          2345678

          導航

          統計

          公告

          this year :
          1 jQuery
          2 freemarker
          3 框架結構
          4 口語英語

          常用鏈接

          留言簿(6)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          相冊

          EJB學習

          Flex學習

          learn English

          oracle

          spring MVC web service

          SQL

          Struts

          生活保健

          解析文件

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 西充县| 永泰县| 兰溪市| 章丘市| 灵武市| 泰顺县| 徐水县| 封开县| 剑阁县| 汶上县| 巴中市| 凤凰县| 洞头县| 手机| 延长县| 宁陕县| 信宜市| 云霄县| 泽州县| 商洛市| 松原市| 陇南市| 昌江| 阿尔山市| 呈贡县| 盖州市| 含山县| 汉川市| 高阳县| 宁城县| 调兵山市| 长岭县| 泉州市| 海宁市| 安达市| 永泰县| 丰宁| 罗江县| 松原市| 云南省| 德安县|