Oracle 建库 SQL 脚本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- 首先是删除该数据库中该用户名下的所有表、序列与触发器,
-- 其中触发器是通过表格级联删除的。
declare
  cursor usertables is
    select *
    from user_tables
    where table_name not like 'BIN$%';
  cursor usersequences is
    select *
    from user_sequences;
begin
  for next_row in usertables loop
    execute immediate
      'drop table ' || next_row.table_name ||
      ' cascade constraints';
  end loop;
  for next_row in usersequences loop
    execute immediate
      'drop sequence ' || next_row.sequence_name;
  end loop;
end;
/

-- 然后,就是一砣……的建表语句啦,
-- 比方说下面就是两个含外键的表。
create table "A" (
  "ID" integer primary key,
  "NAME" nvarchar2(64) not null         -- nvarchar2类型支持UTF8
);
-- Oracle中的表名与字段名最好写成这样引号加全大写的形式。

create table "B" (
  "ID" integer primary key,
  "A" integer not null references "B" on delete cascade,
  "VALUE" nvarchar2(64) not null
);

-- 接下来是第三部分,为所有的表创建对应的序列和触发器,以实现自增主键。
declare
  cursor usertables is
    select cols.table_name, cols.column_name
    from all_constraints cons, all_cons_columns cols
    where cols.owner = (
      select sys_context('USERENV', 'SESSION_USER')
      from dual)
    and cols.table_name not like 'BIN$%'
    and cons.constraint_type = 'P'
    and cons.constraint_name = cols.constraint_name
    and cons.owner = cols.owner
begin
  for nextrow in usertables loop
    -- 1000以下的留作初始测试数据用,见下
    execute immediate
      'create sequence "' || nextrow.table_name ||
      '_PK_SEQ" start with 1000';
    execute immediate
      'create or replace trigger "' || nextrow.table_name ||
      '_PK_TRG" before insert on "' || nextrow.table_name ||
      '" for each row begin if :new."' || nextrow.column_name ||
      '" is null then select "' || nextrow.table_name ||
      '_PK_SEQ".nextval into :new."' || nextrow.column_name ||
      '" from dual; end if; end;';
  end loop;
end;
/

-- 最后当然就是测试数据啦。
insert into "A" (ID, NAME) values (1, 'first name');
insert into "B" (ID, AID, VALUE) values (1, 1, 'Jay');
insert into "B" (ID, AID, VALUE) values (2, 1, '杰');
 
comments powered by Disqus