29、创建表inline

create table department(id number(2) not null primary key, name  varchar2(14) unique)；

drop table tb;

---

--创建表，有匿名约束

create table tb(id number not null primary key,

name varchar2(20) uniquecheck(length(name)>2),

deptid number references department(id) on

delete cascade); -- 或者set null

---

drop table def_table;

--创建有名字的约束

-- default 应该紧跟在数据类型后面

create table def_table(

 col_id number(5),

 name varchar2(20 char) default 'noname' constraint ck_name_nn not nullenable ,--disable

 email varchar2(200 char) default 'email',

 constraint pk_col_id primary key(col_id),

 constraint ck_name_len check(length(name)>3)

);

---

select * from sys.user_tables;

--判断表是否存在，存在则删除，然后在建立

declare

  t     varchar(200) :='tb';

 tname number;    

begin

 select count(table_name) into tname from sys.user_tables wheretable_name=Upper(t);

  iftname=0 then        --如果使用select table_name into tname，不存在是会报错

    dbms_output.put_line(t || ' not exists.');

  else

    dbms_output.put_line(t || ' already exists.');

    execute immediate 'drop table ' || t;

  endif;

---

 execute immediate 'create table ' || t || '(

  idnumber not null primary key,

 name varchar2(20) unique check(length(name)>2),

 deptid number references dept(id) on delete cascade)';

 dbms_output.put_line(t || ' created .');

end;             

---