【openGauss笔记】存储过程和函数

存储过程是一组SQL语句和逻辑控制的集合。数据库系统需要支持创建、删除和修改存储过程的语法。
存储过程相比普通的SQL命令，具有如下优点：

1. 创建的存储过程保存在数据库系统中，在使用时被调出并且在数据库系统本地进行编译执行，一次编译，多次执行，具有很好的执行效率。
2. 数据库系统和应用程序之间通常需要有大量的数据交互，而存储过程可以将应用的逻辑“下推”给数据库系统，降低数据的传输量。
3. 存储过程还具有过程化的控制语句，可以实现固定的业务逻辑，并且通过存储过程的封装，应用程序只需要访问存储过程即可，从而可以使部分基本表对用户透明，提高了数据库系统的安全性。
4. 简而言之，存储过程具有简单、安全、高性能等优点。

1. 存储过程的声明

创建一个存储过程可以通过CREATE PROCEDURE命令来实现，其主要形式如下：

CREATE [OR REPLACE] PROCEDURE 存储过程名(
	[IN|OUT] 参数1数据类型,
	[IN|OUT] 参数2数据类型,
	...
)
AS
DECLARE
	变量1数据类型,
	变量2数据类型,
...
BEGIN
	存储过程的程序体
END;

下面定义一个存储过程。

CREATE TABLE warehouse (
	w_id SMALLINT,
	w_name VARCHAR(10),
	w_street_1 VARCHAR(20),
	w_street_2 VARCHAR(20),
	w_city VARCHAR(20),
	w_state CHAR(2),
	w_zip CHAR(9),
	w_tax DECIMAL(4,2),
	w_ytd DECIMAL(12,2)
);

例2-54：统计warehouse表中元组的数量。具体语句如下：

CREATE PROCEDURE warehouse_count() 
AS 
BEGIN
	SELECT COUNT(*) 
	FROM warehouse;
END;
/

存储过程可以带有参数，参数的类型就是SQL标准中的多种类型，在向存储过程传递参数时需要保证参数类型的一致，否则存储过程就无法正常执行。
存储过程的参数有3种不同的输入／输出模式：IN、OUT、INOUT。

1. IN参数是存储过程的输入参数，它将存储过程外部的值传递给存储过程使用。
2. OUT参数是存储过程的输出参数，存储过程在执行时，会将执行的中间结果赋值给OUT参数，存储过程执行完毕后，外部用户可以通过OUT参数获得存储过程的执行结果。
3. INOUT参数则同时具有IN参数和OUT参数的性质，它既是存储过程的输入参数，同时在存储过程执行中也会通过INOUT参数将中间结果输出给外部用户。

例2-55：向new_orders基本表中插入数据。具体语句如下：

CREATE TABLE new_orders
(
	no_o_id INTEGER NOT NULL,
	no_d_id SMALLINT NOT NULL,
	no_w_id SMALLINT NOT NULL
);

CREATE PROCEDURE new_orders_insert(
	IN o_id INTEGER,
	IN d_id INTEGER,
	IN w_id INTEGER
) 
AS 
BEGIN 
	INSERT INTO new_orders VALUES(o_id, d_id, w_id); 
END;
/

调用存储过程，具体语句如下：

CALL new_orders_insert(1,1,1);

检查存储过程的效果，具体语句如下：

SELECT * FROM new_orders;
 no_o_id | no_d_id | no_w_id 
---------+---------+---------
       1 |       1 |       1
(1 row)

2. 存储过程的修改

SQL中没有提供显式的存储过程修改命令，通常需要通过REPLACE关键字来指定使用当前的存储过程替代之前的同名存储过程。

例2-56：将例2-54定义的存储过程替换为按照地区分组的数量统计。具体语句如下：

CREATE OR REPLACE PROCEDURE warehouse_count() 
AS 
BEGIN
	SELECT w_state, w_city, COUNT(*) 
	FROM warehouse
	GROUP BY w_state, w_city;
END;
/

3. 函数的声明和修改

CREATE [OR REPLACE] FUNCTION 函数名(
	[IN|OUT] 参数1数据类型,
	[IN|OUT] 参数2数据类型,
	...
)
RETURNS 数据类型
AS
DECLARE
	变量1数据类型,
	变量2数据类型,
...
BEGIN
	函数的程序体
END;

函数可以应用在SQL语句中，而存储过程则必须独立调用，另外函数必须指定返回值。

例2-57：向new-orders表中插入数据，并将new-orders中的元组数作为返回值，具体语句如下：

CREATE FUNCTION new_orders_insert_func(
	IN o_id INTEGER,
	IN d_id INTEGER,
	IN w_id INTEGER
) 
RETURNS INTEGER 
AS 
$BODY$
DECLARE 
count INTEGER;
BEGIN 
	INSERT INTO new_orders VALUES (o_id, d_id, w_id);
	SELECT COUNT(*) INTO count FROM new_orders;
	RETURN count;
END;
$BODY$ 
	LANGUAGE plpgsql;

执行获得返回值：

CALL new_orders_insert_func(2,2,2);
 new_orders_insert_func 
------------------------
                      2
(1 row)