存储过程

spool elb.log

set sqlblanklines on
set feedback off
set define off
set serveroutput on


-- ---------------------------------------- USER_T ----------------------------------------
prompt create table USER_T ...
-- If the table is exists,drop it ...
DECLARE
  v_table_exists number := 0;
BEGIN
  select count(1) into v_table_exists
    from user_tables t
    where t.TABLE_NAME = 'USER_T';
    
    dbms_output.put_line('USER_T v_table_exists: ' || v_table_exists);
    if(v_table_exists > 0) then
      execute immediate 'drop table USER_T';
    end if;
END;
/
-- If the primary key is exists,drop it ...
DECLARE
  v_pk_exists number := 0;
BEGIN
  select count(1) into v_pk_exists
    from USER_CONSTRAINTS t
    where t.TABLE_NAME = 'USER_T' and t.CONSTRAINT_NAME = 'USER_PK';
    
    dbms_output.put_line('USER_T v_pk_exists: ' || v_pk_exists);
    if(v_pk_exists > 0) then
      execute immediate 'alter table USER_T drop constraint USER_PK';
    end if;
END;
/
-- Create table
create table USER_T
(
  id               VARCHAR2(100) not null,
  username         VARCHAR2(2000),
  age              VARCHAR2(100),
  gender           VARCHAR2(255),
  address          VARCHAR2(100)
)
tablespace TS_NAME
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16K
    next 8K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_T
  add constraint USER_PK primary key (ID)
  using index
  tablespace TS_NAME
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );