CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldb2
Saju
Saju

Reputation: 422

Overload stored procedure in DB2

I know, it is possible to overload PLSQL functions and procedures in ORACLE. I tried to overload a procedure in DB2 but was throwing error. Does DB2 restrict this or am I doing something wrong.

Pro 1

CREATE OR REPLACE PROCEDURE  saju_pro (
     s_num1      IN     NUMBER,
     s_num2      IN OUT     NUMBER )
AS
BEGIN
    s_num2 := (s_num1+s_num2);
END saju_pro;
/

PRO2

CREATE  PROCEDURE  saju_pro (
     s_num1      IN     NUMBER,
     s_num2      IN     NUMBER,
     s_num3      OUT     NUMBER  )
AS
BEGIN
    s_num3 := (s_num1+s_num2);
END saju_pro;

Upvotes: 0

Views: 1027

Answers (1)

mustaccio
mustaccio

Reputation: 18945

It is possible to overload procedures in DB2. There are two ways of doing this. One would be to declare and define the procedures as part of a PL/SQL package:

create or replace package body blah
as
  PROCEDURE  saju_pro (
    s_num1      IN     NUMBER,
    s_num2      IN OUT     NUMBER )
  AS
  ...
  PROCEDURE  saju_pro (
    s_num1      IN     NUMBER,
    s_num2      IN     NUMBER,
    s_num3      OUT     NUMBER  )
  AS
  ...
end;

If you really want standalone procedures, you must create them using the DB2 SQL PL syntax, not PL/SQL syntax, because only using the SQL PL syntax you can use the SPECIFIC option to let DB2 distinguish the two.

CREATE OR REPLACE PROCEDURE  saju_pro (
 IN    s_num1   NUMBER,
 INOUT s_num2   NUMBER )
SPECIFIC pro1
BEGIN
 SET s_num2 = (s_num1+s_num2);
END;
-- etc.

Upvotes: 2

Related Questions