openGauss SQL表值函数应用

一、用户自定义函数与表值函数

1.用户自定义函数

函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数。

函数的调用是表达式的一部分。

函数在创建编译后放在内存中供用户使用，调用时函数要用表达式。

函数必须返回特定数据，可以返回一个或多个值。在一个函数中必须包含一个或多个RETURN语句。

创建自定义函数时，最后的“/”符号用于标示自定义函数定义语句的结束，不能省略，且必须单独成行。

用户自定义函数可以直接被存储过程调用，也可以像普通函数一样在SQL语句中使用。

2.自定义函数语法格式

CREATE OR REPLACE FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_value ] [, ...] ] )

RETURNS return_type

AS $$

  -- 函数体

  -- 这里是 SQL 语句和/或 PL/pgSQL 代码

$$ LANGUAGE language_name [ IMMUTABLE | STABLE | VOLATILE ] [ CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT ]

/

参数说明：

function_name 是用户创建的函数的名称。

argmode 是参数模式，可以是 IN、OUT、INOUT 或 VARIADIC。

argname 是参数的名称。

argtype 是参数的数据类型。

default_value 是参数的默认值。

return_type 是函数返回的数据类型。

$$ 内是函数的实现代码，可以是 SQL 语句、PL/pgSQL 代码等。

LANGUAGE 指定了函数的实现语言，常见的有 plpgsql、sql 等。

IMMUTABLE、STABLE、VOLATILE 定义了函数的稳定性。

IMMUTABLE 表示函数的结果不依赖于外部参数，对于相同的输入总是返回相同的结果。

STABLE 表示函数的结果在同一个数据库会话中对于相同的输入是不变的，但在不同的会话中可能会改变。

VOLATILE 表示函数的结果可能会在每次调用时都不同。

CALLED ON NULL INPUT 表示函数即使在输入参数为 NULL 时也会被调用。

RETURNS NULL ON NULL INPUT 表示如果输入参数为 NULL，函数将返回 NULL。

STRICT 表示如果输入参数为 NULL，函数将不会执行，而是直接返回 NULL。参数说明：

argname参数的名称。取值范围：字符串，要符合标识符的命名规范。

argtype 参数的数据类型。可以使用%TYPE或%ROWTYPE间接引用变量或表的类型。取值范围：可用的数据类型。

3.函数调用

在 openGauss SQL 中调用已定义的函数与在其他 SQL 数据库中调用函数非常类似。函数调用的基本语法格式如下：

SELECT function_name([argument1, argument2, ...]);

这里的 function_name 是用户自定义的函数名称，而 argument1, argument2, ... 是传递给函数的实际参数列表。

如果函数定义为返回多列数据，可以这样调用：

SELECT  *  FROM function_name([argument1, argument2, ...]);

函数也可以在 SQL 语句的其他部分被调用，例如在 WHERE 子句中、或者在 ORDER BY 子句中等。

调用函数时需确保函数的参数与定义时的参数类型和数量相匹配，否则会抛出错误。

4.删除自定义函数

语法格式：

DROP FUNCTION [ IF EXISTS ] function_name

[ ( [ {[ argname ] [ argmode ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ];

4.表值函数

openGauss支持表值函数（Table-Valued Function, TVF），这类函数可以返回一个表结构的结果集set of tableName。表值函数在 SQL 查询中非常有用，尤其是在需要从函数中获取多行数据时。

二、表值函数应用

1．表值函数应用1：定义返回简单的表数据集的表值函数。

--创建根据课程编号返回课程信息的函数(O风格)

CREATE OR REPLACE FUNCTION f_getCourse(in cid course.courseId%type)

 RETURN SETOF course

AS

--$$

DECLARE

BEGIN

    RETURN QUERY

    SELECT *

    FROM course

    WHERE courseId=cid; 

END;

--$$ LANGUAGE SQL;

/

--创建根据课程编号返回课程信息的函数(兼容PostgreSQL风格)

CREATE OR REPLACE FUNCTION f_getCourse(in cid course.courseId%type)

 RETURNS SETOF course

AS

$$

DECLARE

BEGIN

    RETURN QUERY

    SELECT *

    FROM course

    WHERE courseId=cid; 

END;

$$ LANGUAGE PLPGSQL;

/

--调用函数f_getCourse(),获得课程编号为“B083804”的课程信息

SELECT * FROM student.f_getCourse('B083804');

2.表值函数应用2：定义表值函数获取某学期每个教师的排课信息。

已知排课记录表courseScheduleRecord和-排课记录序列seq_courserscheduleRecordsn。

--排课记录序列seq_courserscheduleRecordsn

 CREATE  SEQUENCE seq_courserscheduleRecordsn

 START  WITH  1

 INCREMENT  BY  1

 NO MINVALUE 

 CACHE 1;

--排课记录表coursescheduleRecord

CREATE TABLE coursescheduleRecord (

    courseScheduleRecordSn bigint not null default(nextval('seq_courserscheduleRecordsn')),

    coursescheduleid character varying(40) NOT NULL,

    studentNumber integer NOT NULL,

    courseid character(7) NOT NULL,

    termid character(20) NOT NULL,

    teacherids character varying(120) NOT NULL,

    CONSTRAINT pk_coursescheduleRecord PRIMARY KEY (coursescheduleRecordSn),

    CONSTRAINT fk2_courserscheduleRecord FOREIGN KEY (termid) REFERENCES term(termid),

    CONSTRAINT fk1_courserscheduleRecord FOREIGN KEY (courseid) REFERENCES course(courseid)

);

--向courserschedulerecord表中添加记录

INSERT INTO coursescheduleRecord (

coursescheduleid,

studentNumber,

courseid,

termid,

teacherids)

VALUES( '（2023-2024-2）-B083804-01',37,'B083804','2023-2024-2','00542;01232');

INSERT INTO coursescheduleRecord (

coursescheduleid,

studentNumber,

courseid,

termid,

teacherids)

VALUES( '（2023-2024-2）-B083852-01',47,'B083852','2023-2024-2','00542');

--查询coursescheduleRecord表

SELECT * FROM coursescheduleRecord;

2.创建表值函数tf_courseScheduleByTerm获取某学期每个教师的排课信息

（1）创建表t_courseSchedule

CREATE TABLE t_courseSchedule(

   courseScheduleRecordSn bigint,

   courseScheduleId varchar(40),

   termId varchar(20),

   courseId varCHAR(10),

   studentNumber INTEGER,

   teacherId CHAR(5)

);

（2）创建表值函数tf_courseScheduleByTerm

CREATE OR REPLACE FUNCTION

tf_courseScheduleByTerm(IN termId_1 courseScheduleRecord.termid%type)

RETURNS SETOF t_courseSchedule

AS

$$

DECLARE

    i INTEGER; --循环变量，每一条排课记录中教师标号对应的序号

    tid CHAR(5); --临时教师编号

    teacherIds VARCHAR(120); --每一条教师编号序列

    c INTEGER; --某学期的排课记录数

    snMin INTEGER;--courseScheduleRecordSn的最小值

    snMax INTEGER;--courseScheduleRecordSn的最大值

    sn INTEGER; --循环变量，每一条排课记录序号，取值范围是[snMIn,snMax]

BEGIN 

    --清除本termId_1学期的教师排课记录

    DELETE FROM t_courseSchedule WHERE termId=termId_1;

    --得到c

    SELECT COUNT(courseScheduleRecordSn) INTO c

    FROM courseScheduleRecord

    WHERE termId=termId_1;

    --得到snMax

    SELECT MAX(courseScheduleRecordSn) INTO snMax FROM courseScheduleRecord 

    WHERE termId=termId_1;

    --得到snMin

    SELECT MIN(courseScheduleRecordSn) INTO snMIN FROM courseScheduleRecord 

    WHERE termId=termId_1;

    --给sn赋初值

    sn:=snMin;

    WHILE sn<=snMAX LOOP

        teacherIds:=(SELECT teacherIds

        FROM courseScheduleRecord

        WHERE courseScheduleRecordSn=sn);

          IF length(teacherIds)=5 THEN

                INSERT INTO t_courseSchedule(courseScheduleRecordSn,courseScheduleId,termId,courseId,studentNumber,teacherId)

              SELECT courseScheduleRecordSn,courseScheduleId,termId,courseId,studentNumber,teacherIds

                FROM courseScheduleRecord

                WHERE courseScheduleRecordSn=sn

                  AND termId=termId_1;

          ELSE

                i:=0;

              WHILE i < (length(teacherIds)+1)/6 LOOP

                  tid := SUBSTRING(teacherIds,i*6+1,5);

                  INSERT INTO t_courseSchedule(courseScheduleRecordSn,courseScheduleId,termId,courseId,studentNumber,teacherId)

                  SELECT courseScheduleRecordSn,courseScheduleId,termId,courseId,studentNumber,tid

                  FROM courseScheduleRecord

                    WHERE courseScheduleRecordSn=sn

                          AND termId=termId_1;           

                    i := i+1;

              END LOOP;

          END IF;

            sn := sn+1;

    END LOOP;

    RETURN QUERY

    SELECT courseScheduleRecordSn,courseScheduleId,termId,courseId,studentNumber,teacherId

    FROM t_courseSchedule;

END;

$$ LANGUAGE PLPGSQL;

/

（3）调用表值函数tf_courseScheduleByTerm

SELECT tcs.*,term.termName

FROM term, tf_courseScheduleByTerm('2023-2024-2') AS tcs

WHERE tcs.termId=term.termId;

--或者

SELECT tcs.*,term.termName

FROM term

INNER JOIN tf_courseScheduleByTerm('2023-2024-2') AS tcs

ON tcs.termId=term.termId;

（4）查询表courseScheduleRecord

SELECT * FROM courseScheduleRecord

WHERE termed='2023-2024-2';

（4）查询表t_courseSchedule

SELECT * FROM t_courseSchedule

WHERE termed='2023-2024-2';