Search This Blog

Thursday, October 31, 2013

Playing with PL record type

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.

see thoses examples, it will speak to you.

PROCEDURE INSERT_REC
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);

END INSERT_REC;

PROCEDURE INSERT_REC_BULK
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC_BULK');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;
INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;

END INSERT_REC_BULK;

PROCEDURE UPDATE_REC_BULK
AS
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('UPDATE_REC_BULK');
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;

END UPDATE_REC_BULK;

No comments: