Stored Procedure in pl/sql in oracle


Stored Procedure with SELECT example

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 )
 )

A stored procedure, uses SELECT INTO mechanism to assign the matched values to OUT parameters.
CREATE OR REPLACE PROCEDURE getDBUSERByUserId (
         p_userid IN DBUSER.USER_ID%TYPE,
         o_username OUT DBUSER.USERNAME%TYPE,
         o_createdby OUT  DBUSER.CREATED_BY%TYPE,
         o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  from  DBUSER WHERE USER_ID = p_userid;
 
END;
/

Calls from PL/SQL
Call from PL/SQL like this :
DECLARE
   o_username DBUSER.USERNAME%TYPE;
   o_createdby DBUSER.CREATED_BY%TYPE;
   o_date DBUSER.CREATED_DATE%TYPE;
BEGIN

   getDBUSERByUserId(1001,o_username,o_createdby,o_date);
  
   DBMS_OUTPUT.PUT_LINE('username :  ' || o_username);
   DBMS_OUTPUT.PUT_LINE('createdby :  ' || o_createdby);
   DBMS_OUTPUT.PUT_LINE('createddate :  ' || o_date);
  
END;
/

Oracle Stored Procedure Cursor example

2. Stored Procedure

A stored procedure, return the record as cursor type (SYS_REFCURSOR) base on the matched username.
CREATE OR REPLACE PROCEDURE getDBUSERCursor(
         p_username IN DBUSER.USERNAME%TYPE,
         c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN
 
  OPEN c_dbuser FOR
  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';
 
END;
/
Get the returned ref cursor and navigate the records like this :
DECLARE 
  c_dbuser SYS_REFCURSOR;
  temp_dbuser DBUSER%ROWTYPE;
BEGIN
 
  --records are assign to cursor 'c_dbuser'
  getDBUSERCursor('mkyong',c_dbuser);
 
  LOOP
 
        --fetch cursor 'c_dbuser' into dbuser table type 'temp_dbuser'
      FETCH c_dbuser INTO temp_dbuser;
 
        --exit if no more records
        EXIT WHEN c_dbuser%NOTFOUND;
 
        --print the matched username
        dbms_output.put_line(temp_dbuser.username);
 
  END LOOP;
 
  CLOSE c_dbuser;
 
END;
/
Result
The records of username like ‘mkyong%’ are returned as cursor via
getDBUSERCursor store procedure.

Oracle Stored Procedure DELETE example

Stored Procedure

A stored procedure, delete the record base on the provided userId.
CREATE OR REPLACE PROCEDURE deleteDBUSER(p_userid IN DBUSER.USER_ID%TYPE)
IS
BEGIN
 
  DELETE DBUSER where USER_ID = p_userid;
  
  COMMIT;
 
END;
/

Calls from PL/SQL

Call from PL/SQL like this :
BEGIN
   deleteDBUSER(1001);
END;
Copy
Result
Record of userid=1001 is deleted via
deleteDBUSER store procedure.

Oracle Stored Procedure UPDATE example

A stored procedure, accept 2 IN parameters and update the username field based on the provided userId.
CREATE OR REPLACE PROCEDURE updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN
 
  UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;
  
  COMMIT;
 
END;
/

Calls from PL/SQL

Call from PL/SQL like this :
BEGIN
   updateDBUSER(1001,'new_mkyong');
END;
Copy
Result
The username, which has a userid of 1001 is updated via updateDBUSER store procedure.

No comments:

Post a Comment

Popular Posts

Total Pageviews

Followers