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

------------------------------------------------------------------------
►►►LINKS◄◄◄
Previous Tutorial

-------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
-------------------------------------------------------------------------
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on

___Facebook Official Page___

You can also Email me at
for E-mail address please check the About section

Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos

♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

[ramasettidivyakranth]

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;

[yashkumar]

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.

[vinod]

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 :)

[prabhatshrama]

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;
/

[karishmapawar]

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

[aravinthvs]

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

[tanmaykumbhar]

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

[ashefshahrior]

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.

[subhashisoracle]

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 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

[rajraj-rvii]

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, highest salaries respectively.

[kalyanisarode]

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

[samnang]

Manish, could you make tutorial about RMAN ?

[manishchauhan]

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

[bharathipanamala]

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;
/

[faizanansari]

How to create same with the select query?

[debashisbehera]

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

[satyabratakar]

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);

[Srikrishnasundar]

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

[neerajjoshi]

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

[gurukiran]

Select Sal from emp where row num = 3