Oracle基本修練: PL/SQL Variables & Constants 變量與常量

本篇記錄PL/SQL variables與Constants使用方式，是宣告的區塊要設定，並進行執行。

Content

Part 1: Variable

• Declaring variables
• Default values
• NOT NULL constraint
• Variable assignments
• Anchored declarations

Part 2: Constants

• Introduction to PL/SQL constants
• PL/SQL constant examples

Let’s Start!

在進行執行PL／SQL之前，我們需要宣告變數，而這部分就會結合上一篇介紹PL/SQL有包含哪些資料格式，在此開始進入實作!

Part 1: Variable

Declaring variables

基本語法如下:

variable_name datatype [NOT NULL] [:= initial_value];

命名邏輯應包含使用的用途敘述，並且根據需求設定對應的資料格式。例如:

DECLARE
    l_total_sales NUMBER(15,2);      --銷量單位使用數值
    l_credit_limit NUMBER (10,0);    --限制數量一樣用數值儲存
    l_contact_name VARCHAR2(255);    --名稱用character儲存
BEGIN
    NULL;
END;

Default values

設計變數如進入迴圈的使用或判斷式時，我們會可能會需要在初始值帶入default數值，使用方式用 := 或 DEFAULT 都可以指定default。例如:

--使用:=設定預設
DECLARE
  l_product_name VARCHAR2( 100 ) := 'Laptop';
BEGIN
  NULL;
END;
--使用DEFAULT設定預設，與前者有一樣結果
DECLARE 
   l_product_name VARCHAR2(100) DEFAULT 'Laptop';
BEGIN 
   NULL; 
END;

NOT NULL constraint

在SQL設計table時，我們會為欄位加入限制(constraint)，在PL/SQL也可以設定 NOT NULL constraint，那麼該variable就不能接受null的狀況，且必須在使用前給予預設值。

DECLARE
  l_shipping_status VARCHAR2( 25 ) NOT NULL := 'Shipped';
BEGIN
  l_shipping_status := '';     -- 執行區塊使variable但會報錯，如下
END;
Code language: SQL (Structured Query Language) (sql)

PL/SQL issued the following error:

ORA-06502: PL/SQL: numeric or value error

Variable assignments

變數賦值用 := 。

DECLARE     
    l_business_parter VARCHAR2(100) := 'Distributor';     
    l_lead_for VARCHAR2(100); 
BEGIN     
    l_lead_for := l_business_parter;         
    DBMS_OUTPUT.PUT_LINE(l_lead_for); 
END;

Anchored declarations 錨定宣告

用法為當我們給予變數一個資料格式，但在執行過程中，可能會碰到不同資料格式的問題。

這時候就可以透過錨定宣告來解決。例如:

DECLARE   
    l_customer_name customers.name%TYPE;
    l_credit_limit customers.credit_limit%TYPE; 
BEGIN   
    SELECT  name, credit_limit   
    INTO    l_customer_name, l_credit_limit   
    FROM    customers   
    WHERE   customer_id = 38;       
    DBMS_OUTPUT.PUT_LINE(l_customer_name || ':' || l_credit_limit ); 
END; /

1. 首先宣告l_customer_name ,l_credit_limit數據類型分別錨定到name、credit_limit。
2. 再執行區塊查詢後，就會根據customer name和credit limit與where條件賦值給l_customer_name,l_credit_limit ，最後回傳結果。

PL/SQL 返回以下輸出：

Jimmy Wang: 168168168 

Part 2: Constants

Introduction to PL/SQL constants

就是給予一個明確、不可變的特定數值、布林值或字串。而其也有幾個特性:

1. 相對於變量(variables)，常量具備更易讀性，特別是用於計算公式中。
2. 在 NOT NULL constraint常量也可以使用，避免塞入null或空字串。

PL/SQL constant examples

1. 下面範例如先給予 co_payment_term =45，但在執行區塊企圖改寫，系統就會跳error。

DECLARE     
    co_payment_term   CONSTANT NUMBER   := 45;       -- days      
    co_payment_status CONSTANT BOOLEAN  := FALSE;  
BEGIN     
    co_payment_term := 30;                           -- error 
END;

error message:

PLS-00363: expression 'CO_PAYMENT_TERM' cannot be used as an assignment target

2. 當然常量也可以用來在宣告中計算

DECLARE      
    co_pi     CONSTANT REAL := 3.14159;     
    co_radius CONSTANT REAL := 10;     
    co_area   CONSTANT REAL := (co_pi * co_radius**2); 
BEGIN     
    DBMS_OUTPUT.PUT_LINE(co_area); 
END;

如上面範例co_area為另外兩個常量的表達式接收值。

以上為PL/SQL會使用到的variables與Constants使用方式。

資料來源:
https://www.oracletutorial.com/plsql-tutorial/plsql-data-types/
https://www.oracletutorial.com/plsql-tutorial/plsql-constants/