復習內容：
PL/SQL的基本語法、記錄類型、流程控制、游標的使用、
異常處理機制、存儲函數/存儲過程、觸發器。
為方便大家跟著我的筆記練習，為此提供數據庫表文件給大家下載
為了要有輸出的結果，在寫PL/SQL程序前都在先運行這一句：
set serveroutput on
結構：
declare
--聲明變量、類型、游標
begin
--程序的執行部分（類似于java里的main()方法）
exception
--針對begin塊中出現的異常，提供處理的機制
--when...then...
--when...then...
end;
舉例1：
declare v_sal number(10); （注意每句話后面別忘記了分號，跟java中的一樣）begin select salary into v_sal from employees where employee_id = 100; dbms_output.put_line(v_sal);end;
舉例2：
declare v_sal number(10); （注意，這里聲明的空間大小不能比原表中的小） v_email varchar2(20); v_hire_date date;begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);end;或者：declare v_sal employees.salary%type; v_email employees.email%type; v_hire_date employees.hire_date%type;begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);end;
記錄：
declare type emp_record is record( v_sal employees.salary%type, v_email employees.email%type, v_hire_date employees.hire_date%type ); v_emp_record emp_record;begin select salary,email,hire_date into v_emp_record from employees where employee_id = 100; dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','|| v_emp_record.v_hire_date);end;
1、pl/sql基本的語法格式
2、記錄類型 type ... is ...record(,,,);
3、流程控制：
3.1 條件判斷（兩種）
方式一： if ... then elseif then ... else ... end if;
方式二： case ... when ... then ...end;
3.2 循環結構（三種）
方式一：loop ... exit when ... end loop;
方式二：while ... loop ... end loop;
方式三：for i in ... loop ... end loop;
3.3 goto、exit
4.游標的使用（類似于java中的Iterator）
5.異常的處理
6.會寫一個存儲函數（有返回值）、存儲過程（沒有返回值）
7.會寫一個觸發器
復習記錄類型：
declaretype emp_record is record( -- v_emp_id employees.employee_id%type, -- v_sal employees.salary%type v_emp_id number(10) := 120, v_sal number(10,2) :=12000); v_emp_record emp_record;begin -- select employee_id,salary into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'|| v_emp_record.v_sal);end; 也可以升級一下，要是想對表的所有列都輸出，則：（須注意輸出的列名要跟表中的列名要一樣）
declare v_emp_record employees%rowtype;begin select * into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'|| v_emp_record.salary);end;使用記錄來執行update操作：declare v_emp_id number(10);begin v_emp_id :=123; update employees set salary = salary + 100 where employee_id = v_emp_id; dbms_output.put_line('執行成功！~~');end; 流程控制：
查詢150號員工的工資，若其工資大于或等于10000 則打印‘salary >= 10000';
若在5000到10000之間，則打印‘5000 <= salary <10000';否則打印‘salary < 5000'
declare v_sal employees.salary%type;begin select salary into v_sal from employees where employee_id =150; if v_sal >= 10000 then dbms_output.put_line('salary >= 10000'); elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000'); else dbms_output.put_line('salary < 5000'); end if; dbms_output.put_line('salary:'||v_sal);end;利用case ... when ... then ... when ...then ... else ... end實現上題;declare v_sal employees.salary%type; v_temp varchar2(20);begin select salary into v_sal from employees where employee_id =150; v_temp := case trunc(v_sal/5000) when 0 then 'salary < 5000' when 1 then '5000 <= salary < 10000' else 'salary >= 10000' end; dbms_output.put_line('salary:'||v_sal||' '||v_temp);end;
查詢出122號員工的job_id,若其值為 ‘IT_PROG', 則打印‘GRADE:A'
‘AC_MGT', 則打印‘GRADE:B'
‘AC_ACCOUNT', 則打印‘GRADE:B'
否則打印‘GRADE:D'
declare v_job_id employees.job_id%type; v_temp varchar2(20);begin select job_id into v_job_id from employees where employee_id =122; v_temp := case v_job_id when 'IT_PROG' then 'A' when 'AC_MGT' then 'B' when 'AC_ACCOUNT' then 'C' else 'D' end; dbms_output.put_line('job_id:'||v_job_id||' '||v_temp);end; 使用循環語句打印：1-100
declare v_i number(5) :=1;begin loop dbms_output.put_line(v_i); exit when v_i >=100; v_i := v_i + 1; end loop;end;使用while實現：declare v_i number(5) :=1;begin while v_i <= 100 loop dbms_output.put_line(v_i); v_i := v_i + 1; end loop;end;使用for...in...loop...end loop;實現：begin for c in 1..100 loop dbms_output.put_line(c); end loop;end;
輸出2-100之間的質數
declare v_i number(3):= 2; v_j number(3):= 2; v_flag number(1):= 1;begin while v_i<=100 loop while v_j<=sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; end if; v_j:= v_j+1; end loop; if v_flag = 1 then dbms_output.put_line(v_i); end if; v_j :=2; v_i := v_i + 1; v_flag := 1; end loop;end;
利用for循環實現輸出2-100之間的質數：
declare v_flag number(1):= 1;begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; end if; end loop; if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop;end;
可以用goto改進一下：
declare v_flag number(1):= 1;begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; goto label; end if; end loop; <<label>> if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop;end;
打印1-100的自然數，當打印到50時，跳出循環 ，輸出‘打印結束'：
begin for i in 1..100 loop if i=50 then goto label; end if; dbms_output.put_line(i); end loop;<<label>> dbms_output.put_line('打印結束');end;或者：begin for i in 1..100 loop if i=50 then dbms_output.put_line('打印結束'); exit; end if; dbms_output.put_line(i); end loop;end;
游標：
打印出80部門的所有的員工的工資：salary:XXX
declare
v_sal employees.salary%type;
--定義游標
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打開游標
open emp_sal_cursor;
--提取游標
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'||v_sal);
fetch emp_sal_cursor into v_sal;
end loop;
--關閉游標
close emp_sal_cursor;
end;
可以進行優化如下：
declarev_empid employees.employee_id%type;v_lastName employees.last_name%type;v_sal employees.salary%type;cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin open emp_sal_cursor;fetch emp_sal_cursor into v_empid,v_lastName,v_sal;while emp_sal_cursor%found loopdbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||', '||'salary:'||v_sal);fetch emp_sal_cursor into v_empid,v_lastName,v_sal;end loop;close emp_sal_cursor;end; 或者使用記錄再優化一下：
declaretype emp_record is record(v_empid employees.employee_id%type,v_lastName employees.last_name%type,v_sal employees.salary%type);v_emp_record emp_record;cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin open emp_sal_cursor;fetch emp_sal_cursor into v_emp_record;while emp_sal_cursor%found loopdbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'|| v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal);fetch emp_sal_cursor into v_emp_record;end loop;close emp_sal_cursor;end; 可以使用for循環最優化：（注意：在for循環中它會自動的打開游標、提取游標，當提取完里面的數據后也會自動
的關閉游標）
declarecursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin for c in emp_sal_cursor loopdbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||', '||'salary:'||c.salary);end loop;end;
利用游標，調整公司中員工的工資：
工資范圍 調整基數
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
實現：
declare cursor emp_cursor is select employee_id,salary from employees; v_empid employees.employee_id%type; v_sal employees.salary%type; v_temp number(4,2);begin open emp_cursor; fetch emp_cursor into v_empid,v_sal; while emp_cursor%found loop if v_sal < 5000 then v_temp:=0.05; elsif v_sal < 10000 then v_temp:=0.03; elsif v_sal < 15000 then v_temp:=0.02; else v_temp:=0.01; end if; dbms_output.put_line(v_empid||','||v_sal); update employees set salary = salary * (1+v_temp) where employee_id = v_empid; fetch emp_cursor into v_empid,v_sal; end loop; close emp_cursor;end;
用for循環實現
declare cursor emp_cursor is select employee_id,salary from employees; v_temp number(4,2); begin for c in emp_cursor loop if c.salary <5000 then v_temp:=0.05; elsif c.salary <10000 then v_temp:=0.03; elsif c.salary <15000 then v_temp:=0.02; else v_temp:=0.01; end if; update employees set salary = salary * (1+v_temp) where employee_id = c.employee_id; end loop;end;
隱式游標：更新員工salary（漲工資10），如果該員工沒有找到，則打印“查無此人”信息：
begin update employees set salary = salary + 10 where employee_id = 1001; if sql%notfound then dbms_output.put_line('查無此人'); end if;end; 異常：
預定義異常：（有24個預定義異常，可查表）
declare v_sal employees.salary%type;begin select salary into v_sal from employees where employee_id > 100; dbms_output.put_line(v_sal); exception when too_many_rows then dbms_output.put_line('輸出的行數過多'); when others then dbms_output.put_line('出現其它的異常了');end; 非預定義異常：
declare e_deleteid_exception exception; pragma exception_init(e_deleteid_exception,-2292);begin delete from employees where employee_id = 100; exception when e_deleteid_exception then dbms_output.put_line('違反了完整性約束，故不能刪除此用戶'); when others then dbms_output.put_line('出現其它的異常了');end; 用戶自定義異常：
declare e_sal_hight exception; v_sal employees.salary%type;begin select salary into v_sal from employees where employee_id = 100; if v_sal > 10000 then raise e_sal_hight; end if;exception when e_sal_hight then dbms_output.put_line('工資太高了'); when others then dbms_output.put_line('出現其它的異常了');end; 通過select...into...查詢某人的工資，若沒找到則打印出“未找到此數據”：
declare v_sal employees.salary%type;begin select salary into v_sal from employees where employee_id = 1001;exception when no_data_found then dbms_output.put_line('未找到此數據'); when others then dbms_output.put_line('出現其它的異常了');end;更新指定員工工資，如工資小于300，則加100，對NO_DATA_FOUND異常，TOO_MANY_ROWS進行處理。declare v_sal employees.salary%type;begin select salary into v_sal from employees where employee_id = 1001; if v_sal < 300 then update employees set salary = salary + 100 where employee_id =101; end if;exception when no_data_found then dbms_output.put_line('未找到此數據'); when too_many_rows then dbms_output.put_line('輸出的行數太多了'); when others then dbms_output.put_line('出現其它的異常了');end; 自定義異常：
更新指定員工工資，增加100；若指定員工不在，則拋出異常：NO_RESULT;
declare no_result exception;begin update employees set salary = salary + 100 where employee_id = 1001; if sql%notfound then raise no_result; end if;exception when no_result then dbms_output.put_line('查無此數據,更新失敗'); when others then dbms_output.put_line('出現其它異常'); end;
存儲過程：
寫個簡單的hello_world存儲函數
create or replace function hello_world return varchar2is （相當于declare，可以在其后面定義變量、記錄、游標）begin return 'helloworld';end;存儲函數的調用：begin dbms_output.put_line(hello_world);end;或者：select hello_world from dual;
帶參數的存儲函數：
create or replace function hello_world1(v_logo varchar2)return varchar2is begin return 'helloworld'||v_logo;end;調用：select hello_world1('shellway') from dual或者：begin dbms_output.put_line(hello_world1('shellway'));end;
定義一個獲取系統時間的函數：
create or replace function get_sysdatereturn varchar2is begin return to_char(sysdate,'yyyy-MM-dd HH24:mi:ss');end;
定義帶參數的函數，兩個數相加
create or replace function add_param(v_num1 number,v_num2 number)return numberis v_num3 number(10);begin v_num3 := v_num1 + v_num2; return v_num3;end;調用：select add_param(2,5) from dual;或者：begin dbms_output.put_line(add_param(5,4));end;
定義一個函數：獲取給定部門的工資總和，要求：部門號定義為參數，工資總額為返回值：
create or replace function get_sal(dept_id number)return numberis v_sumsal number(10) := 0; cursor salary_cursor is select salary from employees where department_id = dept_id;begin for c in salary_cursor loop v_sumsal := v_sumsal + c.salary; end loop; return v_sumsal;end;調用：select get_sal(80) from dual;
定義一個函數：獲取給定部門的工資總和 和 該部門的員工總數（定義為OUT類型的參數）。
要求：部門號定義為參數，工資總額定義為返回值。
create or replace function get_sal(dept_id number,total_count out number)return numberis v_sumsal number(10) := 0; cursor salary_cursor is select salary from employees where department_id = dept_id;begin total_count := 0; for c in salary_cursor loop v_sumsal := v_sumsal + c.salary; total_count := total_count + 1; end loop; return v_sumsal;end;調用：declare v_count number(4);begin dbms_output.put_line(get_sal(80,v_count)); dbms_output.put_line(v_count);end;
定義一個存儲過程：獲取給定部門的工資總和（通過out參數），要求部門號和工資總額定義為參數。
（注意：存儲過程和存儲函數是不一樣的，存儲函數有返回值而存儲過程沒有，調用時候存儲過程直接調用）
create or replace procedure get_sal1(dept_id number,sumsal out number)is cursor salary_cursor is select salary from employees where department_id = dept_id;begin sumsal := 0; for c in salary_cursor loop sumsal := sumsal + c.salary; end loop; dbms_output.put_line(sumsal);end;調用：declare v_sal number(10):=0; begin get_sal1(80,v_sal);end;
對給定部門（作為輸入參數）的員工進行加薪操作，若其到公司的時間在（？，95）期間，為其加薪5%
（95，98） 3%
（98，？） 1%
得到以下返回結果：為此次加薪公司每月額外付出多少成三（定義一個OUT型的輸出參數）
create or replace procedure add_sal(dept_id number,temp out number)is cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id; v_temp number(4,2):=0;begin temp := 0; for c in sal_cursor loop if to_char(c.hire_date,'yyyy') < '1995' then v_temp:=0.05; elsif to_char(c.hire_date,'yyyy') < '1998' then v_temp:=0.03; else v_temp:=0.01; end if; update employees set salary = salary * (1+v_temp) where employee_id = c.employee_id; temp := temp + c.salary*v_temp; end loop; dbms_output.put_line(temp);end;調用：declare v_i number(10):=0;begin add_sal(80,v_i);end;
觸發器：
觸發事件：在INSERT,UPDATE,DELETE情況下會觸發TRIGGER
觸發時間：該TRIGGER是在觸發事件發生之前(BEFORE)還是之后（AFTER）
觸發器本身：該TRIGGER被觸發之后的目的和意圖，正是觸發器本身要做的事情，如PL/SQL塊
觸發頻率：有語句級(STATEMENT)觸發器和行級(ROW)觸發器
寫一個簡單的觸發器：
create or replace trigger update_emp_triggerafter update on employeesfor each row (行級觸發器，即每更新一條記錄就會輸出一次'helloworld'，若沒有這語句則是語句級觸發器)begin dbms_output.put_line('helloworld');end;
使用:new,:old修飾符：
1、create table emp1asselect employee_id,salary,email from employees where department_id = 80;2、create or replace trigger update_emp_trigger2after update on emp1for each rowbegin dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary);end;3、update emp1 set salary = salary + 100 ；
編寫一個觸發器，在對my_emp記錄進行刪除的時候，在my_emp_bak表中備份對應的記錄
1、創建my_emp表：create table my_emp asselect employee_id,salary from employees ;2、創建my_emp_bak表：create table my_emp_bakasselect employee_id,salary from employees where 1=2;3、檢查創建的表中的記錄：select * from my_empselect * from my_emp_bak4、創建一個觸發器：create or replace trigger delete_emp_trigger before delete on my_emp for each rowbegin insert into my_emp_bak values(:old.employee_id,:old.salary);end;5、執行含有觸發器時間的語句：delete from my_emp6、檢查觸發器執行后的結果：select * from my_empselect * from my_emp_bak
