-- Start
所谓记录(Record)指的是表的一行。值得注意的是我们不能比较两个记录变量是否相等或检查是否为 NULL。
CREATE TABLE Student(
Id INT NOT NULL,
Name VARCHAR2(30) NOT NULL,
Class VARCHAR2(10)
);
INSERT INTO Student values (2, '李四', '117班');
DECLARE
-- 定义记录类型
TYPE Student IS RECORD (
Id INT NOT NULL := 1, -- 如果定义 NOT NULL, 必须有默认值
Name VARCHAR2(30)
);
--定义一个集合
TYPE STUDENT_ARRAY_TYPE IS TABLE OF Student;
Student_Array STUDENT_ARRAY_TYPE;
-- 定义记录变量
StudentVar Student;
BEGIN
-- 赋值
StudentVar.Name := '张三';
-- 赋值
SELECT Id, Name INTO StudentVar FROM Student WHERE Name = '李四';
-- 赋值
INSERT INTO Student values (3, '王五', '118班')
RETURNING Id, Name INTO StudentVar;
-- 将表中所有的记录放入一个集合中
SELECT Id, Name BULK COLLECT INTO Student_Array FROM Student;
FOR i IN Student_Array.FIRST()..Student_Array.LAST() LOOP
StudentVar := Student_Array(i);
DBMS_OUTPUT.PUT_LINE('学号: ' || StudentVar.Id || ', 姓名: ' || StudentVar.Name );
END LOOP;
END;
/
我们还可以根据表结构来定义一个记录。
CREATE TABLE Student(
Id INT NOT NULL,
Name VARCHAR2(30) NOT NULL,
Class VARCHAR2(10)
);
INSERT INTO Student values (2, '李四', '117班');
INSERT INTO Student values (3, '王五', '118班');
DECLARE
-- 根据表定义记录变量
ZhangSan Student%ROWTYPE;
Lisi Student%ROWTYPE;
WangWu Student%ROWTYPE;
BEGIN
-- 赋值
ZhangSan.Id := 1;
ZhangSan.Name := '张三';
ZhangSan.Class := '116班';
-- 将记录变量插入表
INSERT INTO Student VALUES ZhangSan;
-- 赋值
SELECT * INTO Lisi FROM Student WHERE Name = '李四';
-- 根据记录更新表
Lisi.Class := '118班';
UPDATE Student SET ROW=Lisi WHERE Name = '李四';
-- 赋值
UPDATE Student SET Class = '117班' WHERE Name = '王五'
RETURNING Id, Name, Class INTO WangWu;
DBMS_OUTPUT.PUT_LINE('学号: ' || ZhangSan.Id || ', 姓名: ' || ZhangSan.Name || ', 班级: ' || ZhangSan.Class);
DBMS_OUTPUT.PUT_LINE('学号: ' || Lisi.Id || ', 姓名: ' || Lisi.Name || ', 班级: ' || Lisi.Class);
DBMS_OUTPUT.PUT_LINE('学号: ' || WangWu.Id || ', 姓名: ' || WangWu.Name || ', 班级: ' || WangWu.Class);
END;
/
我们还可以根据游标定义记录。
CREATE TABLE Student(
Id INT NOT NULL,
Name VARCHAR2(30) NOT NULL,
Class VARCHAR2(10)
);
INSERT INTO Student VALUES (2, '李四', '117班');
INSERT INTO Student VALUES (3, '王五', '118班');
DECLARE
--定义游标
CURSOR c IS SELECT Id, Name FROM Student;
-- 根据游标定义记录变量
ZhangSan c%ROWTYPE;
WangWu c%ROWTYPE;
StudentRec c%ROWTYPE;
BEGIN
-- 赋值
ZhangSan.Id := 1;
ZhangSan.Name := '张三';
OPEN c;
LOOP
-- 赋值
FETCH c INTO StudentRec;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('学号: ' || StudentRec.Id || ', 姓名: ' || StudentRec.Name);
END LOOP;
CLOSE c;
-- 赋值
DELETE FROM Student WHERE Name = '王五'
RETURNING Id, Name INTO WangWu;
END;
/
--更多参见:Oracle PL/SQL 精萃
-- 声明:转载请注明出处
-- Last Edited on 2015-01-25
-- Created by ShangBo on 2015-01-09
-- End