Oracle編程( 其三)

一、PL/SQL編程語言

• pl/編程語言是對sql語言的擴展，使得sql語言具有程序化編譯的特性，
• pl/編程語言比一般的程序化編程語言，更加靈活高效，
• pl/編程語言主要用來撰寫存盤程序和存盤函式

1、宣告方法

賦值操作 ： （：=）或者（into查詢陳述句）兩種方式賦值，
例：參考型變數（type）和記錄型變數（rowtype）的使用

set serveroutput on
declare
    i number(2) :=10;
    s varchar2(10) :='小明';
    ena emp.ename%type;-- 參考型變數
    emprow emp%rowtype;--記錄型變數
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno=7499;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno=7499;
    dbms_output.put_line(emprow.ename||'的作業為：' ||emprow.job);
end;

2、pl/sql中的if判斷陳述句

通用句式：(條件陳述句中必須要以end if結束，并且只有這里加分號、可以省去elsif和else，類似于java)

declare
begin
  if  	then
  elsif 	 then 
  else
  end if;
end;

例：
輸入小于18的數字，輸出未成年
輸入小于40大于18的數字，輸出中年人
輸入大于40的數字，輸出老年人

set serveroutput on
declare
  x number(3):=&xx;
begin
  if x<18 then
    dbms_output.put_line('未成年');
  elsif x<40 then 
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

3、pl/sql中的loop回圈

三種回圈方式輸出1-10
1、while回圈

set serveroutput on
declare
 i number(2):=1;
begin
  while i<11 loop
    dbms_output.put_line(i);
    i :=i+1;
  end loop;
end;

2、exit回圈（重點掌握）

set serveroutput on
declare 
 i number(2):=1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i:=i+1;
  end loop;

end;
3、for回圈

set serveroutput on
declare
  i number(2):=1;
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

三種回圈輸出結果相同：

3、游標（cursor）

游標：可以存放多個物件，多行記錄，

例：輸出emp表中所有員工的姓名，

set serveroutput on
declare
  cursor c1 is select * from emp;
  emprow emp%rowtype;
begin
  open c1;
    loop
      fetch c1 into emprow;
      exit when c1%notfound;
      dbms_output.put_line(emprow.ename);
    end loop;
  close c1;
end;

例：給制定部門員工漲工資

set serveroutput on
declare
  cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;
  en emp.empno%type;
begin
  open c2(10);
    loop
      fetch c2 into en;
      exit when c2%notfound;
      update emp set sal=sal+100 where empno=en;
      commit;
    end loop;
  close c2;
end;

查詢emp表中部門號為10的員工資訊

再一次查詢emp表就會發現給部門號為10的漲了100工資，

4、存盤程序（procedure）

存盤程序：提前已經編譯好的一段pl/sql語言，放置在資料庫端，可以直接被呼叫，（這一段pl/sql一般都是固定步驟的業務）
例：給指定員工漲100工資

create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set sal=sal+500 where empno=eno;
  commit;
end;

or replace作用：沒有時 --> 創建這個程序，有重名時 --> 替換為現在的，（保證能夠每次都創建、使用or replace時，其它用戶在該函式上的權限不會丟失和變化）

測驗p1：

declare

begin
  p1(7499);
end;

out型別引數使用方法：
例：使用存盤程序來計算年薪

create or replace procedure p_yearsal(eno emp.empno%type,yealsal out number)
is
  s number(10);
  c emp.comm%type;
begin
  select sal*12,nvl(comm,0) into s,c from emp where empno=eno;
  yealsal := s+c;
end;

測驗P_YEARSAL：

declare
  yealsal number(10);
begin
  p_yearsal(7499,yealsal);
  dbms_output.put_line(yealsal);
end;

in 和 out 型別引數的區別是什么？
凡是涉及到into查詢陳述句賦值、:= 操作的引數，都必須使用out來修飾，

5、存盤函式（function）

存盤程序和存盤函式的引數都不能帶長度
存盤函式的回傳值型別不能帶長度

例：通過存盤函式實作計算指定員工的年薪
在oracle中，null與數字相加減，結果為null，所以需要nvl（）函式將comm去除null值，

create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);
begin 
  select sal*12+nvl(comm,0) into s from emp where empno =eno;
  return s;
end;

測驗函式（F_YEARSAL）：
函式在呼叫的時候，回傳值需要接收

declare 
  s number(10);
begin
  s:=f_yearsal(7499);
  dbms_output.put_line(s);
end;

存盤程序和存盤函式的區別：

1. 語法區別：
   關鍵字不一樣（存盤函式比存盤程序多了兩個return）
2. 本質區別：
   存盤函式有回傳值，存盤程序沒有回傳值，
   如果存盤程序想實作有回傳值的業務，我么就必須用out型別的引數，
   即便是存盤程序使用了out型別的引數，本質也不是真的有了回傳值，
   而是在存盤程序內部給out型別引數賦值，再執行完畢后，我們直接拿到輸出型別的值，

案例需求：查詢出員工姓名，員工所在部門名稱，（我們可以使用存盤函式有回傳值的特性，來自定義函式.存盤程序不能用來自定義函式，）

1、傳統方式來實作：

select e.ename,d.dname 
from  emp e,dept d
where e.deptno=d.deptno;

2、使用存盤函式來實作提供一個部門編號，輸出一個部門名稱，

create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is 
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno=dno;
  return dna;
end;

測驗（fdna存盤函式來實作案例需求）：

select e.ename,fdna(e.deptno)
from emp e;

6、觸發器（trigger）

指定一個規則，在我們增刪改操作的時候只要滿足，自動觸發，無需呼叫

1、陳述句級觸發器：不包含for each row

例：插入一條記錄，輸出一個新學生入學

reate or replace trigger t1
after 
insert
on student
declare

begin
  dbms_output.put_line('一個新員工入職！');
end;

觸發t1觸發器

insert into student values('1111111','王延新','女',18,'04');
commit;

2、行級觸發器：包含for each row

加for each row目的：為了使用： a、 ：old 物件、b、：new 物件、c、一行記錄，

例：不能給員工降薪
raise_application_error(-20001~-20999之間,‘錯誤提示資訊’);

create or replace trigger t2
before
update
on emp
for each row
declare 

begin
  if :old.sal>:new.sal then
    raise_application_error(-20001,'不能給員工降薪');
  end if;
end;

觸發t2：

update emp set sal=sal-1 where empno=7499;
commit;

觸發器實作主鍵自增

分析：在用戶做插入操作之前，拿到即將插入的資料，給主鍵列賦值

create or replace trigger auid
before
insert
on student
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;

使用auid實作主鍵自增

insert into person (pname) value('a');
commit;