Oracle Stored Procedure INSERT example

By mkyong | Updated: August 29, 2012

Viewed: 13,042 (+53 pv/w)

Tags: insert oracle store procedure

Here’s an INSERT stored procedure example in Oracle database.

1. Table SQL Script

DBUSER table creation script.

CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Stored Procedure

A stored procedure, accept 4 IN parameters and insert it into table “DBUSER”.

CREATE OR REPLACE PROCEDURE insertDBUSER(
	   p_userid IN DBUSER.USER_ID%TYPE,
	   p_username IN DBUSER.USERNAME%TYPE,
	   p_createdby IN DBUSER.CREATED_BY%TYPE,
	   p_date IN DBUSER.CREATED_DATE%TYPE)
IS
BEGIN

  INSERT INTO DBUSER ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_createdby, p_date);

  COMMIT;

END;
/

3. Calls from PL/SQL

Call from PL/SQL like this :

BEGIN
   insertDBUSER(1001,'mkyong','system',SYSDATE);
END;

Result
A record is inserted into DBUSER table via insertDBUSER store procedure.