ddl.sql

来自「对于移动数据库而言」· SQL 代码 · 共 77 行

--let user be able to access v$session
conn sys/....@....
grant select on v_$session to test;

conn test/test@...
select client_info from v$session b where b.audsid=to_number(USERENV('SESSIONID'))

-- department info
create table Dept
( DepID  number(2),
  Name   varchar2(10),
  MgrID  number(3)
);
-- primary key constraints 
alter table Dept add constraint PK_Dept primary key (DepID);

-- employee info
create table Emp
( EmpID  number(3),
  Name   varchar2(15),
  DepID  number(2)
);
-- primary key constraints 
alter table Emp add constraint PK_Emp primary key (EmpID);

-- foreign key constraints
alter table Dept add constraint FK_Dept_Emp foreign key (MgrID) references Emp (EmpID);
alter table Emp add constraint FK_Emp_Dept foreign key (DEPID) references dept (DepID);

-- user info
create table Users
( UserID    number(2),
  LoginName varchar2(10),
  Password  varchar2(10)
);
-- primary key constraints 
alter table Users add constraint PK_Users primary key (UserID);

-- Maximum Access Number
create table AccessNumber
( UserID       number(2),
  Table_Name   varchar2(10),
  AccessNumber number(2)
);
-- primary key constraints 
alter table AccessNumber add constraint PK_AccessNumber primary key (UserID,Table_Name);
-- foreign key constraints
alter table AccessNumber add constraint FK_AccessNumber_Users foreign key (UserID) references Users (UserID);

-- department filter info
create table DeptFilter
( UserID           number(2),
  DepID            number(2),
  Name             varchar2(10),
  MgrID            number(3),
  Last_Access_Date Date
);
-- primary key constraints 
alter table DeptFilter add constraint PK_DeptFilter primary key (UserID,DepID);
-- foreign key constraints
alter table DeptFilter add constraint FK_DeptFilter_Users foreign key (UserID) references Users (UserID);
alter table DeptFilter add constraint FK_DeptFilter_Dept foreign key (DepID) references Dept (DepID);

-- employee filter info
create table EmpFilter
( UserID           number(2),
  EmpID           number(3),
  Name             varchar2(10),
  DepID           number(3),
  Last_Access_Date Date
);
-- primary key constraints 
alter table EmpFilter add constraint PK_EmpFilter primary key (UserID,EmpID);
-- foreign key constraints
alter table EmpFilter add constraint FK_EmpFilter_Users foreign key (UserID) references Users (UserID);
alter table EmpFilter add constraint FK_EmpFilter_Emp foreign key (EmpID) references Emp (EmpID);