PL/SQL tutorial 42: How To Create PL/SQL Stored Procedure With Parameters In Oracle Database

Select Sal from emp where row num = 3

Can we select statement in procedure?? To get out put of cretain coloumns..??

your tone makes one learn easily

guys plz any one help me to get free certification from oracle as developer

Thanks manish for such a invaluable lectures.
does anybody know how to copy one table's field into another table using stored procedure.
well i have tried but occurs an errror.

create or replace procedure table_copy(
source_table varchar2,
target_table varchar2)
is
begin
execute immediate 'insert into '||target_table||' (select * from '||source_table||')';
end;

execute table_copy (source_table,target_table);

error popping is : PLS-00357: Table,View Or Sequence reference 'table_name' not allowed in this context

bhai manish u cant describe how to exec this emp_sal procedure

How to print two tables in stored procedures using a dbms_output.put_line statement ... Please provide me this answer as possible as

How to create same with the select query?

--3rd highest salary of the employees
CREATE OR REPLACE PROCEDURE third_hg_salary IS
CURSOR cr_empSal IS
SELECT ename, sal FROM emp
ORDER BY sal desc;

var_empSal cr_empSal%ROWTYPE;
var_counter NUMBER := 1;
BEGIN
OPEN cr_empSal;
FOR var_counter IN 1..3 LOOP
FETCH cr_empSal INTO var_empSal;
EXIT WHEN cr_empSal%NOTFOUND;
IF var_counter = 3 then
DBMS_OUTPUT.PUT_LINE(var_empSal.eName ||' '|| var_empSal.sal);
END IF;
END LOOP;
CLOSE cr_empSal;
END;
/

EXEC third_hg_salary;

Thankyou sir

CREATE OR REPLACE PROCEDURE NTHSALARY ( N INTEGER)
IS
SALARY SCOTT.EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO SALARY FROM (SELECT DISTINCT SAL FROM (SELECT SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) AS DRANK FROM SCOTT.EMP) WHERE DRANK=N);
DBMS_OUTPUT.PUT_LINE('SALARY OF RANK' || N || '=' || SALARY);
END NTHSALARY ;
/
EXEC NTHSALARY(3);

I have to pass. Table parameter to oracle store procedure

How to execute?

create or replace procedure usp_max_sal (n number) is
v_salary number:=0;
BEGIN
SELECT Salary into v_sal
FROM employees W1
WHERE n = (
SELECT COUNT( DISTINCT ( W2.Salary ) )
FROM employees W2
WHERE W2.Salary >= W1.Salary
);
DBMS_OUTPUT.PUT_LINE(v_sal||' is the '||n||' highest salary');
END;

Then execute it

SET SERVEROUTPUT ON;
EXEC usp_max_sal(n);

Thanks

SQL> create or replace procedure my_poc (p_rank number)
2 as
3 cursor mycursor is
4 select * from (select ename,sal,dense_rank() over(order by sal desc)rank
5 from emp)where rank<=p_rank;
6 begin
7 for myindex in mycursor loop
8 dbms_output.put_line(myindex.ename||' sal is '||myindex.sal);
9 end loop;
10 end;
11 /

Procedure created.

SQL> exec my_poc(3);
KING sal is 5000
SCOTT sal is 3000
FORD sal is 3000
JONES sal is 2975

i wanted to create a PL SQL code for sending zip file in a mail as attachment. but I am not able to do this, Can you help me here Manish???? @Manish Sharma

TOP

create or replace procedure third_sal(dense_ran number)
as
v_sal emp.sal%type;
begin
select sal into v_sal from(select sal,dense_rank()over(order by sal desc)r from emp)where r=dense_ran;
dbms_output.put_line('the'||dense_ran||'th highest sal is'||v_sal);
end;
/
begin
third_sal(&dens_rank);
end;

Thanks manish for your knowledge sharing.
Short and accurate code for nth highest salary.just pass the n value what ever you want ;-)
create or replace procedure n_highsal(n number) is
vsal number:=0;
begin
select salary into vsal from
(select salary,dense_rank()
over(order by salary desc) r from employees)where r = n;
dbms_output.put_line(vsal||' is the '||n||' highest salary');
end;

First of All a big thank to Manish.
I solve the question in a dynamic way by using a PROCEDURE with n parameter and it includes CURSOR base record variable and FOR LOOP. n represents the order of the salary noting that salaries are ordered descendingly.
CREATE OR REPLACE PROCEDURE n_high_salary
(n NUMBER) IS
CURSOR cur_salary IS
SELECT salary FROM employee ORDER BY salary DESC;
v_salary cur_salary%ROWTYPE;
counter NUMBER(10) := 1;
BEGIN
FOR v_salary IN cur_salary LOOP
IF counter = n THEN
DBMS_OUTPUT.PUT_LINE('The highest '||n||' salary is '||v_salary.salary);
EXIT;
END IF;
counter := counter + 1;
END LOOP;
END n_high_salary;
/


to see the 3rd highest salary just execute the procedure in a block as follows:
BEGIN
n_high_salary(3);
END;
/

CREATE OR REPLACE PROCEDURE nm_highest_sal (nm_of_salary NUMBER)
IS
v_salary number:=0;
BEGIN
SELECT salary
INTO v_salary
FROM
(SELECT salary, ROWNUM as RN
FROM (SELECT DISTINCT employees.salary
FROM employees
ORDER BY 1 DESC))
WHERE RN = nm_of_salary;
DBMS_OUTPUT.PUT_LINE(v_salary ||' is the ' || nm_of_salary ||' highest salary.');
END;
/

create or replace procedure proc_1
is
e_name varchar2(20);
salary number;
begin
select max(sal) into salary from emp22 where sal < (select max(sal) from emp22 where sal < (select max(sal) from emp22));
select ename into e_name from emp22 where sal = salary;
dbms_output.put_line( 'Third highest salary is ' || salary ||' drawn by '|| e_name );
end;
/

set serveroutput on;
execute proc_1;

create or replace procedure nth_highest_sal(which_sal number)
is
var_nth_sal employees.salary%type;
begin
select distinct salary into var_nth_sal from employees a where which_sal = (select count(distinct salary) from employees b
where b.salary >= a.salary) order by a.salary desc;
dbms_output.put_line(which_sal||'th salary of employee is '||var_nth_sal);
end;
/
begin
nth_highest_sal(3);
end;
/

Here is one solution,


create or replace procedure hi_sal(sal number:=3)
is
sal12 number;
begin
select distinct salary into sal12 from employees e1 where 3 = (select count(distinct salary) from employees e2 where e1.salary<=e2.salary);
dbms_output.put_line(sal12);
end;
/

what is the differnce between IS and AS ? Where it is used ?

Create or REPLACE PROCEDURE third_hig_Salary(sal out Number) IS
Begin
Select max(salary) INTO sal from employee
WHERE salary<(Select MAX(salary) FROM employee
where salary<(select max(salary) from employee));
DBMS_OUTPUT.put_line('Third Highest Salary is '||sal);
ENd third_hig_Salary;
/
VARIABLE sala Number;
EXECUTE third_hig_salary(:sala);

what happens next? can you show how to execute it further

I want to create a stored procedure with the capability of handling variable number of arguments. Suppose that the procedure sometimes may be- "PROCC(a,b,c)" and some other time it may be "PROCC(a,b)". How to do that? It'd be great if anyone could provide me with a demo code capable of doing so. Thanks in advance.

create or replace procedure pr_third_sal (var_third number)
is
var_salary employees.salary%type;
begin
SELECT salary
INTO var_salary
FROM employees
ORDER BY salary desc
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
dbms_output.put_line('Third Highest salaray of the employees is:'||var_salary);
END;
/

EXEC pr_third_sal(1);

you are the best tutor Manish Sharma. God bless you bro.
The question which i was thinking, you were replying.
Note: This is not only related to this videos but also others plsql videos as well.

Best PL/SQL video I've ever seen

great job!

create or replace procedure emp_third_highest_Sal(var_dept_id number)
is
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
v_employee_name EMPLOYEES.FIRST_NAME%TYPE;
v_employee_salary EMPLOYEES.SALARY%TYPE;
v_employee_dept EMPLOYEES.DEPARTMENT_ID%TYPE;
begin
select employee_id,first_name,salary,department_id into v_employee_id,v_employee_name,v_employee_salary,v_employee_dept
from (select employee_id,first_name,salary,department_id,dense_rank() over(PARTITION by department_id order by salary desc) as rank
from EMPLOYEES) where rank=3 and department_id=var_dept_id;
dbms_output.put_line(v_employee_id||' '||v_employee_name||' '||v_employee_salary||' '||v_employee_dept);

--fully working and tested code for dept wise third highest salary using window function.



end;

CREATE OR RPLACE PROCEDURE high_sal (first_high NUMBER , second_high NUMBER)
IS
BEGIN
SELECT MAX(salary) from employees where salary != first_high AND salary != second_high;
END high_sal;
/

--------------------------------------------

DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT MAX(salary) INTO a FROM employees;
SELECT MAX(salary) INTO b FROM employees WHERE salary!= a;
high_sal(a,b);
END;
/

---

Create Or Replace PROCEDURE High3sal
Is
VSAL EMP.SAL%TYPE;
Begin
Select Sal Into Vsal From Emp Order By Sal Desc
Offset 3 Rows
Fetch NEXT 1 Row Only;
DBMS_OUTPUT.PUT_LINE(VSAL);
End;
EXECUTE HIGH3SAL

---

In case you're wondering the outcome of this SP e.g:

SET SERVEROUTPUT ON;

EXECUTE emp_sal (90, 10);

SELECT * FROM EMPLOYEES;

you should see in employees table, that all employees with department_id = 90 had their salary increased 10X.

Sir please solve this error
create procedure ffirst is
var_name varchar(20) :='Ateeq';
var_reg varchar(20) :='15-SE-104';
begin
dbms_output.put_line('Hellow world my name is '||var_name ||'Registration Number is' ||var_reg);
end ffirst;
execute ffirst;

Error:
create procedure ffirst is
var_name varchar(20) :='Ateeq';
var_reg varchar(20) :='15-SE-104';
begin
dbms_output.put_line('Hellow world my name is '||var_name ||'Registration Number is' ||var_reg);
end ffirst;
execute ffirst;

VSAP INTERNET!!!!

can you give your mobile number to contact you?

create or replace procedure proc_third_high_Sal(var_rank Number)
is
var_emp_id employees.employee_id%type;
var_salary NUMBER;
Begin
select employee_id, salary into var_emp_id, var_salary from (
select employee_id,salary, dense_rank() over (order by salary desc) as sal_rank from employees
)
where sal_rank = var_rank;
dbms_output.put_line('Third Highest salaray of the employees is:'||var_salary);
End;
/

Here is another way,

CREATE OR REPLACE PROCEDURE PP IS
CURSOR THIRD IS SELECT * FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES
WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEES
WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEES)));
BEGIN
FOR I IN THIRD
LOOP
DBMS_OUTPUT.PUT_LINE('THE 3RD SALARY IS:'||I.SALARY);
END LOOP;
END;

3RD HIGEST SAL


CREATE OR REPLACE PROCEDURE THIRD_HIGH_SAL
AS
CURSOR THIRD IS
SELECT * FROM EMP A WHERE 3=
(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL <= B.SAL);
BEGIN
FOR I IN THIRD LOOP
DBMS_OUTPUT.PUT_LINE('EMP 3RD HIGEST SAL IS::'||I.SAL);
END LOOP;
END THIRD_HIGH_SAL;

Hi Manish,

Below is the assignment

create or replace procedure nth_highest (nth number) is
v_salary number;
v_sql varchar(1000);
begin
v_sql:= 'select a.salary from employees a where '||nth||' = (select count(distinct(b.salary)) from employees b where a.salary<=b.salary)';
execute immediate v_sql into v_salary;
dbms_output.put_line('the'||' '||nth||' '||'highest salary is'||':'||v_salary);
end;

I will pass either 1, 2 , 3 .... and get the output correspondingly for 1st , 2nd, 3rd...... highest salaries respectively.

Hi Manish,

Please check this for third highest salary :

create or replace procedure proc_third_high_sal
is

var_third_high EMPloyees.SALARY%type;

begin

select salary into var_third_high from (select salary, rownum as rownumber from
(select distinct salary from EMPloyees order by salary desc)) where
rownumber=3;

dbms_output.put_line('third highest sal is ' || var_third_high);

end;

thanks a lot man for tutorials :)

Manish , could you make tutorial about RMAN ?

Manish help me ..is there high scope for us. I mean that SQL or plsql skill holder ?