create or replace procedure create_user_login()
is
begin
if exists (select table_name from user_tables where table_name='user_login'; ) then
drop table user_login;
end if;
exec immediate 'create table user_login
(id integer primary key,
name varchar2(20),
sex varchar2(4),
office_id integer,
bussiness varchar2(100),
tele varchar2(20),
pirror_id integer,
photo long raw,
password varchar2(20),
regist_date date,
invalid_date date)';
end
/
is
begin
if exists (select table_name from user_tables where table_name='user_login'; ) then
drop table user_login;
end if;
exec immediate 'create table user_login
(id integer primary key,
name varchar2(20),
sex varchar2(4),
office_id integer,
bussiness varchar2(100),
tele varchar2(20),
pirror_id integer,
photo long raw,
password varchar2(20),
regist_date date,
invalid_date date)';
end
/
IS
i INTEGER;
BEGIN SELECT COUNT(*) INTO i FROM user_tables where table_name=Upper('user_login') ;
if i > 0 then
EXECUTE IMMEDIATE 'drop table user_login';
end if;
EXECUTE immediate 'create table user_login
(id integer primary key,
name varchar2(20),
sex varchar2(4),
office_id integer,
bussiness varchar2(100),
tele varchar2(20),
pirror_id integer,
photo long raw,
password varchar2(20),
regist_date date,
invalid_date date)';
END;
-- EXISTS 只能用在SQL语句中
-- tablename需转换成大写,否则查询不到
-- 用EXECUTE IMMEDIATE语句须用户有EXECUTE权限
is
num number;
str varchar2(300);
begin
select 1 into num from user_tables where table_name='USER_LOGIN';
str:='create table user_login
(id integer primary key,
name varchar2(20),
sex varchar2(4),
office_id integer,
bussiness varchar2(100),
tele varchar2(20),
pirror_id integer,
photo long raw,
password varchar2(20),
regist_date date,
invalid_date date)';
execute immediate str;
exception
when others then
str:='drop table user_login';
execute immediate str;
str:='create table user_login
(id integer primary key,
name varchar2(20),
sex varchar2(4),
office_id integer,
bussiness varchar2(100),
tele varchar2(20),
pirror_id integer,
photo long raw,
password varchar2(20),
regist_date date,
invalid_date date)';
execute immediate str;
end
/