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

Manish from RebellionRider.com presents PL/SQL Tutorial 42 on How To Create PL/SQL Stored Procedure With Parameters In Oracle Database
————————————————————————
►►►LINKS◄◄◄
Blog : http://bit.ly/create-procedure-2
Previous Tutorial
► PL/SQL Tutorial 40: https://youtu.be/buaSuEMi4lw
► PL/SQL Tutorial 41: https://youtu.be/Uld8eE016Kg

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

https://twitter.com/rebellionrider
http://instagram.com/rebellionrider
https://plus.google.com/+Rebellionrider
http://in.linkedin.com/in/mannbhardwaj/

___Facebook Official Page___

https://www.facebook.com/RebellionRider.official/

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

This is Manish from RebellionRider.com
♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

Comments

Samnang says:

Manish , could you make tutorial about RMAN ?

Ashef Shahrior says:

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.

Manish Chauhan says:

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

Nilutpal Dutta says:

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

Subha Jena says:

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;

raj raj says:

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.

yash kumar says:

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 Kumar says:

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

coderz says:

can you give your mobile number to contact you?

Sathiya seelan says:

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;

anupam kumar tejaswi says:

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;

Mohammad Faraz says:

Packages lectures

david banner says:

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.

Jeff Xi says:

great job!

Stjepan Mudronja says:

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

Міша Чолій says:

VSAP INTERNET!!!!

Vivek Khurpe says:

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

Jeff Xi says:

Best PL/SQL video I’ve ever seen

Bharathi Panamala says:

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

Ateeq Rehman says:

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;

Write a comment

*

Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.


Do you like our videos?
Do you want to see more like that?

Please click below to support us on Facebook!

Send this to a friend

▷ Other ReviewsVehicles▷ Show Cars▷ Motorbikes▷ Scooters▷ Bicycles▷ Rims & Tires▷ Luxury BoatsFashion▷ Sunglasses▷ Luxury Watches▷ Luxury Purses▷ Jeans Wear▷ High Heels▷ Kinis Swimwear▷ Perfumes▷ Jewellery▷ Cosmetics▷ Shaving Helpers▷ Fashion HatsFooding▷ Chef Club▷ Fooding Helpers▷ Coktails & LiquorsSports▷ Sport Shoes▷ Fitness & Detox▷ Golf Gear▷ Racquets▷ Hiking & Trek Gear▷ Diving Equipment▷ Ski Gear▷ Snowboards▷ Surf Boards▷ Rollers & SkatesEntertainment▷ DIY Guides▷ Zik Instruments▷ Published Books▷ Music Albums▷ Cine Movies▷ Trading Helpers▷ Make Money▷ Fishing Equipment▷ Paintball Supplies▷ Trading Card Games▷ Telescopes▷ Knives▷ VapesHigh Tech▷ Flat Screens▷ Tech Devices▷ Camera Lenses▷ Audio HiFi▷ Printers▷ USB Devices▷ PC Hardware▷ Network Gear▷ Cloud Servers▷ Software Helpers▷ Programmer Helpers▷ Mobile Apps▷ Hearing AidsHome▷ Home Furniture▷ Home Appliances▷ Tools Workshop▷ Beddings▷ Floor Layings▷ Barbecues▷ Aquarium Gear▷ Safe Boxes▷ Office Supplies▷ Security Locks▷ Cleaning ProductsKids▷ Baby Strollers▷ Child Car Seats▷ Remote ControlledTravel▷ Luggages & Bags▷ Airlines Seats▷ Hotel Rooms▷ Fun Trips▷ Cruise Ships▷ Mexico Tours