Home SQL Oracle

# Create Type Basic

  • 使用 type 建表可以局限于只为这张表提供对应的属性或方法
-- way 1
create or repalce type type_name as object (
  field_name type,
  field_name type,
  ...
);
-- way 2
Create or replace type type_name of custom_type;
# AS OBJECT


create or replace type t1 as object (
   userid varchar2(100),
   username varchar2(200)
);
create table user1 of t1;
# OF CUSTOM_TYPE


example 1:
  create or replace type t2 as table of varchar2(10);
example 2:
  create or replace type t3 as varray(10) of varchar2(10);
example 3:
  declare
    v_array t3:= t3('a');
  begin
    v_array.extend;
    v_array(2):= 'b';
    v_array.extend;
    select 'c' into v_array(3) from dual;
    for i in 1..v_array.count  loop
      dbms_output.put_line(v_array(i));  -- a b c
    end loop;
  end;
# Type Function


# Member Function


-- Create Type Defined
create or replace type userinfo_type as object(
  userid varchar(100),
  username varchar2(200),
  gender number,
  age number,
  member function getGender(gender in number) return varchar2,
  member function setGender(gender in varchar2) return varchar2,
  member function getYear(age in number) return number,
  pragma restrict_references(getGender,   rnps),
  pragma restrict_references(setGender,   wnps)
);
/*
RNDS: (Read No Database State) 表示该 subprogram 不会查询 (query) 数据库中的表: ORA-00452
WNDS: (Write No Database State) 表示该 subprogram 不会改变数据库中的表的数据
RNPS: (Read No Package State) 不访问包中的变量
WNPS:(Write No Package State) 不改变包中的变量值
*/
-- Create Type Body
create or replace type body userinfo_type as
  member function getGender(gender in number) return varchar2
  is
  begin
    if gender = 0 then return '男'; end if;
    return '女';
  end;
  member function setGender(gender in varchar2) return varchar2
  is
  begin
    if gender = '男' then return 0; end if;
    return 1;
  end;
  member function getYear(age in number) return number
  is
  begin
    return  extract(year from sysdate) - age;
  end;
end;
    

  • Member Function Test
    • 



create table userinfo of userinfo_type;
insert into userinfo values('001','jack', '0', 18);
insert into userinfo values('002', 'annie', '1', 21);
commit;
select t.userid, t.username, t.getGender(gender) getGender, t.setGender('男') setGender, t.getYear(age) getYear from userinfo t;
# Static Function


-- create type
create or replace type typ_calendar force as object(
  year varchar2(10),
  month varchar2(10),
  Sunday varchar2(10),
  Monday varchar2(10),
  Tuesday varchar2(10),
  Wednesday varchar2(10),
  Thursday  varchar2(10),
  Friday varchar2(10),
  Saturday varchar2(10),
  curday varchar2(10),
  member function format(
    curday date        := sysdate,
    fmtlen pls_integer := 8
  ) return typ_calendar,
  static function format_day(
    day varchar2
  ) return varchar2
);
-- create type body
create or replace type body typ_calendar as
  member function format(
    curday date        := sysdate,
    fmtlen pls_integer := 8
  ) return typ_calendar as
      v_calendar   typ_calendar := typ_calendar('','','','','','','','','','');
      v_dd       varchar2(2)  := to_char(curday, 'dd');
    function fmt( fmtstr varchar2 )return varchar2 as
    begin
      return lpad(fmtstr, fmtlen, ' ');
    end fmt;
  begin
    v_calendar.year := year;
    v_calendar.month := month;
    v_calendar.Sunday := fmt(Sunday);
    v_calendar.Monday := fmt(Monday);
    v_calendar.Tuesday := fmt(Tuesday);
    v_calendar.Wednesday := fmt(Wednesday);
    v_calendar.Thursday := fmt(Thursday);
    v_calendar.Friday := fmt(Friday);
    v_calendar.Saturday := fmt(Saturday);
    v_calendar.Curday := nvl(self.Curday, fmt(to_char(curday, 'dd')));
    dbms_output.put_line(self.curday );
    if (YEAR || lpad(MONTH, 2, '0') = to_char(curday, 'yyyymm')) then
      case v_dd
      when Sunday then
        v_calendar.Sunday := fmt('【' || Sunday || '】');
      when Monday then
        v_calendar.Monday := fmt('【' || Monday || '】');
      when Tuesday then
        v_calendar.Tuesday := fmt('【' || Tuesday || '】');
      when Wednesday then
        v_calendar.Wednesday := fmt('【' || Wednesday || '】');
      when Thursday then
        v_calendar.Thursday := fmt('【' || Thursday || '】');
      when Friday then
        v_calendar.Friday := fmt('【' || Friday || '】');
      when Saturday then
        v_calendar.Saturday := fmt('【' || Saturday || '】');
      else null;
      end case;
    end if;
    return v_calendar;
  end format;
  static function format_day (day varchar2) return varchar2 as
  v_day number := to_number(day);
  v_value varchar2(20);
  begin
    case
      when v_day = 1 then v_value:= '星期一';
      when v_day = 2 then v_value:= '星期二';
      when v_day = 3 then v_value:= '星期三';
      when v_day = 4 then v_value:= '星期四';
      when v_day = 5 then v_value:= '星期五';
      when v_day = 6 then v_value:= '星期六';
      when v_day = 7 then v_value:= '星期日';
      else v_value:= '日期天数不合法，';
    end case;
   return v_value;
  end;
end;
    

  • Static Function Test
    • 



create table calendar of typ_calendar;
-- 插入数据使用 calendar.format 进行类型转换格式化
insert into calendar
  select typ_calendar('2021', '06', '01', '02', '03', '04', '05', '06', '07','01').format() typ_calendar from dual;
insert into calendar
  select typ_calendar('2022', '08', '01', '02', '03', '04', '05', '06', '07','02').format() typ_calendar from dual;
insert into calendar
  select typ_calendar('2023', '05', '01', '02', '03', '04', '05', '06', '07','').format() typ_calendar from dual;
commit;
-- Member Function Test
-- 1.format 是 member（成员函数），可以直接 (表别名.function) 调用 member function 修饰的方法
select t.format(sysdate, 8) from calendar t;
-- 2. 直接通过 （type.function） 调用  member function 修饰的方法，会报错：ORA-06553: PLS-306
-- select typ_calendar.format(sysdate, 8) from calendar;
-- 3. 调用静态方法时不能使用实例值: ORA-06553: PLS-587
-- select typ_calendar('2022', '08', '01', '02', '03', '04', '05', '06', '07','02').format().format_day('01') typ_calendar from dual;
-- Static Function Test
-- 1. 将数字转星期，通过 type 直接调用 (static function) 修饰的静态方法是没有问题的
select typ_calendar.format_day(curday) from calendar;
-- 2. 调用静态方法时不能使用实例值: ORA-06553: PLS-587
-- select t.format_day('01') from calendar t;
# Constructor Function


    

  • 定义构造方法必须使用关键字 constructor，构造方法名称必须要与对象类型名称一致。
    • 

  • 下方定义了三个构造函数，相同构造名称，含有不同个数参数（你可以理解为 java 里面的重载）
    • 



-- create type
create or replace type type_user as object (
  name      varchar2(100),
  year      number,
  month     number,
  day       number,
  Birthdate varchar2(200),
  constructor function type_user(name varchar2, year number)
    return self as result,
  constructor function type_user(name varchar2, year number, month number)
    return self as result,
  constructor function type_user(name varchar2, year number, month number, day number)
    return self as result
);
-- create type Body
create or replace type body type_user
as
  -- 当只传入了参数 year 时，他的生日信息只会显示 年份
  constructor function type_user(name varchar2, year number)
    return self as result as
  begin
    self.name := name;
    self.year := year;
    self.Birthdate  := year;
    return;
  end;
  -- 当传入了参数 year、month 时，他的生日信息会显示 年份 - 月份
  constructor function type_user(name varchar2, year number, month number)
    return self as result as
  begin
    self.name := name;
    self.year := year;
    self.month := month;
    self.Birthdate  := year||'-'||month;
    return;
  end;
  -- 当传入了参数 year、month、day 时，他的生日信息会完整的显示 年 - 月 - 日
  constructor function type_user(name varchar2, year number, month number,day number)
    return self as result as
  begin
    self.name := name;
    self.year := year;
    self.month := month;
    self.day := day;
    self.Birthdate  := year||'-'||month||'-'||day;
    return;
  end;
end;
    

  • Constructor Function Test
    • 



-- 调用
declare
  u1 type_user;
  u2 type_user;
  u3 type_user := type_user('C',2003, 03, 040);
begin
  -- 我们可以调用默认 3 个参数的构造函数（因为定义了 3 个属性）
  u1 := new type_user('A', 2001);
  dbms_output.put_line(u1.name || ' Birthdate：' || u1.Birthdate);
  u2 := new type_user('B', 2002, 02);
  dbms_output.put_line(u2.name || ' Birthdate：' || u2.Birthdate);
  dbms_output.put_line(u3.name || ' Birthdate：'|| u3.Birthdate);
  /**
   Result
     A Birthdate：2001
     B Birthdate：2002-2
     C Birthdate：2003-3-40
  */
end;
    

  • 注意构造函数的存在对在使用 DMl 语句，没有进行类型转换时是不会产生影响的。
    • 



create table users of type_user;
 -- 默认是不会参数影响的
 select 'C',2003, 03, 04 from users;
 -- 需要进行 type_user 转换
 select type_user('C',2003) from dual;
 select type_user('C',2003, 03) from dual;
 select type_user('C',2003, 03, 04) from dual;
# Abstract Type


create or replace type other_type as object (
  sports varchar2(200),
  hobby varchar2(200)
);
create or replace type user_type as object(
  userid varchar2(100),
  username varchar2(200),
  other other_type
);
# Abstract Type Test


create table userinfo2 of user_type;
-- insert
insert into userinfo2(userid, username, other) values('001', 'jack', other_type('ping pong', 'animation'));
insert into userinfo2(userid, username, other) values('002', 'lisa', other_type('badminton', 'animation'));
commit;
select t.userid, t.username, t.other.sports sports, t.other.hobby hobby from userinfo2 t;
-- update
update userinfo2 t set t.username = 'jerry', t.other.sports = 'football' where userid = '001';
commit;
select t.userid, t.username, t.other.sports sports, t.other.hobby hobby from userinfo2 t;
# Type Extends


    

  • a final object type is a type from which cannot be derived
    • 

  • 最终对象类型是无法派生的类型
    • 



# Not Final & Final


-- not final
create or replace type this_type_not_final as object(
  money number,
  member function sum(x in number) return number
) not final;
create or replace type body this_type_not_final as
 member function sum(x in number) return number
 is
 begin
   return money * x;
 end sum;
end;
-- final
create or replace type this_type_final as object(
  color varchar2(200),
  material varchar2(200),
  member function sum(x in number, y in number) return number
);
create or replace type body this_type_final as
 member function sum(x in number, y in number) return number
 is
 begin
   return x + y;
 end sum;
end;
# Type Derive


-- The following type can be derived because the base type is not final.
-- 可以派生以下类型，因为基础类型不是最终类型 (not final)。
create or replace type derived_from_NOT_final under this_type_NOT_final (
  member procedure bar
);
-- The following type can not be derived because the base type is final.
-- 无法派生以下类型，因为基类型是最终类型 (final)。
create or replace type derived_from_final under this_type_final (
  member procedure bar
);
-- Trying to derive from a final type gives a PLS-00590: attempting to create a subtype UNDER a FINAL type.
-- pls-00590: 正在尝试创建一个最终类型的子类型
# Final Function


    

  • not final member procedures/functions
    • 

  • 不是最终成员程序 / 函数
    • 



-- create type
create or replace type type_member_isfinal as object (
  x number,
  not final member function NOT_FINAL_func(y in number) return number,
  final     member function FINAL_func    (y in number) return number
) not final;
-- create type body
create or replace type body type_member_isfinal as
  not final member function NOT_FINAL_func(y in number) return number
  is
  begin
    return x + y;
  end;
  final member function FINAL_func(y in number) return number
  is
  begin
    return x - y;
  end;
end;
# Overriding Function


    

  • a member function (or procedure) can be overriden if it is declared not final in the base class
    • 

  • 如果成员函数（或过程）在基类中声明不是 final，则可以重写该函数（或过程）
    • 



create or replace type type_override_member_isfinal under type_member_isfinal (
  overriding member function NOT_FINAL_func(y in number) return number
);
-- 以下内容不起作用，因为无法重写最终成员函数
create or replace type type_override_member_isfinal under type_member_isfinal (
  overriding member function FINAL_func(y in number) return number
);
-- pls-00637: 无法覆盖或隐藏 final 方法
    

  • 建议参考：http://adp-gmbh.ch/ora/plsql/oo/not_final.html
    • 



      

          
          
      

  


   


    

  
    
      
    
    Update on
    
  
  
      
        
      
      Views
      
      times
  



      


  
  
Give me a cup of [coffee]~(￣▽￣)~*

  

      
      

        Nico Niconi WeChat Pay
        
WeChat Pay

      

      
      

        Nico Niconi Alipay
        
Alipay