openGauss存储过程创建及应用

目录

一、引言

二、存储过程

1. 什么是存储过程

2. 创建和使用存储过程

创建员工表

创建存储过程

调用存储过程

3. 存储过程的高级应用

批量数据处理

​编辑

数据校验和清洗

​编辑

自动化任务

四、单节点容器化安装

支持的架构和操作系统版本

配置准备

创建openGauss docker镜像

环境变量

开启实例

从操作系统层面连接数据库 

数据持久化 

四、总结

---

一、引言

openGauss 是一款开源关系型数据库管理系统，广泛应用于企业级应用中。随着数据量的增长和业务逻辑的复杂化，数据库管理和操作的自动化需求越来越高。存储过程（Stored Procedures）和触发器（Triggers）作为数据库中重要的编程工具，能够极大地简化复杂操作，提高系统的性能和安全性。本文将详细介绍 openGauss 的存储过程和触发器，并提供具体的代码和案例，以帮助读者更好地理解和应用这些工具。

二、存储过程

1. 什么是存储过程

存储过程是一组预先编写好的 SQL 语句集合，存储在数据库中，可以通过调用存储过程来执行一系列操作。存储过程能够简化复杂的数据库操作，减少代码重复，提高效率。此外，存储过程运行在数据库服务器端，这意味着可以减少客户端和服务器之间的通信开销，提高执行效率。

存储过程的特点包括：

• 封装性：将一系列操作封装在一个过程里，简化调用。
• 重用性：定义一次，可以在多个地方调用，减少代码重复。
• 安全性：通过存储过程可以控制访问权限，提高数据安全性。
• 性能：减少客户端和服务器之间的通信，执行效率高。

2. 创建和使用存储过程

在 openGauss 中，创建存储过程使用 CREATE PROCEDURE 语句。一个存储过程可以包含多个输入参数、输出参数，甚至没有参数。下面是一个详细的例子，演示如何创建和调用存储过程。

创建员工表

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(15, 2),
    department VARCHAR(100)
);

创建存储过程

-- 创建插入员工的存储过程

CREATE OR REPLACE PROCEDURE add_employee(

    emp_id INT,

    emp_name VARCHAR,

    emp_salary NUMERIC,

    emp_department VARCHAR

)LANGUAGE plpgsql AS $$

BEGIN

    INSERT INTO employees (id, name, salary, department)

VALUES (emp_id, emp_name, emp_salary, emp_department);

END;

$$;

-- 创建更新触发器函数

-- 创建更新员工的存储过程

CREATE OR REPLACE PROCEDURE update_employee(

    emp_id INT,

    emp_name VARCHAR,

    emp_salary NUMERIC,

    emp_department VARCHAR

)

LANGUAGE plpgsql

AS $$

BEGIN

    UPDATE employees

    SET name = emp_name, salary = emp_salary, department = emp_department

    WHERE id = emp_id;

END;

$$;

-- 创建删除员工的存储过程
CREATE OR REPLACE PROCEDURE delete_employee(emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM employees
    WHERE id = emp_id;
END;
$$;

-- 创建查询员工的存储过程
CREATE OR REPLACE PROCEDURE get_employee(emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM * FROM employees WHERE id = emp_id;
END;
$$;

调用存储过程

-- 调用存储过程插入员工

CALL add_employee(1, 'John Doe', 50000, 'Engineering');

-- 调用存储过程更新员工
CALL update_employee(1, 'John Doe', 55000, 'Marketing');

-- 调用存储过程删除员工
CALL delete_employee(1);

-- 调用存储过程查询员工
CALL get_employee(1);

3. 存储过程的高级应用

存储过程不仅可以简化常见的增删改查操作，还可以用于更复杂的业务逻辑处理。以下是一些存储过程的高级应用场景：

批量数据处理

在实际业务中，经常需要对大量数据进行批量处理，如批量插入、批量更新等。使用存储过程可以极大地简化这些操作，并提高执行效率。

-- 定义员工记录的复合类型

CREATE TYPE emp_record AS (

    id INT,

    name VARCHAR(100),

    salary NUMERIC(15, 2),

    department VARCHAR(100)

);

-- 创建批量插入员工的存储过程

CREATE OR REPLACE PROCEDURE batch_insert_employees(emp_records emp_record[])

LANGUAGE plpgsql

AS $$

DECLARE

    rec emp_record;

BEGIN

    FOREACH rec IN ARRAY emp_records

    LOOP

        INSERT INTO employees (id, name, salary, department)

        VALUES (rec.id, rec.name, rec.salary, rec.department);

    END LOOP;

END;

$$;

-- 调用批量插入存储过程

CALL batch_insert_employees(ARRAY[

    ROW(2, 'Jane Doe', 60000, 'HR')::emp_record,

    ROW(3, 'Alice', 70000, 'Finance')::emp_record,

    ROW(4, 'Bob', 80000, 'IT')::emp_record

]);

数据校验和清洗

在存储过程中可以添加数据校验和清洗的逻辑，确保插入数据库的数据的完整性和准确性。例如，可以在插入员工数据前，检查数据是否符合业务规则。

CREATE OR REPLACE PROCEDURE add_employee_with_validation(

    emp_id INT,

    emp_name VARCHAR,

    emp_salary NUMERIC,

    emp_department VARCHAR

)

LANGUAGE plpgsql

AS $$

BEGIN

    IF emp_salary < 0 THEN

        RAISE EXCEPTION 'Salary cannot be negative';

    END IF;



    IF emp_department IS NULL THEN

        RAISE EXCEPTION 'Department cannot be null';

    END IF;



    INSERT INTO employees (id, name, salary, department)

    VALUES (emp_id, emp_name, emp_salary, emp_department);

END;

$$;

-- 调用存储过程插入员工

CALL add_employee_with_validation(5, 'Charlie', 5000, 'Sales');

自动化任务

存储过程可以用于自动化任务，如定时任务、数据备份等。可以结合数据库调度器（如 cron 表达式）来实现定时调用存储过程，完成自动化管理。

-- 定时任务：每天凌晨2点备份员工表

CREATE OR REPLACE PROCEDURE backup_employees()

LANGUAGE plpgsql

AS $$

BEGIN

    EXECUTE 'COPY employees TO ''/path/to/backup/employees_' || to_char(current_date, 'YYYYMMDD') || '.csv'' WITH CSV HEADER';

END;

$$;

四、单节点容器化安装

主要介绍通过Docker安装openGauss，方便DevOps用户的安装、配置和环境设置。

支持的架构和操作系统版本

• x86-64 CentOS 7.6

• ARM64 openEuler 20.03 LTS

配置准备

使用 buildDockerImage.sh脚本构建docker镜像，buildDockerImage.sh是一个方便使用的shell脚本，提供SHA256的检查。

创建openGauss docker镜像

说明：

• 安装前需要提供openGauss二进制安装包，解压后将以bz2结尾的包（openGauss-X.X.X-CentOS-64bit.tar.bz2。X.X.X为当前openGauss版本号）放到 dockerfiles/<version>文件夹。二进制包可以从 软件包 | openGauss下载，确保有正确的yum源。

• 运行buildDockerImage.sh脚本时，如果不指定-i参数，此时默认提供SHA256检查，需要您手动将校验结果写入sha256_file_amd64文件。## 修改sha256校验文件内容
  cd /soft/openGauss-server/docker/dockerfiles/X.X.X
  sha256sum openGauss-X.X.X-CentOS-64bit.tar.bz2 > sha256_file_amd64 

• 安装前需要从华为开源镜像站获取openEuler_aarch64.repo文件，并放到openGauss-server-master/docker/dockerfiles/X.X.X文件夹下面。openEuler_aarch64.repo获取方法：wget -O openEuler_aarch64.repo https://mirrors.huaweicloud.com/repository/conf/openeuler_aarch64.repo

在dockerfiles文件夹运行buildDockerImage.sh脚本。

[root@ecs-complie dockerfiles]# ./buildDockerImage.sh
Usage: buildDockerImage.sh -v [version] [-i] [Docker build option]
Builds a Docker Image for openGauss
Parameters:
   -v: version to build
       Choose one of: X.X.X
   -i: ignores the SHA256 checksums

LICENSE UPL 1.0

 

环境变量

为了更灵活的使用openGauss镜像，可以设置额外的参数。未来我们会扩充更多的可控制参数，当前版本支持以下变量的设定。

GS_PASSWORD

使用openGauss镜像的时候，必须设置该参数。该参数值不能为空或者不定义。该参数设置了openGauss数据库的超级用户omm以及测试用户gaussdb的密码。openGauss安装时默认会创建omm超级用户，该用户名暂时无法修改。测试用户gaussdb是在entrypoint.sh中自定义创建的用户。

openGauss镜像配置了本地信任机制，因此在容器内连接数据库无需密码，但是如果要从容器外部（其它主机或者其它容器）连接则必须要输入密码。

openGauss的密码有复杂度要求

密码长度8个字符以上，必须同时包含大写字母、小写字母、数字、以及特殊符号（特殊符号仅包含“#?!@$%^&*-”，并且“!$&”需要用转义符“\”进行转义）。

GS_NODENAME

指定数据库节点名称，默认为gaussdb。

GS_USERNAME

指定数据库连接用户名，默认为gaussdb。

GS_PORT

指定数据库端口，默认为5432。

开启实例

$ docker run --name opengauss --privileged=true -d -e GS_PASSWORD=XXXXXXXX opengauss:2.0.0

从操作系统层面连接数据库 

$ docker run --name opengauss --privileged=true -d -e GS_PASSWORD=XXXXXXXX -p8888:5432 opengauss:2.0.0
$ gsql -d postgres -U gaussdb -W'Enmo@123' -h your-host-ip -p8888

数据持久化 

$ docker run --name opengauss --privileged=true -d -e GS_PASSWORD=Enmo@123 -v /opengauss:/var/lib/opengauss opengauss:2.0.0

 

四、总结

存储过程在openGauss数据库中能够帮助开发者简化复杂的数据库操作，实现自动化管理。在实际应用中，通过合理地使用存储过程和触发器，可以提高数据库系统的效率和可靠性。本文详细介绍了存储过程的基本概念、创建方法、应用场景，并提供了具体的代码和案例，帮助读者更好地理解和应用这些工具。希望本文能够对您在数据库开发和管理中有所帮助。