-------------------------------PLSQL ASSESSMENT QUERIES------------------------------------------------
2. Write a pl/sql stored procedure to accept a dept number as input and return
all employees table column values in PL/SQL table
create or replace procedure emp_33_details (Lv_deptno number) as
type emp_dep is table of employee_2233_tb%rowtype index by binary_integer;
Lv_Details emp_dep;
cursor c1 is select * from employee_2233_tb where deptno = Lv_deptno;
begin
open c1;
fetch c1
bulk collect into Lv_Details;
close c1;
for i in Lv_Details.first..Lv_Details.last
loop
dbms_output.put_line('emp_no :'||' '||Lv_Details(i).empno||chr(9)||'empname :'||' '||
Lv_Details(i).ename||' '||'job:'||' '||Lv_Details(i).JOB||' ' ||
'mgr:'||' '||Lv_Details(i).MGR ||' '||'hiredate:'||' '||Lv_Details(i).HIREDATE||' '||'sal:'||' '||
Lv_Details(i).SAL||' '||'comm:'||' '||Lv_Details(i).COMM
'deptno:'||Lv_Details(i).DEPTNO);
end loop;
end;
exec emp_33_details(20);
/
3a) What is the result of anonymous block:
DECLARE
a number := NULL;
b number := null;
BEGIN
IF a=b then
DBMS_OUTPUT.PUT_LINE('a=b');
ELSIF a<>b then
DBMS_OUTPUT.PUT_LINE('a<>b');
ELSE
DBMS_OUTPUT.PUT_LINE('Else');
END IF;
END;
--OUTPUT:--- Else
------------------------------------------------------
3b) What is the difference between RAISE_APPLICATION_ERRORS and User defined Exception
RAISE_APPLICATION_ERRORS:
In this we can raise explict and implicit errors.
It allows developer to raise an exception and gives error message and error number.
It is defined by oracle .error numbers defined between -20,000 and -20,999.
User defined Exception:
In this we can raise only explicit errors.
It is defined by users according to the [Link] doesnt provide
any message to the calling [Link] is declared
and then raised explicitly using either a RAISE statement or the procedure DBMS_STANDARD.
----------------------------------------------------
3c) Write smallest anonymous block
BEGIN
DBMS_OUTPUT.put_line('PLSQL ASSESSMENT');
END;
----------------------------------------------------
3d) What are all the exception which we can’t handle in Cursors
4) Write a database trigger on EMP table to insert data into back up table EMP_BACKUP
when delete data on EMP table on weekends:
Backup table structure
USER_NAME VARCHAR2(100)
CREATION_DATE DATE
EMPNO NUMBER
DEPTNO NUMBER
ENAME VARCHAR2
---
Create table Emp_2233(
USER_NAME VARCHAR2(100),
CREATION_DATE DATE,
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(50)
);
INSERT INTO EMP_2233 VALUES('SYSADMIN','12-JAN-2021',111,01,'RESHMITHA');
INSERT INTO EMP_2233 VALUES('SYSADMIN','14-JAN-2021',111,01,'HARSHINI');
INSERT INTO EMP_2233 VALUES('SYSADMIN','18-JAN-2021',111,01,'BHUVAN');
INSERT INTO EMP_2233 VALUES('SYSADMIN','19-JAN-2021',111,01,'KAVYA');
Create table EMP_BACKUP_2233(
USER_NAME VARCHAR2(100),
CREATION_DATE DATE,
EMPNO NUMBER,
DEPTNO NUMBER,
ENAME VARCHAR2(50)
);
CREATE or replace TRIGGER backup_tr1
BEFORE
DELETE ON Emp_2233
FOR EACH ROW
Declare
cursor c1 is select * from EMP_2233;
Emp_rec c1%rowtype;
BEGIN
open c1;
while(c1%found)
loop
fetch c1 into Emp_rec;
insert into EMP_BACKUP_2233(USER_NAME,CREATION_DATE,EMPNO,DEPTNO,ENAME)
VALUES (EMP_rec.USER_NAME,
EMP_rec.CREATION_DATE,EMP_rec.EMPNO,EMP_rec.DEPTNO,EMP_rec.ENAME);
end loop;
close c1;
END;
select * from EMP_BACKUP_2233;
-----------------------------------------
5.a) Write a procedure to accept a string. If input string has any numbers
then procedure will return value as “Y” else will return value as “N”
create or replace procedure proc_str1 (lv_string in varchar2, result out varchar2)
as
BEGIN
if lv_string LIKE '%0%'
OR lv_string LIKE '%1%'
OR lv_string LIKE '%2%'
OR lv_string LIKE '%3%'
OR lv_string LIKE '%4%'
OR lv_string LIKE '%5%'
OR lv_string LIKE '%6%'
OR lv_string LIKE '%7%'
OR lv_string LIKE '%8%'
OR lv_string LIKE '%9%' Then
result := 'Y';
Else
result := 'N';
end if;
end;
/
Variable v1 varchar2;
exec proc_str1('RBS12G',:v1);
print :v1;
----------------------------------------
5b) What is the function over loading
we can use the same function name for several different functions but their parameters are
different type. by using this we can have improve code performance and also can have reusability.
-----------------------------------------
5c) What is the result of anonymous block
DECLARE
a number := NULL;
BEGIN
SELECT 'X'
INTO a
FROM DUAL
WHERE 1=1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
END;
--OUTPUT:--Compilation error
we have to specify others at last among all the exception handlers in the block
-----------------------------------
6. Write a PLSQL stored procedure to accept a DEPTNO of input and display employee details.
If user entered input parameter value then procedure will display employee details of given input
parameter value.
If user did not enter any parameter value (Blank) then procedure will display all employee details (All
departments).
----------------------------------
create or replace procedure proc_dept(lv_deptno in varchar2)is
cursor cur_dept select * from employee_2233_tb where DEPTNO=lv_deptno OR DEPTNO='NULL';
Emp_rec1 cur_dept%rowtype;
BEGIN
for emp_rec1 in cur
loop
dbms_output.put_line(emp_rec1.EMPNO||' '||emp_rec1.ENAME||' '||emp_rec1.JOB||' ' ||
emp_rec1.MGR ||' '||emp_rec1.HIREDATE||' '||emp_rec1).SAL||' '||emp_rec1.COMM
||' '||emp_rec1.DEPTNO);
END loop;
end ;
exec proc_dept('30');
--------------------
7)Create two tables and insert one lakh records in to first table and
Write a procedure to transfer data from first table to second table with bulk collect
and forall statements
---------------
CREATE TABLE TEST_SRCE(my_new_id NUMBER,
CONSTRAINT PK_EMPID1 PRIMARY KEY (my_new_id));
INSERT INTO TEST_SRCE SELECT rownum FROM dual CONNECT BY rownum<=100000;
CREATE TABLE TEST_DEST1 (ID NUMBER,
CONSTRAINT PK_EID1 PRIMARY KEY (ID));
Create or Replace PROCEDURE My_Procedure
NUM number
) IS
BEGIN
insert into TEST_DEST1
select *
from TEST_SRCE WHERE 1=1;
END;
EXEC My_Procedure(1);
SELECT * FROM TEST_SRCE;
SELECT * FROM TEST_DEST1;
select count(id) from TEST_DEST1;
---------------------------------
9. a) Write an anonymous block to update comm. In EMP table based one below conditions
1) If SAL is null then update comm = 1000
2) If SAL is <= 200 then update comm = 500
3) If SAL is > 200 then update comm = 300
begin
update Employee_2233_tb set comm=1000 where sal='NULL';
update Employee_2233_tb set comm=500 where sal<=200;
update Employee_2233_tb set comm=300 where sal>200;
end;
------------------------------------------------------------------------
9b) What is pragma autonomous transaction
Autonomous transcation is [Link] transaction become visible to other transactions
when the autonomous transaction commits.
Autonomous transaction pragma changes the way a subprogram works within a transaction.
A subprogram marked with this pragma can do sql operations and commit or rollback those
operations,without commiting or rolling back the data in the main transaction.
9 c) What is pragma exception init
It is user named [Link] this we can define our own error message and error number.
Pragma exception name with an oracle error [Link] this it refers to any internal exception by
name and we can write specific handler for it.
we can use exception_init in
the declarative part of any pl/sql block,subprogram or package.I t is used to deal with errors
10 a) Limitation of function while calling in SQL query
-By using user defined functions we cannot call a stored procedure.
-user defined functions doesnt allows to use of dynamic SQL or temp tables.
-- using this we can not return multiple result sets.
10 b) How to call commit in data base triggers
In this we cant use commit directly .
we have to call other procedure or function should havePRAGMA Autonomous_Transaction in it.
10 c) Write an anonymous block to return employee data with ref cursor
set serveroutput on;
declare
TYPE ref_cur IS REF CURSOR;
c_cur ref_cur;
r_emp employee_2233_tb%rowtype;
begin
open c_cur for
select * from employee_2233_tb;
loop
fetch c_cur into r_emp;
dbms_output.put_line(r_emp.EMPNO||' '||r_emp.ename||' '||r_emp.job||' '||r_emp.sal|| ' '||
r_emp.deptno||' '||r_emp.mgr||' '||r_emp.comm);
EXIT WHEN c_cur%notfound;
end loop;
end;