CODEWITHSUNDEEP
sqloracle-databasemethodsreturn-valueuser-defined-types
user2381256
user2381256

Reputation: 101

SQL Method to retrieve a value

In SQL, how can I create a simple method to retrieve a value from an object?

Here is the code:

create type CurrentUser_objtyp as Object (
CurrUserName varchar2(20),
CurrUserPassword varchar2(20),
CurrUserType varchar2(15)
)
/

How can I retrieve the value of CurrUserName from a method?

Upvotes: 0

Views: 160

Answers (1)

APC
APC

Reputation: 146209

As Oracle SQL types only allow public attributes there is no point in creating getter and setter methods for them.

But the general principle is to create member methods: 

create type CurrentUser_objtyp as Object (
CurrUserName varchar2(20),
CurrUserPassword varchar2(20),
CurrUserType varchar2(15),
member function getname return varchar2
)
/

You need to write a body for the implementation, which is basically PL/SQL:

create type body CurrentUser_objtyp as 

    member function getname return varchar2
    is
    begin
         return self.CurrUserName;
    end;
end;
/

"How do I call the member function in your above code?"

To call a member function (or procedure) we first have to instantiate the object. There are various different ways of using objects, but again here is the simplest:

declare
    currUser CurrentUser_objtyp := new CurrentUser_objtyp('MR KNOX', 'PASSWORD1', 'MANAGER');
begin
    dbms_output.put_line('user name is '||currUser.getname);
end;
/

Oracle have devoted an entire manual to their Object-Relational constructs. You should read it to find out more.

Upvotes: 1

Related Questions