Easy Learning... (Web Development,.NET Technology,Oracle,Silverlight,REST,SQL,Oracle,MySQL,Data Science, Python and many more ...)
Wednesday, September 30, 2020
RDBMS PLSQL Program 30 Program to handle multiple exceptions
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No such employee!');
WHEN NOT_LOGGED_ON THEN
dbms_output.put_line('user not logged in ');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('multiple records found');
WHEN VALUE_ERROR THEN
dbms_output.put_line('value type of size not appropriate');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
RDBMS PLSQL Program 29 Program to handle User defined exception
SET serveroutput On
DECLARE
veno emp.eno%type;
vename emp.ename%type;
ex_invalid_no EXCEPTION;
BEGIN
veno := '&veno';
IF substr(veno,1,1) <> 'E' THEN
RAISE ex_invalid_no;
ELSE
SELECT ename INTO vename
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
END IF;
EXCEPTION
WHEN ex_invalid_no THEN
dbms_output.put_line('ID must start with E');
END;
/
RDBMS PLSQL Program 28 Program to handle User named exception
DECLARE
resource_busy_nowait EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);
veno emp.eno%type;
vename emp.ename%type;
BEGIN
veno := '&veno';
vename := '&vename';
SELECT eno, ename into veno, vename
FROM emp
Where eno = veno
FOR UPDATE NOWAIT;
UPDATE emp SET ename = vename WHERE eno = veno;
EXCEPTION
WHEN resource_busy_nowait THEN
Dbms_output.Put_line('emp is locked - please try later');
END;
/
RDBMS PLSQL Program 27 Program to handle oracle named exception
set serveroutput on
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
BEGIN
veno := '&veno';
SELECT ename, esalary INTO vename, vesalary
FROM emp
WHERE eno = veno;
DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);
DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such employee!');
END;
/
RDBMS PLSQL Program 26 Procedure with in and out parameters and program to call it
Procedure
=======================================================
CREATE or REPLACE PROCEDURE pro1(veno in char,temp out emp%rowtype)
IS
BEGIN
SELECT * INTO temp FROM emp WHERE Eno = veno;
END;
/
Program to call above procedure
=======================================================
set serveroutput on
DECLARE
temp emp%rowtype;
veno emp.eno%type;
BEGIN
veno :='&veno';
pro1(veno,temp);
dbms_output.put_line(temp.eno||' -> '||temp.ename||' -> '||temp.edno||' -> '||temp.esalary);
END;
/
RDBMS PLSQL Program 25 Function with parameter, return value and program to call it
Function
================================================
CREATE or REPLACE FUNCTION fun1(veno in varchar)
RETURN varchar2
IS
name emp.ename%type;
BEGIN
select ename into name from emp where eno = veno;
return name;
END;
/
Program to call above function
=============================================
set serveroutput on
DECLARE
veno emp.eno%type;
name emp.ename%type;
BEGIN
veno := '&veno';
name := fun1(veno);
dbms_output.put_line('Name:'||' '||name);
end;
/
RDBMS PLSQL Program 24 Function to count number of employees in emp table and program to call it
Function
============================================
CREATE OR REPLACE FUNCTION totalemps
RETURN number IS
total number(3) := 0;
BEGIN
SELECT count(*) into total
FROM emp;
RETURN total;
END;
/
Program to call above function
======================================
set serveroutput on
DECLARE
e number(3);
BEGIN
e := totalemps();
dbms_output.put_line('Total no. of Employees: ' || e);
END;
/