Oracle Τύπος εγγραφών PL/SQL με παραδείγματα

What is a Record Type?

A record type is a complex data type that allows the programmer to create a new data type with the desired column structure. Its main characteristics are:

• It groups one or more columns to form a new data type.
• Each of these columns has its own name and data type.
• A record type can accept the data as a single record consisting of many columns, or it can accept the value for one particular column of a record.
• A record type is simply a new data type. Once created, it is stored as a new data type and can be used to declare a variable in programs.
• It uses the keyword 'ΤΥΠΟΣ' to instruct the compiler that a new data type is being created.
• It can be created at the database level, stored as a database object and used across the database, or at the subprogram level, visible only inside the subprogram.
• A database-level record type can also be declared for table columns, so that a single column can hold complex data.
• The data is accessed by referring to the variable name, then a period operator (.), then the column name, as ‘<record_type_variable_name>.<column_name>’.

Syntax to Declare a Record Type

Σύνταξη για δήλωση σε επίπεδο βάσης δεδομένων:

CREATE TYPE <type_name_db> IS RECORD
(
<column 1> <datatype>,
);

In the first syntax, the keyword ‘CREATE TYPE’ instructs the compiler to create the record type named “type_name_db” with the specified columns as a database object. This is given as an individual statement, not inside any block.

Syntax for declaration at the subprogram level:

DECLARE
TYPE <type_name> IS RECORD
(
<column1> <datatype>,
);
BEGIN
<execution_section>;
END;

In this syntax, we create the record type named “type_name” only inside the subprogram. In both methods, the way of defining the column and data type is similar.

Example 1: Record Type as a Database Object

In this program, we see how to create a record type as a database object. We create the record type ’emp_det’ with four columns. The columns and their data types are:

• EMP_NO (NUMBER)
• EMP_NAME (VARCHAR2 (150))
• MANAGER (ΑΡΙΘΜΟΣ)
• ΜΙΣΘΟΣ (ΑΡΙΘΜΟΣ)

CREATE TYPE emp_det IS OBJECT
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
/

Παραγωγή:

Type created

Code εξήγηση

• The above code creates the type emp_det as a database object.
• It has 4 columns: emp_no, emp_name, manager, and salary, as defined.
• Now ’emp_det’ is similar to any other Τύπος δεδομένων (like NUMBER or VARCHAR2) and is visible across the entire database. Hence it can be used anywhere in the database to declare a variable of this type.

Example 2: Subprogram Level, Column-level Access

In this example, we see how to create a record type at the subprogram level and how to populate and fetch values from it by column. We create the ’emp_det’ record type at the subprogram level and use it to populate and display data.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
guru99_emp_rec emp_det;
BEGIN
guru99_emp_rec.emp_no:= 1001;
guru99_emp_rec.emp_name:= 'XXX';
guru99_emp_rec.manager:= 1000;
guru99_emp_rec.salary:= 10000;
dbms_output.put_line('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name);
dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary);
dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager);
END;
/

Παραγωγή:

Employee Detail
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000

Code εξήγηση

• Code γραμμή 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
• Code γραμμή 9: The guru99_emp_rec variable is declared as ’emp_det’ data type. This μεταβλητή can hold a value that contains all four fields.
• Code γραμμή 11: Συμπλήρωση του πεδίου "emp_no" του "guru99_emp_rec" με τιμή 1001.
• Code γραμμή 12: Populating the ’emp_name’ field with value XXX.
• Code γραμμή 13: Populating the ‘manager’ field with value 1000.
• Code γραμμή 14: Populating the ‘salary’ field with value 10000.
• Code γραμμή 15-19: Displaying the value of ‘guru99_emp_rec’ in the output.

Example 3: Subprogram Level, Row-level Access

In this example, we see how to create a record type at the subprogram level and populate it at the row level. We create the ’emp_det’ record type at the subprogram level and use it to populate and display data.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
guru99_emp_rec emp_det;
BEGIN
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
COMMIT;
SELECT emp_no, emp_name, salary, manager INTO guru99_emp_rec FROM emp WHERE emp_no=1002;
dbms_output.put_line ('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name);
dbms_output.put_line ('Employee Salary: '||guru99_emp_rec.salary);
dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager);
END;
/

Code εξήγηση

• Code γραμμή 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
• Code γραμμή 9: The guru99_emp_rec variable is declared as ’emp_det’ data type and can hold all four fields.
• Code γραμμή 11: Populating the table emp with 1002 as emp_no, YYY as emp_name, 15000 as salary, and 1000 as manager number.
• Code γραμμή 12: Πραγματοποίηση της συναλλαγής εισαγωγής.
• Code γραμμή 13: Populating the ‘guru99_emp_rec’ variable at the row level from the select query for employee number 1002.
• Code γραμμή 15-19: Displaying the value of ‘guru99_emp_rec’ in the output.

Παραγωγή:

Employee Detail
Employee Number: 1002
Employee Name: YYY
Employee Salary: 1000
Employee Manager Number: 15000

Σημείωση: A record type can be accessed only at the column level when redirecting its value to any output mode. Notice that in this example the SELECT list order does not match the record field order, which is why the salary and manager values appear swapped in the output.

Types of PL/SQL Records

Beyond the programmer-defined record shown above, Oracle offers two anchored record kinds that copy their structure automatically. Using them avoids re-declaring columns and keeps the record in step with the table or cursor it is based on.

A %ROWTYPE record is the safest choice when the shape should follow a table or a δρομέας, because a column change is picked up automatically on the next compile.

Συχνές Ερωτήσεις