在編寫存儲過程前,需確保Debian系統已安裝PostgreSQL數據庫。使用以下命令完成安裝:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
安裝完成后,啟動PostgreSQL服務并設置開機自啟:
sudo systemctl start postgresql
sudo systemctl enable 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
:包裹業務邏輯代碼塊。假設有一個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
的記錄。
創建存儲過程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
創建存儲過程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; -- 顯示員工總數
創建存儲過程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的員工薪水輸出提示
創建存儲過程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();
創建存儲過程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); -- 正常更新
使用以下SQL語句查看數據庫中的存儲過程:
-- 查看所有函數(包括存儲過程)
SELECT proname, prosrc, proretset, proargtypes FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');
-- 查看特定存儲過程的定義
\d+ function_name
使用CREATE OR REPLACE FUNCTION
或CREATE OR REPLACE PROCEDURE
語句修改已有存儲過程(需保持參數列表和返回類型一致):
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a * b; -- 修改為計算乘積
END;
$$;
使用DROP FUNCTION
或DROP 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
CREATE
權限);FOREACH
結合數組);RAISE NOTICE
輸出調試信息,幫助定位問題;CREATE PROCEDURE
語法,若使用舊版本,需用CREATE FUNCTION
替代。