1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Retrive full record in PLSQL by using %rowtype

Discussion in 'SQL PL/SQL' started by Puru, Oct 14, 2010.

  1. Puru

    Puru Forum Advisor

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    How to display full record of an employee from the emp table. I know only empno by entering the empno how to get full record by using %rowtype in PLSQL ;)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> DECLARE
      2          r_cont emp%rowtype;
      3          v_snarf varchar2(80);
      4          v_snorf varchar2(40);
      5
      6          cursor get_empinfo IS
      7          SELECT *
      8          FROM emp;
      9
     10  BEGIN
     11          OPEN get_empinfo;
     12          loop
     13                  fetch get_empinfo INTO r_cont;
     14                  exit WHEN get_empinfo%notfound;
     15
     16                  CASE
     17                  WHEN r_cont.empno < 7600 THEN v_snarf := 'Hester Blodgett';
     18                  ELSE NULL;
     19                  END CASE;
     20
     21                  CASE
     22                  WHEN r_cont.job <> 'SALESMAN' THEN v_snorf := 'Bazooka';
     23                  ELSE NULL;
     24                  END CASE;
     25
     26
     27                  IF r_cont.job <> 'SALESMAN' THEN
     28                          IF r_cont.sal IS NOT NULL AND r_cont.comm IS NOT NULL THEN
     29                                  dbms_output.put_line(r_cont.empno||' '|| r_cont.ename||' '|| r_cont.job||' '|| r_cont.sal||' NSAL '||v_snarf);
     30                                  dbms_output.put_line(r_cont.empno||' '|| r_cont.ename||' '|| r_cont.job||' '|| r_cont.comm||' NCOM '||v_snorf);
     31                  ELSE NULL;
     32                          END IF;
     33                  END IF;
     34          END loop;
     35  END;
     36  /
    7369 SMITH CLERK 800 NSAL Hester Blodgett
    7369 SMITH CLERK 0 NCOM Bazooka
    7566 JONES MANAGER 2975 NSAL Hester Blodgett
    7566 JONES MANAGER 0 NCOM Bazooka
    7698 BLAKE MANAGER 2850 NSAL Hester Blodgett
    7698 BLAKE MANAGER 0 NCOM Bazooka
    7782 CLARK MANAGER 2450 NSAL Hester Blodgett
    7782 CLARK MANAGER 0 NCOM Bazooka
    7788 SCOTT ANALYST 3000 NSAL Hester Blodgett
    7788 SCOTT ANALYST 0 NCOM Bazooka
    7839 KING PRESIDENT 5000 NSAL Hester Blodgett
    7839 KING PRESIDENT 0 NCOM Bazooka
    7876 ADAMS CLERK 1100 NSAL Hester Blodgett
    7876 ADAMS CLERK 0 NCOM Bazooka
    7900 JAMES CLERK 950 NSAL Hester Blodgett
    7900 JAMES CLERK 0 NCOM Bazooka
    7902 FORD ANALYST 3000 NSAL Hester Blodgett
    7902 FORD ANALYST 0 NCOM Bazooka
    7934 MILLER CLERK 1300 NSAL Hester Blodgett
    7934 MILLER CLERK 0 NCOM Bazooka
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>