PLSQL Sessions by Samba on 2026-Sep-02

 

PLSQL Sessions by Samba on 2026-Sep-02

Session 1

What is your rating with respect to SQL ?  8/10

What is your rating with respect to PLSQL ? 8/10

PLSQL 

Block Structure 

   unonymous Blocks 

   named Blocks 

   

   

   Begin 

   null    ;

   end ;

   

  Declare 

  lv_percent_hike NUMBER:=8;

  lv_deptno NUMBER :=20;

  Begin 

  dbms_output.put_line('Welcome to A2CF ERP Solutions PVt LTD :-  '||to_char(sysdate,'DD-MON-YYYY'))  ;

  

  update emp set sal=sal+(sal*lv_percent_hike/100) where deptno=lv_deptno;

  

  IF SQL%found THEN 

  dbms_output.put_line('No of employees for HIKE in the department : '||lv_deptno ||' is : '||sql%rowcount);

  COMMIT;

  ELSE 

  dbms_output.put_line('There is no eligible employee for HIKE in the department : '||lv_deptno);

  END IF;

  

  end ;

  

  

Write a procedure to give specified % of hike for specified department employees only ?

   

   

CREATE OR REPLACE PROCEDURE xxib_salary_hike_prc (

    p_deptno NUMBER,

    p_percent NUMBER

) IS

    lv_percent_hike   NUMBER;

    lv_deptno         NUMBER;

BEGIN

    lv_percent_hike := p_percent;

    lv_deptno := p_deptno;

    dbms_output.put_line('Welcome to A2CF ERP Solutions PVt LTD :-  ' || TO_CHAR(SYSDATE,'DD-MON-YYYY') );

    UPDATE emp

    SET

        sal = sal + ( sal * lv_percent_hike / 100 )

    WHERE

        deptno = lv_deptno;

    IF SQL%found THEN

        dbms_output.put_line('No of employees for HIKE in the department : '

                               || lv_deptno

                               || ' is : '

                               || SQL%rowcount);

        COMMIT;

    ELSE

        dbms_output.put_line('There is no eligible employee for HIKE in the department : ' || lv_deptno);

    END IF;

END;

   Session 2

Named Blocks 

   Procedure 

   Function 

   Trigger 

   

   

   package 

   

Create plsql block to get employee count for the given department no 

select count(*) from emp where deptno=20 

   

CREATE OR REPLACE FUNCTION get_emp_count (

    p_deptno IN NUMBER

)

RETURN NUMBER

IS

    l_emp_count NUMBER;

BEGIN

    SELECT COUNT(*)

    INTO l_emp_count

    FROM emp

    WHERE deptno = p_deptno;

xxib_salary_hike_prc( p_deptno,5 );

    RETURN l_emp_count;

END;

/  

select round(12.567,2) from dual

select round(12.567,2),get_emp_count(30) from dual

select e.*,get_emp_count(e.deptno),initcap(ename) from emp e

declare 

ln_emp_cnt number;

ln_deptno number:=20;

ln_percent_hike number:=5;

BEGIN 

ln_emp_cnt:=get_emp_count(ln_deptno);

IF ln_emp_cnt>0 then 

xxib_salary_hike_prc( ln_deptno,ln_percent_hike );

else 

 dbms_output.put_line('There is no eligible employee for HIKE in the department : ' || ln_deptno);

END IF ;

END ;

is it possible to call procedure in function ?

what is cursor ? how many types of cursors are there ? what are they ?

 Cursor is a private memory to store Query result set   

   2 types 

     1- Implicit   --cursor execution steps Done by Oracle engine itself

2-Explicit  -- 

   what are cursor execution steps ?

   

   Declare cursor 

   Open Cursor 

   Fetch Cursor and process data 

   close cusrsor 

   

 what are cursor attributes ?

Cursor attributes -4

   SQL%found

   %not found

   %is Open

   %rowcount  --