Trending September 2023 # Oracle Pl/Sql Records Type With Examples # Suggested October 2023 # Top 12 Popular

You are reading the article Oracle Pl/Sql Records Type With Examples updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Oracle Pl/Sql Records Type With Examples

What is Record Type?

A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.

It groups one or more column to form a new data type

These columns will have its own name and data type

A Record type can accept the data

As a single record that consists of many columns OR

It can accept the value for one particular column of a record

Record type simply means a new data type. Once the record type is created, it will be stored as a new data type in the database and the same shall be used to declare a variable in programs.

It will use the keyword ‘TYPE’ to instruct the compiler that it is creating the new data type.

It can be created at “database level” which can be stored as database objects, used all-over the database or it can be created at the “subprogram levels”, which is visible only inside the subprograms.

The database level record type can also be declared for the table columns so that single column can hold the complex data.

Syntax for declaration at the database level:

( );

In the first syntax, we can see the keyword ‘CREATE TYPE’ this instructs the compiler to create the record type named “type_name_db” with the specified column as a database object.

This is given as an individual statement and not inside any block.

Syntax for declaration at subprogram level:

DECLARE ( ); BEGIN END;

In the syntax, we are creating the record type named “type_name” only inside the subprogram.

In both declaration method, the way of defining the column and data type is similar.

Example 1: RECORD Type as Database Object

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

EMP_NO (NUMBER)

EMP_NAME (VARCHAR2 (150))

MANAGER (NUMBER)

SALARY (NUMBER)

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

Output:

Type created

Code Explanation:

The above code will create type emp_det as a database object.

It will have 4 column emp_no, emp_name, manager and salary as defined.

Now ’emp_det’ is a similar to other data type (like NUMBER, VARCHAR@, etc.) And it is visible in the entire database. Hence this can be used in the entire database to declare the variable of this type.

Output:

Created the type ’emp_det’ as record type at the database level.

Example 2: Record Type at Subprogram level- Column level access

In this example, we are going to see how to create a record type at subprogram level and how to populate and fetch the values from it by column level.

We are going to create ’emp_det’ record_type at subprogram level, and we are going to use the same to populate and to display data from it.

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'); END; /

Output:

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

Code Explanation:

Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.

Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns.

Code line 11: Populating the ’emp_no’ field of ‘guru99_emp_rec’ with value 1001.

Code line 12: Populating the ’emp_name’ field of ‘guru99_emp_rec’ with value XXX.

Code line 13: Populating the ‘manager’ field of ‘guru99_emp_rec’ with value 1000.

Code line 14: Populating the ‘salary’ field of ‘guru99_emp_rec’ with value 10000.

Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.

Example 3: Record Type at Subprogram level-Row level access

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

DECLARE TYPE emp_det IS RECORD ( EMP_NO NUMBER, EMP_NAME YARCHAR2( 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’); END; /

Code Explanation:

Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.

Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns.

Code line 11: Populating the table emp with data 1002 as emp_no, YYY as emp_name, 15000 as salary and 1000 as manager number.

Code line 12: Committing the above insert transaction.

Code line 13: Populating the ‘guru99_emp_rec’ variable as a row level data from the select query for employee number 1002.

Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.

Output:

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

Note: The record type can be accessed only in column level while redirecting its value to any output mode.

You're reading Oracle Pl/Sql Records Type With Examples

Update the detailed information about Oracle Pl/Sql Records Type With Examples on the Dacvumuahe.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!