.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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類