PL SQL Tutorial
In PL/SQL, Record is a composite data structure,which has multiple fields. You must first declare and define it in declare section.Record is a placeholder for single row of data.
Records can be table based, cursor based or programmer defined. %ROWTYPE is used to declare a table based or cursor based records. For user defined typed "TYPE record_name is RECORD is used."%ROWTYPE for table/cursor based records, it derives all fileds from source
Syntax:
variable_name table_name%ROWTYPE;
variable_name cursor_name%ROWTYPE;
DECLARE
EMP_REC EMP%ROWTYPE;
BEGIN
EMP_REC.EMPNO := 7899;
EMP_REC.DEPTNO := 30;
EMP_REC.ENAME := 'JHNOSON';
INSERT INTO EMP VALUES(EMP_REC);
END;
PL/SQL: ORA-00947: not enough values
PL/SQL supports updating a Row(s) using %ROWTYPE record or custom record. %ROWTYPE record holds all variables of a table, where as custom record type can hold all or few fields.
Update a row in DEPT table where deptno is 10, using %ROWTYPE variable.
DECLARE
dept_rec dept%ROWTYPE;
BEGIN
dept_rec.deptno:=10;
dept_rec.loc := 'Los Angeles';
UPDATE DEPT SET ROW=DEPT_REC where deptno = dept_rec.deptno;
END;
In above example set ROW= record_variable is used to update a row in a Table.
Note: Where condition should be specified, otherwise all rows in the Table will be updated.
UPDATE: 10% salary hike for MARTIN in scott EMP table. and get the Updated salary of this employee. it can be stored in RECORD variable.
Scott schema emp table has more than one employee in Dept no 20.
DECLARE
EMP_REC EMP%ROWTYPE;
EMP_UPDATED_REC EMP%ROWTYPE;
BEGIN
select empno,ename,job,mgr,HIREDATE,SAL,COMM,DEPTNO into emp_rec from emp where ename ='MARTIN';
dbms_oupt.put_line('Before Updating...');
dbms_output.put_line(emp_rec.ename||' '||emp_rec.sal);
--update the salary by 10%
emp_rec.sal := emp_rec.sal+(0.1*emp_rec.sal);
--update row with record, and then return updated record
UPDATE EMP SET ROW = EMP_REC WHERE ENAME= EMP_REC.ENAME
RETURNING EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO INTO EMP_UPDATED_REC;
--After updation
DBMS_OUTPUT.PUT_LINE('AFTER UPDATING EMP SALARY BY 10%');
DBMS_OUTPUT.PUT_LINE(EMP_UPDATED_REC.ENAME||' '||EMP_UPDATED_REC.SAL);
END;
Note: 2 Record variables cannot be compared to each other, only individual fields can be compared
User defined records should be declared first, then it must defined in order to use in the Program.
User-Defined Records can be used to fetch few columns from a table(s) or View(s).
Syntax:TYPE my_record is RECORD( var1 TYPE, var2 TYPE, var3 TYPE, ..... );
Get empno,sal,job,mgr details for ename=king, Here we are going to fetch few fields. for employee 'KING', assuming there is only record with ename 'KING', otherwise TOO_MANY_ROWS exception will be raised.
DECLARE
TYPE emprec is RECORD(
empno emp.empno%TYPE,
sal emp.sal%TYPE,
job emp.job%TYPE,
mgr emp.mgr%TYPE
);
EMP_REC EMPREC;
BEGIN
SELECT EMPNO,SAL,JOB,MGR INTO EMP_REC FROM EMP WHERE ENAME = 'KING';
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO||' '||EMP_REC.SAL||' '||EMP_REC.JOB||>
END;
SQL>/
7839 5000 PRESIDENT
PL/SQL procedure successfully completed.
ADS