How to make Procedure in Oracle?

Oracle Procedure:

Procedures are a combination of SQL and PL/SQL. The difference between in procedure and function is that function should return the value but in procedure return is optional. The sample code is given below. Functions are normally used for computations where as procedures are normally used for executing business logic.

Main difference between Function and Procedures are:

In Functions:

Functions have to return a single value to the calling program.
Function can call in SQL statements.
Function cannot return Images.

In Procedures:
Procedure do not return any value except assigning values to OUT variables
Procedure cannot call procedures in SQL statements
procedure can return images

Sample Code:

SQL> CREATE TABLE  EMP
2  (
3  EMP_CODE        NUMBER,
4  EMP_NAME        VARCHAR2(100),
5  EMP_SALARY      NUMBER,
6  USER_NAME       VARCHAR2(100),
7  DATE_TIME       DATE
8  );

Table created.

SQL> INSERT INTO EMP
2  VALUES (100,’ROBERT’,50000,’ALENA’,SYSDATE);

1 row created.

SQL> CREATE OR REPLACE PROCEDURE PRU_EMP_NAME(V_EMP_CODE IN NUMBER,V_EMP_NAME OUT VARCHAR2) is
2  BEGIN
3  SELECT EMP_NAME INTO V_EMP_NAME FROM EMP WHERE EMP_CODE = V_EMP_CODE;
4  END;
5  /

Procedure created.

SQL>