程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle從入門到精通復習筆記續集之PL/SQL(輕量版),oracle從入門到精通

oracle從入門到精通復習筆記續集之PL/SQL(輕量版),oracle從入門到精通

編輯:Oracle教程

oracle從入門到精通復習筆記續集之PL/SQL(輕量版),oracle從入門到精通


復習內容:

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

 

吾時而躬身自省,自省使知已之不足,知不足而奮起,未為晚也!

 


問誰有《ORACLE PL/SQL 入門到精通》這本書的電子版?

CSDN上有:Oracle9i PL_SQL從入門到精通

你搜索一下。

已發私信,網盤共享。
 

oracle PL/SQL 從入門到精通 PDF 作者: 丁士鋒 出版社: 清華大學

《Oracle PL/SQL從入門到精通》以面向應用為原則,深入淺出地介紹了Oracle平台上使用PL/SQL語言進行數據庫開發的技術。通過大量的示例,詳細介紹了PL/SQL的語言特性、使用技巧,同時配以兩個在實際工作中的案例深入地剖析了使用PL/SQL進行Oracle開發的方方面面。 《Oracle PL/SQL從入門到精通》附帶1張DVD光盤,內容為作者為《Oracle PL/SQL從入門到精通》錄制的全程語音教學視頻及《Oracle PL/SQL從入門到精通(附光盤)》所涉及的源代碼。
《Oracle PL/SQL從入門到精通》分為5大篇共20章。涵蓋的內容主要有PL/SQL語言基礎、開發環境、變量與類型、控制語句、數據表的管理和查詢、數據表的操縱、使用PL/SQL的記錄與集合、各種內置函數、游標、事務處理、異常處理、子程序、包、面向對象的開發等技術點。通過示例性的代碼,由淺入深,詳細介紹了每一個技術要點在實際工作中的應用,對各種技術要點的應用場合進行了細致的分析。
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved