The PL/SQL Text Book
《Oracle PL/SQL Programming》
Anchored Declarations
When you anchor a datatype,you tell PL/SQL to set the datatype of your variable based on the datatype of an already defined data structure-another PL/SQL variable,a predefined TYPE or SUBTYPE,a database table,or a specific column in a table.PL/SQL offers two kinds of anchoring:
- Scalar anchoring:use the %TYPE attribute to define you variable based on a table`s column or some other PL/SQL scalar variabe.
- Record anchoring:use the %ROWTYPE attribute to define your record structure based on a table or a predefined PL/SQL explicit curor.
- The anchoring reference is reserved at the time the code is compiled;there is no runtime overhead to anchoring.
- The anchor also establishes a dependency between the code and the anchored element(the table,cursor,or package containing the variable referenced).Thi meas that if those elements are changed,the code in which the anchoring takes place is marked INVALID.
- When it is recomiled ,the anchor will again be resolved,thereby keeping the code current with the anchored element.
Another %ROWTYPE Attribute Example
SET SERVEROUT ON; --创建退休表retired_emp --创建表的时候,不能使用anchoring declaration
/* CREATE TABLE retired_emps ( empno employees.employee_id%TYPE, ename employees.last_name%TYPE, job employees.job_id$TYPE, mgr employees.manager_id%TYPE, hiredate employees.hire_date%TYPE, leavedate employees.hire_date$TYPE, comm employees.commission_pct%TYPE, deptno employees.department_id%TYPE ); */ /* DROP TABLE retired_emps; CREATE TABLE retired_emps AS SELECT * FROM employees WHERE 1=2; ALTER TABLE retired_emps DROP COLUMN FIRST_NAME; ALTER TABLE retired_emps DROP COLUMN EMAIL; ALTER TABLE retired_emps DROP COLUMN PHONE_NUMBER; ALTER TABLE retired_emps ADD LEAVEDATE DATE; */ TRUNCATE TABLE retired_emps; DECLARE v_emp_id number := 124; v_emp_rec employees%ROWTYPE; BEGIN SELECT * INTO v_emp_rec FROM employees WHERE employee_id = v_emp_id; INSERT INTO retired_emps(employee_id,last_name,job_id,manager_id,hire_date,leavedate,salary,commission_pct,department_id) VALUES( v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.manager_id, v_emp_rec.hire_date, SYSDATE, v_emp_rec.salary, v_emp_rec.commission_pct, v_emp_rec.department_id ); COMMIT;
--不能添加此查询语句,否则会报错.
--SELECT * FROM retired_emps; END; /
DROP TABLE retired_emps; CREATE TABLE retired_emps ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, LEAVEDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); DECLARE v_employee_number NUMBER := 124; v_emp_rec employees%ROWTYPE; BEGIN SELECT * INTO v_emp_rec FROM employees WHERE employee_id = v_employee_number; INSERT INTO retired_emps(empno,ename,job,mgr,hiredate,leavedate,sal,comm,deptno) VALUES ( v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.manager_id, v_emp_rec.hire_date, SYSDATE, v_emp_rec.salary, v_emp_rec.commission_pct, v_emp_rec.department_id ); COMMIT; END; /
Inserting a Record by Using %ROWTYPE
DECLARE v_employee_number NUMBER := 125; v_emp_rec retired_emps%ROWTYPE; BEGIN SELECT employee_id,last_name,job_id,manager_id,hire_date,SYSDATE,salary,commission_pct,department_id INTO v_emp_rec FROM employees WHERE employee_id = v_employee_number; INSERT INTO retired_emps VALUES v_emp_rec; COMMIT; END; / SELECT * FROM retired_emps;
Updating a Row in a Table by Using a Record
SET VERIFY OFF DECLARE v_emp_id NUMBER := 125; v_emp_rec retired_emps%ROWTYPE; BEGIN SELECT * INTO v_emp_rec FROM retired_emps WHERE empno = v_emp_id; v_emp_rec.leavedate := CURRENT_DATE; UPDATE retired_emps SET ROW = v_emp_rec WHERE empno = v_emp_id; COMMIT; END; / select empno,ename,job,mgr,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') hiredate,TO_CHAR(leavedate,'YYYY-MM-DD HH24:MI:SS') leavedate from retired_emps;
Record Level Operations
When you work at the record level,you avoid any references to individual fields in the record.Here are the record-level operations currently supported by PL/SQL:
- You can copy the contents of one record to another,as long as they are compatible in structure.
- You can assign a value of NULL to a record with a simple assignment.
- You can define and pass the record as an argument in a parameter list.
- You can RETURN a record back through the interface of a function.
DECLARE v_emp_rec employees%ROWTYPE; BEGIN v_emp_rec := NULL; END; /
DROP TABLE cust_sales_roundup; CREATE TABLE cust_sales_roundup ( customer_id NUMBER(5), customer_name VARCHAR2(100), total_sales NUMBER(15,2) ); DECLARE --基于表的定义Record cust_sales_roundup_rec cust_sales_roundup%ROWTYPE; CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup; --基于游标的定义Record cust_sales_rec cust_sales_cur%ROWTYPE; TYPE customer_sales_rectype IS RECORD ( customer_id NUMBER(5), customer_name departments.department_name%TYPE, total_sales NUMBER(15,2) ); --基于自定义的Record prefererred_cust_rec customer_sales_rectype; BEGIN -- Assign one record to another cust_sales_roundup_rec := cust_sales_rec; prefererred_cust_rec := cust_sales_rec; END; /
基于Anchoring declareation技术,声明Record的不同方法(3种).
You Cannot
Several record-level operations are not yet supported:
- You cannot use the IS NULL syntax to see if all fields in the record have NULL values.Instead,you must apply the IS NULL operator to each field individually.
- You cannot compare two records - for example,you cannot ask if the records(the value of their fields) are same or different,or if one record is greater than or less than another.To answer these kinds of questions,you must compare each individually.
如果要比较两个record是否相等,必须通过r1.column1 == r2.column1,r1.column2 == r2.column2,...
record赋值可以赋NULL,但是不能用IS NULL来判断.
record可以当做参数传递;