ANYDATA

ANYDATA 타입의 기본 개념과 메소드에 대하여 기술합니다.

개요

ANYDATA 타입은 임의의 타입을 담을 수 있는 타입입니다.

메소드

CONVERT

임의의 타입을 입력으로 받아, ANYDATA 타입의 인스턴스를 생성합니다.

CONVERT 메소드 시리즈의 세부 내용은 다음과 같습니다.

• 프로토타입

STATIC FUNCTION ConvertNumber(num IN NUMBER)
       return AnyData;
STATIC FUNCTION ConvertDate(dat IN DATE)
       return AnyData;
STATIC FUNCTION ConvertChar(c IN CHAR)
       return AnyData;
STATIC FUNCTION ConvertVarchar(c IN VARCHAR)
       return AnyData;
STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2)
       return AnyData;
STATIC FUNCTION ConvertRaw(r IN RAW)
       return AnyData;
STATIC FUNCTION ConvertBlob(b IN BLOB)
       return AnyData;
STATIC FUNCTION ConvertClob(c IN CLOB)
       return AnyData;
STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP_UNCONSTRAINED)
       return AnyData;
STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP_TZ_UNCONSTRAINED)
       return AnyData;
STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP_LTZ_UNCONSTRAINED)
       return AnyData;
STATIC FUNCTION ConvertIntervalYM(inv IN YMINTERVAL_UNCONSTRAINED)
       return AnyData;
STATIC FUNCTION ConvertIntervalDS(inv IN DSINTERVAL_UNCONSTRAINED)
       return AnyData;
STATIC FUNCTION ConvertNchar(nc IN NCHAR)
       return AnyData;
STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2)
       return AnyData;
STATIC FUNCTION ConvertNClob(nc IN NCLOB)
       return AnyData;
STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT)
       return AnyData;
STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE)
       return AnyData;
STATIC FUNCTION ConvertObject(obj IN "<object>")
       return AnyData;
STATIC FUNCTION ConvertCollection(col IN "<collection>")
       return AnyData;

• 파라미터

파라미터	설명
num, da, c, r, b, ts, inv, nc, fl, dbl, obj, col	ANYDATA 타입으로 변환할 임의의 타입의 입력값 obj는 사용자가 정의한 임의의 object 타입이 가능 col은 ddl로 생성한 전역 콜렉션 타입만 가능

• 예제

declare
    var sys.anydata;
    nvar number := 10;
begin
    var := sys.anydata.convertNumber(nvar);
end;
/


create or replace type SIMPLE_OBJ is object ( c1 number, c2 number);
/

declare
     v1 SIMPLE_OBJ;
     result sys.anydata;
begin
     v1 := SIMPLE_OBJ (10, 20);
     
     result := sys.anydata.convertobject(v1);
end;
/

create or replace type SIMPLE_COLL as table of varchar(128);
/

declare
   any1 sys.anydata;
   any2 sys.anydata;
   
   type LOCAL_COLL is table of varchar(128);
   var1 SIMPLE_COLL ;
   var2 LOCAL_COLL;

   res1 SIMPLE_COLL ;
   status pls_integer;
begin
   var1 := SIMPLE_COLL ('a', 'b');
   any1 := sys.anydata.convertCollection(var1);
   
   begin
       var2 := LOCAL_COLL('a', 'b');
       any2 := sys.anydata.convertCollection(var2);
   exception when dbms_types.incorrect_usage then
       dbms_output.put_line('Anydata does not support local collection type');
   end;
   
end;
/

GET

ANYDATA 타입 인스턴스를 임의의 타입으로 변환합니다. 리턴값은 성공했을 때 DBMS_TYPES.SUCCESS를 리턴합니다.

GET 메소드 시리즈의 세부 내용은 다음과 같습니다.

• 프로토타입

MEMBER FUNCTION GetNumber(self IN AnyData, num OUT NOCOPY NUMBER)
       return PLS_INTEGER;
MEMBER FUNCTION GetDate(self IN AnyData, dat OUT NOCOPY DATE)
      return PLS_INTEGER;
MEMBER FUNCTION GetChar(self IN AnyData, c OUT NOCOPY CHAR)
      return PLS_INTEGER;
MEMBER FUNCTION GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR)
      return PLS_INTEGER;
MEMBER FUNCTION GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR2)
      return PLS_INTEGER;
MEMBER FUNCTION GetRaw(self IN AnyData, r OUT NOCOPY RAW)
      return PLS_INTEGER;
MEMBER FUNCTION GetBlob(self IN AnyData, b OUT NOCOPY BLOB)
      return PLS_INTEGER;
MEMBER FUNCTION GetClob(self IN AnyData, c OUT NOCOPY CLOB)
      return PLS_INTEGER;
MEMBER FUNCTION GetTimestamp(self IN AnyData
      ts OUT NOCOPY TIMESTAMP_UNCONSTRAINED) return PLS_INTEGER;
MEMBER FUNCTION GetTimestampTZ(self IN AnyData
      ts OUT NOCOPY TIMESTAMP_TZ_UNCONSTRAINED) return PLS_INTEGER;
MEMBER FUNCTION GetTimestampLTZ(self IN AnyData,
      ts OUT NOCOPY TIMESTAMP_LTZ_UNCONSTRAINED) return PLS_INTEGER;
MEMBER FUNCTION GetIntervalYM(self IN AnyData,
      inv IN OUT NOCOPY YMINTERVAL_UNCONSTRAINED) return PLS_INTEGER;
MEMBER FUNCTION GetIntervalDS(self IN AnyData,
      inv IN OUT NOCOPY DSINTERVAL_UNCONSTRAINED) return PLS_INTEGER;
MEMBER FUNCTION GetNchar(self IN AnyData, nc OUT NOCOPY NCHAR)
      return PLS_INTEGER;
MEMBER FUNCTION GetNVarchar2(self IN AnyData, nc OUT NOCOPY NVARCHAR2)
      return PLS_INTEGER;
MEMBER FUNCTION GetNClob(self IN AnyData, nc OUT NOCOPY NCLOB)
      return PLS_INTEGER;
MEMBER FUNCTION GetBFloat(self IN AnyData, fl OUT NOCOPY BINARY_FLOAT)
      return PLS_INTEGER;
MEMBER FUNCTION GetBDouble(self IN AnyData, dbl OUT NOCOPY BINARY_DOUBLE)
      return PLS_INTEGER;
MEMBER FUNCTION GetCollection(self IN AnyData, obj OUT NOCOPY "<collection>")
      return PLS_INTEGER;
MEMBER FUNCTION GetObject(self IN AnyData, obj OUT NOCOPY "<object>")
      return PLS_INTEGER;

• 파라미터

파라미터	설명
self	ANYDATA 타입 인스턴스 자신 자신의 값은 변경되지 않음
num, da, c, r, b, ts, inv, nc, fl, dbl, obj, col	NYDATA 타입에서 꺼내올 변수

• 예외 상황

예외 상황	설명
DBMS_TYPES.TYPE_MIS MATCH	생성했던 타입과 다른 타입으로 GET*을 호출하는 경우 발생

• 예제

declare
    var sys.anydata;
    nvar number := 10; rvar number;
    status pls_integer;
begin
    var := sys.anydata.convertNumber(nvar);
    status := var.getnumber(rvar);
    dbms_output.put_line( rvar );
end;
/

declare
    v1 SIMPLE_OBJ;
    v2 SIMPLE_OBJ;
    result sys.anydata;
    flags pls_integer;
begin
    v1 := SIMPLE_OBJ (10, 20);
    
    result := sys.anydata.convertobject(v1);

    flags := result.getobject(v2);

    dbms_output.put_line (v2.c1);
    dbms_output.put_line (v2.c2);
end;
/
declare
    any1 sys.anydata;

    var1 SIMPLE_COLL ;
    res1 SIMPLE_COLL ;
    status pls_integer;
begin
    var1 := SIMPLE_COLL ('a', 'b');
    any1 := sys.anydata.convertCollection(var1);
    status := any1.getcollection(res1);
    dbms_output.put_line(res1(1));
    dbms_output.put_line(res1(2));
end;
/

ACCESS

ANYDATA 타입 인스턴스를 임의의 타입으로 변환합니다. 예외가 발생하지 않으며, 잘못된 타입으로 연산 한 경우 GET 시리즈와 달리 NULL을 리턴합니다.

ACCESS 메소드 시리즈의 세부 내용은 다음과 같습니다.

• 프로토타입

MEMBER FUNCTION AccessNumber(self IN AnyData)
       return NUMBER DETERMINISTIC;
MEMBER FUNCTION AccessDate(self IN AnyData)
       return DATE DETERMINISTIC;
MEMBER FUNCTION AccessChar(self IN AnyData)
       return CHAR DETERMINISTIC;
MEMBER FUNCTION AccessVarchar(self IN AnyData)
       return VARCHAR DETERMINISTIC;
MEMBER FUNCTION AccessVarchar2(self IN AnyData)
       return VARCHAR2 DETERMINISTIC;
MEMBER FUNCTION AccessRaw(self IN AnyData)
       return RAW DETERMINISTIC;
MEMBER FUNCTION AccessBlob(self IN AnyData)
       return BLOB DETERMINISTIC;
MEMBER FUNCTION AccessClob(self IN AnyData)
       return CLOB DETERMINISTIC;
MEMBER FUNCTION AccessTimestamp(self IN AnyData)
       return TIMESTAMP_UNCONSTRAINED DETERMINISTIC;
MEMBER FUNCTION AccessTimestampTZ(self IN AnyData)
       return TIMESTAMP_TZ_UNCONSTRAINED DETERMINISTIC;
MEMBER FUNCTION AccessTimestampLTZ(self IN AnyData)
       return TIMESTAMP_LTZ_UNCONSTRAINED DETERMINISTIC;
MEMBER FUNCTION AccessIntervalYM(self IN AnyData)
       return YMINTERVAL_UNCONSTRAINED DETERMINISTIC;
MEMBER FUNCTION AccessIntervalDS(self IN AnyData)
       return DSINTERVAL_UNCONSTRAINED DETERMINISTIC;
MEMBER FUNCTION AccessNchar(self IN AnyData)
       return NCHAR DETERMINISTIC;
MEMBER FUNCTION AccessNVarchar2(self IN AnyData)
       return NVARCHAR2 DETERMINISTIC;
MEMBER FUNCTION AccessBFloat(self IN AnyData)
       return BINARY_FLOAT DETERMINISTIC;
MEMBER FUNCTION AccessBDouble(self IN AnyData)
       return BINARY_DOUBLE DETERMINISTIC;

• 파라미터

파라미터	설명
self	ANYDATA 타입 인스턴스 자신 자신의 값은 변경되지 않음

• 예제

declare
    var sys.anydata;
    nvar number := 10;
    rvar number;
    status pls_integer;
begin
    var := sys.anydata.convertNumber(nvar);
    dbms_output.put_line( var.accessnumber );
    dbms_output.put_line( var.accessvarchar ); -- null 출력
end;
/

GETTYPENAME

ANYDATA 타입에서 타입이름을 반환합니다.

GETTYPENAME 메소드의 세부 내용은 다음과 같습니다.

• 프로토타입

MEMBER FUNCTION GetTypeName(self IN AnyData) return VARCHAR;

• 파라미터

파라미터	설명
self	ANYDATA 타입 인스턴스 자신 자신의 값은 변경되지 않음

• 예제

create or replace type SIMPLE_COLL  as table of varchar(128);
/
create or replace type SIMPLE_OBJ is object ( c1 number, c2 number);
/

declare
        var1 sys.anydata;
        var2 sys.anydata;
        var3 sys.anydata;
        num number := 10;
        obj SIMPLE_OBJ;
        coll SIMPLE_COLL;
begin
        obj := SIMPLE_OBJ (10, 20);
        coll := SIMPLE_COLL('a', 'b');
        var1 := sys.anydata.convertNumber(num);
        var2 := sys.anydata.convertObject(obj);
        var3 := sys.anydata.convertCollection(coll);
        dbms_output.put_line(var1.gettypename );
        dbms_output.put_line(var2.gettypename );
        dbms_output.put_line(var3.gettypename );
end;
/