在 PostgreSQL 中使用變數

Shihab Sikder 2023年1月30日 2022年5月14日
  1. 在 PostgreSQL 中使用 DECLARE 宣告變數
  2. 使用 RETURNING 為 PostgreSQL 中的變數賦值
在 PostgreSQL 中使用變數

本文將演示我們如何在 PostgreSQL 中為變數宣告和賦值。

在 PostgreSQL 中使用 DECLARE 宣告變數

通常,你需要 PL/SQL 指令碼中的變數。在名為 DECLARE 的部分中,你需要告訴指令碼你的變數是什麼以及它的型別是什麼。

在 PL/SQL 中,有兩個部分。一個是宣告,另一個是指令碼部分,其中編寫了標準 SQL。格式如下。

DO $$
DECLARE variable_name <TYPE>

BEGIN
    SQL Commands/Scripts
END $$

現在,我們有一張學生表和他們的任務表。我們的工作是找到符合某些條件的學生併為該學生髮出通知。

Students 表如下所示:

CREATE TABLE Students(
    ID SERIAL,
    Student_Name VARCHAR NOT NULL,
    Task VARCHAR,
    Marks INT
);

假設你要儲存學生姓名和任務資訊,其中 id 等於 3。現在,這裡要提一下,我們不知道 id、name 和 task 的資料型別。

如果型別不匹配,則可能會發生錯誤。為了解決這個問題,我們需要使用 <column_name>%type

例子:

do $$
DECLARE
    _name students.student_name%type;
    _task students.task%type;
BEGIN
    SELECT student_name, task
    FROM students
    INTO _name,_task
    WHERE id=3;

    RAISE NOTICE '% got task %', _name,_task;

end; $$;

輸出:

postgres=# select * from students;
 id | student_name | task | marks
----+--------------+------+-------
  1 | Alice        | HW1  |    10
  2 | Alice        | HW2  |     9
  3 | Alice        | HW3  |     0
  4 | Alice        | HW4  |     6
  5 | Bob          | HW1  |     6
  6 | Bob          | HW2  |    10
  7 | Bob          | HW3  |     8
  8 | Bob          | HW4  |     7
(8 rows)

如果你是第一次執行這種 PL/SQL,RAISE 將不起作用,這意味著在執行 SQL 指令碼後不會顯示任何內容。要啟用這個功能,你需要在 psql shell 中執行以下命令。

SET client_min_messages TO NOTICE;

設定後,你可以看到這樣的輸出(執行 PL/SQL 命令後):

postgres=# do $$
postgres$# DECLARE
postgres$#     _name students.student_name%type;
postgres$#     _task students.task%type;
postgres$# BEGIN
postgres$#     SELECT student_name, task
postgres$#     FROM students
postgres$#     INTO _name,_task
postgres$#     WHERE id=3;
postgres$#
postgres$#     RAISE NOTICE '% got task %', _name,_task;
postgres$#
postgres$# end; $$;
NOTICE:  Alice got task HW3
DO

這是另一個關鍵字,INTO。它將所選列的資料放置到相應的變數中。

使用 RETURNING 為 PostgreSQL 中的變數賦值

你已經看到,ID 是上表中的 SERIAL 型別資料。因此,每次插入後它都會增加一。

但是在插入過程中,我們永遠不知道哪個 id 被分配給當前行。

因此,假設你想在向學生表插入命令後檢視 ID。命令如下:

do $$
DECLARE
    _id students.id%type;
BEGIN
    INSERT INTO Students(Student_Name, Task, Marks) 
    VALUES ('Trude','HW1',6)
    RETURNING id INTO _id;

    RAISE NOTICE 'Last insert has id: %', _id;

end; $$;

輸出:

NOTICE:  Last insert has id: 9
postgres=# SELECT * FROM STUDENTS WHERE ID=9;
 id | student_name | task | marks
----+--------------+------+-------
  9 | Trude        | HW1  |     6
(1 row)

此外,你可以在 PL/SQL begin to part 中使用多個查詢。然後,你可以使用該變數檢查一些條件並進行一些 CRUD 操作。

更多資訊可在官方文件中此處獲得。

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

相關文章 - PostgreSQL Variable