PROCEDURE

A procedure is nothing but a PL/SQL wrapped up  with in a name to save the PL/SQL in the database.

What is the difference between a PL/SQL block and a Procedure?
When you execute a PL/SQL block the RDBMS check the syntax, parses the query and creates the execution plan and then executes the PL/SQL block, where as if we create a stored procedure in the database while saving the stored procedure it checks the syntax, parses the queries and saves all the information in the database so that when we execute the stored procedure it wont do all that stuff again instead it executes the stored procedure using the existing information.

Syntax
CREATE OR REPLACE PROCEDURE procedure_name
( argument1 in/out data type, argument2 in/out data type....)
AS
PL/SQL Block
End Procedure_name

IN argument
OUT argument
IN OUT argument

IN - pass the value from calling environment into the procedure.
OUT - return a value from the procedure to the calling program.
IN OUT - pass the value from calling program and the called program passes some other calculated value through the same variable to the calling program.

Following diagram explains the difference between IN and IN OUT arguments passing to a stored procedure or stored function.

From where we call stored procedures
We can call a stored procedure from a Pl/SQL block, another stored procedure, function or a trigger.

---

Write a stored procedure when you sell a product, check if the qty_on_hand is equal to reorder level or less than reorder level, if so insert a row into the orders table. If you already placed the order with in last 2 days then do not place an order on that product.

CREATE OR REPLACE PROCEDURE
check_update_reorder ( prod_id in number, curr_qty in number ) is
v_reorder_level product.reorder_level%type;
begin
select reorder_level into v_reorder_level from products where product_id = prod_id;
If curr_qty <= v_reorder_level then
begin
select 1 from orders where product_id = prod_id and order_date between trunc(sysdate) - 2 and trunc(sysdate);
exception
when no_data_found then
insert into orders ( order_id, product_id, order_date ) values ( order_seq.nextval, prod_id, sysdate );
end;
End If;
end check_update_level;