Skip to content
Advertisements

DBMS/U3 Topic 9 Oracle- Procedures

A procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL.

Create Procedure

Syntax

CREATE[OR REPLACE] PROCEDURE procedure_name  

[ (parameter [,parameter]) ]  

IS

[declaration_section]  

BEGIN

executable_section  

[EXCEPTION  

exception_section]  

END[procedure_name];  

Following are the three types of procedures that must be defined to create a procedure.

  • IN: It is a default parameter. It passes the value to the subprogram.
  • OUT: It must be specified. It returns a value to the caller.
  • IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.

Oracle Create procedure example

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

Table creation:

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

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

Procedure Code:

createor replace procedure “INSERT USER”    

(id IN NUMBER,    

name IN VARCHAR2)    

is

begin

insert into user values(id,name);    

end;    

/       

Output:

Procedure created.

Oracle program to call procedure

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

BEGIN

insert user(101,’Rahul’);  

put_line(‘record inserted successfully’);    

END;    

/    

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

ID Name
101 Rahul

Oracle Drop Procedure

Syntax

DROP PROCEDURE procedure_name;

Example to drop procedure

DROP PROCEDURE pro1;

Advertisements
Advertisements
Advertisements
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: