溫馨提示×

Debian PostgreSQL存儲過程編寫教程

小樊
42
2025-09-23 22:32:32
欄目: 云計算

Debian環境下PostgreSQL存儲過程編寫全教程

一、環境準備:在Debian上安裝PostgreSQL

在編寫存儲過程前,需確保Debian系統已安裝PostgreSQL數據庫。使用以下命令完成安裝:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

安裝完成后,啟動PostgreSQL服務并設置開機自啟:

sudo systemctl start postgresql
sudo systemctl enable postgresql

二、連接到PostgreSQL數據庫

使用psql命令行工具連接數據庫(默認以postgres超級用戶身份登錄):

sudo -u postgres psql

若需創建新數據庫或用戶,可在psql中執行以下命令:

-- 創建數據庫
CREATE DATABASE mydb;

-- 創建用戶并授予權限
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

退出psql:輸入\q并按回車鍵。

三、存儲過程基礎語法

PostgreSQL通過CREATE FUNCTION語句創建存儲過程(從PostgreSQL 11開始支持CREATE PROCEDURE語法,用于不返回結果集的過程)?;菊Z法結構如下:

CREATE OR REPLACE FUNCTION function_name(
    parameter1 datatype [DEFAULT default_value],
    parameter2 datatype [DEFAULT default_value]
)
RETURNS return_datatype
LANGUAGE plpgsql
AS $$
DECLARE
    -- 變量聲明(可選)
    variable1 datatype;
    variable2 datatype;
BEGIN
    -- 業務邏輯(SQL語句、控制結構、異常處理等)
    RETURN value; -- 僅RETURNS指定類型時需要
END;
$$;
  • CREATE OR REPLACE FUNCTION:創建新函數或替換已有同名函數;
  • parameter:輸入參數(IN為默認模式,可省略);
  • RETURNS:指定返回值類型(若不返回結果,用RETURNS VOID);
  • LANGUAGE plpgsql:指定使用PL/pgSQL過程語言(PostgreSQL內置,支持復雜邏輯);
  • DECLARE:聲明局部變量(可選);
  • BEGIN...END:包裹業務邏輯代碼塊。

四、簡單存儲過程示例

1. 無參存儲過程:插入測試數據

假設有一個employees表(需提前創建):

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2)
);

創建無參存儲過程insert_employee,向表中插入一條測試數據:

CREATE OR REPLACE FUNCTION insert_employee()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, salary) VALUES ('Alice', 5000.00);
    RAISE NOTICE 'Inserted a new employee: Alice';
END;
$$;

調用方式

SELECT insert_employee();
-- 或使用CALL(PostgreSQL 11+推薦)
CALL insert_employee();

執行后,employees表將新增一條id=1、name='Alice'、salary=5000.00的記錄。

2. 帶輸入參數的存儲過程:計算兩數之和

創建存儲過程add_numbers,接收兩個整數參數并返回它們的和:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

調用方式

SELECT add_numbers(10, 20); -- 返回30

3. 帶輸出參數的存儲過程:獲取員工總數

創建存儲過程get_employee_count,通過輸出參數返回employees表的記錄數:

CREATE OR REPLACE FUNCTION get_employee_count(out_count OUT INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO out_count FROM employees;
END;
$$;

調用方式

CALL get_employee_count(out_count => employee_count);
SELECT employee_count; -- 顯示員工總數

五、控制結構與異常處理

1. 條件判斷(IF-ELSE)

創建存儲過程check_salary,根據員工薪水輸出不同提示:

CREATE OR REPLACE FUNCTION check_salary(emp_id INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    emp_sal NUMERIC(10, 2);
BEGIN
    SELECT salary INTO emp_sal FROM employees WHERE id = emp_id;
    IF emp_sal > 10000 THEN
        RAISE NOTICE 'Employee % has a high salary: %.2f', emp_id, emp_sal;
    ELSIF emp_sal > 5000 THEN
        RAISE NOTICE 'Employee % has a medium salary: %.2f', emp_id, emp_sal;
    ELSE
        RAISE NOTICE 'Employee % has a low salary: %.2f', emp_id, emp_sal;
    END IF;
END;
$$;

調用方式

CALL check_salary(1); -- 根據id=1的員工薪水輸出提示

2. 循環(FOR)

創建存儲過程batch_insert,批量插入10條員工記錄:

CREATE OR REPLACE FUNCTION batch_insert()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..10 LOOP
        INSERT INTO employees (name, salary) VALUES ('Employee_' || i, 3000.00 + i * 100);
    END LOOP;
    RAISE NOTICE 'Batch inserted 10 employees';
END;
$$;

調用方式

CALL batch_insert();

3. 異常處理(TRY-CATCH)

創建存儲過程safe_update,嘗試更新員工信息,若員工不存在則捕獲異常:

CREATE OR REPLACE PROCEDURE safe_update_employee(emp_id INTEGER, new_name VARCHAR, new_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET name = new_name, salary = new_salary WHERE id = emp_id;
    IF NOT FOUND THEN
        RAISE NOTICE 'Employee with ID % not found', emp_id;
    ELSE
        RAISE NOTICE 'Employee % updated successfully', emp_id;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'An error occurred: %', SQLERRM; -- 輸出錯誤信息
END;
$$;

調用方式

CALL safe_update_employee(999, 'Nonexistent', 10000); -- 若id=999不存在,捕獲異常
CALL safe_update_employee(1, 'Alice Updated', 5500); -- 正常更新

六、存儲過程管理

1. 查看存儲過程

使用以下SQL語句查看數據庫中的存儲過程:

-- 查看所有函數(包括存儲過程)
SELECT proname, prosrc, proretset, proargtypes FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- 查看特定存儲過程的定義
\d+ function_name

2. 修改存儲過程

使用CREATE OR REPLACE FUNCTIONCREATE OR REPLACE PROCEDURE語句修改已有存儲過程(需保持參數列表和返回類型一致):

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a * b; -- 修改為計算乘積
END;
$$;

3. 刪除存儲過程

使用DROP FUNCTIONDROP PROCEDURE語句刪除存儲過程:

DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);
DROP PROCEDURE IF EXISTS safe_update_employee(INTEGER, VARCHAR, NUMERIC);

七、實戰案例:批量更新用戶標簽

假設有一個user_log表(需提前創建):

CREATE TABLE user_log (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    tag VARCHAR(50)
);

創建存儲過程batch_update_tags,批量更新指定用戶的標簽:

CREATE OR REPLACE PROCEDURE batch_update_tags(tag_name VARCHAR, user_ids INTEGER[])
LANGUAGE plpgsql
AS $$
BEGIN
    FOREACH uid IN ARRAY user_ids LOOP
        UPDATE user_log SET tag = tag_name WHERE user_id = uid;
    END LOOP;
    RAISE NOTICE 'Successfully updated % records', array_length(user_ids, 1);
END;
$$;

調用方式

CALL batch_update_tags('VIP', ARRAY[1, 2, 3]); -- 將id=1,2,3的用戶標簽設為VIP

注意事項

  1. 權限控制:確保存儲過程創建用戶有足夠的權限(如CREATE權限);
  2. 事務管理:存儲過程中的所有操作在同一個事務中執行,若發生錯誤,整個事務將回滾;
  3. 性能優化:避免在循環中執行SQL語句(如批量操作可使用FOREACH結合數組);
  4. 調試技巧:使用RAISE NOTICE輸出調試信息,幫助定位問題;
  5. 版本兼容:PostgreSQL 11及以上版本支持CREATE PROCEDURE語法,若使用舊版本,需用CREATE FUNCTION替代。

0
亚洲午夜精品一区二区_中文无码日韩欧免_久久香蕉精品视频_欧美主播一区二区三区美女