Oracle series: Record

Oracle series: Record)

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 regarded as null.
 
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]
)
Filed1 is the name of a scalar.

② Declare record type variables:
Record type variable name record type

③ Fill records.

④ Access record Member
Record type variable name. filed1
.........
Record type variable name. filedN


Note:
After modifying the table field type, you also need to modify the record field type. Sometimes, you may forget it, resulting in 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, which will cause a certain amount of database overhead. If the system uses a large number of record types, the performance will be affected.
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 are generally not important, but for High-load database servers,
Performance issues should be taken into account in all stages, saving a little bit for each part, and the overall performance will be 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]
);
 
Example: record values can be assigned as a whole
/* 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
Emprec1.empno: = 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, but can only compare record Fields
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
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;
 
Example: The record cannot be regarded as null as a whole, but only the record fields can be determined.
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 field.
/* 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 = '20140901 ';
If emprec. rjob = 'cler' then
Dbms_output.put_line ('name: '| emprec. Rename );
End if;
End;
 
Example: Relationship between records in a dataset and data in the record type.
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 = '20140901 ';
Dbms_output.put_line ('myrectype. rjob: '| emprec. rjob );
Emprec. rjob: = 'after modifying the value ';
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;
/
 
 
4. insert data into a 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 % rowtype is used to insert data, % rowtype can be used directly.
 
Example: insert data into a table using record Members
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 = '20140901 ';
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 = '20140901 ';
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 % rowtype is used to update data, % rowtype can be used directly.
 
Example: insert data to a table using % rowtype
Declare
Vemp EMPA % rowtype;
Begin
Select * into vemp from EMPA where EMPA. empno = '000000 ';
Update EMPA sets ROW = vemp where empno = 1001;
End;

6. Use records to delete data?
When deleting a record, you can only use record Members in the WHERE clause of the delete statement.

Next article: Oracle series: PL/SQL tables

If you have any questions or correct me, please contact: qfs_v@qq.com thank you!