CODEWITHSUNDEEP
sqloracle-databaseplsqlobject-type
Dan Armour
Dan Armour

Reputation: 11

How do procedures work with object types in PL/SQL?

I don't fully understand the purpose of procedures for PL/SQL object types. From reading online, the impression I got was that functions should not modify the data and should only return a value, instead procedures should be used, however it appears that for object types, changes in a procedure don't propagate outside the local context:

CREATE OR REPLACE TYPE TestType FORCE
AS OBJECT
(
    TestValue NUMBER

, CONSTRUCTOR FUNCTION TestType
    RETURN SELF AS RESULT

,   MEMBER PROCEDURE IncrementValueAsProcedure

,   MEMBER FUNCTION IncrementValueAsFunction
    RETURN TestType
)
/

CREATE OR REPLACE TYPE BODY TestType
AS
    CONSTRUCTOR FUNCTION TestType
    RETURN SELF AS RESULT
  IS
  BEGIN
        SELF.TestValue := 0;
        RETURN;
    END;

    MEMBER PROCEDURE IncrementValueAsProcedure
    IS
        MyType TestType := SELF;
    BEGIN
        MyType.TestValue := TestValue + 1;
    END;

    MEMBER FUNCTION IncrementValueAsFunction
    RETURN TestType
    IS
        MyType TestType := SELF;
    BEGIN
        MyType.TestValue := TestValue + 1;
        RETURN MyType;
    END;
END;
/

DECLARE
    MyTest TestType;
BEGIN
    MyTest := TestType();
    DBMS_OUTPUT.PUT_LINE(MyTest.TestValue);
    MyTest.IncrementValueAsProcedure();
    DBMS_OUTPUT.PUT_LINE(MyTest.TestValue);
    MyTest := MyTest.IncrementValueAsFunction();
    DBMS_OUTPUT.PUT_LINE(MyTest.TestValue);
END;

This gives the output:

0
0
1

So the change in the update procedure hasn't been picked up. What am I doing wrong?

Upvotes: 0

Views: 529

Answers (1)

Dan Armour
Dan Armour

Reputation: 11

Oh, worked this out.

MEMBER PROCEDURE IncrementValueAsProcedure
IS
    MyType TestType := SELF;
BEGIN
    MyType.TestValue := TestValue + 1;
END;

This creates a copy of the current object and updates it. The following works as expected:

MEMBER PROCEDURE IncrementValueAsProcedure
IS
BEGIN
    TestValue := TestValue + 1;
END;

Upvotes: 1

Related Questions