oracle-PL/SQL2

標籤：修改   tin   模式   creat   記錄   alter   info   異常處理   number   

一 預存程序

預存程序是SQL 陳述式和可選控制流程語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。在 ORACLE SERVER上建立預存程序,可以被多個應用程式調用，可以向預存程序傳遞參數,也可以向預存程序傳回參數。

預存程序儲存在資料庫內，可由應用程式通過一個調用執行，而且允許使用者聲明變數、有條件執行以及其它強大的編程功能。

預存程序在建立時即在伺服器上進行編譯，所以執行起來比單個SQL語句快。

1.1 預存程序的優點

（1）預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。

（2）當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。

（3）預存程序可以重複使用,可減少資料庫開發人員的工作量。

（4）安全性高,可設定只有某此使用者才具有對指定預存程序的使用權。

文法

      CREATE [OR REPLACE] PROCEDURE Procedure_name[ (argment [ { IN | IN OUT }] Type,argment [ { IN | OUT | IN OUT } ] Type ]{ IS | AS }<類型.變數的說明>BEGIN<執行部分>EXCEPTION       可選的異常錯誤處理程式>END; 

第一個案例(建立一個簡單的預存程序)

例如：向emp表中插入資料（111,’張三’）

create procedure pro1 isbegininsert into emp(empno, ename)values(111,‘張三‘);end;調用預存程序：exec pro1帶參數的預存程序：刪除emp表中指定員工編號的員工資訊。create procedure pro2(in_empno number) isbegindelete from emp where empno = in_empno;end;調用：exec pro2(111);練習：建立一個預存程序，根據員工編號，顯示員工的薪資和所在部門的平均工資create or replace procedure pro1(v_in_empno in number,v_out_mysal out number, v_out_avgsal out number)isbegin  select sal into v_out_mysal from emp where empno=v_in_empno;  select avg(sal) into v_out_avgsal from empwhere deptno=(select deptno from emp where empno=v_in_empno);end; 調用執行：declare  v_mysal number;  v_avgsal number;begin  pro1(7902,v_mysal,v_avgsal);  dbms_output.put_line(v_mysal||‘  ‘||v_avgsal);end;

二 函數

函數一般用於計算和返回一個值，可以經常需要進行的計算寫成函數。函數的調用是運算式的一部分，而調用過程是一條PL/SQL語句。

2.1 函數的結構

聲明部分：包括類型、變數、遊標

執行部分：完成功能而編寫的SQL語句或是PL/SQL代碼塊

異常處理部分：

函數與過程在建立的形式是有些相似，也是編譯後放在記憶體中供使用者使用，只不過調用函數時要用運算式，而不像過程只需要用過程名。另外，函數必須有一個傳回值，而過程則沒有。

2.2 建立函數

函數用於返回特定的資料，當建立函數時，在函數頭部必須包含return子句，而在函數體內必須包含return語句返回的資料。

文法：

create  or  replace  function 函數名(參數1,...)return 資料類型 is       定義變數;begin       執行語句;end;/

舉例：定義函數，輸入工作名，返回負責該工作的員工數

--建立函數CREATE OR REPLACE Function GETCOUNT(Major IN varchar2 )--聲明傳回型別return number isf_count number;BEGIN--使用INTO 語句將結果賦值給變數select count(*) into f_count from students where major=’Major’ ;--使用RETURN 語句返回return f_count;  --返回結果END;

 函數調用

方式一：--調用函數--DECLARE--聲明變數接收函數的傳回值          v_count number;BEGIN           v_count:=GETCOUNT(‘MUSIC‘);            Dbms_Output.put_line(v_count);END;方式二：var 變數名 變數類型call 函數名(參數值,...) into :變數名;print 變數名方式三：select 函數名(參數,...) from dual;

2.3 刪除函數

文法：

DROP FUNCTION 函數名

案例

請編寫一個函數，可以接收使用者名稱並返回該使用者的年薪。create function inName_outSal(v_in_name varchar2)return number is        v_annual_sal number;begin       select (sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=v_in_name;       return v_annual_sal;end;/

2.4 函數和過程的區別

 

1．函數必須有傳回值，而過程可以沒有；2．函數和過程在java中調用的方式不一樣；3．java中調用oracle函數可以在select語句中直接調用，如：select 自訂的函數名(參數) from 表;過程則是使用CallableStatement完成調用。

 

三 遊標

遊標：用來處理使用select語句從資料庫中檢索到的多行記錄的工具

分類：

1）    顯示遊標

返回多條記錄時，使用顯示遊標逐行讀取

2）    隱式遊標

PL/SQL自動為DML語句建立隱式遊標，包含一條返回記錄

3.1 遊標的屬性

屬性名稱	說  明
%found	用於檢驗遊標是否成功，通常在FETCH語句前使用，當遊標按照條件查詢出一條記錄時，返回true
%isopen	判斷遊標是否處於開啟狀態，試圖開啟一個已經開啟或者已經關閉的遊標，將會出現錯誤
%notfound	與%found的作用相反，當按照條件無法查詢到記錄時，返回true
%rowcount	迴圈執行遊標讀取資料時，返回檢索出的記錄資料的行數

3.2 遊標的使用

1）   遊標的聲明

文法：

CURSOR cursor_name [ ( parameter [ , parameter]……)]

[ RETURN return_type ] IS selectsql

說明：

CURSOR：用於聲明一個遊標

parameter：選擇性參數，用於指定參數類型、模式等

return：可選，指定遊標的傳回型別

selectsql：需要處理的select語句，不能含INTO子句

2）開啟遊標

文法： open test_cursor  使用OPEN語句開啟一個遊標

3）提取遊標

文法：

FETCH cursor_name INTO variable_list

說明：

使用FETCH語句實現對遊標內容的讀取

variable_list必須與從遊標提取的結果集類型相同

4）關閉遊標

文法：

close cursor_name

說明：

使用CLOSE語句關閉一個遊標

關閉遊標後，所有資源都將被釋放，且不能再次被開啟

樣本：遊標的使用

1）讀取一條資料

例如：查詢員工標號為：7369的員工姓名和薪水；

DECLARE     v_ename varchar2(20);     v_sal emp.sal%type;     --聲明一個遊標     CURSOR c_emp is select ename, sal from emp where empno=7369 ;BEGIN     --開啟遊標    OPEN c_emp;    - - 判斷遊標是否返回記錄    if c_emp %NOTFOUND THEN           dbms_output.put_line(‘沒有找到相應的記錄‘);    else    - -從遊標中讀取記錄          fetch c_emp into v_ename, v_sal;          dbms_output.put_line(v_ename ||‘薪水為‘|| v_sal);    end if;   CLOSE c_emp;END;

2）讀取多條記錄：

使用FETCH語句只能讀取一行記錄，那麼如何讀取多行記錄呢？

讀取多行記錄時，可以採用迴圈的方式：LOOP迴圈、while迴圈和FOR迴圈

例如： 查詢薪水小於3000的所有人的姓名和薪水？

適用for 迴圈，自動開啟遊標，而無需使用open語句

DECLARE        v_ename varchar2(20);        v_sal emp.sal%type;        CURSOR c_emps is select ename, sal from emp where sal<=3000 ;BEGIN       FOR c_emp IN c_emps LOOP         V_ename:=c_emp.ename;         V_sal:=c_emp.sal;         dbms_output.put_line(‘員工姓名:‘||v_ename||‘薪水為‘||v_sal);       END LOOP;END;

說明：

1.使用for迴圈時，自動開啟遊標，而無需使用OPEN語句

2.PL/SQL會自動對變數進行隱式聲明

3.當迴圈結束後，遊標會總動關閉

3.3 練習

l  需求說明：

l  編寫PL/SQL語句，實現使用遊標從表中讀取出‘SMITH’的相關資訊（姓名，編號，部門名稱）

l  提示：

l  使用OPEN開啟遊標

l  對遊標內容進行判斷

l  使用FETCH進行讀取

l  關閉遊標

declare    v_ename emp.ename%type;    v_deptno   emp.deptno%type;    v_dname  dept.dname%type;--聲明一個遊標    cursor c_emp is select ename,emp.deptno,dnamefrom emp,deptwhere ename=‘SMITH‘ and emp.deptno=dept.deptno; begin--開啟遊標   open c_emp;--判斷遊標是否返回記錄   if c_emp%NOTFOUND then     dbms_output.put_line(‘沒有找到相應的記錄!‘);     else       --從遊標中讀取記錄       fetch c_emp into v_ename,v_deptno,v_dname ;       dbms_output.put_line(v_ename||‘部門編號：‘||v_deptno||‘部門名稱：‘||v_dname);       end if;--關閉遊標   close c_emp;   end;

 

l  需求說明：

l  在上一練習基礎上，實現讀取‘smith’所在部門員工資訊並顯示

l  提示：使用FOR迴圈實現遊標的迴圈讀取

create or replace procedure p3 is   v_ename varchar2(20);   v_deptno number(4);   v_dname varchar2(20);     cursor c_emps is select ename,dept.deptno,dname                    from emp ,dept                    where emp.deptno=dept.deptno                          and emp.deptno = (select deptno                                            from emp                                            where ename=‘SMITH‘);   begin     for c_emp in c_emps loop       v_ename:= c_emp.ename;       v_deptno:=c_emp.deptno;       v_dname:= c_emp.dname;       dbms_output.put_line(v_ename||‘部門編號‘||v_deptno||‘部門名稱：‘||v_dname);       end loop;    end;

 

四 觸發器

先思考幾個問題：

（1）當一個使用者登入到Oracle時，在一張表中記錄登入到Oracle的使用者名稱和登入時間等資訊，怎麼辦？

（2）禁止使用者在星期天對某一張表進行刪除操作，怎麼辦？

（3）當使用者在刪除一張表的時候，自動把刪除的記錄備份到另一張表裡去……

很多關係型資料庫都提供了一種技術，可以在使用者進行某種操作的時候，自動地進行另外一種操作，我們把這種技術成為觸發器技術。

觸發器是指存放在資料庫中，被隱含執行的預存程序，可以支援DML觸發器，還支援基於系統事件（啟動資料庫、關閉資料庫、登入資料庫以及DDL操作建立觸發器）。

4.1 觸發器的分類

l  DML觸發器（insert 、delete 、update觸發器）

l  DDL觸發器（create table、create view 、drop table……）

l  系統觸發器（與系統相關的觸發器，比如使用者登入、退出、啟動資料庫、關閉資料庫）

4.2 建立觸發器

文法：

Create [or replace] trigger trigger_name   –觸發器名字{before| after}                        —在操作前還是後{insert | delete | update} –進行增刪改查的哪一種操作On [schema.] table_name               --在哪個使用者的哪張表中[for each row]                        --每一行   BeginTrigger_body;End;

 

after—是在記錄操縱之後觸發，是先完成資料的增刪改，再觸發，觸發的語句晚於監視的增刪改操作;

before—是在記錄操縱之前觸發，是先完成觸發，再增刪改，觸發的語句先於監視的增刪改。

4.3 DML觸發器

案例1：在某張表（u1）添加一條資料的時候，提示“ 添加了一條資料”。

create table u1(id number,name varchar2(20));create or replace trigger t1after insert on u1begin  dbms_output.put_line(‘你在u1表中插入了一條資料！‘);  end;insert into u1 values(1,‘張三‘);

 

案例2：在某張表(emp)修改多條資料的時候，提示多次“修改了資料”。

如果這樣寫，只會輸出一句話。（語句級的寫法）

create or replace trigger t1after update on scott.empbegin    dbms_output.put_line(‘修改了一條資料‘);end;--執行更新操作update emp set sal = sal +10;應該這樣寫，才會輸出多條語句：（變為行級寫法）create or replace trigger t1after update on scott.empfor each row--代表行級觸發器begin    dbms_output.put_line(‘修改了一條資料‘);end;--執行更新操作update emp set sal = sal +10;

 

案例3：

為了禁止工作人員在休息日刪除員工的資訊，開發人員可以建立before語句觸發器，從而實現資料的安全。

create or replace trigger t1before delete on scott.empbeginif to_char(sysdate,‘day‘)in(‘星期六‘,‘星期日‘) then     dbms_output.put_line(‘休息日不能刪除員工資訊！‘);     raise_application_error(-20001,   ‘對不起，休息日不能刪除員工！‘);end if;end;--執行刪除操作，拋出錯誤資訊。delete from emp where empno  =7499;

 

說明：

1）raise_application_error ( error_number number,  error_msg varchar2)系統已經定義好的一個預存程序，讓系統報錯。

2）Error_number [自訂]從-20000到-20999之間，這樣就不會與Oracle的任何錯誤碼發生衝突。Error_msg_in[自訂]的長度不能超過2K，否則截取2K，是錯誤的提示資訊;

使用條件謂詞：

當觸發器中同時包含多個觸發事件(insert 、update、delete)時，為了在觸發器代碼中區分具體的觸發事件，可以使用三個條件:inserting、updating、deleting。

案例3：使用者動作表u1時，給出提示，明確提示使用者是進行的insert、update還是delete操作。

create or replace trigger t1before delete or update or insert on scott.u1begincasewhen inserting then         dbms_output.put_line(‘您進行中插入操作！‘);when updating then         dbms_output.put_line(‘您進行中修改操作！‘);when deleting then         dbms_output.put_line(‘您進行中刪除操作！‘);end case;end;--執行插入操作insert into u1(empno, ename)values(1,‘張三‘);--刪除操作delete from emp where empno =7499;--更新操作update emp set ename =‘張三‘where empno =7499;

 

 

4.4 :old和 :new關鍵字

思考：

當觸發器被觸發時，要使用被插入、刪除或修改的記錄中的列值，有時要使用操作前、後列的值。

:old修飾符訪問操作完成前列的值

:new 修飾符訪問操作

 

 

案例4：在修改emp表僱員的薪水時，顯示僱員工資修改前和修改後的值。如何確保在修改員工工資不能低於原有工資。

create or replace trigger t1before update on scott.empfor each rowbeginif :new.sal < :old.sal then dbms_output.put_line(‘更新後的工資值小於更新前的值！不能這樣操作！‘); raise_application_error(-20005,‘更新後的工資值 小於更新前的值！不能這樣操作！‘);else dbms_output.put_line(‘更新前工資為:‘||:old.sal ||‘更新後工資為：‘||:new.sal);endif;end;--執行update操作update emp set sal =2 where empno =7499;update emp set sal =7000 where empno =7499;

 

案例5：編寫一個觸發器，保證當使用者在刪除一張表（emp）記錄的時候，自動將刪除的記錄備份到另一張表(u1)中。

create or replace trigger t2before delete on scott.empfor each rowbegininsert into u1(id, ename)values(:old.empno,:old.ename);end; delete from emp where empno =7499;

 

 

4.5 系統觸發器

系統觸發器是指基於Oracle事件（例如使用者登入logon、logoff和資料庫的啟動和關閉startup、shutdown）所建立的觸發器，通過使用系統事件觸發器，提供了跟蹤系統或者是資料庫變化的機制;

下面介紹一些常用的系統事件屬性函數，和建立各種事件觸發程序的方法，在建立系統事件觸發器時，我們需要使用事件屬性函數，常用的時間屬性函數如下：

ora_client_ip_address  //返回用戶端的ip

ora_database_name   //返回資料庫名

ora_login_user       //返回登入使用者名稱

ora_sysevent        //返回觸發觸發器的系統事件名

ora_des_encrypted_password  //返回使用者des(md5)加密後的密碼

案例：建立登入或者退出觸發器

文法：

create or replace trigger 觸發器名字after [before] logon [logoff] on databasebegin--執行語句……;End;

 

案例：

完成登入觸發器和退出觸發器的建立。

為了記錄使用者的登入和退出事件，我們可以建立登入或者退出觸發器。為了記錄使用者名稱稱、時間、ip地址，我們首先建立一張資訊表：

開啟命令列視窗：Sqlplus system/[email protected] as dba;--建立一張儲存登入或者退出系統資訊的表。Create table log_table( username varchar2(20),logon_time date,logoff_time date, address varchar2(20));--建立登入觸發器：Create or replace trigger mytrigger1After logon on databaseBeginInsert into log_table(username, logon_time , address)values(ora_login_user, sysdate, ora_client_ip_address);End;--建立退出觸發器：Create or replace triggerBefore logoff on databaseBeginInsert into log_table(username, logoff_time, address)values(ora_login_user, sysdate , ora_client_ip_address);End;

 

4.6 DDL觸發器

DDL簡單說就是我們平常使用的create、alter和drop這些資料定義語句。

文法：

create or replace trigger 觸發器的名字before|After ddl|create|alter|drop  on 使用者名稱.schema—如scott.schemaBegin--執行語句……;End;

 

案例：

編寫一個觸發器，可以記錄某個使用者進行的DDL操作。

為了記錄系統發生的ddl事件，應該建立一張表(my_ddl_record)用來儲存相關的資訊

注意：

需要使用system使用者登入: 

conn system/manager as sysdba;Create table my_ddl_record (event varchar2(64),username varchar2(64),ddl_time date);--建立觸發器Create or replace trigger mytrigger2After ddl on scott.schemaBegin       Insert into my_ddl_record values(ora_sysevent, ora_login_user, sysdate);End;

 

注意：在Oracle中，DML的事務需要手動提交，DDL語句不需要進行手動提交。我們在寫DML語句時，如果沒有手動提交，在退出控制台的時候也會自動認可。

4.7 觸發器管理

禁止觸發器

指讓觸發器臨時失效。

Alter trigger 觸發器名 disable;

啟用觸發器

Alter trigger 觸發器名 enable;

刪除觸發器

Drop trigger 觸發器名;

oracle-PL/SQL2