Oracle——PL/SQL

標籤：ror   lin   參數   if語句   bsp   第一條   varchar   acid   err   

什麼是PL/SQL

PL/SQL是一種過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle資料庫對SQL語句的擴充。在普通SQL語句的使用上增加了程式設計語言的特點，所以PL/SQL就是把資料操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、迴圈等操作實現複雜的功能或者計算的程式語言。

PL/SQL文法1）hello,world

--列印Hello Worlddeclare  --說明部分begin  --程式部分  dbms_output.put_line(‘Hello World‘);　　-- dbms_output相當於java中的類end;/

2）定義變數類型

• 引用型變數

--引用型變數: 查詢並列印7839的姓名和薪水declare  --定義變數儲存姓名和薪水  --pename varchar2(20);-- 聲明變數類型的方式1：直接定義變數的類型  --psal   number;  pename emp.ename%type;-- 聲明變數類型的方式2（引用型變數）：與emp表中的ename列的類型相同  psal   emp.sal%type;begin  --得到7839的姓名和薪水  select ename,sal into pename,psal from emp where empno=7839;-- 使用into為變數賦值  --列印  dbms_output.put_line(pename||‘的薪水是‘||psal);-- ||是Oracle中的字串拼接end;/

• 記錄型變數

--記錄型變數: 查詢並列印7839的姓名和薪水declare  --定義記錄型變數：代表emp表中的一行  emp_rec emp%rowtype;begin  select * into emp_rec from emp where empno=7839;    dbms_output.put_line(emp_rec.ename||‘的薪水是‘||emp_rec.sal);end;/

3）if語句

-- 判斷使用者從鍵盤輸入的數字--接受鍵盤輸入--變數num：是一個地址值，在該地址上儲存了輸入的值accept num prompt ‘請輸入一個數字‘;declare   --定義變數儲存輸入 的數字  pnum number := #begin  if pnum = 0 then dbms_output.put_line(‘您輸入的是0‘);     elsif pnum = 1 then dbms_output.put_line(‘您輸入的是1‘);     elsif pnum = 2 then dbms_output.put_line(‘您輸入的是2‘);     else dbms_output.put_line(‘其他數字‘);  end if;end;/

4）迴圈

-- 列印1~10declare   -- 定義變數  pnum number := 1;begin  loop    --允出準則    exit when pnum > 10;        --列印    dbms_output.put_line(pnum);    --加一    pnum := pnum + 1;  end loop;end;/

5）游標

-- 查詢並列印員工的姓名和薪水/*游標的屬性： %isopen（是否開啟）   %rowcount（影響的行數）             %found（是否有值）    %notfound（是否無值）*/declare    --定義游標（遊標）   cursor cemp is select ename,sal from emp;   pename emp.ename%type;   psal   emp.sal%type;begin  --開啟游標  open cemp;  loop       --取目前記錄       fetch cemp into pename,psal;       --exit when 沒有取到記錄;       exit when cemp%notfound;              dbms_output.put_line(pename||‘的薪水是‘||psal);  end loop;  --關閉游標  close cemp;end;/

樣本：給員工漲工資

-- 給員工漲工資，總裁1000 經理800 其他400declare   --定義游標  cursor cemp is select empno,job from emp;  pempno emp.empno%type;  pjob   emp.job%type;begin  --開啟游標  open cemp;    loop       --取一個員工       fetch cemp into pempno,pjob;       exit when cemp%notfound;              --判斷職位       if pjob = ‘PRESIDENT‘ 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;  --關閉游標  close cemp;    --提交  ----> why?: 事務 ACID  commit;    dbms_output.put_line(‘完成‘);end;/

• 帶參數的游標

-- 查詢某個部門的員工姓名declare    --形參   cursor cemp(dno number) is select ename from emp where deptno=dno;   pename emp.ename%type;begin   --實參   open cemp(20);   loop        fetch cemp into pename;        exit when cemp%notfound;                dbms_output.put_line(pename);   end loop;   close cemp;end;/

6）例外（異常）

• 系統例外

-- 被0除declare   pnum number;begin  pnum := 1/0;  exception  when zero_divide then dbms_output.put_line(‘1:0不能做分母‘);                        dbms_output.put_line(‘2:0不能做分母‘);  when value_error then dbms_output.put_line(‘算術或者轉換錯誤‘);                        when others then dbms_output.put_line(‘其他例外‘);end;/

• 自訂例外

-- 查詢50號部門（50號部門不存在）的員工declare   cursor cemp  is select ename from emp where deptno=50;  pename emp.ename%type;    --自訂例外  no_emp_found exception;begin  open cemp;    --取第一條記錄  fetch cemp into pename;  if cemp%notfound then    --拋出例外，使用raise    raise no_emp_found;  end if;    --進程：pmon進程(proccesss monitor)  close cemp;exception  when no_emp_found then dbms_output.put_line(‘沒有找到員工‘);  when others then dbms_output.put_line(‘其他例外‘);end;/

執行個體1：統計每年入職的員工個數（使用PL/SQL）

/*1、SQL語句select to_char(hiredate,‘yyyy‘) from emp;---> 集合 ---> 游標 ---> 迴圈---> 退出: notfound2、變數：（*）初始值  （*）最終如何得到每年入職的人數count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;*/declare    --定義游標   cursor cemp is select to_char(hiredate,‘yyyy‘) from emp;   phiredate varchar2(4);      --每年入職的人數  count80 number := 0;  count81 number := 0;  count82 number := 0;  count87 number := 0;begin  --開啟游標  open cemp;  loop    --取一個員工的入職年份    fetch cemp into phiredate;    exit when cemp%notfound;        --判斷年份是哪一年    if phiredate = ‘1980‘ then count80:=count80+1;       elsif phiredate = ‘1981‘ then count81:=count81+1;       elsif phiredate = ‘1982‘ then count82:=count82+1;       else count87:=count87+1;     end if;  end loop;    --關閉游標  close cemp;    --輸出  dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87));  dbms_output.put_line(‘1980:‘|| count80);  dbms_output.put_line(‘1981:‘|| count81);  dbms_output.put_line(‘1982:‘|| count82);  dbms_output.put_line(‘1987:‘|| count87);end;/

執行個體2：為員工漲工資，總工資最低的人開始漲，沒人漲10%，但工資總額不超過萬元，請計算長工資的人數和長工資後的工資總額。

/*1、SQL語句selet empno,sal from emp order by sal;---> 游標  ---> 迴圈  ---> 退出：1. 總額>5w   2. notfound2、變數：（*）初始值  （*）最終如何得到漲工資的人數: countEmp number := 0;漲後的工資總額：salTotal number;(1)select sum(sal) into salTotal from emp;(2)漲後=漲前 + sal *0.1練習： 人數：8    總額:50205.325*/declare    cursor cemp is select empno,sal from emp order by sal;    pempno emp.empno%type;    psal   emp.sal%type;        --漲工資的人數:     countEmp number := 0;    --漲後的工資總額：    salTotal number;begin    --得到工資總額的初始值    select sum(sal) into salTotal from emp;        open cemp;    loop         -- 1. 總額 >5w         exit when salTotal > 50000;         --取一個員工         fetch cemp into pempno,psal;         --2. notfound         exit when cemp%notfound;                  --漲工資         update emp set sal=sal*1.1 where empno=pempno;         --人數+1         countEmp := countEmp +1;         --漲後=漲前 + sal *0.1         salTotal := salTotal + psal * 0.1;    end loop;    close cemp;        commit;    dbms_output.put_line(‘人數：‘||countEmp||‘    總額:‘||salTotal);end;/

執行個體3：

/*1、SQL語句部門：select deptno from dept; ---> 游標部門中員工的薪水: select sal from emp where deptno=?? ---> 帶參數的游標2、變數：（*）初始值  （*）最終如何得到每個段的人數count1 number; count2 number; count3 number;部門的工資總額salTotal number := 0;(1)select sum(sal) into salTotal  from emp where deptno=??(2)累加*/declare  --部門  cursor cdept is select deptno from dept;  pdeptno dept.deptno%type;    --部門中員工的薪水  cursor cemp(dno number) is select sal from emp where deptno=dno;  psal emp.sal%type;    --每個段的人數  count1 number; count2 number; count3 number;  --部門的工資總額  salTotal number := 0;begin  --部門  open cdept;  loop       --取一個部門       fetch cdept into pdeptno;       exit when cdept%notfound;              --初始化       count1:=0; count2:=0; count3:=0;       --得到部門的工資總額       select sum(sal) into salTotal  from emp where deptno=pdeptno;              --取部門的中員工薪水       open cemp(pdeptno);       loop            --取一個員工的薪水            fetch cemp into psal;            exit when cemp%notfound;                        --判斷            if psal < 3000 then count1:=count1+1;               elsif psal >=3000 and psal<6000 then count2:=count2+1;               else count3:=count3+1;            end if;       end loop;       close cemp;       --儲存結果       insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));  end loop;  close cdept;    commit;  dbms_output.put_line(‘完成‘);  end;/

 

Oracle——PL/SQL