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 --
