Stored procedure oracle - oracle tutorial - sql tutorial



What is procedure in Oracle ?

  • 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.

Oracle Procedure Query Execution

Oracle Procedure Query Execution

Create Procedure

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];
click below button to copy the code. By - oracle tutorial - team
  • 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));
click below button to copy the code. By - oracle tutorial - team
  • Now write the procedure code to insert record in user table.

Procedure Code:

 create or replace procedure "INSERTUSER"  
(id IN NUMBER,  
name IN VARCHAR2)  
is  
begin  
insert into user values(id,name);  
end;  
/     
click below button to copy the code. By - oracle tutorial - team

Output:

Procedure created.

Oracle program to call procedure

  • Let's see the code to call above created procedure.
BEGIN  
   insertuser(101,'Rahul');
   dbms_output.put_line('record inserted successfully');  
END;  
/  
click below button to copy the code. By - oracle tutorial - team
  • Now, see the "USER" table, you will see one record is inserted.
ID Name
101 Smith

Oracle Drop Procedure

Syntax

DROP PROCEDURE procedure_name;
click below button to copy the code. By - oracle tutorial - team

Example to drop procedure

DROP PROCEDURE pro1;
click below button to copy the code. By - oracle tutorial - team

Oracle Stored Procedures Example

Oracle Stored Procedures Example

Stored Procedure Vs Functions

Stored Procedure Vs Functions

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Stored procedure oracle