.Net程式員學用Oracle系列(26)：PLSQL 之類型、變數和結構

来源:http://www.cnblogs.com/hanzongze/archive/2017/05/16/Oracle-plsql-1.html
-Advertisement-
Play Games

" 1、類型 " "1.1、屬性類型" "1.2、記錄類型" " 2、變數 " "2.1、變數類型" "2.2、變數定義" "2.3、變數賦值" " 3、結構 " "3.1、順序結構" "3.2、選擇結構" "3.3、迴圈結構" " 4、總結 " 1、類型 在 "《.Net程式員學用Oracle系列 ...


1、類型

在《.Net程式員學用Oracle系列(5)：三大數據類型》一文中詳細地講述了 Oracle 的基本數據類型，文中還提到，除基本數據類型之外，Oracle 還在語法上支持一些非固有數值類型。

事實上，Oracle 在語法上支持的數據類型遠不止於此，Oracle 還支持一些複雜而強大的數據類型。如屬性類型%TYPE和%ROWTYPE，記錄類型 RECORD，集合類型 VARRAY 和 TABLE 等。本節將會介紹實用的屬性類型和靈活的記錄類型。

1.1、屬性類型

屬性類型是一種可以直接引用資料庫中列的數據類型來描述變數類型的類型。Oracle 提供了兩種屬性類型，分別是%TYPE和%ROWTYPE，下文將逐一說明。

%TYPE：該屬性允許在聲明中引用資料庫中的列或先前聲明的變數的數據類型，而不是硬編碼類型名稱。在聲明常量、變數和參數時，都可以使用%TYPE屬性作為數據類型說明符。如果引用的類型被更改，則聲明也將自動更新，這點有利於後期代碼維護。

示例：

DECLARE
  v_staff_name t_staff.staff_name%TYPE;
BEGIN
  SELECT t.staff_name INTO v_staff_name FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff_name);
END;

%ROWTYPE：該屬性可以表示資料庫中表或游標的行的記錄類型。使用%ROWTYPE聲明的變數可以存儲從表中選擇或從游標或游標變數獲取的整行數據，且變數記錄中的欄位和每行中的相應列具有相同的名稱和數據類型。

示例：

DECLARE
  v_staff t_staff%ROWTYPE;
BEGIN
  SELECT t.* INTO v_staff FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff.staff_name);
END;

想要更多的瞭解屬性類型可參考：《Database PL/SQL User's Guide and Reference: %TYPE Attribute》和《Database PL/SQL User's Guide and Reference: %ROWTYPE Attribute》。

1.2、記錄類型

記錄類型是由單行多列標量構成的複合結構。可以看做是一種用戶自定義的數據類型，提供了將一個或多個標量封裝成一個對象進行操作的能力。在使用記錄數據類型的變數時，需要在聲明部分先定義記錄的成員變數，然後在執行部分引用該記錄變數本身或其中的成員。但不可以對記錄做整體性的比較運算，如判斷記錄類型的變數是否為 NULL。

示例：

DECLARE
  TYPE staff_type IS RECORD(
    staff_name VARCHAR2(50),
    gender VARCHAR2(2)
  );
  v_staff staff_type;
BEGIN
  SELECT t.staff_name,DECODE(t.gender,1,'男',0,'女','兩性') INTO v_staff.staff_name,v_staff.gender
  FROM demo.t_staff t WHERE t.staff_id=5;
  DBMS_OUTPUT.PUT_LINE(v_staff.staff_name||'|'||v_staff.gender);
END;

記錄類型和%ROWTYPE屬性在用途上比較相似，區別在於前者是自定義結構，而後者為表結構，前者比較靈活，而後者比較方便。

2、變數

一般電腦編程語言中都有變數的概念，PL/SQL 也不例外，變數用於存儲計算結果和表示可變狀態，本節將著重介紹在 PL/SQL 中變數如何定義及賦值。另外，PL/SQL 中也有常量，只是極少有人使用，有興趣的讀者可以參考《Oracle Database PL/SQL Language Reference: Constant》。

2.1、變數類型

在 PL/SQL 中定義變數的可選類型非常多，包括 Oracle 中常見的三大類基本數據類型，以及 Oracle 在語法上支持的諸多非固有數據類型。如整形(INT/INTEGER)、布爾類型(BOOLEAN)等 Oracle 本身並不支持，卻在 PL/SQL 中可用的數據類型。

2.2、變數定義

在 PL/SQL 中定義變數與 C# 中定義變數本質上並無區別，不同的是 PL/SQL 中的變數得集中定義，變數定義區域得用DECLARE關鍵字開頭，且每行只能定義一個變數。如果是 SQL*Plus 環境則必須用VAR[IABLE]開頭。

語法：

variable_name datatype [[NOT NULL] {:= | DEFAULT} expression];

如果使用了 NOT NULL 則必須給變數賦初始值。另外，在命名變數的時候還需要遵守以下命名規則：

  • 1、變數名必須以字母開頭。
  • 2、變數名長度不能超過 30 個字元。
  • 3、變數名中不能含有空格。
  • 4、同一語句塊內變數名不能重覆。
  • 5、變數名不能與查詢中的列名相同。

2.3、變數賦值

給 PL/SQL 變數賦值的寫法與給 C# 欄位賦值寫法基本一樣，既可以在定義變數時就賦給它一個初始值，也可以在使用之前給它賦值，如果不賦值也會有預設值。唯一的區別就是，C# 中類型不同預設值也不同，而 PL/SQL 中所有類型的預設值都一樣，都是 NULL。

在 PL/SQL 中還有一點比較怪的就是，可能所有編程語言的賦值操作符都是=，而 PL/SQL 中的賦值操作符卻偏偏是:=。而且 Oracle 相關的 API 中參數寫法也與其它大多數資料庫不同。

示例一（普通 PL/SQL 環境）：

DECLARE
  v1 NUMBER;
  v2 NUMBER(5,2);
  v3 NUMBER := 50.20;
  v4 NUMBER(4) := 1998;
  v5 VARCHAR2(4) DEFAULT 'A';
  v6 DATE NOT NULL := fn_now;
BEGIN
  v1 := 100;
  v2 := 99.99;
  v5 := 'A5';
  v6 := SYSDATE;
  DBMS_OUTPUT.PUT_LINE(v1||'|'||v2||'|'||v3||'|'||v4||'|'||v5||'|'||v6);
END;

示例二（SQL*Plus 環境）：

VARIABLE v1 NUMBER
BEGIN
  :v1 := 12;
  DBMS_OUTPUT.PUT_LINE(:v1);
END;
/

3、結構

和普通編程語言一樣，PL/SQL 中也有常見的三大控制結構以及順序控制語句——GOTO。本節將重點講述被廣泛接受的三大控制結構，至於不受待見的 GOTO 語句，有興趣的讀者可以參考《Oracle Database PL/SQL User's Guide and Reference: Using the GOTO Statement》。

3.1、順序結構

順序結構是面向過程編程中最基本、最簡單、最常用的程式控制結構。順序結構用於表示若幹個依次執行的處理步驟，表現形式就是線性結構，一個方向走下去、不拐彎。使用時只要按照解決問題的順序寫出相應的語句就行，它的執行順序是自上而下、依次執行。

3.2、選擇結構

PL/SQL 中提供了兩種選擇結構，分別是IF結構和CASE結構。其中IF結構有三種變體，CASE結構有兩種變體。下文將逐一講述各個選擇語句：

IF 結構變體一：

語法：

IF condition THEN
  {...statements to execute when condition is TRUE...}
END IF;

示例：

BEGIN
  IF 1>0 THEN
    DBMS_OUTPUT.PUT_LINE('executed');
  END IF;
END;

IF 結構變體二：

語法：

IF condition THEN
  {...statements to execute when condition is TRUE...}
ELSE
  {...statements to execute when condition is FALSE...}
END IF;

示例：

BEGIN
  IF 1>2 THEN
    DBMS_OUTPUT.PUT_LINE('The result is true');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The result is false');
  END IF;
END;

IF 結構變體三：

語法：

IF condition1 THEN
  {...statements to execute when condition1 is TRUE...}
ELSIF condition2 THEN
  {...statements to execute when condition2 is TRUE...}
[ELSE
  {...statements to execute when both condition1 and condition2 are FALSE...}]
END IF;

示例：

BEGIN
  IF 1>2 THEN
    DBMS_OUTPUT.PUT_LINE('1>2 branch');
  ELSIF 1<2 THEN
    DBMS_OUTPUT.PUT_LINE('1<2 branch');
  ELSE
    DBMS_OUTPUT.PUT_LINE('1=2 branch');
  END IF;
END;

註意：IF 結構變體三中有個巨坑，就是 IF 和 ELSE 之間的分支寫法，不是 ELSE IF 也不是 ELSEIF 而是 ELSIF。儘管你寫成 ELSE IF 編輯器也有智能提示，但當你執行的時候就會報ORA-06550的錯，而且這個錯誤的描述正常人基本沒可能看懂。

CASE 結構變體一

示例：

DECLARE
  v_grade VARCHAR2(1);
BEGIN
  v_grade:='B';
  CASE v_grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('甲');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('乙');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('丙');
    ELSE DBMS_OUTPUT.PUT_LINE('丁');
  END CASE;
END;

CASE 結構變體二

示例：

DECLARE
  v_score NUMBER(3);
BEGIN
  v_score:=78;
  CASE
    WHEN v_score>=80 THEN DBMS_OUTPUT.PUT_LINE('優');
    WHEN v_score>=70 THEN DBMS_OUTPUT.PUT_LINE('良');
    WHEN v_score>=60 THEN DBMS_OUTPUT.PUT_LINE('中');
    ELSE DBMS_OUTPUT.PUT_LINE('差');
  END CASE;
END;

與多分支的 IF 語句相比，CASE 語句更可讀、更高效，所以當程式分支較多時，應儘可能的使用 CASE 而不是 IF。CASE 語句的 ELSE 子句是可選的。但如果省略 ELSE 字句，PL/SQL 將為 CASE 語句添加以下隱式的 ELSE 子句：

ELSE RAISE CASE_NOT_FOUND;

換句話說，如果你省略了 ELSE 子句，且 CASE 語句與 WHEN 子句不匹配，PL/SQL 就會引發預定義的異常CASE_NOT_FOUND。

3.3、迴圈結構

PL/SQL 中提供了三種迴圈結構，分別是LOOP、WHILE LOOP和FOR LOOP。另外 PL/SQL 還提供了EXIT語句用於退出當前迴圈。下文將逐一講述各個迴圈語句：

LOOP 迴圈

語法：

LOOP
  {...statements...}
  EXIT [ WHEN boolean_condition ];
END LOOP;

示例一：

DECLARE
  v_counter BINARY_INTEGER := 0;
BEGIN
  LOOP
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE(v_counter); -- 輸出結果：1、2、3、4、5、6、7、8、9
    IF v_counter >= 9 THEN
      EXIT;
    END IF;
    -- 上面的 IF 語句塊還可以由“EXIT WHEN v_counter >= 9;”代替
  END LOOP;
END;

示例二（嵌套迴圈）：

DECLARE
  i BINARY_INTEGER := 0;
  j BINARY_INTEGER := 0;
BEGIN
  LOOP
    i := i + 1;
    j := 0;
    
    LOOP
      j := j + 1;
      DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
      EXIT WHEN j >= 3;
    END LOOP;
    
    EXIT WHEN i >= 4;
  END LOOP;
END;

示例三（標記迴圈）：

DECLARE
  i BINARY_INTEGER := 0;
  j BINARY_INTEGER := 0;
BEGIN
  <<outer_loop>>
  LOOP
    i := i + 1;
    j := 0;
    <<inner_loop>>
    LOOP
      j := j + 1;
      DBMS_OUTPUT.PUT_LINE('i*j=('||i||'*'||j||')='||i*j);
      EXIT inner_loop WHEN j >= 3;
      EXIT outer_loop WHEN i >= 4;
    END LOOP inner_loop;
  END LOOP outer_loop;
END;

WHILE LOOP 迴圈

語法：

WHILE condition
LOOP
  {...statements...}
END LOOP;

示例：

DECLARE
  v_score NUMBER(3) := 0;
BEGIN
  WHILE v_score < 60 LOOP
    v_score := v_score + 10;
    DBMS_OUTPUT.PUT_LINE(v_score); -- 輸出結果：10、20、30、40、50、60
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('over');
END;

FOR LOOP 迴圈

語法：

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
  {...statements...}
END LOOP;

示例一（正向迴圈）：

BEGIN
  FOR i IN 3..7 LOOP
    DBMS_OUTPUT.PUT_LINE(i); -- 輸出結果：3、4、5、6、7
  END LOOP;
END;

示例二（反向迴圈）：

BEGIN
  FOR i IN REVERSE 3..7 LOOP
    DBMS_OUTPUT.PUT_LINE(i); -- 輸出結果：7、6、5、4、3
  END LOOP;
END;

註意：FOR LOOP 迴圈中的計數器（變數）可以被讀取，但不能被修改。另外，在 LOOP 迴圈的示例中用到的 EXIT 和迴圈標記，同樣可用於 WHILE LOOP 迴圈和 FOR LOOP 迴圈中。

4、總結

本文主要講述了 PL/SQL 中的變數和結構兩個基本的編程元素，以及屬性類型和記錄類型。本人覺得對於常年使用 Oracle 的朋友來說，這幾個知識點應該是必須要掌握的。

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-plsql-1.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫博客，水平有限，若有不當之處，敬請批評指正，謝謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在excel中將缺失數據全部用0補齊
    先ctrl+H ，出現如下對話框 點擊“定位”，選擇“空值” 在表格中空的位置上編輯欄輸入0,CTRL+ENTER，即可將缺失數據全部用0補齊。 ...
  • mysql-存儲過程（轉載）
    本來想自己寫存儲過程的，但是看到別人寫的很全面，就直接轉載過來了。 轉自（http://www.cnblogs.com/exmyth/p/3303470.html） 14.1.1 創建存儲過程 MySQL中，創建存儲過程的基本形式如下： 其中，sp_name參數是存儲過程的名稱；proc_param ...
  • MySQL : 資料庫和表的基本操作總結
    針對database和table的操作大致可分為三類：創建，查詢，修改，刪除 1. 創建 create ①創建資料庫 create database db_name; ②創建表 create table db_name.tb_name (列的屬性) [表選項]; tip : 必須指明所屬的資料庫（也 ...
  • PL/SQL database character set(AL32UTF8) and Client character set(ZHS16GBK) are different
    轉自：http://blog.163.com/magicc_love/blog/static/18585366220123111128283/ 修改部分為紅色部分，原作者寫錯了 啟動PL/SQL Developer 報字元編碼不一致錯誤 Database character set (AL32UTF ...
  • 用Docker在一臺筆記本電腦上搭建一個具有10個節點7種角色的Hadoop集群（下）-搭建Hadoop集群
    上篇：用Docker在一臺筆記本電腦上搭建一個具有10個節點7種角色的Hadoop集群（上）-快速上手Docker 上篇介紹了快速上手Docker部分，下麵接著介紹搭建Hadoop集群部分。 六、搭建Hadoop偽分佈模式 我們先用前面創建的這個容器來搭建Hadoop偽分佈模式做測試，測試成功後再搭 ...
  • informix資料庫的日誌
    看到一句sql，沒見過： 經過百度，找到如下解釋： ...
  • SQL必知必會 -------- 聚集函數、分組排序
    聚集函數 1.AVG()函數 輸入：SELECT AVG(prod_price) AS avg_price FROM Products 輸出： 警告：只用於單個列AVG()只能用來確定特定數值列的平均值，而且列名必須作為函數參數給出。為了獲得多個列的平均值，必須使用多個AVG()函數。說明：NULL ...
  • Powerpivot PowerBI相關組件下載安裝（附操作截圖）
    載入方式：com載入項載入方法： 點擊Excel界面【文件】→【選項】→【載入項】→【COM載入項】→【轉到】 Excel2013載入PowerView Excel216PowerQuery不需要載入，在數據標簽下： 16載入完成界面 PowerPivot 說明：13、16版本集成 10版下載地址： ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類