数据库存储过程

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

);