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_id | First_Name | Last_name | Salary | DEPT_ID |
|---|
| 1 | Shruti | Shrabya | 50000 | 1 |
| 2 | Jaya | Singh | 10000 | 2 |
| 3 | Mangala | Thokal | 60000 | 3 |
| 4 | Surendra | Maurya | 70000 | 4 |
Table2: 'Department'
| Dept_ID | Dept_Name | Manager_ID |
|---|
| 1 | Accounting | 1 |
| 2 | Shipping | 3 |
| 3 | Store | 3 |
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:
