时间戳

首页 > 解决方案 > 创建类型时如何设置常量值？

sql - 创建类型时如何设置常量值？

问题描述

我正在尝试将超类型“OperationTY”中的“Datetime”和子类型“CallTY”中的“Op_type”设置为常量值（CURRENT_TIMESTAMP）和“Call”。我真的不知道该怎么做，我是 Oracle 的新手，所以我决定使用构造函数并设置默认值：

CREATE TYPE OperationTY AS OBJECT (
 Datetime TIMESTAMP, 
 Customer REF Customerty,
 Content BLOB, 
 CONSTRUCTOR FUNCTION Operationty (
  Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  Customer REF Customerty DEFAULT NULL,
  Content BLOB DEFAULT NULL)
  RETURN SELF AS RESULT
 ) 
NOT FINAL;

CREATE TYPE BODY OperationTY AS
CONSTRUCTOR FUNCTION OperationTY (
 Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 Customer REF CustomerTY DEFAULT NULL,
 Content BLOB DEFAULT NULL)
RETURN SELF AS RESULT IS
BEGIN
 SELF.Datetime := Datetime;
 SELF.Customer := Customer;
 SELF.Content := Content;
 RETURN;
 END;
END;

以同样的方式创建子类型

CREATE TYPE CallTY UNDER OperatinTY(
 Duration NUMBER,
 Location VARCHAR2(20),
 Receiver VARCHAR2(20),
 Op_type VARCHAR2(4),
 CONSTRUCTOR FUNCTION Callty (
  Duration NUMBER DEFAULT NULL,
  Location VARCHAR2 DEFAULT NULL,
  Receiver VARCHAR2 DEFAULT NULL,
  Op_type VARCHAR2 DEFAULT 'Call')
  RETURN SELF AS RESULT)

CREATE TYPE BODY CallTY AS
CONSTRUCTOR FUNCTION CallTY (
 Duration NUMBER DEFAULT NULL,
 Location VARCHAR2 DEFAULT NULL,
 Receiver VARCHAR2 DEFAULT NULL,
 Op_type VARCHAR2 DEFAULT 'Call')
 RETURN SELF AS RESULT IS
 BEGIN
  SELF.Duration := Duration;
  SELF.Location := Location;
  SELF.Receiver := Receiver;
  SELF.Op_type := Op_type;
  RETURN;
 END;
END;
 
CREATE TABLE Operation of Operationty;

因此，当我尝试进行 INSERT 时：

Insert into Operation values (CallTY('', (Select REF(c) from customer c Where c.name = 'John'), NULL, 1, 'London', '1234567890', '')

然后我没有查询默认值（CURRENT_TIMESTAMP）和“呼叫”，而是得到一个空值。

我究竟做错了什么？

标签： sqloracle

解决方案

指定子类型中的所有构造函数参数：

CREATE TYPE CallTY UNDER OperationTY(
  Duration NUMBER,
  Location VARCHAR2(20),
  Receiver VARCHAR2(20),
  Op_type VARCHAR2(4),

  CONSTRUCTOR FUNCTION Callty (
    Datetime TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    Customer REF CustomerTY DEFAULT NULL,
    Content  BLOB           DEFAULT NULL,
    Duration NUMBER         DEFAULT NULL,
    Location VARCHAR2       DEFAULT NULL,
    Receiver VARCHAR2       DEFAULT NULL,
    Op_type  VARCHAR2       DEFAULT 'Call'
  )
  RETURN SELF AS RESULT
);

CREATE TYPE BODY CallTY AS
  CONSTRUCTOR FUNCTION CallTY (
    Datetime TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    Customer REF CustomerTY DEFAULT NULL,
    Content  BLOB           DEFAULT NULL,
    Duration NUMBER         DEFAULT NULL,
    Location VARCHAR2       DEFAULT NULL,
    Receiver VARCHAR2       DEFAULT NULL,
    Op_type  VARCHAR2       DEFAULT 'Call'
  )
  RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.Datetime := Datetime;
    SELF.Customer := Customer;
    SELF.Content  := Content;
    SELF.Duration := Duration;
    SELF.Location := Location;
    SELF.Receiver := Receiver;
    SELF.Op_type  := Op_type;
    RETURN;
  END;
END;
/

然后你可以使用命名参数并跳过你想使用的DEFAULT那些：

Insert into Operation values (
  CallTY(
    Customer => (Select REF(c) from customer c Where c.name = 'John'),
    Content  => NULL,
    Duration => 1,
    Location => 'London',
    Receiver => '1234567890', 
    op_type  => NULL
  )
);

或指定所有参数：

Insert into Operation values (
  CallTY(
    CURRENT_TIMESTAMP,
    (Select REF(c) from customer c Where c.name = 'John'),
    NULL,
    1,
    'London',
    '1234567890', 
    NULL
  )
);

我究竟做错了什么？

您传递了一个空字符串文字''（在 Oracle 中，它与一个NULL值相同）并覆盖任何DEFAULT. 所以在字段INSERT中输入一个NULL值是因为你告诉它这样做而不是在它使用DEFAULT.

db<>在这里摆弄

推荐阅读