oracle 如何在其他表中使用其他表对象

pbossiut 于 2022-11-22 发布在 Oracle

关注(0)|答案(1)|浏览(103)

这是我的第一个问题。所以我试图在account_branchtype中使用类型accounttype的act_no varchar 2（10），act_name varchar 2（10）。所以我输入了命令= create or replace type account_branchtype作为对象（act_no ref accounttype，act_name ref accounttype，act_branchvarchar 2（10））;
但是它不工作。我已经粘贴了代码，请帮助我如何在account_branchtype中使用act_no，act_name。

create or replace type  accounttype as object(act_no varchar2(10), act_name varchar2(10),      act_balance number(10), act_dob date, member function age return number);

create or replace type body accounttype as member function age return number
2 as
3 begin
4 return(round((sysdate-dob)/365));
5 end age;
6 end;
7 /

create or replace type account_branchtype as object(act_no ref accounttype, act_name ref accounttype, act_branch varchar2(10));

create or replace type account_citytype as object(account ref accounttype, account ref accounttype, act_city varchar2(10), act_ pincodenumber(6), act_ state varchar2(15));

create table account of accounttype;

insert into account values(accounttype('19DCS001','Rajesh','35000','12-JUL-2001'));
insert into account values(accounttype('19DCS002','Shyam','30000','05-NOV-1993'));
insert into account values(accounttype('19DCS003','Bimal','55000','12-DEC-1997'));
insert into account values(accounttype('19DCS004','Neel','46000','31-JAN-2000'));
insert into account values(accounttype('19DCS005','Tushar','37900','27-FEB-2002'));

select * from account;

create table account_branch of account_branchtype;

insert into account_branch values(account_branchtype ('19DCS001','Rajesh','Manjalpur'));
insert into account_branch values(account_branchtype ('19DCS002','Shyam','MG Road'));
insert into account_branch values(account_branchtype ('19DCS003','Bimal','Mayapuri'));
insert into account_branch values(account_branchtype ('19DCS004','Neel','Borivali'));
insert into account_branch values(account_branchtype ('19DCS005','Tushar','Ghogha'));

select * from account_branch;

create table account_city of account_citytype;

insert into account_city values(account_citytype ('19DCS001','Rajesh','Vadodara','390011','Gujarat'));
insert into account_city values(account_citytype ('19DCS002','Shyam','Bangalore','400032','Karnataka'));
insert into account_city values(account_citytype ('19DCS003','Bimal','Delhi','110064','Delhi'));
insert into account_city values(account_citytype ('19DCS004','Neel','Mumbai','400092','Maharastra'));
insert into account_city values(account_citytype ('19DCS005','Tushar','Bhavnagar','364110','Gujarat'));

我尝试了很多方法，但是没有找到合适的解决方案。我想使用account_分支中类型accounttype的act_no varchar 2（10）、act_name varchar 2（10）。请帮助我。

oracle

来源：https://stackoverflow.com/questions/74471176/how-to-use-other-table-object-in-another-table

1条答案

按热度按时间

n3ipq98p1#

不要尝试重复列，使用第三范式并确保数据具有单一的真实来源，然后当您想要显示帐户名称时，可以从对象引用中获取值。
将类型创建为：

CREATE TYPE accounttype AS OBJECT(
  no      varchar2(10),
  name    varchar2(10),
  balance number(10),
  dob date,
  member function age return number
);

CREATE TYPE BODY accounttype AS
  MEMBER FUNCTION age RETURN NUMBER
  AS
  BEGIN
    RETURN FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
  END age;
END;
/

CREATE TYPE account_branchtype AS OBJECT(
  account REF accounttype,
  branch  varchar2(10)
);

CREATE TYPE account_citytype AS OBJECT(
  account ref accounttype,
  city    varchar2(10),
  pincode number(6),
  state   varchar2(15)
);

注意：如果需要，您可以在任何地方使用act_前缀，但您知道它与一个帐户相关，因为它在一个名称以“account”开头的表中，所以这看起来像是大量不必要的键入。*

和您的表作为：

create table account of accounttype (
  no CONSTRAINT account__no__pk PRIMARY KEY
);

create table account_branch of account_branchtype (
  account SCOPE IS account
);

create table account_city of account_citytype (
  account SCOPE IS account
);

然后，您可以使用以下命令插入数据：

insert into account (no, name, balance, dob)
  values('19DCS001','Rajesh','35000', DATE '2001-07-12');

insert into account_branch (account, branch)
  values( (SELECT REF(a) FROM account a WHERE no = '19DCS001'),'Manjalpur');

insert into account_city (account, city, pincode, state)
  values(
    (SELECT REF(a) FROM account a WHERE no = '19DCS001'),
    'Vadodara',
    '390011',
    'Gujarat'
  );

然后道：

SELECT a.*,
       a.age() AS age
FROM   account a;

输出：
| 否|名称|平衡|出生日期|年龄|
| - -|- -|- -|- -|- -|
| 小行星19 DCS 001|拉杰什|三万五千|2001年07月12日上午10时00分|二十一个|
以及：

SELECT b.account.no,
       b.account.name,
       b.branch
FROM   account_branch b;

输出：
| ACCOUNT.NO | ACCOUNT.NAME |分支|
| - -|- -|- -|
| 小行星19 DCS 001|拉杰什|曼贾尔普尔|
以及：

SELECT c.account.no,
       c.account.name,
       c.city,
       c.pincode,
       c.state
FROM   account_city c;

输出：
| ACCOUNT.NO | ACCOUNT.NAME |城市名称|PIN码|状态|
| - -|- -|- -|- -|- -|
| 小行星19 DCS 001|拉杰什|瓦多达拉|小行星39001|古吉拉特语Name|
fiddle

赞(0）回复(0）举报 2022-11-22

我来回答

oracle 如何在其他表中使用其他表对象

1条答案

相关问题

热门标签

最新问答