• PL/SQL

    Functions Vs Stored Procedures

    It is one of the famous or frequently asked interview question, so I am stating the difference between these two which I think is appropriate, Please correct me if I have left anything unattended here. Basic difference is a procedure can return nth values whereas function can return only one value which is mandatory to be. Function can have only input parameters whereas procedures can have both input/output parameters. Function doesn’t allow an update, delete, create(DML Statements) statements into it where as Procedure do. Functions can be called from DML or select statements whereas procedures canonly call from procedure or direct through call statements. We can not do transaction management…

    Comments Off on Functions Vs Stored Procedures
  • PL/SQL

    Nth PL/SQL to get Second largest Salary from table

    Today, I get an interview in which PL/SQL question was asked, we have a table lets say employee_master which has salary of employee, so requirement is to fetch the employee details who has second largest salary in table, This requirement can be implimented in various ways, Please find below the SQLs to achieve this: select top 1 Salary_amount from (select top 2 Salary_amount from employee_master order by salary_amount desc) a order by salary_amount asc ;with CTE AS( select row_number() over(order by salary_amount Desc) as id, salary_amount from employee_master ) select * from CTE where id = 2  DECLARE @SQL VARCHAR(2000), @N INT –@N is level at which you  –required Salary                                         SET @N = 3 SET @N = @N – 1  SET @sql = ‘select top 1 salary_amount from employee_master where salary not in…