Create or Drop Procedure in Oracle PL/SQL

Procedure is set of statements which execute together to give desired result.
You can pass arguments as IN or OUT or INOUT form. It does not return value.
IN is used in pass value in procedure, OUT is used for take out value, IN OUT is used for both.
Can be execute with EXEC statement.

Permission required:

--Create procedure privileges required
CREATE PROCEDURE privilieges for own schema.
CREATE ANY PROCEDURE privilieges for anywhere.

-- For execution procedure privileges required
EXECUTE privileges to run an existing procedure.
GRANT EXECUTE on prcedure_name to username;

Syntax of Creating Procedure:

CREATE OR REPLACE PROCEDURE <procedure_name>
( <parameterl IN/OUT <datatype>, parameter2 IN/OUT <datatype> ...)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END; 

Example of Create Procedure

-- Create the Procedure
CREATE OR REPLACE PROCEDURE Message_display (p_name IN VARCHAR2) 
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello '|| p_name);
END;
/
-- Execute the procedure
EXEC Message_display(p_name => 'Sunny');

Example of Create Procedure using SQL statements:

-- For create the Procedure 
CREATE OR REPLACE PROCEDURE Pn(v_employee_id number, v_salary number) as 
BEGIN
    SELECT salary to v_salary FROM employees WHERE employee_id = v_employee_id;
	v_salary := v_salary * 1.1;
	UPDATE employees SET salary = v_salary FROM employees WHERE employee_id = v_employee_id;
END;
/

--For Execute the procedure
EXEC Pn(101,10000);

Drop the procedure:

DROP PROCEDURE procedurename;