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;DECLAREv_emp_id number := 124;v_emp_rec employees%ROWTYPE; BEGINSELECT * 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) );DECLAREv_employee_number NUMBER := 124;v_emp_rec employees%ROWTYPE; BEGINSELECT * 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

DECLAREv_employee_number NUMBER := 125;v_emp_rec retired_emps%ROWTYPE; BEGINSELECT employee_id,last_name,job_id,manager_id,hire_date,SYSDATE,salary,commission_pct,department_id INTO v_emp_rec FROM employeesWHERE 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 DECLAREv_emp_id NUMBER := 125;v_emp_rec retired_emps%ROWTYPE; BEGINSELECT * 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.

DECLAREv_emp_rec employees%ROWTYPE; BEGINv_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--基于表的定義Recordcust_sales_roundup_rec cust_sales_roundup%ROWTYPE;CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup;--基于游標的定義Recordcust_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 anothercust_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可以當做參數傳遞；

?

?

?

?

轉載于:https://www.cnblogs.com/arcer/archive/2013/04/21/3029932.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的[bbk5128]第12集 - Chapter 06- Working with Composite Data Types -01-4998(Record)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。