Oracle 筆記（五）

標籤：dml   other   output   rom   roc   not   cut   val   color   

1、              Oracle的自訂函數

2、              Oracle的觸發器

3、              Oracle的預存程序

 

知識點一：自訂函數

文法：create [or replace] function 函數名（參數）

              return傳回值類型—必須

              is

       --變數的聲明

begin

       --執行語句

       --return 返回變數

       --exception 異常塊（return 異常編號）

              end；

Java：

int add(int no1,int no2)

{

   int sum = no1+no2;

   return sum;

}

 

Oracle

--編寫一個簡單的自訂函數 createorreplacefunction fun_add(no1 number,no2 number) returnnumber is v_sum number; begin   v_sum := no1+no2; return v_sum; exception whenothersthen return-1; end;

 

調用函數

方式一：

select fun_add(50,50) from dual;

 

方式二：

declare v_no1 number(2); v_no2 number(2); v_sum number(3); begin   v_no1 :=50;   v_no2 :=50;   v_sum := fun_add(v_no1,v_no2);   if v_sum =-1then      dbms_output.put_line(‘輸入的資料有誤！‘); else        dbms_output.put_line(v_sum); endif;   exception whenothersthen       dbms_output.put_line(‘輸入的資料有誤！‘); end;

 

知識點二：觸發器 trigger(DML觸發器)

文法：

create[or replace]  trigger tri_名字

after|before

insert|delete|update

on 表名

[for each row]—行級觸發器

begin

       --執行語句塊

end；

案例：刪除emp表資料的時候，讓emp_bak表自動備份

--刪除emp_bak表資料的時候，讓emp_bak01表自動備份 createorreplacetrigger  tri_delemp afterdelete on emp_bak foreachrow begin insertinto emp_bak01(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal, :old.comm,:old.deptno); end;

 

案例：建立userinfo（userid,uname,upw）,userid自動+1實現

步驟一：建立一個表 createtable userinfo( userid number(4) primary key, uname varchar2(10)notnullunique, upw varchar2(10)notnull )

步驟二：建立序列 create sequence seq_userid;

步驟三：建立觸發器 createorreplacetrigger tri_userid beforeinsert on userinfo foreachrow begin select seq_userid.nextval into:new.userid from dual; end;

步驟四：啟動觸發器 insert into userinfo(uname,upw) values(‘lily‘,‘123456‘);

 

案例：實現更改userinfo表的名字這個欄位的時候，對原來資料進行備份，備份到userinfo_bak表中

 

知識點三：預存程序procedure

文法：

create or replace procedure pro_預存程序名稱（輸入參數 in，輸出參數 out）

as

   --變數的聲明

begin

   --執行過程

   --exception異常處理過程

end；

案例：定義預存程序，完成userinfo表的插入功能（完成賬戶註冊這個功能）

--完成userinfo表的插入功能 createorreplaceprocedure pro_userreg( v_uname in userinfo.uname%type,v_upw in userinfo.upw%type) as begin insertinto userinfo(uname,upw)values(v_uname,v_upw); end;

執行預存程序 方法一：命令列執行 execute pro_userreg(‘jack‘,‘123456‘);

方法二：plsql執行 begin   pro_userreg(‘jack‘,‘123456‘); end;

方法三：應用程式Java，C#，php調用預存程序

 

案例二：定義預存程序完成登入

--定義預存程序完成登入 createorreplaceprocedure pro_userlog( v_uname userinfo.uname%type,v_upw userinfo.upw%type, v_result outnumber) as begin selectcount(*)into v_result from userinfo where   uname = v_uname and upw = v_upw; end;

declare v_uname userinfo.uname%type; v_upw userinfo.upw%type; v_result number(1); begin   v_uname :=‘jack‘;   v_upw :=‘123456‘;     pro_userlog(v_uname,v_upw,v_result);   if v_result =1then     dbms_output.put_line(‘登陸成功！‘); else        dbms_output.put_line(‘登陸失敗！‘); endif;   end;

 

 2017-10-31 18:37:14

Oracle 筆記（五）