In Oracle, you first need to declare the procedure in a package specification and then create the full procedure in the package body. Because if you will try to create procedure only in the package body without its declaration in package specification then you will get the error PLS-00302 component must be declared and ORA-06550 invalid PL/SQL block. So below I am giving an example to how to create a procedure inside a package in Oracle.
In this example, we will create a procedure update_comm to update commission in EMP table. This procedure will take the parameter for commission percentage.
Create a Procedure Inside a Package in Oracle Example
Declare The Procedure in Package Specification
CREATE OR REPLACE PACKAGE emp_pkg IS PROCEDURE update_comm (i_comm IN emp.comm%TYPE); END emp_pkg;
Create The Full Procedure in Package Body
CREATE OR REPLACE PACKAGE BODY emp_pkg IS PROCEDURE update_comm (i_comm IN emp.comm%TYPE) IS BEGIN UPDATE emp SET comm = sal * NVL (i_comm, 0) / 100; COMMIT; END update_comm; END emp_pkg;
Now you can execute this procedure as follows:
BEGIN /* Updating commission with 5% of salary */ emp_pkg.update_comm (5); END;