InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
PL/SQL handles how many types of records? |
|
Answer» A record is a data structure in PL/SQL to hold data items of different kinds. For our examples on records in PL/SQL, we have the following sample table: <DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+PL/SQL handles the following types of records:
To create table-based records, you need to use the %ROWTYPE attribute. Using the same we will fetch the records of a department with id = 3: DECLARE rec_dept deptbudget%rowtype; BEGIN SELECT * into rec_dept FROM customerdeptbudgets WHERE deptid = 3; dbms_output.put_line('Department ID =' || rec_dept.deptd); dbms_output.put_line('Department Name = ' || rec_dept.deptname); dbms_output.put_line('Department Location = ' || rec_dept.deptloc); dbms_output.put_line('Department Budget = ' || rec_dept.budget); END; /The output: Department ID = 3 Department Name = Operations Department Location = WEST Department Budget = 98979 PL/SQL PROCEDURE successfully completed.
In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement. The following is an example: DECLARE CURSOR cur_dept is SELECT deptid, deptname, deptloc FROM deptbudget; rec_dept cur_dept %rowtype; BEGIN OPEN cur_dept ; LOOP FETCH cur_dept into rec_dept; EXIT WHEN cur_dept %notfound; DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc); END LOOP; END; /The output: 1 Finance NORTH 2 Marketing EAST 3 Operations WEST 4 Technical SOUTH 5 Accounting NORTHWEST PL/SQL procedure successfully completed.
In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures. Let us take an example of a Laptop’s record. Here is how you can declare: DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;You can now ACCESS the fields using the dot(.) operator. The following is the example wherein we have created user-define records and accesses the fields: DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;The output: Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989 |
|