Oracle record types

Oracle Record type Introduction 1. What is Record )? A composite structure composed of scalar values of multiple columns in a single row. It can be seen as a user-defined data type. It is similar to a multi-dimensional array. Encapsulate one or more scalar values into one object for operations. Is a temporary composite object type. Records can be directly assigned values. RECORD1: = RECORD2; the record cannot be compared as a whole. The record cannot be determined as empty as a whole. Www.2cto.com 2, % ROWTYPE and Record )? Please differentiate the % ROWTYPE and Record types. % ROWTYPE is a simplified version of Record. The difference is that the former structure is the table structure, and the latter is the custom structure. There is no big difference between the two. The former is convenient while the latter is flexible. It is used according to actual conditions. Record + PL/SQL tables can store data in multiple rows and multiple columns. 3. How to create and use records? ① Create record type Syntax: type record Name is record (filed1 type1 [not null] [: = eXPr1], ......, filedN typen [not null] [: = exprn]) Where filed1 is the name of a scalar. ② Declare record type variable: record type variable name record type ③ fill record. ④ Access record Member record type variable name. filed1 ......... record type variable name. filedN www.2cto.com Note: After modifying the table field type, you also need to modify the record field type. Sometimes you may forget it and cause an error. For each field (filed1. ..) in the record ....), You can specify or use % TYPE and % ROWTYPE to dynamically specify the record field TYPE. The advantage is that the table field changes and the record field changes automatically. However, because % TYPR or % ROWTYPE is encountered before each execution, the database system will view the corresponding table field type, resulting in a certain amount of database overhead. If the system uses a large number of record types, this will have a certain impact on the performance. In addition, if you delete a field that is used in the Custom record, you may forget to delete the field. For systems with low database load, performance issues generally do not need to be focused. However, for high-load database servers, performance issues should be considered in all aspects, saving a little bit at each point, the overall performance is greatly improved. Syntax: type record Name is record (filed1 table. filed % Type [not null] [: = eXPr1], filed2 table. filed % Type [not null] [: = eXPr1],..., filedn table. filed % Type [not null] [: = exprn]); www.2cto.com example: records can be assigned an overall value [SQL]/* conn scott/tiger Create Table empa As Select * From emp; */Declare Type EmpType is Record (EMPNO number (4), ENAME varchar2 (10), JOB varchar2 (15), SAL number (7, 2), DEPTNO number (2); EmpRec1 EmpType; EmpRec2 EmpType; Begin parameters: = 7369; EmpRec1.Ename: = 'Smith '; EmpRec1.Job: = 'cler'; EmpRec1.Sal: = 800; EmpRec1.Deptno: = 10; EmpRec2: = EmpRec1; DBMS_output.put_line (EmpRec2.empno); End; example: the Record cannot be compared as a whole. Only the Record field [SQL] Declare Type EmpType is Record (EMPNO number (4) can be compared ), ENAME varchar2 (10), JOB varchar2 (15), SAL number (7,2), DEPTNO number (2); EmpRec1 EmpType; EmpRec2 EmpType; Begin EmpRec1. Empno: = 7369; EmpRec1.Ename: = 'Smith '; EmpRec1.Job: = 'cler'; EmpRec1.Sal: = 800; EmpRec1.Deptno: = 10; if EmpRec1.sal <EmpRec2.sal then DBMS_output.put_line ('xiao xiao'); end if; End; www.2cto.com example: the record cannot be null as a whole, but only the record fields can be determined. [SQL] Declare Type EmpType is Record (EMPNO number (4), ENAME varchar2 (10), JOB varchar2 (15), SAL number (7, 2), DEPTNO number (2 )); empRec EmpType; Begin if EmpRec. ename is null then DBMS_output.put_line ('ONG Kong '); end if; End; example: Use % TYPE and % ROWTYPE to dynamically specify the record fields. [SQL]/* conn scott/tiger Create Table empa As Select * From emp; */DECLARE Type MyRecType Is Record (RENO EMPA. EMPNO % Type, rename empa. ENAME % Type, rjob empa. JOB % Type); EmpRec MyRecType; Begin Select EMPNO, ENAME, JOB InTo EmpRec From empa Where empa. EMPNO = '000000'; If EmpRec. RJOB = 'cler' Then DBMS_OUTPUT.PUT_LINE ('name: '| EmpRec. RENAME); End If; End; example: Relationship between records in the dataset and data in the record type. Www.2cto.com [SQL] DECLARE Type MyRecType Is Record (RENO EMPA. EMPNO % Type, rename empa. ENAME % Type, rjob empa. JOB % Type); EmpRec MyRecType; vJob EMPA. JOB % Type; Begin Select EMPNO, ENAME, JOB InTo EmpRec From empa Where empa. EMPNO = '000000'; DBMS_OUTPUT.PUT_LINE ('myrectype. RJOB: '| EmpRec. RJOB); EmpRec. RJOB: = 'change value post'; DBMS_OUTPUT.PUT_LINE ('myrectype. RJOB: '| EmpRec. RJOB); Select JOB InTo vJob from Empa Where empa. EMPNO = EmpRec. RENO; DBMS_OUTPUT.PUT_LINE ('empa. JOB: '| vJob); End;/www.2cto.com 4. insert data into the table using records? Reasonably arrange record fields according to the table structure. For example, the primary foreign key. If you use RECORD to insert data, you can only use RECORD Members. If you use % ROWTYPE to insert data, you can use % ROWTYPE directly. Example: insert data into a table using Record members [SQL] DECLARE Type MyRecType Is Record (RENO EMPA. EMPNO % Type, RENAME VARCHAR2 (10), rjob empa. JOB % Type); EmpRec MyRecType; Begin Select EMPNO, ENAME, JOB InTo EmpRec From empa Where empa. EMPNO = '000000'; DBMS_OUTPUT.PUT_LINE (EmpRec. RENO | ''| EmpRec. RENAME | ''| EmpRec. RJOB); EmpRec. RENO: = 1001; EmpRec. RENAME: = 'jack'; EmpRec. RJOB: = 'clerk '; Insert InTo empa (EMPNO, ENAME, JOB) Values (EmpRec. RENO, EmpRec. RENAME, EmpRec. RJOB); Select EMPNO, ENAME, JOB InTo EmpRec From empa Where empa. EMPNO = '000000'; DBMS_OUTPUT.PUT_LINE (EmpRec. RENO | ''| EmpRec. RENAME | ''| EmpRec. RJOB); End; 5. Update data using records? If you use RECORD to update data, you can only use RECORD Members. If you use % ROWTYPE to update data, you can use % ROWTYPE directly. Example of www.2cto.com: Use % ROWTYPE to insert data to the table [SQL] DECLARE vEmp empa % RowType; Begin Select * InTo vEmp From empa Where empa. EMPNO = '000000'; UpDate empa Set ROW = vEmp Where EMPNO = 7369; End; 6. delete data using records? When deleting a record, you can only use record Members in the where clause of the delete statement.