2.记录变量之间的赋值问题
下面的例子中两个PL/SQL变量name_rec1与name_rec2尽管具有表面上相同的定义,但两者之间不能相互赋值
DECLARE
TYPE ex_type1 IS RECORD(
first_name VARCHAR2(15),
last_name VARCHAR2(30));
TYPE ex_type2 IS RECORD(
first_name VARCHAR2(15),
last_name VARCHAR2(30));
ex_rec1 ex_type1;
ex_rec2 ex_type2;
BEGIN
ex_rec1.first_name := 'Robinson';
ex_rec1.last_name := 'Cheng';
ex_rec2 := ex_rec1; -- 不合理的赋值方式
END;
ex_rec2 := ex_rec1;
*
ERROR at line 13:
ORA-06550: line 13, column 25:
PLS-00382: expression is of wrong type
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored
具有相同的记录类型时,不同记录变量之间可以相互赋值
DECLARE
TYPE ex_type1 IS RECORD(
first_name VARCHAR2(15),
last_name VARCHAR2(30));
ex_rec1 ex_type1;
ex_rec2 ex_type1;
BEGIN
ex_rec1.first_name := 'Robinson';
ex_rec1.last_name := 'Cheng';
ex_rec2 := ex_rec1;
DBMS_OUTPUT.PUT_LINE('ex_rec1 is '||ex_rec1.first_name||' '||ex_rec1.last_name);
DBMS_OUTPUT.PUT_LINE('ex_rec2 is '||ex_rec2.first_name||' '||ex_rec2.last_name);
END;
ex_rec1 is Robinson Cheng
ex_rec2 is Robinson Cheng
3.基于表,基于游标,基于自定义PL/SQL记录的综合使用
DECLARE
CURSOR dept_cur IS --声明游标
SELECT * FROM dept WHERE deptno = 30;
TYPE dept_type IS RECORD( --声明一个自定义的PL/SQL记录类型
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
dept_rec1 dept%ROWTYPE; --声明基于表dept的记录变量
dept_rec2 dept_cur%ROWTYPE; --声明基于游标dept_cur的记录变量
dept_rec3 dept_type; --声明基于自定义dept_type的记录变量
BEGIN
SELECT * INTO dept_rec1 FROM dept WHERE deptno = 30; --将查询的结果插入到基于表dept的记录变量中
OPEN dept_cur;
LOOP
FETCH dept_cur
INTO dept_rec2; --将游标的内容插入到游标记录变量中
EXIT WHEN dept_cur%NOTFOUND;
END LOOP;
dept_rec1 := dept_rec2;
dept_rec3 := dept_rec2;
DBMS_OUTPUT.PUT_LINE(dept_rec1.deptno || ' ' || dept_rec1.dname);
DBMS_OUTPUT.PUT_LINE(dept_rec2.deptno || ' ' || dept_rec2.dname);
DBMS_OUTPUT.PUT_LINE(dept_rec3.deptno || ' ' || dept_rec3.dname);
END;
30 SALES
30 SALES
30 SALES