SQL WITH PLSQL Examples

Ex: 1:-


DECLARE
  V_EMPNO NUMBER:=&ENTER;
  V_ENAME VARCHAR2(20);
  V_JOB VARCHAR2(20);
  V_SAL NUMBER(9,2);
BEGIN
    SELECT
     ENAME,
     JOB,
     SAL
    INTO
     V_ENAME,
     V_JOB,
     V_SAL
    FROM EMP
    WHERE EMPNO=V_EMPNO;
        DBMS_OUTPUT.PUT_LINE('THE NAME : '||V_ENAME);
        DBMS_OUTPUT.PUT_LINE('THE JOB : '||V_JOB);
        DBMS_OUTPUT.PUT_LINE('THE SAL : '||V_SAL);
END;
/

Ex: 2:-

DECLARE

  V_EMPNO NUMBER:=&ENTER;

  V_ENAME VARCHAR2(20);

  V_JOB VARCHAR2(20);

  V_COMM NUMBER(9,2);

BEGIN

    SELECT 

     ENAME,

     JOB, 

     COMM 

    INTO 

     V_ENAME, 

     V_JOB,

     V_COMM 

    FROM EMP 

    WHERE EMPNO=V_EMPNO;

        DBMS_OUTPUT.PUT_LINE('THE NAME : '||INITCAP(V_ENAME));

        DBMS_OUTPUT.PUT_LINE('THE JOB : '||INITCAP(V_JOB));

        DBMS_OUTPUT.PUT_LINE('THE SAL : '||NVL(TO_CHAR(V_COMM),'NO COMMITION'));

END;

/

Ur's

AmarAlam