All for Joomla All for Webmasters

PL/SQL Procedure

The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.

The procedure contains a header and a body.

  • Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
  • Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:

  1. IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
  2. OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
  3. INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

A procedure may or may not return any value.

PL/SQL Create Procedure

Syntax for creating procedure:

  1. CREATE [OR REPLACEPROCEDURE procedure_name
  2.     [ (parameter [,parameter]) ]
  3. IS
  4.     [declaration_section]
  5. BEGIN
  6.     executable_section
  7. [EXCEPTION
  8.     exception_section]
  9. END [procedure_name];

Create procedure example

In this example, we are going to insert record in user table. So you need to create user table first.

Table creation:

  1. create table user(id number(10) primary key,name varchar2(100));

Now write the procedure code to insert record in user table.

Procedure Code:

  1. create or replace procedure “INSERTUSER”
  2. (id IN NUMBER,
  3. name IN VARCHAR2)
  4. is
  5. begin
  6. insert into user values(id,name);
  7. end;
  8. /

Output:

Procedure created.

PL/SQL program to call procedure

Let’s see the code to call above created procedure.

  1. BEGIN
  2.    insertuser(101,‘Rahul’);
  3.    dbms_output.put_line(‘record inserted successfully’);
  4. END;
  5. /

Now, see the “USER” table, you will see one record is inserted.

ID Name
101 Rahul

PL/SQL Drop Procedure

Syntax for drop procedure

  1. DROP PROCEDURE procedure_name;

Example of drop procedure

  1. DROP PROCEDURE pro1;
PinIt
submit to reddit

Leave a Reply

Top