我想創建一個具有屬性的型別，該屬性應該是三個可能的值。類似于以下陳述句：
CREATE TYPE my_type AS OBJECT(
attrib1 VARCHAR2(30),
attrib2 VARCHAR2(30),
attrib3 VARCHAR2(30) -- this attribute should accept three possible values: val1, val2, or val3
);
有沒有辦法做到這一點，就像在桌子的情況下一樣？
先感謝您。
uj5u.com熱心網友回復：
您可以使用自定義型別建構式并在 PL/SQL 代碼中執行此檢查。
create type my_type as object(
attrib1 varchar2(30),
attrib2 varchar2(30),
attrib3 varchar2(30),
constructor function my_type (
attrib1 in varchar2,
attrib2 in varchar2,
attrib3 in varchar2
) return self as result
);
/
create type body my_type as
constructor function my_type (
attrib1 in varchar2,
attrib2 in varchar2,
attrib3 in varchar2
) return self as result
as
invalid_attr_value exception;
pragma exception_init(invalid_attr_value, -20001);
begin
if attrib3 not in ('val1', 'val2', 'val3') then
raise_application_error(-20001, 'Invalid attrib3 value supplied');
end if;
self.attrib1 := attrib1;
self.attrib2 := attrib2;
self.attrib3 := attrib3;
return;
end;
end;/
with a(col) as (
select my_type('a', 'b', 'val1')
from dual
)
select
a.col.attrib1,
a.col.attrib2,
a.col.attrib3
from a a
| COL.ATTRIB1 | COL.ATTRIB2 | COL.ATTRIB3 |
|---|---|---|
| 一個 | b | val1 |
with a(col) as (
select my_type('a', 'b', 'val10')
from dual
)
select
a.col.attrib1,
a.col.attrib2,
a.col.attrib3
from a a
ORA-20001: Invalid attrib3 value supplied
ORA-06512: at "FIDDLE_INSFSVTZEJFLABZQAKDJ.MY_TYPE", line 12
小提琴
uj5u.com熱心網友回復：
這是你會滿意的，但是 - 不幸的是 - 它不起作用：
SQL> create or replace type my_type as object
2 (attrib1 varchar2(30),
3 attrib2 varchar2(30),
4 attrib3 varchar2(30) check(attrib3 in ('A', 'B'))
5 );
6 /
create or replace type my_type as object
*
ERROR at line 1:
ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 4, column 23:
PLS-00103: Encountered the symbol "CHECK" when expecting one of the following:
:= ) , not null default external character
The symbol ":= was inserted before "CHECK" to continue.
ORA-06550: line 0, column 0:
PLS-00565: MY_TYPE must be completed as a potential REF target (object type)
因此，按原樣創建型別...
SQL> create or replace type my_type as object
2 (attrib1 varchar2(30),
3 attrib2 varchar2(30),
4 attrib3 varchar2(30)
5 );
6 /
Type created.
...然后在陳述句中attrib3使用該型別時應用檢查約束，例如create table
SQL> create table test of my_type
2 (attrib1 primary key,
3 attrib2 not null,
4 attrib3 default 'A' check (attrib3 in ('A', 'B'))
5 );
Table created.
SQL>
uj5u.com熱心網友回復：
有沒有辦法做到這一點，就像在桌子的情況下一樣？
不，您不能將CHECK約束應用于OBJECT資料型別。
從CONSTRAINT檔案中：
約束子句可以出現在以下陳述句中：
CREATE TABLE（見CREATE TABLE）ALTER TABLE（見ALTER TABLE）CREATE VIEW（見CREATE VIEW）ALTER VIEW（見ALTER VIEW）
因此，您不能在 aCREATE TYPE或ALTER TYPE陳述句中創建約束。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522714.html
標籤：sql甲骨文类型