How to chain calls in a pl/sql object type of func

问题:

I want to make an oracle object return itself and be able to chain these calls. How do I do that?

I have tried returning the same type, but it doesnt work, I also tried adding a procedure that is invoqued by the funcition but it doesn't work either. Always complains about modifying the value of the width member. Looks like functions wont admit side effects?, are they modelled after a more mathematical function principle? Is this achievable?. I guess I could write the function so it builds a new rectangle with SELF, but that is so much work.

My goal is to be able to chain calls like jQuery or some java classes (a singleton?). Something like:

r := r.setWidth(0).setWidth(1).setWidth(2);

Of course, it would have more methods and it wouldn't be a rectangle. This is the error:

Error: PLS-00363: expression 'SELF' cannot be used as an assignment target
Line: 18
Text: stWidth(w);

-

CREATE OR REPLACE TYPE rectangle AS OBJECT
(
-- The type has 3 attributes.
  length NUMBER,
  width NUMBER,
  area NUMBER,
-- Define a constructor that has only 2 parameters.
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT,
  MEMBER FUNCTION setWidth(w NUMBER) RETURN rectangle,
  MEMBER PROCEDURE stWidth(w NUMBER)
)

-

CREATE OR REPLACE TYPE BODY rectangle AS
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.length := length;
    SELF.width := width;
-- We compute the area rather than accepting it as a parameter.
    SELF.area := length * width;
    RETURN;
  END;
  MEMBER PROCEDURE stWidth(w NUMBER) IS
  BEGIN
    self.width := w;
  END;
  MEMBER FUNCTION setWidth(w NUMBER) RETURN rectangle IS
    BEGIN
        stWidth(w);

        RETURN SELF;
  END;
END;

Thanks in advance.

回答1:

You cannot both change the object and assign to it at the same time. You already know the solution, "build a new rectangle with SELF". But it won't be a lot of work.

Replace this:

  MEMBER FUNCTION setWidth(w NUMBER) RETURN rectangle IS
    BEGIN
        stWidth(w);
        RETURN SELF;
  END;

with this:

  MEMBER FUNCTION setWidth(w NUMBER) RETURN rectangle IS
      v_rectangle rectangle := self;
    BEGIN
        v_rectangle.width := w;
        RETURN v_rectangle;
  END;

You were actually getting a compilation error. By default, SELF is an IN parameter. The call to stWidth failed because it was modifying an IN parameter with self.width := w;.

See: http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm#CHDCFEEE

SELF is always the first parameter passed to the method.

• In member functions, if SELF is not declared, its parameter mode defaults to IN.

• In member procedures, if SELF is not declared, its parameter mode defaults to IN OUT. The default behavior does not include the NOCOPY compiler hint.

回答2:

Sorry, I am a bit late, but the other answers are incorrect, at least using Oracle 11gR2, and what you are trying to achieve is entirely possible indeed.

I just have recently stumbled upon the problem and I see one can definitively return a reference to SELF exactly as you were trying to, without yielding to any trade-off or applying workarounds.

The only thing one needs is to redefine the method by (explicitly) setting the (implicit) SELF parameter as SELF IN OUT rectangle.
The SELF is the leading parameter that is silently passed to every object method, and for functions is defined as IN (immutable; this is what presumably makes the compiler complain). This is sort of established at compile time, but the good part is that when invoking the method you can omit it.
In the example at the end of the post (slightly rewritten from yours), we define a

MEMBER FUNCTION incrementWidth(SELF IN OUT rectangle, w NUMBER) RETURN rectangle

and we execute it omitting the SELF reference:

declare
  r rectangle := rectangle(1,2);
begin

  dbms_output.put_line('width is: ' || r.width);
  dbms_output.put_line('new width is: ' || r.incrementWidth(3).width);

end;
/

Note that there are two warnings to be aware of.

warning 1

Every method call will temporarily create a fresh copy of the object.
But just temporarily, the new instance is short lived, just between the start and the end of the method. This is inherent to using IN OUT parameters on all functions or procedures, and is not specific to object types. If you want to prevent this behaviour, you may want to redefine the signature of your function using the NOCOPY hint:

MEMBER FUNCTION incrementWidth(SELF IN OUT NOCOPY rectangle, w NUMBER) RETURN rectangle

See ORACLE-BASE - NOCOPY for more details. Note it is a hint however, that doesn't assure you are finally using the same object reference and not a newly created object, so use with caution.

warning 2

Given you have raised this question, chances are that you have a OOP background, and you may get a surprise when trying to invoke the method without using the returned reference like this

r.incrementWidth(10);

The compiler will return an error:

PLS-00221: 'INCREMENTWIDTH' is not a procedure or is undefined

So what is happening here? Well, the so called "static polymorfism" (i.e. the selection of the method overload during compilation) in pl/sql is slightly different than in other OOP languages, because it takes into account even the usage of the RETURNed type. To solve this, add a companion procedure with a signature whose difference is just in the lack of a returned type:

MEMBER FUNCTION incrementWidth(SELF IN OUT rectangle, w NUMBER) RETURN rectangle,
MEMBER PROCEDURE incrementWidth(SELF IN OUT rectangle, w NUMBER)

Reasonably, if you don't want to duplicate the same code in the function and the procedure, the procedure will internally delegate the function; and depending on the version of Oracle you are using you may want to play with code inlining (see OCP: More New PL/SQL Features) to achieve the same speed as a copy-pasted implementation (hardly you'll notice a real difference). Explicit "inlining" points at a method by name, however it works also in this case where the method name is overloaded.

In the example below you will see that either the function or procedure is alternatively invoked depending on the usage of the returned/not_returned parameter.

so finally...

The code one may want to write is the following (I have not used the NOCOPY not to pollute the relevant stuff, but it is straightforward to do)

CREATE OR REPLACE TYPE rectangle AS OBJECT
(
  length NUMBER,
  width NUMBER,
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT,
  MEMBER FUNCTION incrementWidth(SELF IN OUT rectangle, w NUMBER) RETURN rectangle,
  MEMBER PROCEDURE incrementWidth(SELF IN OUT rectangle, w NUMBER)
);
/

CREATE OR REPLACE TYPE BODY rectangle AS
  CONSTRUCTOR FUNCTION rectangle(length NUMBER, width NUMBER)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.length := length;
    SELF.width := width;
    RETURN;
  END;
  MEMBER FUNCTION incrementWidth(SELF IN OUT rectangle, w NUMBER) RETURN rectangle IS
    BEGIN
        dbms_output.put_line('...invoking the function with input ' || w);
        width := width + w;
        RETURN SELF;
  END;
  MEMBER PROCEDURE incrementWidth(SELF IN OUT rectangle, w NUMBER) IS
    BEGIN
        PRAGMA INLINE (incrementWidth, 'YES');
        dbms_output.put_line('...invoking the procedure with input ' || w || ', that in turn is...');
        self := incrementWidth(w);
  END;
END;
/

Upon execution of...

set serveroutput on
select * from v$version where rownum = 1;

declare
  r rectangle := rectangle(1,2);
begin

  dbms_output.put_line('width is: ' || r.width);

  --this is invoking the "function" version, because we are making use of
  --the returned rectangle object
  dbms_output.put_line('new width is: ' || r.incrementWidth(3).width);

  --the original reference has been updated even without using the NO COPY hint
  dbms_output.put_line('original object has width updated: ' || r.width);

  --this is invoking the "procedure" version, because we are not using the returned object
  r.incrementWidth(3);

  --of course this has finally worked as well
  dbms_output.put_line('again what is the new width like now?: ' || r.width);

end;

/

you get

BANNER                                                                     
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  

width is: 2
...invoking the function with input 3
new width is: 5
original object has width updated: 5
...invoking the procedure with input 3, that in turn is...
...invoking the function with input 3
again what is the new width like now?: 8

回答3:

You can't return SELF from a member function. You can create a copy, but I'm not sure why a setwidth function would return a rectangle object. I know this basic example probably came from some old Oracle docs, but I would not put a computed field (area) as an attribute. If its computed, it should be a member function (or procedure). Reason is that unless you want to always remember to update area in functions that affect area, you'll shoot yourself in the foot. Your example is broken (aside from the setWidth returning self), since only the constructor calculates area.

set serveroutput on
declare
  r rectangle := rectangle(3,2);
begin
  dbms_output.put_line('Area is: ' || r.area);
  -- change the width
  r.stWidth(4);
  dbms_output.put_line('Area is: ' || r.area);
end;

Output:

Area is: 6
Area is: 6

Obviously this is incorrect. I would do something like:

CREATE OR REPLACE TYPE rectangle AS OBJECT
(
-- The type has 2 attributes.
  len NUMBER,
  width NUMBER,
-- Define a constructor that has only 2 parameters.
  CONSTRUCTOR FUNCTION rectangle(len NUMBER, width NUMBER)
    RETURN SELF AS RESULT,
  MEMBER PROCEDURE setLength(l NUMBER),
  MEMBER PROCEDURE setWidth(w NUMBER),
  MEMBER FUNCTION getArea return NUMBER,
  MEMBER PROCEDURE showArea
);


CREATE OR REPLACE TYPE BODY rectangle AS
  CONSTRUCTOR FUNCTION rectangle(len NUMBER, width NUMBER)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.len := len;
    SELF.width := width;
    RETURN;
  END;

  MEMBER PROCEDURE setLength(l NUMBER) IS
  BEGIN
    self.len := l;

  END;
  MEMBER PROCEDURE setWidth(w NUMBER) IS
  BEGIN
    self.width := w;

  END;

  MEMBER FUNCTION getArea return NUMBER IS
  BEGIN
    return self.len * self.width;
  END;

  MEMBER PROCEDURE showArea IS
  BEGIN
    -- Just shows how we calculated area and spits to console
    dbms_output.put_line('Area is: ' || self.getArea || ' (' || self.len || ' * ' || self.width || ')');
  END;
END;

And this way you'd have:

set serveroutput on
declare
  r rectangle := rectangle(3,2);
begin
  dbms_output.put_line('Area is: ' || r.getArea);
  -- change the width
  r.setWidth(4);
  dbms_output.put_line('Area is: ' || r.getArea);
end;

Output:

Area is: 6
Area is: 12

回答4:

I have run into the same problem, and since the top answers disagree, I had to experiment myself.

I have the following code that won't work despite what Antonio's answer says:

create or replace type tst as object (
  inner_value varchar2(100),

  constructor function tst return self as result,

  member function update_value(
    self in out nocopy tst, 
    p_value varchar2)
  return tst,

  member procedure print_value(self in out nocopy tst)

) final;
/

create or replace type body tst as
  constructor function tst return self as result
  is
  begin
    self.inner_value := 'DEFAULT';
    return;
  end;

  member function update_value(
    self in out nocopy tst, 
    p_value varchar2)
  return tst
  is
  begin
    self.inner_value := p_value;
    return self;
  end;

  member procedure print_value(self in out nocopy tst)
  is
  begin
    dbms_output.put_line(self.inner_value);
  end;

end;
/

The following code will not run no matter whether you the self parameter is defined with nocopy or not.

set serveroutput on;
begin
  tst().update_value('TEST').print_value;
end;
/

If print_value is redefined to be member procedure print_value(self in tst), then tst().print_value; becomes a valid statement.

The constructor scannot be chained if the self parameter is in out or in out nocopy, no matter if you return a copy or self, you'll always get a PLS-00363. The only way to run nested calls off a constructor is to use self in parameters everywhere and make a copy, which is disappointing:

create or replace type tst as object (
  inner_value varchar2(100),

  constructor function tst return self as result,

  member function update_value(
    self in tst, 
    p_value varchar2)
  return tst,

  member procedure print_value(self in tst)

) final;
/

create or replace type body tst as
  constructor function tst return self as result
  is
  begin
    self.inner_value := 'DEFAULT';
    return;
  end;

  member function update_value(
    self in tst, 
    p_value varchar2)
  return tst
  is
    a_copy tst;
  begin
    a_copy := tst;
    a_copy.inner_value := p_value;
    return a_copy;
  end;

  member procedure print_value(self in tst)
  is
  begin
    dbms_output.put_line(self.inner_value);
  end;

end;
/

Then this prints TEST as expected:

set serveroutput on;
begin
  tst().update_value('TEST').print_value;
end;
/

If you can split the statement into t := tst(); t.update_value('TEST').print_value;, then it works even with in out nocopy self parameters.