%ROWTYPE属性允许你声明表示数据库表或视图的整行的记录变量。 声明表示整行的记录变量语法: variable_name table_or_view_name%ROWTYPE; CREATE OR REPLACE PROCEDURE P_5_29 AS dept_rec departments%ROWTYPE; --departments是存储在数据库中的表 BEGIN -- 赋值 dept_rec.department_id := 10; dept_rec.department_name := 'Administration'; dept_rec.manager_id := 200; GBase 8s PL/SQL手册 南大通用数据技术股份有限公司 - 87 - dept_rec.location_id := 1700; -- 打印 DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.department_id); DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name); DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.manager_id); DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.location_id); END; --Result: --dept_id: 10 --dept_name: Administration --mgr_id: 200 --loc_id: 1700 DROP TABLE t1; CREATE TABLE t1 ( c1 INTEGER DEFAULT 0 NOT NULL, c2 INTEGER DEFAULT 1 NOT NULL ); CREATE OR REPLACE PROCEDURE P_5_30 As t1_row t1%ROWTYPE; BEGIN DBMS_OUTPUT. PUT_LINE ('t1.c1 = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t1_row.c1), 'NULL')); DBMS_OUTPUT. PUT_LINE ('t1.c2 = '); print(t1_row.c2); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(t1_row.c2), 'NULL')); END; --Result: --t1.c1 = NULL --t1.c2 = NULL %ROWTYPE定义的变量并不继承初始值和约束 声明表示部分行的记录变量,语法: variable_name cursor%ROWTYPE; 游标是与一个查询相关联的。对于查询选择的每个列,记录变量必须有相应的兼容类 型的列。如果查询选择了表或视图的所有列,则变量就表示整行。否则变量只表示行的一 部分。游标必须是显性游标或者强游标变量。 CREATE OR REPLACE PROCEDURE P_5_31 As CURSOR c IS SELECT first_name, last_name, phone_number GBase 8s PL/SQL手册 南大通用数据技术股份有限公司 - 88 - FROM employees; friend c%ROWTYPE; BEGIN friend.first_name := 'John'; friend.last_name := 'Smith'; friend.phone_number := '1-650-555-1234'; DBMS_OUTPUT.PUT_LINE (friend.first_name || ' ' || friend.last_name || ', ' || friend.phone_number); END; --Result: --John Smith, 1-650-555-1234 CREATE OR REPLACE PROCEDURE P_5_32 As CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id; join_rec c2%ROWTYPE; -- includes columns from two tables BEGIN NULL; END;