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; 

/

RDBMS PLSQL Program 23 Procedure to print hello world message

 set serveroutput on

BEGIN 

   greetings; 

END; 

/

RDBMS PLSQL Program 22 Procedure to print hello world message and program to call it

Procedure

=====================================

 CREATE OR REPLACE PROCEDURE greetings

AS 

BEGIN 

   dbms_output.put_line('Hello World!');

END; 

/

Program to call above procedure

========================================

 set serveroutput on

BEGIN 

   greetings; 

END; 

/

RDBMS Theory Assignment 1

Write short answers of following questions:

1. Define self join.

2. What difference is between truncate and drop table ?

3. Give the definition of Active Data Set.

4. Write the difference between unique constraint and primary constraint.

5. What do you mean by save point ? How to createSave point ?

6. Difference between CYCLE and NO CYCLE option in sequence. 

7. Define equi join. 

8. What is the difference between NO_DATA_FOUND and %NOTFOUND. 

9. State the difference between function and procedure in oracle. 

10. Explain intersect clause. 

11. Write a command to give insert and modify privileges on emp from user Raj to user Anand. 

12. Define view with syntax. 

13. What is the functionality of Rollback statement?

14. State the difference between function and procedure in oracle.

15. How to create two procedures having same name ?

16. What is meaning of :New and :Old in trigger ?

Write answers of following questions in detail:

1. List out E.F.T. codd’s rule. Explain any four in detail.

2. What is cursor? Explain types of cursor with example.

3. Write a note on implicit and explicit cursor with example.

4. What is View ? Explain types of View.

5. What is Data Constraints ? List out all types of constraint. Explain foreign key constraint with ON DELETE SET NULL.

6. Discuss types of join.

7. Discuss PL/SQL block structure 

8. What is Exception ? Explain types of exception handling

in oracle. Give one example.

9. Write a PL/SQL code for automatic primary key generation using database trigger. 

10. Define overloading. Explain procedure overloading in oracle with proper example. 

 

Write notes on the following:

1. Any four string functions

2. Any four date functions

3. Index

4. Sequence

5. Range searching and pattern matching operator 

6. Aggregate functions 

7. Sub-query 

8. Trigger 

Do as directed:

Consider the following table and do as directed:

Employee (Empno, Emp-name, Date-Of—Birth,Address,City)

Salary (Emp-no, Basic, DA, HRA, Tax, Gross, NET)

1. Create a procedure which calculate gross and Net Salary

after incrementing basic salary by 11% given emp-no.

2. Create a function which return name of employee who

is youngest when empno is passed.

3. Create a trigger which removes records of those

employees who cross their 45 years of age.

Train(Train-no,Train-name) 

Station(Station-code,Station-name) 

Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out) 

(Halt-code,Seq-no are composite key) 

1. Create a procedure which return Time-in and Time-out for given Train-no and Station-name. 

2. Create a function which return name of train which stops at maximum stations. 

3. Create a trigger which delete all related records from other tables when any record is deleted from train table.