復習內容:
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.會寫一個觸發器
復習記錄類型:
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;
也可以升級一下,要是想對表的所有列都輸出,則:(須注意輸出的列名要跟表中的列名要一樣)
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;
可以進行優化如下:
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;
或者使用記錄再優化一下:
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循環最優化:(注意:在for循環中它會自動的打開游標、提取游標,當提取完裡面的數據後也會自動
的關閉游標)
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;
利用游標,調整公司中員工的工資:
工資范圍 調整基數
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 varchar2 is (相當於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 varchar2 is 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_sysdate return varchar2 is 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 number is 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 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; 調用: select get_sal(80) from dual;
定義一個函數:獲取給定部門的工資總和 和 該部門的員工總數(定義為OUT類型的參數)。
要求:部門號定義為參數,工資總額定義為返回值。
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; 調用: 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_trigger after update on employees for each row (行級觸發器,即每更新一條記錄就會輸出一次'helloworld',若沒有這語句則是語句級觸發器) 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 ;
編寫一個觸發器,在對my_emp記錄進行刪除的時候,在my_emp_bak表中備份對應的記錄
1、創建my_emp表: create table my_emp as select employee_id,salary from employees ; 2、創建my_emp_bak表: create table my_emp_bak as select employee_id,salary from employees where 1=2; 3、檢查創建的表中的記錄: select * from my_emp select * from my_emp_bak 4、創建一個觸發器: 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、執行含有觸發器時間的語句: delete from my_emp 6、檢查觸發器執行後的結果: select * from my_emp select * from my_emp_bak
PL/SQL塊的結構如下:
DECLARE
/* 聲明部分: 在此聲明PL/SQL用到的變量,類型及游標,以及局部的存儲過程和函數 */
BEGIN
/* 執行部分: 過程及SQL 語句 , 即程序的主要部分 */
EXCEPTION
/* 執行異常部分: 錯誤處理 */
END;
其中 執行部分是必須的
先說說我的親自經歷吧:我是個中專生,原本學的是電子技術專業,從學校畢業後,一個偶然的機會接觸到ORACLE,並且提供了很好的學習環境,經過一段時間後,掌握了一些基本的SQL語句,漸漸開始接手更加復雜的工作,自已單獨開發FORM,REPORT等,慢慢地愛上了這個行業,因為它總是帶來很多新鮮事物,擁有較多學習機會,碰到問題時的挑戰感以及解決問題後的成就感,讓我在這行做了6年,目前服務於一家軟件公司,專門編寫鞋廠的ERP,主要就是通過用ORACLE的開發工具Developer編寫程式及報表,讓使用者利用他們輸入一些簡單的資料後,通過匯總,計算等,產生一些報表,以利於記錄數據,便於分析及統計.
其實學習ORACLE並不需要什麼專業計算機知識,關鍵是最初能有一個學習的環境,最好有人先指導一番,了解一個大概,再多花時間看書,如果是屬於那種獨立思維能力強,分析能力強且有耐心,做得住的人,我相信,通過一段時間的努力,一定會掌握這門技術,並且愛上這個行業的!加油哦!