%TYPE and %ROWTYPE

--%type is used to fetch the data type of the particular column


create table product_details
(
p_id     NUMBER(3),
p_nm     VARCHAR2(30),
p_qty    NUMBER(8),
order_dt DATE
);


BEGIN
         INSERT INTO product_details VALUES(100,'Name0',400,'23-Mar-13');
         INSERT INTO product_details VALUES(101,'Name1',600,'26-Apr-13');
         INSERT INTO product_details VALUES(102,'Name2',800,'27-Jan-12');
         INSERT INTO product_details VALUES(103,'Name3',300,'23-Jul-11');
         INSERT INTO product_details VALUES(104,'Name4',200,'22-Aug-11');
         INSERT INTO product_details VALUES(105,'Name5',500,'25-Oct-12');
         commit;
END;
/

SELECT * FROM product_details;

------------------------------------
P_ID       P_NM   P_QTY   ORDER_DT
------------------------------------
100  Name0  400  03/23/2013
101  Name1  600  04/26/2013
102  Name2  800  01/27/2012
103  Name3   300  07/23/2011
104  Name4  200  08/22/2011
105  Name5  500   10/25/2012
------------------------------------



DECLARE
    v_name VARCHAR2(4);
BEGIN
    SELECT p_nm
      INTO v_name
      FROM product_details
     WHERE p_id = 100;
    dbms_output.put_line('Product Name  : ' || v_name);
    --error numeric or value error      
END;


DECLARE
    v_name VARCHAR2(5);
BEGIN
    SELECT p_nm
      INTO v_name
      FROM product_details
     WHERE p_id = 100;
    dbms_output.put_line('Product Name  : ' || v_name);
END;
/


ALTER TABLE product_details
MODIFY p_nm VARCHAR2(15); 


INSERT INTO product_details
VALUES
    (106,
     'name6',
     700,
     '26-Dec-12');

commit;


106  name6 700   12/26/2012


DECLARE
      v_name VARCHAR2(5);
BEGIN
      SELECT p_nm INTO v_name
      FROM product_details
      WHERE p_id = 106;
      dbms_output.put_line('Product Name  : ' || v_name);
      --error
END;
/




DECLARE
    v_name product_details.p_nm%TYPE;
BEGIN
    SELECT p_nm
      INTO v_name
      FROM product_details
     WHERE p_id = 106;
    dbms_output.put_line('Product Name  : ' || v_name);
END;
/




DROP TABLE product_details;


DECLARE
    dep_id     departments.department_id%TYPE;
    dep_name   departments.department_name%TYPE;
    dep_man_id departments.manager_id%TYPE;
    dep_loc_id departments.location_id%TYPE;
BEGIN

    SELECT department_id,
           department_name,
           manager_id,
           location_id
      INTO dep_id,
           dep_name,
           dep_man_id,
           dep_loc_id
      FROM departments
     WHERE department_id = 10;

    dbms_output.put_line('Department_id   :    ' || dep_id);
    dbms_output.put_line('Department_name :    ' || dep_name);
    dbms_output.put_line('Manager_id   :    ' || dep_man_id);
    dbms_output.put_line('Location_id  :    ' || dep_loc_id);

END;
/


--%rowtype is used to fetch the data type of all the column
--Insted of using %type if we use %rowtype means we can reduce the no of variables that we declare



DECLARE
    dep_detail departments%ROWTYPE;
BEGIN

    SELECT *
      INTO dep_detail
      FROM departments
     WHERE department_id = 10;

    dbms_output.put_line('Department_id   :  ' || dep_detail.department_id);
    dbms_output.put_line('Department_name :  ' || dep_detail.department_name);
    dbms_output.put_line('Manager_id   :  ' || dep_detail.manager_id);
    dbms_output.put_line('Location_id  :  ' || dep_detail.location_id);

END;
/


DROP TABLE dept_details;

CREATE TABLE dept_details
(
   dept_id           number(3)   ,
   dept_name         varchar2(30),
   dept_manager_name varchar2(30)
);


insert into dept_details values(10,'dept1','manager_name1');
insert into dept_details values(20,'dept2','manager_name2');

SELECT * FROM dept_details;

    -------------------------------------------------------
    |  DEPT_ID   | DEPT_NAME   | DEPT_MANAGER_NAME |
    +------------+-----------------------+-----------------
    |  10  | dept1     | manager_name1     |
    |  20  | dept2     | manager_name2     |    
    ------------+-----------------------+------------------


DECLARE
    all_data dept_details%ROWTYPE;
BEGIN

    all_data.dept_id           := 100;
    all_data.dept_name         := 'Admin';
    all_data.dept_manager_name := 'John';

    UPDATE dept_details
       SET ROW = all_data
     WHERE dept_id = 10;

    dbms_output.put_line(SQL%ROWCOUNT || ' Row(s) get updated');

END;
/


1 Row(s) get updated


select * from dept_details;


      ---------------------------------------------------
    |  DEPT_ID   | DEPT_NAME   | DEPT_MANAGER_NAME|
      ---------------------------------------------------
    |  100 | Admin    | John         |
    |  20 | dept2    | manager_name2    |
      ---------------------------------------------------

Interview Question :

1. What is the use of %TYPE?
2. What is the use of %ROWTYPE?
3. Difference between %TYPE and %ROWTYPE?