Oracle 的PL/SQL語言使用

--PL/SQL語言(procedure language 過程化語言)
--1.宣告型別
declare
  k number;
  m number default 20;
  --Character String buffer too small問題
  --pname varchar2(4);
  --所以更換宣告
  pname emp.ename%type;
  --查詢一行用表名宣告
  prow emp%rowtype;
begin
  k:=30;
  dbms_output.put_line(k);
  dbms_output.put_line(`原樣輸出 m=`||m);
  select ename into pname from emp where empno=7788;
  dbms_output.put_line(pname);
  --查詢結果是一行值
  select * into prow from emp where empno=7654;
  dbms_output.put_line
  (prow.empno||`-`||prow.ename||`-`||prow.job);
end;

--2.1 結構化判斷語句
declare
  k number;
begin
  k:=&這裡可以讓你輸入;
  if k>0 then 
     dbms_output.put_line(` k是正數`||k);
  elsif k<0 then
     dbms_output.put_line(` k是負數`||k);
  else
     dbms_output.put_line(` k是零`||k);
  end if;
end;

--2.2.1 結構化迴圈語句 loop輸出1-10
declare
  k number default 1;
begin
  loop
    dbms_output.put_line(k);
    exit when k=10;
    k:=k+1;
  end loop;
end;
--2.2.2 結構化迴圈語句 while輸出1-10
declare
  k number default 1;
begin
  while k<=10 loop
    dbms_output.put_line(k);
    k:=k+1;
  end loop;
end;
--2.2.3 結構化迴圈語句 for輸出1-10
--1..10 是集合 可稱為遊標
declare
  k number default 1;
begin
  for k in 1..10 loop
    dbms_output.put_line(k);
  end loop;
end;

--2.2.4 結構化迴圈語句 for輸出1-10
-- 使用遊標列印20號部門的員工姓名和工作 方法一
declare
  cursor cur is select ename,job from emp where deptno=20;
begin
  for k in cur loop
    dbms_output.put_line(k.ename||`-`||k.job);
  end loop;
end;
-- 使用遊標列印20號部門的員工姓名和工作 方法二
declare
  pname emp.ename%type;
  pjob emp.job%type;
  cursor cur is select ename,job from emp where deptno=20;
begin
  open cur;
  loop
    fetch cur into pname,pjob;
    exit when cur%notfound;
    dbms_output.put_line(pname||`-`||pjob);
  end loop;
  close cur;
end;
-- 使用遊標對20號部門的員工漲工資
declare
  k number;
  cursor cur is select empno from emp where deptno=20;
begin
  for k in cur loop
    update emp set sal=sal+100 where empno=k.empno;
  end loop;
end;
select * from emp;
-- 例外(基本異常)
declare
  pname emp.ename%type;
  m number;
begin
  m:=`abc`;
  select ename into pname from emp where deptno=20;
  dbms_output.put_line(pname);
  select ename into pname from emp where deptno=40;
  dbms_output.put_line(pname);
exception
  when no_data_found then
      dbms_output.put_line(`沒有記錄`);
  when too_many_rows then
      dbms_output.put_line(`太多記錄`);
  when value_error then
      dbms_output.put_line(`型別轉換異常`);
  when others then
      dbms_output.put_line(`其他異常`);
end;
-- 例外(自定義異常)
declare
  not_found exception;
  pname emp.ename%type;
  cursor cur is select ename from emp where deptno=40;
begin
  open cur;
  fetch cur into pname;
  if cur%notfound then
    raise not_found;
  end if;
  close cur;
exception
  when not_found then
      dbms_output.put_line(`遊標中沒發現記錄`);
  when no_data_found then
      dbms_output.put_line(`沒有記錄`);
  when too_many_rows then
      dbms_output.put_line(`太多記錄`);
  when value_error then
      dbms_output.put_line(`型別轉換異常`);
  when others then
      dbms_output.put_line(`其他異常`);
end;
-- 3 儲存過程(感覺包含了PL/SQL所有)
-- 封裝了一組SQL語句,提前編譯號,放在伺服器端,等待呼叫
--3.1 根據員工編號得到員工的年薪
create or replace procedure getYearSal(eno in number,
yearsal out number)
as --宣告變數
begin --過程化語句
      select sal*12+nvl(comm,0) into yearsal from emp
      where empno=eno;
end;
--訪問單值輸出的儲存過程
declare
      yearsal number;
begin
  getYearSal(7499,yearsal);
  dbms_output.put_line(`年薪`||yearsal);
end;
--3.2 給某員工漲工資(列印漲前和漲後工資)
create or replace procedure updateSal(eno in number,
plussal in number)
is --宣告變數
   oldsal number;
   newsal number;
begin --過程化語句
      --漲前
      select sal into oldsal from emp where empno=eno;
      dbms_output.put_line(`漲前的工資:`||oldsal);
      --漲工資
      update emp set sal=sal+plussal where empno=eno;
      commit;
      --漲後
      select sal into newsal from emp where empno=eno;
      dbms_output.put_line(`漲後的工資:`||newsal);
end;
--訪問儲存過程
--方法一
declare
begin
  updateSal(7499,888.88);
end;
--方法二 訪問只有輸入的儲存過程也可以使用call
call updateSal(7499,888.88);
--3.3 得到某部門所有員工的資訊
create or replace procedure getEmps(dno in number,
emps out sys_refcursor)
is --宣告變數

begin --過程化語句
      open emps for select * from emp where deptno=dno;   
end;
--訪問儲存過程
-- 訪問輸出引數為遊標的儲存過程
declare
  emps sys_refcursor;
  prow emp%rowtype;
begin
  getEmps(20,emps);
  --開啟遊標在儲存過程中了
  loop
    fetch emps into prow;
    exit when emps%notfound;
    dbms_output.put_line(prow.empno||`-`||prow.job||`-`||prow.sal||`-`||prow.ename);
  end loop;
  close emps;
end;
--對比普通遊標的使用,不能使用for迴圈
declare
  cursor emps is select * from emp where deptno=20;
  k number;
begin
  for k in emps loop
    dbms_output.put_line(k.empno||`-`||k.job||`-`||k.sal||`-`||k.ename);
  end loop;
end;
-- 4 儲存函式(感覺包含了PL/SQL所有)
-- 必須有return 可以直接用在select查詢中 一般不用out輸出引數
--4.1 根據員工編號得到員工的年薪
create or replace function x(eno in number) return number
as
       psal emp.sal%type;
begin
  select sal into psal from emp where empno=eno;
  return psal;
end;


--訪問單值輸出的儲存函式
declare
      yearsal number;
begin
  yearsal := x(7499);
  dbms_output.put_line(`年薪`||yearsal);
end;
--儲存函式用於select語句中
　　select x(7788) from dual;

-- 5 觸發器 -- 星期三不能插入資料 
create or replace trigger notInsertPerson
before
insert
on emp
for each row
declare
day number;
begin
   select to_char(sysdate,`d`) into day from dual;
   if trim(day)=(`5`) then
      raise_application_error(-20003,`不能在週四辦理入職`);
   end if;
end;
  select to_char(sysdate,`d`) from dual;
insert into emp (empno,ename) VALUES(`1234`,`joke`);
   /*綜合練習:每一位僱員都要根據其收入上繳所得稅，假設所得稅的上繳原則為：
  --2000以下上繳3%、 --2000 ~ 5000上繳8%、5000以上上繳10%，現在要求建立一張新的資料表，
  --可以記錄出僱員的編號、姓名、工資、佣金、上繳所得稅資料，
  --並且在每次修改僱員表中sal和comm欄位後可以自動更新記錄。*/
create or replace function tax(eno in number) return number
as
       psal emp.sal%type;
       tax number;
begin
  select sal+nvl(comm,0) into psal from emp where empno=eno;
  if psal<2000 then
        tax:=psal*0.03;
  elsif psal>=2000 and psal<5000 then
        tax:=psal*0.08;
  elsif psal>=5000 then
        tax:=psal*0.1;
  else 
        tax:=0;
  end if;
  return tax;
end;

declare
     ptax number;
begin
  ptax :=tax(7788);
  dbms_output.put_line(ptax);
end;
--分配建立檢視的許可權給scott
grant create view to scott;
--建立一張新的檢視表(包含稅)
create view newemp as select empno,ename,sal,comm,(select tax(empno) from dual) tax from emp;
select * from newemp;
--設定觸發器
create or replace trigger autocommit
after
update
on emp
for each row
declare
    pragma autonomous_transaction;
begin
  --有待補充
  commit;
end;

update emp set sal=sal+33.33 where empno=7788;
select * from emp;