復(fù)習(xí)內(nèi)容：
PL/SQL的基本語(yǔ)法、記錄類型、流程控制、游標(biāo)的使用、
異常處理機(jī)制、存儲(chǔ)函數(shù)/存儲(chǔ)過(guò)程、觸發(fā)器。
為方便大家跟著我的筆記練習(xí)，為此提供數(shù)據(jù)庫(kù)表文件給大家下載：點(diǎn)我下載
為了要有輸出的結(jié)果，在寫(xiě)PL/SQL程序前都在先運(yùn)行這一句：
set serveroutput on
結(jié)構(gòu)：
declare
--聲明變量、類型、游標(biāo)
begin
--程序的執(zhí)行部分（類似于java里的main()方法）
exception
--針對(duì)begin塊中出現(xiàn)的異常，提供處理的機(jī)制
--when...then...
--when...then...
end;
舉例1：
declare
v_sal number(10); （注意每句話后面別忘記了分號(hào)，跟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基本的語(yǔ)法格式
2、記錄類型 type ... is ...record(,,,);
3、流程控制：
3.1 條件判斷（兩種）
方式一： if ... then elseif then ... else ... end if;
方式二： case ... when ... then ...end;
3.2 循環(huán)結(jié)構(gòu)（三種）
方式一：loop ... exit when ... end loop;
方式二：while ... loop ... end loop;
方式三：for i in ... loop ... end loop;
3.3 goto、exit
4.游標(biāo)的使用（類似于java中的Iterator）
5.異常的處理
6.會(huì)寫(xiě)一個(gè)存儲(chǔ)函數(shù)（有返回值）、存儲(chǔ)過(guò)程（沒(méi)有返回值）
7.會(huì)寫(xiě)一個(gè)觸發(fā)器
復(fù)習(xí)記錄類型：
declare
type 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;
也可以升級(jí)一下，要是想對(duì)表的所有列都輸出，則：（須注意輸出的列名要跟表中的列名要一樣）
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;
使用記錄來(lái)執(zhí)行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('執(zhí)行成功！~~');
end;
流程控制：
查詢150號(hào)員工的工資，若其工資大于或等于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實(shí)現(xiàn)上題;
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號(hào)員工的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;
使用循環(huán)語(yǔ)句打?。?-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實(shí)現(xiàn)：
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;實(shí)現(xiàn)：
begin
for c in 1..100 loop
dbms_output.put_line(c);
end loop;
end;
輸出2-100之間的質(zhì)數(shù)
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循環(huán)實(shí)現(xiàn)輸出2-100之間的質(zhì)數(shù)：
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改進(jìn)一下：
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的自然數(shù)，當(dāng)打印到50時(shí)，跳出循環(huán) ，輸出‘打印結(jié)束'：
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('打印結(jié)束');
end;
或者：
begin
for i in 1..100 loop
if i=50 then dbms_output.put_line('打印結(jié)束');
exit;
end if;
dbms_output.put_line(i);
end loop;
end;
游標(biāo)：
打印出80部門(mén)的所有的員工的工資：salary:XXX
declare
v_sal employees.salary%type;
--定義游標(biāo)
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打開(kāi)游標(biāo)
open emp_sal_cursor;
--提取游標(biāo)
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;
--關(guān)閉游標(biāo)
close emp_sal_cursor;
end;
可以進(jìn)行優(yōu)化如下：
declare
v_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 loop
dbms_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;
或者使用記錄再優(yōu)化一下：
declare
type 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 loop
dbms_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循環(huán)最優(yōu)化：（注意：在for循環(huán)中它會(huì)自動(dòng)的打開(kāi)游標(biāo)、提取游標(biāo)，當(dāng)提取完里面的數(shù)據(jù)后也會(huì)自動(dòng)
的關(guān)閉游標(biāo)）
declare
cursor emp_sal_cursor is select employee_id,last_name,salary from employees where
department_id = 80;
begin
for c in emp_sal_cursor loop
dbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||',
'||'salary:'||c.salary);
end loop;
end;
利用游標(biāo)，調(diào)整公司中員工的工資：
工資范圍 調(diào)整基數(shù)
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
實(shí)現(xiàn)：
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循環(huán)實(shí)現(xiàn)
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;
隱式游標(biāo)：更新員工salary（漲工資10），如果該員工沒(méi)有找到，則打印“查無(wú)此人”信息：
begin
update employees
set salary = salary + 10
where employee_id = 1001;
if sql%notfound then dbms_output.put_line('查無(wú)此人');
end if;
end;
異常：
預(yù)定義異常：（有24個(gè)預(yù)定義異常，可查表）
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('輸出的行數(shù)過(guò)多');
when others then dbms_output.put_line('出現(xiàn)其它的異常了');
end;
非預(yù)定義異常：
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('出現(xiàn)其它的異常了');
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('出現(xiàn)其它的異常了');
end;
通過(guò)select...into...查詢某人的工資，若沒(méi)找到則打印出“未找到此數(shù)據(jù)”：
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('未找到此數(shù)據(jù)');
when others then dbms_output.put_line('出現(xiàn)其它的異常了');
end;
更新指定員工工資，如工資小于300，則加100，對(duì)NO_DATA_FOUND異常，TOO_MANY_ROWS進(jìn)行處理。
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('未找到此數(shù)據(jù)');
when too_many_rows then dbms_output.put_line('輸出的行數(shù)太多了');
when others then dbms_output.put_line('出現(xiàn)其它的異常了');
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('查無(wú)此數(shù)據(jù),更新失敗');
when others then dbms_output.put_line('出現(xiàn)其它異常');
end;
存儲(chǔ)過(guò)程：
寫(xiě)個(gè)簡(jiǎn)單的hello_world存儲(chǔ)函數(shù)
create or replace function hello_world
return varchar2
is （相當(dāng)于declare，可以在其后面定義變量、記錄、游標(biāo)）
begin
return 'helloworld';
end;
存儲(chǔ)函數(shù)的調(diào)用：
begin
dbms_output.put_line(hello_world);
end;
或者：
select hello_world from dual;
帶參數(shù)的存儲(chǔ)函數(shù)：
create or replace function hello_world1(v_logo varchar2)
return varchar2
is
begin
return 'helloworld'||v_logo;
end;
調(diào)用：
select hello_world1('shellway') from dual
或者：
begin
dbms_output.put_line(hello_world1('shellway'));
end;
定義一個(gè)獲取系統(tǒng)時(shí)間的函數(shù)：
create or replace function get_sysdate
return varchar2
is
begin
return to_char(sysdate,'yyyy-MM-dd HH24:mi:ss');
end;
定義帶參數(shù)的函數(shù)，兩個(gè)數(shù)相加
create or replace function add_param(v_num1 number,v_num2 number)
return number
is
v_num3 number(10);
begin
v_num3 := v_num1 + v_num2;
return v_num3;
end;
調(diào)用：
select add_param(2,5) from dual;
或者：
begin
dbms_output.put_line(add_param(5,4));
end;
定義一個(gè)函數(shù)：獲取給定部門(mén)的工資總和，要求：部門(mén)號(hào)定義為參數(shù)，工資總額為返回值：
create or replace function get_sal(dept_id number)
return number
is
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;
調(diào)用：
select get_sal(80) from dual;
定義一個(gè)函數(shù)：獲取給定部門(mén)的工資總和 和 該部門(mén)的員工總數(shù)（定義為OUT類型的參數(shù)）。
要求：部門(mén)號(hào)定義為參數(shù)，工資總額定義為返回值。
create or replace function get_sal(dept_id number,total_count out number)
return number
is
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;
調(diào)用：
declare
v_count number(4);
begin
dbms_output.put_line(get_sal(80,v_count));
dbms_output.put_line(v_count);
end;
定義一個(gè)存儲(chǔ)過(guò)程：獲取給定部門(mén)的工資總和（通過(guò)out參數(shù)），要求部門(mén)號(hào)和工資總額定義為參數(shù)。
（注意：存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)是不一樣的，存儲(chǔ)函數(shù)有返回值而存儲(chǔ)過(guò)程沒(méi)有，調(diào)用時(shí)候存儲(chǔ)過(guò)程直接調(diào)用）
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;
調(diào)用：
declare
v_sal number(10):=0;
begin
get_sal1(80,v_sal);
end;
對(duì)給定部門(mén)（作為輸入?yún)?shù)）的員工進(jìn)行加薪操作，若其到公司的時(shí)間在（？，95）期間，為其加薪5%
（95，98） 3%
（98，？） 1%
得到以下返回結(jié)果：為此次加薪公司每月額外付出多少成三（定義一個(gè)OUT型的輸出參數(shù)）
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;
調(diào)用：
declare
v_i number(10):=0;
begin
add_sal(80,v_i);
end;
觸發(fā)器：
觸發(fā)事件：在INSERT,UPDATE,DELETE情況下會(huì)觸發(fā)TRIGGER
觸發(fā)時(shí)間：該TRIGGER是在觸發(fā)事件發(fā)生之前(BEFORE)還是之后（AFTER）
觸發(fā)器本身：該TRIGGER被觸發(fā)之后的目的和意圖，正是觸發(fā)器本身要做的事情，如PL/SQL塊
觸發(fā)頻率：有語(yǔ)句級(jí)(STATEMENT)觸發(fā)器和行級(jí)(ROW)觸發(fā)器
寫(xiě)一個(gè)簡(jiǎn)單的觸發(fā)器：
create or replace trigger update_emp_trigger
after
update on employees
for each row (行級(jí)觸發(fā)器，即每更新一條記錄就會(huì)輸出一次'helloworld'，若沒(méi)有這語(yǔ)句則是語(yǔ)句級(jí)觸發(fā)器)
begin
dbms_output.put_line('helloworld');
end;
使用:new,:old修飾符：
1、
create table emp1
as
select employee_id,salary,email from employees where department_id = 80;
2、
create or replace trigger update_emp_trigger2
after
update on emp1
for each row
begin
dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary);
end;
3、
update emp1 set salary = salary + 100 ；
編寫(xiě)一個(gè)觸發(fā)器，在對(duì)my_emp記錄進(jìn)行刪除的時(shí)候，在my_emp_bak表中備份對(duì)應(yīng)的記錄
1、創(chuàng)建my_emp表：
create table my_emp
as
select employee_id,salary from employees ;
2、創(chuàng)建my_emp_bak表：
create table my_emp_bak
as
select employee_id,salary from employees where 1=2;
3、檢查創(chuàng)建的表中的記錄：
select * from my_emp
select * from my_emp_bak
4、創(chuàng)建一個(gè)觸發(fā)器：
create or replace trigger delete_emp_trigger
before
delete on my_emp
for each row
begin
insert into my_emp_bak
values(:old.employee_id,:old.salary);
end;
5、執(zhí)行含有觸發(fā)器時(shí)間的語(yǔ)句：
delete from my_emp
6、檢查觸發(fā)器執(zhí)行后的結(jié)果：
select * from my_emp
select * from my_emp_bak
您可能感興趣的文章:- PL/SQL Dev連接Oracle彈出空白提示框的解決方法分享
- 64位win7下pl/sql無(wú)法連接oracle解決方法
- Oracle中PL/SQL中if語(yǔ)句的寫(xiě)法介紹
- Oracle中在pl/sql developer修改表的2種方法
- ORACLE PL/SQL 觸發(fā)器編程篇介紹
- Oracle 10G:PL/SQL正規(guī)表達(dá)式（正則表達(dá)式）手冊(cè)
- 在Oracle PL/SQL中游標(biāo)聲明中表名動(dòng)態(tài)變化的方法
- PL/SQL實(shí)現(xiàn)Oracle數(shù)據(jù)庫(kù)任務(wù)調(diào)度
- Oracle PL/SQL語(yǔ)言入門(mén)基礎(chǔ)
- Oracle PL/SQL入門(mén)案例實(shí)踐
- Oracle PL/SQL入門(mén)慨述
- Oracle教程之pl/sql簡(jiǎn)介
