oracle--預存程序,遊標,函數,觸發器

標籤：

1.

培訓要求1）掌握PLSQL程式設計2）掌握預存程序,函數和觸發器3）瞭解一些oralceSQL語句最佳化方案-------------------------------------------------------------------------------------準備篇col empno for 9999;col ename for a10;col job for a10;col mgr for 9999;col hiredate for a12;col sal for 9999;col comm for 9999;col deptno for 99;col tname for a40;set pagesize 80;-------------------------------------------------------------------------------------SQL對比PLSQLSQL99是什麼（1）是操作所有關係型資料庫的規則（2）是第四代語言（3）是一種結構化查詢語言 (SQL)（4）只需發出合法合理的命令，就有對應的結果顯示SQL的特點（1）互動性強，非過程化（2）資料庫操縱能力強，只需發送命令，無需關注如何?（3）多表操作時，自動導航簡單，例如：     select emp.empno,emp.sal,dept.dname     from emp,dept     where emp.deptno = dept.deptno（4）容易調試，錯誤提示，直接了當（5）SQL強調結果 PLSQL是什麼     是專用於Oracle伺服器，在SQL基礎之上，添加了一些過程化控制語句，叫PLSQL     過程化包括有：類型定義，判斷，迴圈，遊標，異常或例外處理。。。     PLSQL強調過程為什麼要用PLSQL     因為SQL是第四代命令式語言，無法顯示處理過程化的業務，所以得用一個過程化程式設計語言來彌補SQL的不足之處，     SQL和PLSQL不是替代關係，是彌補關係PLSQL程式的完整組成結構如下：     [declare]          變數聲明;    變數聲明;     begin          DML/TCL操作;  DML/TCL操作;     [exception]          例外處理;  例外處理;     end;     /注意：在PLSQL程式中，；號表示每條語句的結束，/表示整個PLSQL程式結束書寫PLSQL的工具有：（1）SQLPLUS工具（2）SQLDeveloper工具（3）第三方工具（PLSQL & 其它）PLSQL與SQL執行有什麼不同：（1）SQL是單條執行的（2）PLSQL是整體執行的，不能單條執行，整個PLSQL結束用/，其中每條語句結束用；號------------------------------------------------------------------------------------PLSQL類型寫一個PLSQL程式，輸出"hello world"字串，文法：dbms_output.put_line(‘需要輸出的字串‘);begin    --向SQLPLUS用戶端工具輸出字串    dbms_output.put_line(‘hello 你好‘);end;/注意：dbms_output是oracle中的一個輸出對象put_line是上述對象的一個方法，用於輸出一個字串自動換行 設定顯示PLSQL程式的執行結果，預設情況下，不顯示PLSQL程式的執行結果，文法：set serveroutput on/off;set serveroutput on;使用基本類型變數,常量和注釋，求10+100的和declare    --定義變數    mysum number(3) := 0;    tip varchar2(10) := ‘結果是‘;begin    /*業務演算法*/       mysum := 10 + 100;    /*輸出到控制器*/    dbms_output.put_line(tip || mysum);end;/輸出7369號員工姓名和工資，格式如下：7369號員工的姓名是SMITH,薪水是800，文法：使用表名.欄位%typedeclare    --定義二個變數，分別裝姓名和工資    pename emp.ename%type;    psal   emp.sal%type;begin      --SQL語句    --select ename,sal from emp where empno = 7369;    --PLSQL語句，將ename的值放入pename變數中，sal的值放入psal變數中        select ename,sal into pename,psal from emp where empno = 7369;    --輸出    dbms_output.put_line(‘7369號員工的姓名是‘||pename||‘,薪水是‘||psal);    end;/輸出7788號員工姓名和工資，格式如下：7788號員工的姓名是SMITH,薪水是3000，文法：使用表名%rowtypedeclare    emp_record emp%rowtype;begin    select * into emp_record from emp where empno = 7788;    dbms_output.put_line(‘7788號員工的姓名是‘||emp_record.ename||‘,薪水是‘||emp_record.sal);end;/何時使用%type，何時使用%rowtype？當定義變數時，該變數的類型與表中某欄位的類型相同時，可以使用%type當定義變數時，該變數與整個表結構完全相同時，可以使用%rowtype，此時通過變數名.欄位名，可以取值變數中對應的值項目中，常用%type------------------------------------------------------------------------------------PLSQL判斷使用if-else-end if顯示今天星期幾，是"工作日"還是"休息日"declare    pday varchar2(10);begin    select to_char(sysdate,‘day‘) into pday from dual;    dbms_output.put_line(‘今天是‘||pday);    if pday in (‘星期六‘,‘星期日‘) thendbms_output.put_line(‘休息日‘);    elsedbms_output.put_line(‘工作日‘);    end if;end;/從鍵盤接收值，使用if-elsif-else-end if顯示"age<16"，"age<30"，"age<60"，"age<80"declare    age number(3) := &age;begin    if age < 16 then       dbms_output.put_line(‘你未成人‘);    elsif age < 30 then       dbms_output.put_line(‘你青年人‘);    elsif age < 60 then       dbms_output.put_line(‘你奮鬥人‘);    elsif age < 80 then        dbms_output.put_line(‘你享受人‘);    else       dbms_output.put_line(‘未完再繼‘);    end if;end;/-------------------------------------------------------------------------------------PLSQL迴圈使用loop迴圈顯示1-10declare    i number(2) := 1;begin    loop        --當i>10時，退出迴圈        exit when i>10;        --輸出i的值        dbms_output.put_line(i);        --變數自加        i := i + 1;      end loop;end;/使用while迴圈顯示1-10declare    i number(2) := 1;begin    while i<11     loop        dbms_output.put_line(i);        i := i + 1;    end loop;end;/使用while迴圈，向emp表中插入999條記錄declare    i number(4) := 1;begin     while( i < 1000 )    loop        insert into emp(empno,ename) values(i,‘哈哈‘);        i := i + 1;    end loop;   end;/使用while迴圈，從emp表中刪除999條記錄declare    i number(4) := 1;begin     while i<1000    loop        delete from emp where empno = i;        i := i + 1;    end loop;end;/使用for迴圈顯示20-30declare    i number(2) := 20;begin    for i in 20 .. 30    loop        dbms_output.put_line(i);    end loop;end;/-------------------------------------------------------------------------------------PLSQL遊標什麼是游標/遊標/cursor類似於JDBC中的ResultSet對象的功能，從上向下依次擷取每一記錄的內容使用無參游標cursor，查詢所有員工的姓名和工資【如果需要遍曆多條記錄時，使用游標cursor，無記錄找到使用cemp%notfound】declare    --定義遊標    cursor cemp is select ename,sal from emp;    --定義變數    vename emp.ename%type;    vsal   emp.sal%type;begin    --開啟遊標，這時遊標位於第一條記錄之前    open cemp;    --迴圈    loop       --向下移動遊標一次       fetch cemp into vename,vsal;        --退出迴圈,當遊標下移一次後，找不到記錄時，則退出迴圈       exit when cemp%notfound;       --輸出結果       dbms_output.put_line(vename||‘--------‘||vsal);    end loop;    --關閉遊標    close cemp;end;/使用帶參游標cursor，查詢10號部門的員工姓名和工資declare    cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;    pename emp.ename%type;    psal emp.sal%type; begin     open cemp(&deptno);    loop        fetch cemp into pename,psal;         exit when cemp%notfound;        dbms_output.put_line(pename||‘的薪水是‘||psal);    end loop;    close cemp;end;/使用無參游標cursor，真正給員工漲工資，ANALYST漲1000，MANAGER漲800，其它漲400，要求顯示編號，姓名，職位，薪水declare    cursor cemp is select empno,ename,job,sal from emp;    pempno emp.empno%type;    pename emp.ename%type;    pjob   emp.job%type;    psal   emp.sal%type;begin    open cemp;    loop        fetch cemp into pempno,pename,pjob,psal;        --迴圈允出準則一定要寫        exit when cemp%notfound;        if pjob=‘ANALYST‘ then            update emp set sal = sal + 1000 where empno = pempno;        elsif pjob=‘MANAGER‘ then            update emp set sal = sal + 800 where empno = pempno;        else     update emp set sal = sal + 400 where empno = pempno;        end if;    end loop;    commit;    close cemp;end;/-------------------------------------------------------------------------------------PLSQL例外使用oracle系統內建例外，示範除0例外【zero_divide】declare    myresult number;begin    myresult := 1/0;    dbms_output.put_line(myresult);exception    when zero_divide then  dbms_output.put_line(‘除數不能為0‘); delete from emp;  end;/使用oracle系統內建例外，查詢100號部門的員工姓名，示範沒有找到資料【no_data_found】declare    pename varchar2(20);begin    select ename into pename from emp where deptno = 100;    dbms_output.put_line(pename);exception    when NO_DATA_FOUND then  dbms_output.put_line(‘查無該部門員工‘); insert into emp(empno,ename) values(1111,‘ERROR‘);end;/使用使用者自訂例外，使用游標cursor，查詢10/20/30/100號部門的員工姓名，示範沒有找到資料【nohave_emp_found】------------------------------------------------------------------------------------預存程序概念什麼是預存程序【procedure】？為什麼要用預存程序？    （1）PLSQL每次執行都要整體運行一遍，才有結果    （2）PLSQL不能將其封裝起來，長期儲存在oracle伺服器中    （3）PLSQL不能被其它應用程式調用，例如：Java預存程序與PLSQL是什麼關係？--------------------------------------------------------預存程序建立無參預存程序hello，無傳回值，文法：create or replace procedure 過程名 as PLSQL程式刪除預存程序hello，文法：drop procedure 過程名調用預存程序方式一，exec 預存程序名調用預存程序方式二，PLSQL程式調用預存程序方式三，Java程式建立有參預存程序raiseSalary(編號)，為7369號員工漲10%的工資，示範in的用法，預設in，大小寫不敏感建立有參預存程序findEmpNameAndSalAndJob(編號)，查詢7788號員工的的姓名，職位，月薪，返回多個值，示範out的用法什麼情況下用exec調用，什麼情況下用PLSQL調用預存程序？用預存程序，寫一個計算個人所得稅的功能-------------------------------------------------------------------------------------儲存函數建立無參儲存函數getName，有傳回值，文法：create or replace function 函數名 return 傳回型別 as PLSQL程式段刪除儲存函數getName，文法：drop function 函數名調用儲存函數方式一，PLSQL程式調用儲存函數方式二，Java程式建立有參儲存函數findEmpIncome(編號)，查詢7369號員工的年度營收，示範in的用法，預設in建立有參儲存函數findEmpNameAndJobAndSal(編號)，查詢7788號員工的的姓名(return)，職位(out)，月薪(out)，返回多個值-------------------------------------------------------------------------------------過程函數適合情境聲明：適合不是強行要你使用，只是優先考慮什麼情況下【適合使用】預存程序？什麼情況下【適合使用】儲存函數？【適合使用】預存程序： 【適合使用】儲存函數：    什麼情況【適合使用】過程函數，什麼情況【適合使用】SQL？【適合使用】過程函數：    》需要長期儲存在資料庫中            》需要被多個使用者重複調用            》商務邏輯相同，只是參數不一樣    》批操作大量資料，例如：批量插入很多資料【適合使用】SQL：    》凡是上述反面，都可使用SQL    》對錶，視圖，序列，索引，等這些還是要用SQL                 -------------------------------------------------------------------------------------觸發器oracle常用命令:hostcls 清屏rollback 復原 後必須執行 commit 提交show recyclebin;查看資源回收筒flashback table emp to before drop;--閃回drop table emp purge;--徹底刪除表什麼是觸發器【Trigger】？不同的DML(SELECT/UPDATE/DELETE/INSERT)操作,觸發器能夠進行一定的攔截,合格操作,才能操作基表,否則不能操作基表,類似於javaweb中的Filter,Struts2的Interceptor為什麼要用觸發器？ 如果沒有觸發器,那麼DML所有操作,均可無限制的操作基表建立語句級觸發器insertEmpTrigger，當對錶【emp】進行增加【insert】操作前【before】，顯示"hello world"create or replace trigger insertEmpTriggerbefore inserton empbegindbms_output.put_line(‘hello world‘);end;/使用insert語句插入一條記錄，引起insertEmpTrigger觸發器工作insert into emp(empno,ename,sal) values(‘1111‘,‘張三‘,7000);//insert的時候會觸發觸發器,進入前會列印helloworld使用insert語句插入N條記錄，引起insertEmpTrigger觸發器工作insert into emp select * from xxx_emp;刪除觸發器insertEmpTrigger，文法：drop trigger 觸發器名drop trigger insertEmpTrigger;建立語句級觸發器deleteEmpTrigger，當對錶【emp】進行刪除【delete】操作後【after】，顯示"world hello"create or replace trigger deleteEmpTriggerafter deleteon empbegindbms_output.put_line(‘你好世界‘);end;/使用delete語句刪除一條記錄，引起deleteEmpTrigger觸發器工作delete from emp where empno = 1111;//注意按理說是先顯示已刪除一行,後顯示你好世界,現實是反的使用delete語句刪除N條記錄，引起deleteEmpTrigger觸發器工作delete from emp where 1=1;星期一到星期五，且9-20點能向資料庫emp表插入資料，否則使用函數拋出異常,文法：raise_application_error(‘-20000‘,‘例外原因‘)create or replace trigger securityTriggerbeforeinsertdeclarepday varchar2(12);phour number(2);--關係運算最好用數字beginselect to_char(sysdate,‘day‘) into pday from dual; --擷取星期select to_char(sysdate,‘hh24‘) into phour from dual;--擷取時間if pday in(‘星期六‘,‘星期日‘) or phour not between 7 and 23 then --業務  raise_application_error(‘-20000‘,‘只有工作日且工作日7-23點才能插入資料‘)--拋例外end if;end;/--結束標誌測試: insert into emp(empno,ename,sal) values(‘2222‘,‘張三‘,7000); --還會列印helloworld 因為insert觸發器還在呢建立行級觸發器checkSalaryTrigger，漲後工資這一列，確保大於漲前工資，文法：for each row/:new.sal/:old.salcreate or replace trigger checkSalaryTriggerafter update of salon empfor each rowbeginif :new.sal <= :old.sal --如果漲後工資沒有比以前高的話拋異常raise_application_error(‘-20200‘,‘工資不能越漲越低啊‘)--拋例外end if;end;/update emp set sal = sal - 1 where empno = 7369 ;刪除觸發器，表還在嗎？drop trigger checkSalaryTrigger; 表還在將表丟到資源回收筒，觸發器還在嗎？drop table emp;  還在!show recyclebin;查看資源回收筒當閃回表後，觸發器會在嗎？flashback table emp to before drop;--閃回, 還在!徹底刪除表，觸發器會在嗎？drop table emp purge;--徹底刪除表不存在測試:建立一個新表和被刪的emp同名create table emp as select * from xxx_emp;select * from emp;insert into emp(empno,ename,sal) values(‘4545‘,‘小六子‘,80000);-------------------------------------------------------------------------------------oracleSQL最佳化方案為什麼要Oracle最佳化：       隨著實際項目的啟動，Oracle經過一段時間的運行，最初的Oracle設定，會與實際Oracle運行效能會有一些差異，這時我們       就需要做一個最佳化調整。Oracle最佳化這個課題較大，可分為四大類：       》主機效能       》記憶體使用量效能       》網路傳輸效能       》SQL語句執行效能【程式員】下面列出一些oracleSQL最佳化方案：（01）選擇最有效率的表名順序（筆試常考）       ORACLE的解析器按照從右至左的順序處理FROM子句中的表名，       FROM子句中寫在最後的表將被最先處理，      在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表放在最後，      如果有3個以上的表串連查詢,那就需要選擇那個被其他表所引用的表放在最後。      例如：查詢員工的編號，姓名，工資，工資等級，部門名      select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname      from salgrade,dept,emp      where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)        1)如果三個表是完全無關係的話，將記錄和列名最少的表，寫在最後，然後依次類推      2)如果三個表是有關係的話，將引用最多的表，放在最後，然後依次類推（02）WHERE子句中的串連順序（筆試常考）        ORACLE採用自右而左的順序解析WHERE子句,根據這個原理,表之間的串連必須寫在其他WHERE條件之左,      那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的之右。        例如：查詢員工的編號，姓名，工資，部門名        select emp.empno,emp.ename,emp.sal,dept.dname      from emp,dept      where (emp.deptno = dept.deptno) and (emp.sal > 1500)     （03）SELECT子句中避免使用*號      ORACLE在解析的過程中,會將*依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味著將耗費更多的時間      select empno,ename from emp;（04）使用DECODE函數來減少處理時間      使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表（05）整合簡單，無關聯的資料庫訪問（06）用TRUNCATE替代DELETE   （07）盡量多使用COMMIT      因為COMMIT會釋放復原點（08）用WHERE子句替換HAVING子句      WHERE先執行，HAVING後執行     （09）多使用內建函式提高SQL效率     （10）使用表的別名      salgrade s     （11）使用列的別名      ename e（12）用索引提高效率      在查詢中，善用索引      （13）字串型，能用=號，不用like      因為=號表示精確比較，like表示模糊比較 （14）SQL語句用大寫的      因為Oracle伺服器總是先將小寫字母轉成大寫後，才執行      在eclipse中，先寫小寫字母，再通過ctrl+shift+X轉大寫;ctrl+shift+Y轉小寫（15）避免在索引列上使用NOT      因為Oracle伺服器遇到NOT後，他就會停止目前的工作，轉而執行全表掃描（16）避免在索引列上使用計算      WHERE子句中，如果索引列是函數的一部分，最佳化器將不使用索引而使用全表掃描，這樣會變得變慢       例如，SAL列上有索引，      低效：      SELECT EMPNO,ENAME      FROM EMP       WHERE SAL*12 > 24000;      高效：      SELECT EMPNO,ENAME      FROM EMP      WHERE SAL > 24000/12;（17）用 >= 替代 >      低效：      SELECT * FROM EMP WHERE DEPTNO > 3         首先定位到DEPTNO=3的記錄並且掃描到第一個DEPT大於3的記錄      高效：      SELECT * FROM EMP WHERE DEPTNO >= 4        直接跳到第一個DEPT等於4的記錄（18）用IN替代OR      select * from emp where sal = 1500 or sal = 3000 or sal = 800;      select * from emp where sal in (1500,3000,800);（19）總是使用索引的第一個列      如果索引是建立在多個列上，只有在它的第一個列被WHERE子句引用時，最佳化器才會選擇使用該索引      當只引用索引的第二個列時，不引用索引的第一個列時，最佳化器使用了全表掃描而忽略了索引      create index emp_sal_job_idex      on emp(sal,job);      ----------------------------------      select *      from emp        where job != ‘SALES‘;      （20）避免改變索引列的類型，顯示比隱式更安全       當字元和數值比較時，ORACLE會優先轉換數實值型別到字元類型       select 123 || ‘123‘ from dual;          總之，Oracle最佳化不是一天的課題，你得在長期工作實踐中，進行反覆測試與總結，希望學員們日後好好領會

　　

oracle--預存程序,遊標,函數,觸發器