Oracle資料庫基本操作（四） —— PLSQL編程

来源:https://www.cnblogs.com/gdwkong/archive/2018/03/06/8509354.html
-Advertisement-
Play Games

本文主要介紹Oracle中PLSQL的基本語法——if條件判斷、三種迴圈、游標、例外、存儲過程、存儲函數以及觸發器。 ...


　　Procedure Language 實際上是Oracle對SQL語言的能力擴展,讓SQL語言擁有了if條件判斷,for迴圈等處理。

一、PLSQL基本語法

1 DECLARE 
2     -- 聲明部分
3     變數名 變數類型 := 初始值 
4     變數名 emp.sal % TYPE  -- 引用類型的變數
5           emp % rowtype -- 記錄型變數           
6 BEGIN
7     -- 業務邏輯
8 END ;

1、變數的聲明與使用

 1 -- 已知數據類型的賦值聲明 
 2 DECLARE 
 3     i NUMBER := 100 ;
 4 BEGIN
 5     -- 輸出語句相當於 System.out.print();
 6     dbms_output.put_line('Hello World!' || i) ;
 7 END ;
 8 
 9 -- 未知數據類型的類型聲明
10 -- 輸出7369的工資
11 
12 DECLARE 
13     vsal emp.sal % TYPE ;
14 BEGIN
15     -- 給變數賦值
16     SELECT sal INTO vsal FROM emp WHERE empno = 7369 ; 
17     dbms_output.put_line(vsal) ;
18 END ;
19 
20 -- 記錄型變數聲明與賦值
21 -- 輸出7369的所有信息
22 DECLARE 
23     vrow emp % rowtype ;
24 BEGIN    
25     SELECT * INTO vrow FROM emp WHERE empno = 7369 ;
26     dbms_output.put_line(vrow.empno||'   '|| vrow.ename);
27 END ;

2、if條件判斷語法與使用

 1 -- 根據不同年齡輸出信息
 2 DECLARE
 3     -- 由客戶端輸入     
 4     age number := &aaa;
 5 BEGIN
 6     IF age <= 18 THEN
 7         dbms_output.put_line('未成年人');
 8     ELSIF age > 18 AND age <= 24 THEN
 9         dbms_output.put_line('年輕人');
10     ELSIF age > 24 AND age < 48 THEN
11         dbms_output.put_line('中年人');
12     ELSE 
13         dbms_output.put_line('老年人');
14     END IF;
15 END;

3、三種迴圈

 1 /*
 2    三種迴圈
 3    for 變數名 in 起始值..結束值  loop
 4      
 5    end loop; 
 6   ----------------------------------
 7    while 條件 loop
 8      
 9    end loop;
10   -----------------------------------    
11    loop
12      exit when 退出的條件
13      迴圈體
14    end loop;
15 */
16 
17 -- for 迴圈
18 -- 輸出1-10
19 DECLARE 
20 
21 BEGIN
22     FOR i IN 1..10 LOOP
23         dbms_output.put_line(i);
24     END LOOP;
25 END;
26 -- 輸出10-1
27 DECLARE 
28 
29 BEGIN
30     FOR i IN REVERSE 1..10 LOOP
31         dbms_output.put_line(i);
32     END LOOP;
33 END;
34 
35 -- while 迴圈
36 DECLARE 
37     i NUMBER := 1;
38 BEGIN
39     WHILE i <= 10 loop
40         dbms_output.put_line(i);
41         i := i+1;
42     END LOOP;
43 END;
44 
45 -- 簡單迴圈
46 DECLARE 
47     i NUMBER := 1;
48 BEGIN 
49     LOOP
50         EXIT WHEN i > 10;
51         dbms_output.put_line(i);
52         i := i+1;
53     END LOOP;
54 END;

二、游標

1、游標概述　　

1.1 游標: (游標/指針) 是對查詢結果集的封裝, 相當於是jdbc中的ResultSet

1.2 語法:

1 -- 聲明游標
2     CURSOR 游標名 IS 查詢語句;
3     CURSOR 游標名(參數名 參數類型) IS 查詢語句 WHERE 列名 = 參數名;

1.3 開發步驟:

1.打開游標 open 游標名

2.從游標中提取數據:

fetch 游標名 into 變數

　　   游標名%notfound 沒有數據

　　　游標名%found 找到數據

3.關閉游標 close 游標名

2、使用示例：

 1 -- 無參 
 2 -- 輸出所有員工的信息
 3 DECLARE
 4    -- 聲明游標
 5   CURSOR vemps IS SELECT * FROM emp;
 6    -- 聲明變數
 7   vrow emp % rowtype;
 8 BEGIN
 9    --1. 打開游標
10    open vemps;
11    --2. 提取數據
12    LOOP
13        FETCH vemps INTO vrow;
14         -- 判斷是否有數據
15        EXIT WHEN vemps % notfound;
16         -- 列印數據
17        dbms_output.put_line('姓名:'||vrow.ename||'  工資:'||vrow.sal);
18    END LOOP;
19    
20    -- 關閉游標
21    CLOSE vemps;
22 END;
23 ---------------------------------------------------------------- 
24 -- for 變數游標        
25 DECLARE
26    -- 聲明游標
27    CURSOR vemps IS SELECT * FROM emp;
28    -- 聲明記錄型變數
29    vrow emp % rowtype;
30 BEGIN
31    -- 迴圈遍歷游標
32    FOR vrow IN vemps
33    LOOP
34         dbms_output.put_line('姓名:'||vrow.ename||' 工資:'||vrow.sal);
35    END LOOP;
36 END;
37 
38 -- ===============================================================
39 -- 有參
40 -- 輸出指定部門的員工信息
41 DECLARE
42    -- 聲明游標
43    CURSOR vemps(vdeptno NUMBER) IS SELECT * FROM emp WHERE deptno = vdeptno;
44    -- 聲明記錄型變數
45    vrow emp % rowtype;
46 BEGIN
47    -- 1. 打開游標
48    OPEN vemps(20);
49    -- 2.迴圈遍歷游標
50    LOOP
51     FETCH vemps into vrow;
52     EXIT when vemps % notfound;
53       -- 列印數據
54         dbms_output.put_line('姓名:'||vrow.ename||' 工資:'||vrow.sal);
55    END LOOP;
56    -- 3. 關閉游標
57    CLOSE vemps;
58 END;

三、例外

1、例外概述

      例外 (意外): 相當於是java異常

　　語法: 

 1 declare 
 2     聲明部分 
 3 begin 
 4     業務邏輯
 5 exception 
 6     處理例外 
 7     when 例外1 then 
 8 
 9     when 例外2 then 
10 
11     when others then 
12 
13 end;

　　常見的系統的例外:

    • zero_divide : 除零例外
    • value_error : 類型轉換
    • no_data_found : 沒有找到數據例外
    • too_many_rows : 查詢出多行記錄,但是賦值給了單行變數

2、例外使用示例

 1 DECLARE
 2     i NUMBER;
 3     vrow emp % rowtype;
 4 BEGIN
 5 --     i := 5/0;
 6 --     i := 'aaa';
 7 --     select * into vrow from emp where empno = 1234566;
 8     select * into vrow from emp;
 9 EXCEPTION
10     WHEN too_many_rows THEN
11         dbms_output.put_line('查詢出多行記錄,但是賦值給了單行變數');
12     WHEN no_data_found THEN
13         dbms_output.put_line('發生了沒有找到數據例外');
14     WHEN value_error THEN
15         dbms_output.put_line('發生類型轉換的例外');
16     WHEN zero_divide THEN
17         dbms_output.put_line('發生除零的例外');
18     WHEN others THEN
19         dbms_output.put_line('發生未知的例外');
20 END;

3、自定義例外

　　語法：

 1 DECLARE 
 2     -- 聲明例外
 3     例外名稱 EXCEPTION ;
 4 BEGIN
 5     -- 拋出例外
 6     raise 例外名稱 ; 
 7 EXCEPTION 
 8     -- 捕獲例外
 9     WHEN 例外名稱 THEN
10     ....
11 END ;

　　使用示例：

 1 -- 查詢指定編號的員工,若沒有找到,則拋出自定義例外
 2 DECLARE 
 3     -- 聲明游標
 4     CURSOR vemps IS SELECT * FROM emp WHERE empno = 1234 ; 
 5     -- 記錄型變數
 6     vrow vemps % rowtype ; 
 7     -- 定義例外
 8     no_emp_found EXCEPTION ; 
 9 BEGIN 
10     --1.打開游標
11     OPEN vemps ;
12     --2.提取記錄 
13     FETCH vemps INTO vrow ;
14     -- 判斷是否有數據 
15     IF vemps % notfound THEN 
16         -- 拋出例外
17         raise no_emp_found ; 
18     END IF ; 
19     -- 關閉游標
20     CLOSE vemps ; 
21 EXCEPTION 
22     WHEN no_emp_found THEN 
23         dbms_output.put_line('沒有找到對應的員工') ; 
24 END ;

 四、存儲過程

1、概述　　

　　存儲過程: 實際上是將一段已經編譯好的PLSQL代碼片斷,封裝在資料庫中。

　　作用:

1. 提高執行效率

2. 提高代碼復用性

　　語法:

1 create [or replace] procedure 過程名稱[(參數1 in|out 參數類型,參數2 in|out 參數類型)]
2 is | as
3 　　-- 聲明　　　　
4 begin
5  　 -- 業務
6 end;

2、使用示例

　　

 1 -- 給指定員工漲薪,並列印漲薪前和漲薪後的工資
 2 -- 員工編號 : 輸入參數
 3 -- 漲多少 : 輸入參數
 4 /*
 5    1. 查詢當前工
 6    2. 列印漲薪前工資
 7    3. 漲工資
 8    4. 列印漲薪後的工資
 9    5. 提交數據
10 */
11 create or replace procedure proc_updatesal(vempno in number,vcount in number)
12 is
13    -- 聲明變數記錄當前工資
14    vsal number;    
15 begin
16    --1. 查詢當前工資
17    select sal into vsal from emp where empno=vempno;
18    --2. 列印漲薪前工資
19    dbms_output.put_line('漲薪前:'||vsal);
20    --3. 漲工資
21    update emp set sal=vsal+vcount where empno=vempno;
22    -- 4. 列印漲薪後的工資
23    dbms_output.put_line('漲薪後:'||(vsal+vcount));
24    --5. 提交數據
25    commit;
26 end;
27 
28 -- 調用存儲過程
29 -- 方式1:
30 call proc_update_sal(7369,100);
31 
32 -- 方式2:
33 declare
34 
35 begin
36    proc_updatesal(7369,100);
37 end;
38 
39 s
40 -- 獲取指定編號員工的年薪
41 /*
42    編號: in  輸入
43    年薪: out 輸出
44 */
45 create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
46 is
47        
48 begin
49   select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
50 end;
51 
52 -- plsql代碼片斷中調用
53 declare
54    yearsal number;
55 begin
56    proc_getyearsal(7369,yearsal);
57    dbms_output.put_line(yearsal);
58 end;
59 
60 
61 -- 封裝存儲過程,輸出的是游標類型, 所有員工
62 /*
63    sys_refcursor : 系統引用游標
64 */
65 create or replace procedure proc_getemps(vemps out sys_refcursor)
66 is
67 
68 begin
69     -- 打開游標, 誰調用誰關閉
70     open vemps for select * from emp;   
71 end;
72 
73 declare
74   vemps sys_refcursor;
75   vrow emp%rowtype;
76 begin
77   -- 調用存儲過程
78   proc_getemps(vemps);
79   
80   loop
81      fetch vemps into vrow; 
82      exit when vemps%notfound;  
83      dbms_output.put_line(vrow.ename);
84   end loop;
85   -- 關閉游標
86   close vemps;
87 end;

五、存儲函數

1、存儲函數概述

　　存儲函數: 實際上是將一段已經編譯好的PLSQL代碼片斷,封裝在資料庫中。

　　作用:

1. 提高執行效率

2. 提高代碼復用性

　　語法: 

1 create [or replace] function 函數名稱(參數1 in|out 參數類型) return 返回類型 
2 is 
3 
4 begin 
5 
6 end;

　　存儲過程和存儲函數:

1. 函數有返回值,過程沒有返回值

2. 函數可以直接在SQL語句中使用,過程不可以

3. 函數能實現的功能,過程能實現

4. 過程能實現的功能,函數也能實現

5. 函數和過程本質上沒有區別 通常情況下,我們自己開發封裝的是存儲過程

2、使用示例

 1 -- 存儲函數:獲取年薪
 2 create or replace function func_getyearsal(vempno number) return number
 3 is
 4   vyearsal number;
 5 begin
 6   select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
 7   return vyearsal;
 8 end;
 9 
10 -- 調用
11 declare
12    yearsal number;
13 begin
14    yearsal := func_getyearsal(7369);
15    dbms_output.put_line(yearsal);
16 end;
17 
18 select emp.*,func_getyearsal(emp.empno) from emp;

 六、觸發器

　　1、資料庫觸發器是一個與表相關的、存儲的PL/SQL程式。每當一個特定的數據操作語句（insert，update，delete）在指定的表上發出是，Oracle自動地執行觸發器中定義的語句序列。

　　2、作用：

    • 監聽表中的數據變化；
    • 對錶中的數據進行校驗

　　3、語法：

 1 CREATE [OR REPLACE] TRIGGER 觸發器名稱 
 2 {BEFORE | AFTER}
 3 {INSERT | UPDATE | DELETE} 
 4 ON 表名 
 5 [ FOR EACH ROW [WHEN(條件)]]
 6 DECLARE
 7     ....
 8 BEGIN
 9     PLSQL塊
10 END 觸發器名;

　　4、觸發器的類型

  • 行級觸發器：一條SQL語句，影響了多少行記錄，觸發器就會執行多少次；
    • 兩個內置對象：
      • :new 新的記錄
      • :old 舊的記錄
  • 語句級觸發器：一條SQL語句，無論影響了多少行記錄，都只觸發一次；

　　5、使用示例

  1 -- 若用戶向表中插入數據之後, 列印一句話
  2 create or replace trigger tri_test1
  3 after
  4 insert 
  5 on emp
  6 declare
  7 
  8 begin
  9    dbms_output.put_line('有人插入了....');
 10 end;
 11 
 12 insert into emp(empno,ename) values(9527,'華安');
 13 -- 執行一條更新工資的語句
 14 
 15 -- 周二老闆不在,不能辦理員工入職(不能向員工表中插入數據)
 16 -- 觸發器
 17 -- before insert
 18 -- 判斷今天是否是周二
 19 select trim(to_char(sysdate,'day')) from dual;
 20 
 21 create or replace trigger tri_checkday
 22 before
 23 insert
 24 on emp
 25 declare
 26    vday varchar2(20);
 27 begin
 28    -- 查詢當前周幾
 29    select trim(to_char(sysdate,'day')) into vday from dual;
 30    -- 判斷是否為周二,若為周二,則需要中斷插入操作
 31    if vday = 'tuesday' then
 32      --                   -20000 - -20999
 33      raise_application_error(-20001,'周二老闆不在,不能插入');
 34    end if;
 35 end;
 36 
 37 insert into emp(empno,ename) values(9527,'華安');
 38 
 39 select * from emp;
 40 
 41 -- 語句級觸發器
 42 create trigger tri_test3
 43 before
 44 update
 45 on emp
 46 declare
 47 
 48 begin
 49   dbms_output.put_line('語句級觸發器'); 
 50 end;
 51 
 52 -- 行級觸發器
 53 create or replace trigger tri_test4
 54 before
 55 update
 56 on emp
 57 for each row
 58 declare
 59 
 60 begin
 61   dbms_output.put_line('行級觸發器,舊的工資:'||:old.sal||'  新的工資:'||:new.sal); 
 62 end;
 63 
 64 update emp set sal=sal+100;
 65 
 66 -- 6個月 ---> 人事 加薪 ---> 加10塊錢 ---> 老闆簽字
 67 -- 校驗員工薪資 調整後的工資一定要 大於 薪資調整前的工資
 68 -- 觸發器:  before update on emp
 69 -- 行級觸發器
 70 create or replace trigger tri_checksal
 71 before
 72 update
 73 on emp
 74 for each row
 75 declare
 76 
 77 begin
 78   -- 調整後的工資 <= 薪資調整前的工資 ,則中斷更新操作
 79   -- :new.sal    <= :old.sal
 80   if :new.sal <= :old.sal then
 81      raise_applicat
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Oracle總結【PLSQL學習】
    PLSQL介紹 PLSQL是Oracle對SQL99的一種擴展 ，基本每一種資料庫都會對SQL進行擴展，Oracle對SQL的擴展就叫做PLSQL... SQL99是什麼 （1） 是操作所有關係型資料庫的規則 （2）是第四代語言 （3） 是一種結構化查詢語言 （4）只需發出合法合理的命令，就有對應的 ...
  • Oracle資料庫基本操作 （五） —— 使用java調用存儲過程
    本文主要展示如何利用java代碼連接Oracle數據並調用存儲過程的簡單方法。 ...
  • 【大數據開發】hadoop2.x碰到的問題一
    hadoop2.x出現的問題及解決方案：INFO - Connecting to ResourceManager at node-11/202.96.64.121:8032 WARN - Hadoop command-line option parsing not performed. Implem... ...
  • Redis Cluster的搭建與部署，實現redis的分散式方案
    前言 上篇Redis Sentinel安裝與部署，實現redis的高可用實現了redis的高可用，針對的主要是master宕機的情況，我們發現所有節點的數據都是一樣的，那麼一旦數據量過大，redi也會效率下降的問題。redis3.0版本正式推出後，有效地解決了Redis分散式方面的需求，當遇到單機內 ...
  • Redis實現資料庫讀寫分離
    Redis是一種NoSQL的文檔資料庫，通過key-value的結構存儲在記憶體中，Redis讀的速度是110000次/s，寫的速度是81000次/s，性能很高，使用範圍也很廣。 下麵用一個實例實現redis的讀寫分離，步驟如下： 第一步：下載redis 官網下載地址： https://redis.i ...
  • SQL Server 歷史SQL執行記錄
    SELECT TOP 1000 --創建時間 QS.creation_time, --查詢語句 SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, ((CASE QS.statement_end_offset WHEN -1 THEN DATALEN ...
  • 通過phoenix在hbase上創建二級索引，Secondary Indexing
    環境描述： 操作系統版本：CentOS release 6.5 (Final) 內核版本：2.6.32-431.el6.x86_64 phoenix版本：phoenix-4.10.0 hbase版本：hbase-1.2.6 hbase節點分佈：1個HMaster，2個RegionServer 文檔目 ...
  • MYSQL NULL值特性
    NULL是一種“沒有類型”的值，通常表示“無值”，“未知值”，“缺失值”，“超界”，“不在其中”等，我們在日常運用中很容易和NULL字元串混淆，這裡大致整理了下NULL值的一些特性，以便能夠正確使用NULL值。 1，創建表時欄位時若未添加預設值，則預設為NULL值 2，NULL值和NULL字元串的區 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類