Records in PL/SQL

Records in PL/SQL

1. Declaring records
Table-based record
Declare one_book books % rowtype;

Cursor-based record
Declare cursor my_books_cur is
Select * From books
Where author like '% Feuerstein % ';
One_sf_book my_books_cur % rowtype;

Both of the preceding methods use the % rowtype attribute. Syntax:
Record_name [Schema_name.] object_name % rowtype
[Default |: = compatible_record];

Schema_name is optional and is the schema for compiling the code by default.
Object_name can be explicit cursor, cursor variable, table, view, or Synonym
You can specify a default value. The default value must be of the same or compatible type as record.
Example of Using cursor variable:
Declare type book_rc is ref cursor return books % rowtype;
Book_cv book_rc;
One_book book_cv % rowtype;
Begin
...

Programmer-defined record
Declare type book_info_rt is record (
Author books. Author % type,
CATEGORY varchar2 (100 ),
Total_page_count positive );
Steven _as_author book_info_rt;

Implicit record Declaration
Begin
For book_rec in (select * From books)
Loop
Calculate_total_sales (book_rec );
End loop;
End;
Book_rec is PL/SQLImplicitly declared with % rowtype


2. programmer-defined records
Procedure:
1. Declare or define a record type to contain the structure you need
2. Use this record type to declare the actual records you need

Declaring programmer-defined record types
Type type_name is record
(Field_name1 datatype1,
Field_name2 datatype2,
...
Field_namen datatypen
);
Field_name must be unique
Datatype can be:
1. hardcoded, scalar datatype (varchar2, number, etc .).
2. programmer-defined subtype
3. Anchored declarations using % type or % rowtype attributes.
4. PL/SQLCollection type
5. Ref cursor (cursor variable)
Besides datatype, you can also use default or: = to specify the default value. You can also use constraints, such as not null (the default value must be specified when not null is used)

Declaring the record
Once record types is declared, you can use it to declare any records of this type:
Record_name record_type;


3. Working with records
Record-level operations
PL/SQLSupported:
1. Copy content between two records (as long as the two structures are compatible, for example, the same number of fields, the same or compatible data types)
2. Assign null to record
3. You can define a record in the parameter or pass the record to the parameter.
4. It can be used as the return value of the function.
Unsupported:
1. You cannot use is null for record to determine whether each fields is null. Instead, you should determine whether each field is null.
2. You cannot directly compare two records. For example, if two records are equal or not equal, or if one record is larger than the other, you can only compare fields one by one.
3. Before 9i R2, you cannot insert a record into a table. You can only insert each field into the corresponding column.

Field-level operations
Field access Syntax:
[Schema_name.] [package_name.] record_name.field_name
If the record is defined in the package, Schema_name is the schema to which the package belongs, rather than the schema used to compile the code.


4. Trigger pseudo-records
When writing a trigger, Oracle provides two pseudo-records with the same structure as the table-based records declared using % rowtype:
Old indicates the value of each column in the table before the current transaction is completed.
New indicates the value of each column in the table after the current transaction is completed.
When using them, you need to add a colon before them. When using them in when clause, you do not need to add:
Create or replace trigger check_raise
After update of salary
On employee
For each row
When (old. salary! = New. Salary) or
(Old. Salary is null and new. salary is not null) or
(Old. salary is not null and new. Salary is null)
Begin
If: New. salary> 100000 then...