PLSQL: STORED PROCEDURES, STORED FUNCTIONS

Write a stored procedure to display "Hello Oracle" using DBMS_OUTPUT.

(Stored Procedure – Basic)

Write a stored procedure to display employee name for a given empno.

(Procedure + SELECT INTO)

Write a stored procedure to print salary of an employee.

(Procedure + SELECT)

Write a stored procedure to display all employees using cursor.

(Procedure + Cursor)

Write a stored procedure to display department details.

(Procedure – Basic)

Write a stored procedure that takes empno as input and displays employee details.

(IN Parameter)

Write a stored procedure that takes department number and displays employees of that department.

(IN Parameter)

Write a stored procedure that takes salary as input and displays employees earning more than that.

(IN Parameter)

Write a stored procedure with OUT parameter to return employee salary.

(OUT Parameter)

Write a stored procedure with IN OUT parameter to update salary and return new salary.

(IN OUT Parameter)

Write a stored procedure to insert a new employee record.

(Procedure + INSERT)

Write a stored procedure to update employee salary based on empno.

(Procedure + UPDATE)

Write a stored procedure to delete an employee record.

(Procedure + DELETE)

Write a stored procedure to increase salary of all employees by 10%.

(Procedure + UPDATE)

Write a stored procedure to transfer employees from one department to another.

(Procedure + Business Logic)

Write a function to return square of a number.

(Function – Basic)

Write a function to return employee salary based on empno.

(Function + SELECT INTO)

Write a function to return total number of employees.

(Function + Aggregate)

Write a function to return maximum salary from EMP table.

(Function + Aggregate)

Write a function to return department name based on deptno.

(Function)

Write a function to calculate bonus (10% of salary) and return it.

(Function + Calculation)

Write a function to return grade based on salary.

(Function + IF-ELSIF)

Write a function to check whether a number is even or odd.

(Function + Logic)

Write a function to return annual salary of an employee.

(Function + Calculation)

Write a function to return employee experience (based on hiredate).

(Function + Date Logic)

Write a procedure that calls a function to calculate bonus and display result.

(Procedure calling Function)

Write a function and use it inside SQL query.

(Function in SQL)

Write a procedure that uses function to update salary.

(Procedure + Function)

Write a function that returns highest salary and use it in procedure.

(Integration)

Write a stored procedure to display top 5 highest paid employees.

(Procedure + Advanced Query)

Write a stored function to return nth highest salary.

(Function + Logic)

Write a procedure to implement transaction (COMMIT / ROLLBACK).

(Procedure + Transaction Control)

Write a function that returns department-wise total salary.

(Function + GROUP BY)