PLSQL record

PL_SQL Record:-

PLSQL record is a composite data structure, which allows you to manage your data in program more efficiently.

PL/SQl record is a composite data type ,is a group of related data stored in fields. Each field in the PL/SQL record has its own name & data type.

There are three ways to declare PLSQL record type.
A.Table Based Record.
B.Cursor based Recod.
C.Programmer User defined Record.

How to declare Table -Based-Record.
You can use a table name with % Rowtype attribute. The fields of the PL/SQL record has the same name and data type as the column of the table.
Syntax:-
Declare

Table_based_record table_name%Rowtype;
Example:-  Write a pl sql block that display employees last_name & salary of an employees from Employee_id 100 to Empployee_id 109 using Record Type.

SET SERVEROUT ON
DECLARE
  EMPREC EMPLOYEES%ROWTYPE;
BEGIN
  FOR EMPID IN 100..109 LOOP
      SELECT *
      INTO   EMPREC
      FROM   EMPLOYEES
      WHERE  EMPLOYEE_ID=EMPID;

      DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
  END LOOP;
END;


Output You can see is as follows.
King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.



Example 2:- First we need to create a table called RETIRED_EMPS.

CREATE TABLE RETIRED_EMPS

(EMPNO NUMBER,ENAME VARCHAR2(20),JOB VARCHAR2(20),MGR NUMBER, HIREDATE DATE,

 LEAVEDATE DATE, SAL NUMBER, COMM NUMBER, DEPTNO NUMBER);

Now we will write a Plsql block that, it will prompt the employee_id from the user & write employees details into  RETIRED_EMPS table.

DECLARE

  EMPREC EMPLOYEES%ROWTYPE;

BEGIN

  SELECT * INTO EMPREC FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  INSERT INTO RETIRED_EMPS

  VALUES(EMPREC.EMPLOYEE_ID,EMPREC.LAST_NAME,EMPREC.JOB_ID,EMPREC.MANAGER_ID,

         EMPREC.HIRE_DATE,SYSDATE,EMPREC.SALARY,EMPREC.COMMISSION_PCT,

         EMPREC.DEPARTMENT_ID);

  COMMIT;

END;

/

Output you can see:-

SELECT * FROM RETIRED_EMPS;




Example 3:Using ROWCOUNT  & NOTFOUND ATTRIBUT.

DECLARE
  CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
  EMPREC EMPLOYEES%ROWTYPE;
BEGIN
  OPEN EMPCUR;
  LOOP
     FETCH EMPCUR INTO EMPREC;
     DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
     EXIT WHEN EMPCUR%ROWCOUNT >= 10 OR EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
END;