Oracle資料庫之第四篇_ZenDei技術網路在線

/* 授權命令 grant 許可權 to 用戶授權可以授予多個許可權 grant connect,resource to baidu 收回許可權 revoke 許可權 from 用戶 revoke dba from baidu 創建用戶分配表空間--指定用戶的預設表空間... ...
/*
      授權命令 grant 許可權 to 用戶
          授權可以授予多個許可權
          grant  connect,resource to baidu   
      收回許可權  revoke  許可權 from  用戶
          revoke dba from baidu
          
      創建用戶 分配表空間--指定用戶的預設表空間
         create  table p(..) 建表存在預設表空間
          --建表時候指定表空間 
         create table p(...) talebspace 表空間名
    */
    /*
     PlSql編程語言  procedure language 過程語言
          是在sql語句中加入一些處理過程的語句
          常見的條件表達式 if else  還有迴圈結構
     基本結構
        declare
           --聲明部分 理解為定義
              --聲明使用的變數
        begin
           --處理邏輯的代碼塊
        end;
    */
    --psSql簡單示例
    declare
       v_n number := 1; --聲明數值變數 賦值使用符號:=
       v_s varchar2(4) :='s'; --聲明字元類型變數
       emp_ename emp.ename%type ;-- 引用類型變數
       emp_row   emp%rowtype ;-- 記錄類型變數
    begin
       
       dbms_output.put_line('v_n====='||v_n); --輸出語句相當於sys out 
       dbms_output.put_line('v_s====='||v_s);
       
       select ename into emp_ename from emp where empno=7499; --使用into關鍵字賦值
       dbms_output.put_line('emp_ename====='||emp_ename);
       
       select  * into  emp_row    from emp where empno = 7499; --賦值記錄類型變數
       dbms_output.put_line('員工編號=='||emp_row.empno||'員工姓名'||emp_row.ename);
    end;
    /*
      plsql 的條件表達式判斷
      if .. 處理語句 else if ..處理語句
      -------------------------
      if .. then  
      elsif .. then  
      else  
      end if;   
    */
    ---使用條件表達式判斷員工的工資 使用漢字輸出
    declare
      
       emp_row   emp%rowtype ;-- 記錄類型變數
    begin
      
       select  * into  emp_row    from emp where empno = 7499; --賦值記錄類型變數
       --使用表達式判斷工資
       if emp_row.sal > 3000 then
          dbms_output.put_line('員工工資大於3000=='||emp_row.sal);
       elsif   emp_row.sal < 1000  then
          dbms_output.put_line('員工工資小於1000=='||emp_row.sal);
       else
           dbms_output.put_line('員工工資位於1000到3000之間=='||emp_row.sal);
       end if;
       
    end;

    /*
      迴圈結構
    第一種-----
      loop
        exit when 條件
      end loop;
    第二種 ---
      while 條件 loop
        
      end loop;
    第三種 ---
      for 變數  in  範圍 loop
        
      end loop;
    */
    -------使用迴圈輸出數字 1-----10
    /*
    第一種
      loop
        exit when 條件
      end loop;
    */
    declare
       v_n number :=1;
    begin
      
      loop 
        --只是用來判斷退出使用的,並不是相當於if()else{}
        exit when v_n>10 ;   --退出條件
        dbms_output.put_line(v_n);
        v_n:=v_n+1;   --自增
      end loop;
    end;
    /*
    第二種
      while 條件  loop
        
      end loop;
    */
    declare
       v_n number :=1;
    begin
      
      while v_n<11  loop
        dbms_output.put_line(v_n);
        v_n:=v_n+1;   --自增
      end loop;
    end;
    /*
      第三種
      for 變數 in 範圍 loop  變數的聲明和範圍的控制是由for迴圈自動執行
        
      end loop;
    */

    declare
    begin
      
      for i in 1..10  loop
        dbms_output.put_line(i);
      end loop;
    end;
    /*
      游標 游標  是用於接收查詢的記錄結果集 ResultSet 提示記錄使用.next()
      游標的使用步驟
         聲明游標 cursor 游標名 is select 語句 指定游標的記錄結果集
         打開游標  open  游標名
         提取游標  fetch 游標名 into 記錄類型變數 
         關閉游標  close cursor
      游標的兩個屬性  游標名%found     : 判斷它有找到
                      游標名%notfound  : 判斷它沒有找到
        if  emp_cursor%found then
           dbms_output.put_line('found');
         elsif  emp_cursor%notfound then
           dbms_output.put_line('notfound');
         elsif emp_cursor%notfound is null then
            dbms_output.put_line('null');
         end if;
    */
    --使用while迴圈結構演示游標
    declare
         --聲明游標
         cursor emp_cursor is select * from emp;
         --聲明記錄類型變數 用於接收游標提取的記錄
         emp_row  emp%rowtype; 
    begin
         --打開游標
         open emp_cursor; 
         --提取游標(判斷下一個是否有值)
         fetch emp_cursor into emp_row ;
         --有值就執行while迴圈
         while emp_cursor%found  loop
            dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
            --繼續提取游標(並判斷下一個是否有值)
            fetch emp_cursor into emp_row ;
         end loop;
         close emp_cursor;
    end;
    /*
     loop  
        exit when 游標提取不到
     end loop
    */
    declare
         --聲明游標
         cursor emp_cursor is select * from emp;
         --聲明記錄類型變數 用於接收游標提取的記錄
         emp_row  emp%rowtype; 
    begin
         --打開游標
         open emp_cursor; 
         loop
           fetch emp_cursor into emp_row;
           exit when emp_cursor%notfound;
             dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
         end loop;
         close emp_cursor;
    end;
    --使用游標提取某個部門的員工信息
    --聲明帶參數的游標信息
    declare
         --聲明游標
         cursor emp_cursor(dno number) is select * from emp where deptno = dno ;
         --聲明記錄類型變數 用於接收游標提取的記錄
         emp_row  emp%rowtype; 
    begin
         --打開游標 時候傳入參數
         open emp_cursor(10); 
         loop
           fetch emp_cursor into emp_row;
           exit when emp_cursor%notfound;
             dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
         end loop;
         close emp_cursor;
    end;
    /*
      錯誤信息開發中的異常
      資料庫中叫做 例外
            異常的分類 1.系統異常 系統定義好的異常
                           
                       2.自定義的異常
                         new 自定義類繼承Exception 自定義傳值（錯誤代碼，提示信息）
                         使用場景
                            不滿足某些特定業務場景，拋出自定義異常
            異常的處理
               java  try{}catche(IndexOutOfBoundException e){}catche(Exception e){}
               java  try{}catche(Exception e){} catche(IndexOutOfBoundException e){}--報錯
               資料庫可以捕捉處理異常
                     exception   關鍵字捕捉異常
                       when 異常類型 then 處理語句   判斷異常類型 處理異常
                            
    */
    --異常的簡單示例
    /*
      --除0的異常 除數為0
      --賦值錯誤
      
    */

    declare
      v_n number :=0;
      v_m number :=1;
      
    begin
      v_m:='s';  --將字元串賦值給數值變數
      v_m:= v_m/v_n;
    exception
      
      when zero_divide then 
        dbms_output.put_line('除數不能為0');
      when value_error then
        dbms_output.put_line('賦值有誤');

    end;
    ---處理太多記錄數異常
    declare
      
       emp_row   emp%rowtype ;-- 記錄類型變數
    begin
      
       select  * into  emp_row  from emp ; --賦值記錄類型
       
    exception
      
       when too_many_rows then
          dbms_output.put_line('太多記錄數'); 
        when others then    --others是最大範圍的異常 相當於java 的 Exception
         dbms_output.put_line('其他異常');
    end;
    /*
       需求 ：使用游標查詢部門下的員工信息
              如果部門下沒有員工 報錯提示
       需要自定義異常 
       變數名  exception     --聲明自定義異常
              
    */
    declare
      
       cursor emp_cursor is select * from emp where deptno= 40;  --游標結果集不存在
        emp_row   emp%rowtype ;-- 記錄類型變數
       no_dept_emp  exception ;  --聲明自定義異常
    begin
       open emp_cursor;  --打開游標
        
           fetch emp_cursor into emp_row;
           if emp_cursor%notfound then
             --沒有員工  拋出錯誤異常
             raise no_dept_emp;
           end if;
       close emp_cursor;
    exception
       when no_dept_emp then 
         dbms_output.put_line('部門下麵沒人，快招人吧');
    end;

    /*
     
      存儲過程 是一段封裝好的代碼塊，過程是編譯好放在伺服器提供開發人員調用
      
              封裝的代碼塊意義：  提升開發效率  可以復用 誰用直接調用
                                  提升運行效率 一調用直接運行
       語法：create [or repalce]   procedure 過程名稱(參數名 out|in  參數類型) 
             as|is
                 --聲明變數的部分 
             begin
                 --處理過程語句代碼塊
             end;
       調用存儲過程
          在begin和end之間使用 過程名傳參調用
    */
    --存儲過程的簡單示例  使用存儲過程給某個員工增加工資100
    create or replace procedure add_sal(eno in number )
    as
      emp_sal number :=0;
    begin
      select sal into emp_sal from emp where empno = eno ;
      dbms_output.put_line('漲工資之前是===='||emp_sal);
      update emp set sal=sal+100 where empno = eno;
      select sal into emp_sal from emp where empno = eno ;
      dbms_output.put_line('漲工資之後是===='||emp_sal);
      commit;
    end;
    --------調用存儲過程
    declare
    begin
      add_sal(7499);
    end;
    /*
      使用存儲過程統計某個員工的年薪，年薪需要返回輸出列印
      in 類型輸入參數可以 省略 預設就是輸入參數
    */
    create or replace procedure count_sal(eno number,year_sal out number)
    as
    begin
      
        select sal*12+nvl(comm,0) into year_sal  from emp where empno=eno; --使用into賦值給輸出參數

    end;
    ----調用存儲過程計算年薪
    declare
      v_emp_sal number :=0;  
    begin
      count_sal(7499,v_emp_sal);
      dbms_output.put_line('年薪為=='||v_emp_sal);
    end;
    /*
      使用存儲過程 查詢出某個部門的員工信息
      某個部門應該接受一個in類型的輸入參數
      查詢到的部門員工多條記錄返回應該使用結果集 
      聲明游標 cursor 游標名 is select 語句指定結果集
      系統引用游標
       sys_refcursor 
       聲明系統引用游標  變數名 sys_refcursor;  --不需要指定結果集
       打開游標      open 系統引用游標 for select 語句  --使用for關鍵字裝入數據
    */
    create or replace procedure dept_emp(dno number,cusor_emp out sys_refcursor)
    as
    begin
      --根據傳進來的部門編號給游標裝入結果集數據
      open cusor_emp for select * from emp  where deptno = dno;   
    end;
    ----調用存儲過程查詢部門下的員工
    declare
       cursor_emp sys_refcursor;  --聲明系統引用游標傳參使用
       emp_row emp%rowtype ;--記錄類型變數
    begin
      dept_emp(10,cursor_emp);
      --提取游標中的數據
      loop
        fetch cursor_emp into emp_row;
        exit when cursor_emp%notfound;
        dbms_output.put_line('編號'||emp_row.empno||'姓名'||emp_row.ename);
      end loop;
      close cursor_emp; 
    end;
    /*
      存儲函數 是一段封裝好的代碼塊，是編譯好放在伺服器提供開發人員調用
      
              封裝的代碼塊意義：  提升開發效率  可以復用 誰用直接調用
                                  提升運行效率 一調用直接運行
                                  
       語法：create [or repalce]   function 函數名稱(參數名 out|in  參數類型) return 數據類型
                                                    in 代表傳入參數,out 代表傳出參數                        
             as|is
                 --聲明變數的部分 
             begin
                 --處理過程語句代碼塊
                 --return 變數
             end;
       調用存儲函數
          在begin和end之間使用 函數名傳參調用 函數必須使用變數接收 返回值
      
    */
    --使用存儲函數統計某個員工的年薪
    create or replace function count_emp_sal(eno number,year_sal out number) return number
    as
      v_sal number :=0;
    begin
      
        select sal*12+nvl(comm,0) into year_sal  from emp where empno=eno; --使用into賦值給輸出參數
        return v_sal;
    end;
    --不帶out類型輸出參數統計年薪
    create or replace function count_sal_noout(eno number) return number
    as
      v_sal number :=0;
    begin
      
        select sal*12+nvl(comm,0) into v_sal  from emp where empno=eno; --使用into賦值給輸出參數
        return v_sal;
    end;
    --調用函數統計年薪
    declare
      emp_sal number:=0;
      total_sal number :=0;
    begin
       --total_sal := count_emp_sal(7499,emp_sal);
       total_sal := count_sal_noout(7499);
       dbms_output.put_line(emp_sal);--0
       dbms_output.put_line(total_sal); --統計後年薪
    end;
    /*
       存儲函數和過程的區別
       
           1.創建的關鍵字  procedure   funciton
           2.創建函數 必須使用return 聲明函數的返回變數數據類型
           3.在函數的方法體內 必須使用return 返回一個變數
           4.函數的調用 必須有變數接收返回值
           5.函數可以用在select 查詢語句中  select emp.*,count_sal_noout(empno) from emp;
       
       存儲函數和過程使用場景
           開發規範 java代碼待用過程 過程是用來處理業務邏輯代碼
           如果邏輯中需要用到一些功能性的封裝，可以調用函數
           90%情況下 函數和過程通用 過程可以調用函數，函數同樣可以調用過程
       
    */

    /*
      觸發器 是一個監視器，監視對錶中數據的操作
           如果對數據的操作滿足觸發器的執行條件，
           觸發器會自動運行
      觸發器語法：
          create or repalce trigger 觸發器名稱
          after|before   --觸發器執行時機
          insert|update|delete  --監視的動作
          on 表名       --表級觸發器
          declare
          begin          
          end;
       行級觸發器                    insert        update        delete
           :new   動作之後的記錄   要插入的記錄   修改後的記錄     空
           :old   動作之前的記錄      空          原始的記錄     原始的記錄
           
    */
    --創建觸發器監視表，如果表中有數據插入，輸出一個歡迎語句
    create or replace trigger insert_trigger
    after
    insert
    on p
    declare
       
    begin
       dbms_output.put_line('歡迎加入！');
    end;
    ----插入數據測試效果
    insert into p values(1,'zs');
    commit;
    --插入數據不能在休息日插入數據
    --休息日 周六和周日
    /*
     raise_application_error(v1,v2) v1錯誤代碼  v2是提示語句
                                      -20000 -20999 
    */
    create or replace trigger insert_no_work
    before
    insert
    on p
    declare
       v_day varchar2(10) ;
    begin
      --獲取到當前星期
      select to_char(sysdate,'day') into v_day from dual;
      --判斷星期是否在休息日
      if trim(v_day)  in ('saturday','sunday') then
      --如果休息 錯誤提示
         raise_application_error(-20001,'不能休息日插入數據');
      end if;
    end;
    ----插入數據測試效果
    insert into p values(1,'zs');
    commit;

    --使用觸發器監視表中數據修改，不能做降低工資的操作
    create or replace trigger can_not_low
    before
    update
    on emp
    for each row --行級觸發器
    declare
      
    begin
      --獲取到原始記錄的工資  --獲取修改後的工資
      if :old.sal > :new.sal then
      --談錯誤框提示
      raise_application_error(-20002,'不能降低工資');
      end if;
    end;
    --修改員工的工資測試觸發器
    update emp set sal=sal-1 where empno=7499;

    /*
      觸發器實際應用 
          使用觸發器實現 插入數據的id 自增長 面試題
    **/

    create or replace trigger auto_increment_id
    before    
    insert
    on test_trigger
    for each row 
    declare 
    begin
      --補全將要插入記錄的id
      --補全的id 是自增長的數值 如果沒有提前創建序列,需要提前創建序列   --創建序列  create sequence order_sequence 
      select order_sequence.nextval into :new.pid from dual;
    end;

    insert into test_trigger(pname,phone) values('zs','1234566');
    commit;

    package baidu;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class TestJdbc {

     String driverClass = "oracle.jdbc.driver.OracleDriver";
     String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl";
     String user= "baidu_03";
     String password = "baidu_03";
     /*
      *測試jdbc連接資料庫
      *
      * */
     @Test
     public void querEmp(){
         try{
         //載入驅動
         Class.forName(driverClass);
         //獲取鏈接
         Connection con = DriverManager.getConnection(url, user,password);
         //獲取預編譯的statement
         PreparedStatement pst= con.prepareStatement("select * from emp");
         //執行查詢
         ResultSet rs =  pst.executeQuery();
         //處理結果
         while(rs.next()){
             System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename"));
         }
         rs.close();
         con.close();
         //關閉連接
         }catch(Exception e){
             e.printStackTrace();
         }
         
     }
     /*存儲過程的調用
      * {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        add_sal(eno number,addsal number)
      * */
     @Test
     public void callAddSal(){
         try{
         //載入驅動
         Class.forName(driverClass);
         //獲取鏈接
         Connection con = DriverManager.getConnection(url, user,password);
         //獲取預編譯的statement
         CallableStatement pst= con.prepareCall("{call add_sal(?,?)}");
         pst.setInt(1, 7499);
         pst.setInt(2, 1000);
         //執行查詢
         pst.execute();
         con.close();
         //關閉連接
         }catch(Exception e){
             e.printStackTrace();
         }
         
     }
     
     /*存儲過程的調用
      * {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        count_yearsal(eno number,total_year_sal out number)
      * */
     @Test
     public void callCountSal(){
         try{
         //載入驅動
         Class.forName(driverClass);
         //獲取鏈接
         Connection con = DriverManager.getConnection(url, user,password);
         //獲取預編譯的statement
         CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}");
         pst.setInt(1, 7499);
         //註冊輸出參數
         pst.registerOutParameter(2, OracleTypes.NUMBER);
         //執行查詢
         pst.execute();
         int total =  pst.getInt(2);
         System.out.println(total);
         con.close();
         //關閉連接
         }catch(Exception e){
             e.printStackTrace();
         }
         
     }
     /*
      * pro_dept_emp(dno number,dept_emp out sys_refcursor)
      * */
     @Test
     public void callProEmp(){
         try{
         //載入驅動
         Class.forName(driverClass);
         //獲取鏈接
         Connection con = DriverManager.getConnection(url, user,password);
         //獲取預編譯的statement
         CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}");
         pst.setInt(1, 10);
         //註冊輸出參數
         pst.registerOutParameter(2, OracleTypes.CURSOR);
         //執行查詢
         pst.execute();
         OracleCallableStatement ocs = (OracleCallableStatement)pst;
         ResultSet rs =  ocs.getCursor(2);
         while(rs.next()){
             System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename"));
         }
         rs.close();
         ocs.close();
         pst.close();
         con.close();
         //關閉連接
         }catch(Exception e){
             e.printStackTrace();
         }
         
     }
}

public void show4(){
         try {
             
             Class.forName(driverClass);
             Connection con = DriverManager.getConnection(url, user,password);
             CallableStatement pst= con.prepareCall("{?= call count_sal_noout(?)}");

             //給第二個參數賦值
             pst.setLong(2, 7499);
            // stat2.setLong(2, empno);

             //聲明第一個參數的類型
             pst.registerOutParameter(1, OracleTypes.NUMBER);
             pst.execute();
             OracleCallableStatement ocs = (OracleCallableStatement)pst;
             NUMBER num = ocs.getNUMBER(1);
             System.out.println(num);
//             long i = pst.getLong(1);
//             System.out.println(i);
         
             con.close();
             
         } catch (Exception e) {
             e.printStackTrace();
         }
     }
/*