Procedures in SQL

Procedures in SQL are the small subprograms or executable named blocks that can be created and saved in the database. These procedures can be invoked and executed whenever required. It is similar to functions or methods in other programming languages such as C, C++ and Java, etc.

In the section ahead, we will discuss the procedure in-depth such as how it is defined, how it is invoked for execution and the other related things with the help of some examples.

Content: Procedures in SQL

1. Define Procedure
2. Executing and Invoking a Procedure
3. Remove a Procedure
4. Advantages of Procedures

How to Define Procedures in SQL?

A procedure in PL/SQL is a subprogram or set of SQL statements that perform a specific task when invoked by any PL/SQL program that appears within the application. Remember that procedure in PL/SQL only performs a specified task and it does not return any value like functions in PL/SQL return.

A procedure can be defined in two parts i.e. head and body. The head starts with specifying the procedure with the PROCEDURE keyword the procedure name and a list of the parameters. Well, a procedure may or may not accept the parameters.

Now the body of the procedure lies between the keywords IS and END. You can observe that the body of the procedure can be further classified into three sections:

1. The first section is the declarative section that lies within the keywords IS and BEGIN. This section contains all the local declarations that are used in the procedures.
2. The second section is the executable section that lies within the keyword BEGIN and EXCEPTION. This section contains the executable statement. Note that there must be at the minimum one executable statement in this section.
3. The third section is the error or exception handling section that lies within the keyword EXCEPTION and END. This section contains the logic to handle errors or exceptions.

CREATE: The CREATE keyword is used to create a new procedure. If the procedure name already exists then it will show an error.

REPLACE: The REPLACE keyword is used to replace the definition of an already existing procedure.

Procedure_name: This specifies the name of the declared procedure.

ARGUMENT: The arguments are the name of the parameters that the procedure will accept

IN: The procedure only reads the value of this argument when called. So, the value of this argument must be specified when the procedure is called. The parameter with IN keyword can not be overwritten by the procedure.

OUT: The procedure passes a value for this argument i.e. the procedure can overwrite the value of this argument. You may or may not specify the value of this argument when the procedure is called.

IN OUT: The value for this argument must be specified when the procedure is called. The procedure can also pass a value for this argument i.e. the procedure can overwrite the value of this argument.

DATATYPE: The DATATYPE specifies any data type supported by PL/SQL.

The syntax of declaring the procedure is:

CREATE OR REPLACE PROCEDURE procedure_name

[(argument {IN, OUT, IN OUT} data type, . . . . . . . . .)] {IS, AS}

[local variable declarations]

BEGIN

executable statements

EXCEPTION

exception handlers

END [procedure name];

So, this is how you can define or declare a procedure in PL/SQL. Let us take an example of creating a procedure to raise the salary of employees in the Company schema.

CREATE OR REPLACE PROCEDURE raise_sal

[(EID IN EMP.EMP_ID%TYPE)

IS

BEGIN

UPDATE EMP

SET SAL = SAL * 1.25 WHERE EMPID = EID;

END raise_sal;

Where to Declare the Procedure Subprogram?

Well, the developers can declare procedure inside any valid executable block of PL/SQL. The procedures must be defined as the last part of the declarative section of the PL/SQL, as the rest of the declaration must be done before the declaration of the procedure.

In most of the programming languages, we declare an identifier before we use it in the program. Similarly, in PL/SQL we practice forward declaration where we declare the identifier beforehand.

System and Object Privileges for Procedure

1. To create a procedure referring to its own schema developer must have CREATE PROCEDURE system privilege.
2. To create a procedure that will refer to other’s schema developers must have a CREATE ANY PROCEDURE system privilege.
3. To create an error-free procedure the developer must have required privileges to all the objects that are being referred by the created procedure.
4. If the privileges to the objects referenced by the procedures in revoked or withdrawn the procedure may not run.
5. To execute the procedure of the creator’s own schema he must have EXECUTE system privilege.
6. To execute the procedure of other’s schema the creator of the procedure must have EXECUTE ANY PROCEDURE.

Executing Procedures in SQL

To execute a procedure, we use EXECUTE command. The EXECUTE command is followed by the procedure name and the arguments are passed enclosed in the parenthesis preceded by the procedure name. Let’s view an example of EXECUTE command.

EXECUTE raise_sal(‘E101’);

This command will raise the salary of the employee with the ID ‘E101’.

Dropping a Procedure

As you can create a procedure you can even remove the procedure from the database. The command to remove the procedure from the database is as follow:

DROP PROCEDURE procedure_name;

The creator can drop the procedure referring to his own schema using the command above. If the creator has to drop the procedure referring other’s schema he must have DROP ANY PROCEDURE system privilege. Let us view an example of the DROP command.

DROP PROCEDURE rasie_sal;

This command will remove the procedure raise_sal from the database.

Benefits of Procedure

1. The procedure is parsed once during compile-time and is stored in executable form. So, the procedure need not be parsed during each runtime making procedure calls quick and efficient.
2. As the procedure is a set of SQL statements that can be executed with a single call. It reduces the number of calls to database and database network traffic.
3. Executing a procedure ensures either execution of all the SQL statements in the procedure or no execution at all.

So, this is all about the procedures in SQL. We have studied how the developer can define a procedure with the help of procedure syntax. We have also learned the command to execute the procedure. Further, we have discussed about how you can drop a procedure and we have concluded with the benefits of the procedure.