Create a table based record - PL/SQL Program

Pl/Sql uses two types of cursors.

1. Implicit Cursor
  • Implicit cursors are automatically generated by the Oracle engine. If the Oracle Engine opens a cursor for its internal processing, it is known as Implicit cursor.
  • Implicit cursors are created by default to process the statements when DML statements(INSERT, UPDATE, DELETE) are executed.
2. Explicit Cursor
  • If a cursor is opened for processing data through a PL/SQL block as per requirement like user defined cursor, is known as an Explicit cursor.
  • Explicit cursor is created while executing a SELECT statement that returns more than one row.
  • These cursor should be defined in the declaration section of the PL/SQL block and created on a SELECT statement which returns more than one row.
Consider the following tables to complete the following assignment.

Table 1:  'Emp_Detail'

Employee_idFirst_NameLast_nameSalaryDEPT_ID
1ShrutiShrabya500001
2JayaSingh100002
3MangalaThokal600003
4SurendraMaurya700004

Table2: 'Department'

Dept_IDDept_NameManager_ID
1Accounting1
2Shipping3
3Store3

Q. Write a PL/SQL block to Create a table based record.

Answer:

In table based record the entire structure is similar to columns of table.

Note: To create a table based record/ cursor based record  always preferred %ROWTYPE.

DECLARE
vr_emp emp_detail%ROWTYPE;
BEGIN
SELECT *
INTO vr_emp
FROM emp_detail
WHERE employee_id = 1;
DBMS_OUTPUT.PUT_LINE('Employee Details : '||vr_emp.employee_id ||' '||vr_emp.first_name||' '||vr_emp.last_name||' '||vr_emp.salary);
END;


The above code will display the employee whose id = 1.

Output:

table based record