Oracle Procedures, Functions and Packages Sample Code

In this forum Oracle Applications DBA's, System Administrators & Developers can share their knowledge/issues.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Oracle Procedures, Functions and Packages Sample Code

Post by admin »

Here you can find sample code to practice procedure, function, and packages explained in the following video in youtube,

https://youtu.be/_FdPeyjfols

Code: Select all



--select my_procedure(10,10) from dual;

exec my_package.myprocedure(10);

declare
D number;
begin
my_procedure(A => 10, B => 10);

--DBMS_OUTPUT.PUT_LINE ( 'D = ' || D );

end;

CREATE OR REPLACE PROCEDURE my_procedure (A IN NUMBER, B IN NUMBER) IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       my_procedure
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author                Description
   ---------  ----------  ---------------       ------------------------------------
   1.0        5/18/2021   Sikandar Haayat       1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     my_procedure
      Sysdate:         5/18/2021
      Date and Time:   5/18/2021, 10:50:26 PM, and 5/18/2021 10:50:26 PM
      Username:        hayatms (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   tmpVar :=  A * B;
  -- C := tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END my_procedure;



/
SQL, update, delete, case, if,


Code: Select all


select my_package.my_function(10,100) from dual;

declare
D number;
begin

d := my_function(A => 5, B => 10);

DBMS_OUTPUT.PUT_LINE ( 'D = ' || D );

end;

CREATE OR REPLACE FUNCTION my_function(A IN NUMBER, B IN NUMBER) RETURN NUMBER IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       my_function
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author                Description
   ---------  ----------  ---------------       ------------------------------------
   1.0        5/18/2021   Sikandar Haayat       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     my_function
      Sysdate:         5/18/2021
      Date and Time:   5/18/2021, 10:46:37 PM, and 5/18/2021 10:46:37 PM
      Username:        hayatms (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   tmpVar := A * B;
   RETURN tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END my_function;
/

Code: Select all

CREATE OR REPLACE PACKAGE my_package AS
/******************************************************************************
   NAME:       my_package
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        5/18/2021   Sikandar Haayat   1. Created this package.
******************************************************************************/

  FUNCTION my_function(A IN NUMBER, B IN NUMBER) RETURN NUMBER;
  
  PROCEDURE MyProcedure(Param1 IN NUMBER);

END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package AS
/******************************************************************************
   NAME:       my_package
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        5/18/2021   Sikandar Haayat   1. Created this package body.
******************************************************************************/

  FUNCTION my_function(A IN NUMBER, B IN NUMBER) RETURN NUMBER IS
  tmpVar number;
BEGIN
   tmpVar := A * B;
   RETURN tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END;

  PROCEDURE MyProcedure(Param1 IN NUMBER) IS
    tmpVar NUMBER;
BEGIN
   tmpVar :=  Param1 * 10;
--   INSERT
--   UPDATE
--   DELETE
--   IF
--   CASE
--   LOOPS
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END;

END my_package;
/
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests