Procedure in Oracle

Procedure in pl/sq:-

               

Scenario:-

Suppose in an organization, you have a database and that database is accessed by many users. All the users are required to find out the average salary of all the employees. Hence for this purpose user will create anonymous block and execute it. This will downgrade the performance of the database if the table is having millions of records. Other major thing is when they exit connection to the database and if they want to again calculate the average salary then they will have to again create an anonymous block and execute it. But, if you have a named pl/sql block, it is stored in the database and no need to recreate the pl/sql block also multiple users can use it at the same time. You can achieve this by creating stored procedure.

What is stored procedure?

Pl/sql procedure is a precompiled object which store as an object in a database which may or may not return any value.

A stored procedure or a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists of declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

·         A procedure outside the Package is called Stand alone procedure.

·         
To create procedure you must have create procedure privileges.

Syntax of the Procedure:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] 

IS    

   Declaration section 

BEGIN    

   Execution section 

EXCEPTION    

  Exception section 

END; 

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure. The value of the parameter cannot be overwritten by the procedure.
2. OUT - The parameter cannot be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
3. IN OUT - The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

Let’s try to create simple HelloWorld procedure:-

CREATE PROCEDURE HELLOWORLD AS

AS

BEGIN

DBMS_OUTPUT.PUT_LINE (‘HELLO WORLD’);

END;

Now let’s try to execute the procedure HelloWorld:-

Now, let’s try to create parameterized procedure and execute it:-

CREATE OR REPLACE PROCEDURE ADD_TWO_NUMBERS 
(NUM_1 NUMBER, NUM_2 NUMBER)

AS

V_TOTAL NUMBER;

BEGIN

V_TOTAL:= NUM_1+NUM_2;

DBMS_OUTPUT.PUT_LINE('THE TOTAL OF '||NUM_1 ||' AND '|| NUM_2 ||' IS ' ||V_TOTAL||'.');

END;

Suppose you have a EMPLOYEE table and you want to insert values in it by using procedure. You execute the below query:-

Query EMPLOYEE table first:-

Currently we don’t have any records in EMPLOYEE table. Now create a procedure which will insert a record in the EMPLOYEE table.

CREATE OR REPLACE PROCEDURE EMP_INSERT

(P_EMPNO EMPLOYEE.EMPNO%TYPE, P_EMPNAME EMPLOYEE.EMPNAME%TYPE, P_SALARY EMPLOYEE.SALARY%TYPE)

AS

BEGIN

INSERT INTO EMPLOYEE (EMPNO,EMPNAME,SALARY)

VALUES (P_EMPNO,P_EMPNAME,P_SALARY);

COMMIT;

END;

Above there are 2 records inserted.

We have seen above how to create simple procedure and parameterized procedure. Let’s see how to drop procedure:-

Syntax is:-

DROP PROCEDURE PROCEDURE_NAME;

 

 

 

Creating Function