PL/SQL 編程(二)

1    For迴圈
    文法：begin
            for i in reverse 1..10 loop
            insert into users values(i,’奧巴馬‘)；
            end loop；
          end;
    注意：迴圈變數 i 是隱含增加的，所以無法看到
    
2    goto語句
    goto 語句用於跳轉到特定的位置去執行語句。由於goto語句會減少程式的可讀性，所以一般情況下
    不建議使用goto語句
    
3    null語句
    null語句不會執行任何操作,但是可以增加程式的可讀性
    
4    建立返回值是一個結果集的預存程序
    (1) 建立一個包：
        SQL> create or replace package testpackage as
             type test test_cursor is ref cursor;
             end testpackage;
            
    (2) 建立預存程序
        SQL> create or replace procedure sp_procedure1
             (stuNo in number, param_cursor out testpackage.test_cursor) is
             begin
                open param_cursor for select * from emp where sutno=stuNo;
             end;
    
5    分頁
    (1) sql語句
        select * from
        (select *,rownum NO from
        (select * from emp) where rownum <=20) where rownum >=10;
        
    (2)    建立一個包
        create or replace package testpackage2 as
             type test test_cursor is ref cursor;
             end testpackage2;
            
    (3)    建立預存程序
        SQL> create or replace procedure procedureName2
            (tableName in varchar2,                    -- 表名
            pageSize in number,                        -- 每頁顯示的記錄數
            pageNow in number,                        -- 當前是第幾頁
            pageCount out number,                    -- 總頁數
            p_cursor out testpackage2.test_cursor) is
            v_sql varchar2(1000);
            v_beginNum number := (pageNow -1)* pageSize + 1;
            v_endNum number := pageNow * pageSize;
            begin
                v_sql := 'select * from (select *,rownum NO from (select * from '|| tableName ||')
                where rownum <= '|| v_endNum ||') where rownum >= '|| v_beginNum;
                open p_cursor for v_sql;
                -- 建立一個sql語句
                v_sql := 'select count(*) from ' || tableName;
                -- 執行sql語句，將結果儲存
                execute immediate v_sql into rows；
                if mod(rows,pageSize) = 0
                then pageCount := rows / pageSize;
                else
                pageCount := rows / pageSize + 1;
                end if;
                -- 關閉遊標
                close p_cursor;
            end;
    
6    異常處理
    (1) 預定義異常
    (2) 非預定義異常
    (3) 自訂異常
    
    例1
    SQL> declare v_name emp.ename%type;
         begin
         select ename into v_name from emp where empno = &no;
         dbms_output.put_line('名字：' || v_name);
         exception
            when no_data_found
            then dbms_output.put_line('編號沒有！');
         end;
        
    預定義異常
    a case_not_found
      在編寫case 語句時，如果在when子句中沒有包含必須的條件分支(沒有合格)，就會觸發case_not_found異常
    b cursor_already_open
      當重新開啟已經開啟的遊標時觸發此異常
    c dup_val_on_index
      在唯一索引所對應的列上插入重複值時觸發此異常
    d invalid_cursor
      當試圖在不合法的遊標上進行操作時觸發此異常    
    e invalid_number
      當輸入的數字無效時觸發此異常
    f too_many_rows
      當返回值不止是一條記錄時觸發此異常
    g zero_divide
      當進行 x/0,即除數為零的操作時觸發此異常
    h value_error
      當進行賦值操作時，如果變數的長度不足以儲存實際資料時觸發此異常
    i login——denide
      當使用者非法登入時會觸發此異常
    j not_logged_on
      如果使用者沒有登入就執行DML操作，就會觸發此異常
    k storage_error
      如果超出了記憶體空間，就會觸發此異常
    l timeout_on_resource
      當Oracle等待資源時，如果發生逾時情況，就會觸發此異常

    自訂異常
    SQL> create or replace procedure procedureName2(sp_empNo number) is
         MyExpception Exceptiom;        -- 自訂一個異常
         begin
         update emp set sal = sal * 1.2 where empno = &no;
         if sql%notfound then
            raise MyExpception;            -- 觸發自訂異常
         end if;
         exception
            when no_data_found
            then dbms_output.put_line('沒有更新資料！');
         end;