How to create a stored procedure in oracle

In Oracle, you can use the "CREATE PROCEDURE" statement to create a stored procedure. The basic syntax is "CREATE [OR REPLACE] PROCEDURE process name [parameter list] IS [...] BEGIN ... END [process name];".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

What is a stored procedure?

The so-called stored procedure (Stored Procedure) is a set of SQL statements used to complete specific database functions. This SQL statement set is compiled and stored in the database system.

When using it, the user calls and executes it by specifying the defined stored procedure name and giving the corresponding stored procedure parameters to complete one or a series of database operations.

oracle creates a stored procedure

Syntax

CREATE [OR REPLACE] PROCEDURE 过程名 [ (参数 [,参数]) ]  
IS  
    [declaration_section]  
BEGIN  
    executable_section  
[EXCEPTION  
    exception_section]  
END [过程名];

The following are three types of statements that must be defined to create a process.

• IN: This is a default parameter that passes the value to the subroutine.

• OUT: Must be specified, it returns a value to the caller.

• IN OUT: Must be specified, which passes the initial value to the subroutine and returns the updated value to the caller.

Oracle creation process example

In this example, records will be inserted into the user table, so the user table needs to be created first.

User table creation statement:

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

Now write the program code to insert records in the user table.

create or replace procedure "INSERTUSER"    
(id IN NUMBER,    
name IN VARCHAR2)    
is    
begin    
    insert into user values(id,name);    
end;    
/

Execute the above code and get the following results -

Procedure created.

Oracle program calling process

Let us take a look at how to call the process created above. Refer to the following sample code -

BEGIN    
   insertuser(101,'Maxsu');  
   dbms_output.put_line('record inserted successfully');    
END;    
/

Now, look at the records in the USER table and you will see that a record has been inserted above.

ID        Name
---------------------------
101        Maxsu

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to create a stored procedure in oracle. For more information, please follow other related articles on the PHP Chinese website!