关键字：
RECORD;人大金仓；KingbaseES
RECORD类型
- 概述
为了定义记录类型，需要指定名称和定义字段。需指定字段名和字段类型来定义字段。字段默认值为 NULL。可以给字段加 NOT NULL 约束，这种情况下必须指定一个非 NULL 的初始值。如果没有 NOT NULL 约束，这个非 NULL 初始值可选。
语法：TYPE 类型名称 IS RECORD (
成员名称 数据类型[[NOT NULL][:=默认值]表达式],
…
成员名称 数据类型[[NOT NULL][:=默认值]表达式]);
- record类型的变量声明
- 声明record常量
\set SQLTERM /
DECLARE TYPE Rec IS RECORD (a NUMBER, b NUMBER);
r Rec := (0,1);
BEGIN
raise notice 'r = %',r;
raise notice 'r.a = %',r.a;
raise notice 'r.b = %',r.b;
END;
/
- 使用%ROWTYPE 和%TYPE 声明的 record 变量。
\set SQLTERM /
DECLARE
TYPE emp_rec is record(
id emp.id%TYPE,
first_name emp.first_name%TYPE,
last_name emp.last_name%TYPE,
email emp.email%TYPE);
emp emp_rec;
begin
…
END;
/
（3）record类型的赋值方式
- 使用：=进行赋值
\set SQLTERM /
DECLARE
TYPE emp_rec is record(
id emp.id%TYPE,
first_name emp.first_name%TYPE);
emp emp_rec;
begin
emp.id:='2';
raise notice 'id:%',emp.id;
END;
/
- 使用限定表达式赋值
（i）按照名称赋值
set serveroutput on
\set SQLTERM /
DECLARE
TYPE rec1 IS RECORD(c1 int,c2 varchar(50));
v1 rec1 :=rec1(c1=>1,c2=>'a');
BEGIN
DBMS_OUTPUT.PUT_LINE(v1.c2);
END;
/
（ii）按照位置赋值
\set SQLTERM /
DECLARE
TYPE rec1 IS RECORD(c1 int,c2 varchar(50));
v1 rec1 :=rec1(1,'a');
BEGIN
DBMS_OUTPUT.PUT_LINE(v1.c2);
END;
/
自定义RECORD的使用
（1）使用RECORD类型接收查询返回结果
create table emp(
id int,
first_name varchar,
last_name varchar,
email varchar);
/
\set SQLTERM /
DECLARE
TYPE emp_rec is record(
id emp.id%TYPE,
first_name emp.first_name%TYPE,
last_name emp.last_name%TYPE,
email emp.email%TYPE);
emp emp_rec;
begin
select id,first_name,last_name,email from emp where id =1;
DBMS_OUTPUT.PUT_LINE('id:'||emp.id);
DBMS_OUTPUT.PUT_LINE('first_name:'||emp.first_name);
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOEXISTS');
END;
/
（2）自定义RECORD类型，声明变量，并为属性赋值
\set SQLTERM /
DECLARE
TYPE emp_rec is record(
id emp.id%TYPE,
first_name emp.first_name%TYPE,
last_name emp.last_name%TYPE,
email emp.email%TYPE);
emp emp_rec;
begin
emp.id:='2';
emp.first_name:='qw';
emp.last_name:='re';
emp.email:='qw@we.com';
DBMS_OUTPUT.PUT_LINE('id:'||emp.id);
DBMS_OUTPUT.PUT_LINE('first_name:'||emp.first_name);
DBMS_OUTPUT.PUT_LINE('last_name:'||emp.last_name );
DBMS_OUTPUT.PUT_LINE('email:'||emp.email);
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOEXISTS');
END;
/
（3）定义嵌套的RECORD类型
drop table if exists student;
create table student(id int PRIMARY KEY, name text, score number);
insert into student values (1, 'xx', 99);
\set SQLTERM /
DECLARE
TYPE stu_info_rec IS RECORD (
id student.id%TYPE,
name student.name%TYPE
);
TYPE stu_rec IS RECORD (
stu_info stu_info_rec, -- nested record
score student.score%TYPE
);
student1 stu_rec;
BEGIN
student1.stu_info.id := 1;
student1.stu_info.name := 'xx';
student1.score := 88;
RAISE NOTICE '%, %, %', student1.stu_info.id, student1.stu_info.name,student1.score;
END;
/
（4）插入记录，利用RECORD类型保存数据
DROP TABLE IF EXISTS schedule;
CREATE TABLE schedule (
week NUMBER,
Mon VARCHAR2(10),
Tue VARCHAR2(10),
Wed VARCHAR2(10),
Thu VARCHAR2(10),
Fri VARCHAR2(10),
Sat VARCHAR2(10),
Sun VARCHAR2(10)
);
\set SQLTERM /
DECLARE
default_week schedule%ROWTYPE;
BEGIN
default_week.Mon := '0900-1800';
default_week.Tue := '0900-1800';
default_week.Wed := '0900-1800';
default_week.Thu := '0900-1800';
default_week.Fri := '0900-1800';
default_week.Sat := 'Day Off';
default_week.Sun := 'Day Off';
FOR i IN 1..4 LOOP
default_week.week := i;
INSERT INTO schedule VALUES default_week;
END LOOP;
END;
/
\set SQLTERM ;
SELECT * FROM schedule;
（5）修改数据，利用记录类型保存数据
\set SQLTERM /
DECLARE
default_week schedule%ROWTYPE;
BEGIN
default_week.Mon := 'Day Off';
default_week.Tue := 'Day Off';
default_week.Wed := '0800-1700';
default_week.Thu := '0800-1700';
default_week.Fri := '0800-1700';
default_week.Sat := '0800-1700';
default_week.Sun := '0800-1700';
FOR i IN 1..2 LOOP
default_week.week := i;
UPDATE schedule
SET ROW = default_week
WHERE week = i;
END LOOP;
END;
/
\set SQLTERM ;
SELECT * FROM schedule order by week;
自定义RECORD在集合类型中的使用
- record类型嵌套可变数组类型
\set SQLTERM /
DECLARE
TYPE stu_info_rec IS VARRAY(2) OF VARCHAR2(20);
TYPE stu_rec IS RECORD (
stu_info stu_info_rec := stu_info_rec('1', 'xx'), -- varray field
score student.score%TYPE
);
student1 stu_rec;
BEGIN
student1.score := 88;
RAISE NOTICE '%, %, %', student1.stu_info(1), student1.stu_info(2),student1.score;
END;
/
- record类型嵌套关联数组类型
\set SQLTERM /
CREATE OR REPLACE PROCEDURE pr1 IS
TYPE ass_type IS TABLE OF varchar INDEX BY varchar;
TYPE rec IS RECORD(r1 ass_type,r2 varchar);
v1 rec;
BEGIN
v1.r1('key1') :='key1';
v1.r1('key2') :='key2';
v1.r2 :='test';
INSERT INTO t1 VALUES(1,v1.r1('key1'));
INSERT INTO t1 VALUES(2,v1.r1('key2'));
INSERT INTO t1 VALUES(3,v1.r2);
END;
/
\set SQLTERM ;
CALL pr1();
SELECT * FROM t1;
- record类型嵌套嵌套表类型
\set SQLTERM /
DECLARE
TYPE stu_info_rec IS TABLE OF VARCHAR2(20);
TYPE stu_rec IS RECORD (
stu_info stu_info_rec := stu_info_rec('1', 'xx'), -- varray field
score student.score%TYPE
);
student1 stu_rec;
BEGIN
student1.score := 88;
RAISE NOTICE '%, %, %', student1.stu_info(1), student1.stu_info(2),student1.score;
END;
/
