--创建一个存储过程,判断是表或者是序列是否存在,如果存在,就删除,如果不存在,就给出提示,进行创建
create or replace procedure pro_exists(var_uservalue in varchar2,var_is_table_seque in number)
as
temp_table integer;
temp_seque integer;
begin
if var_is_table_seque = 1 then --1:判断表是否存在
select count(*)
into temp_seque
from all_sequences
where sequence_name = upper(var_uservalue)
or sequence_name = lower(var_uservalue);
if temp_seque >0 then
execute immediate 'drop sequence '|| var_uservalue;
end if;
dbms_output.put_line('ODCIEnv1');
else
select count(*)
into temp_table
from user_tables
where table_name = upper(var_uservalue)
or table_name = lower(var_uservalue); if temp_table >0 then
execute immediate 'drop table '|| var_uservalue;
end if;
dbms_output.put_line('ODCIEnv2');
end if;
end;
--在commend 命令里面执行
set serveroutput on
--1:角色表
call pro_exists('role_info',1);
--怎么没有效果哟??他不能删除role_info 这张表
create or replace procedure pro_exists(var_uservalue in varchar2,var_is_table_seque in number)
as
temp_table integer;
temp_seque integer;
begin
if var_is_table_seque = 1 then --1:判断表是否存在
select count(*)
into temp_seque
from all_sequences
where sequence_name = upper(var_uservalue)
or sequence_name = lower(var_uservalue);
if temp_seque >0 then
execute immediate 'drop sequence '|| var_uservalue;
end if;
dbms_output.put_line('ODCIEnv1');
else
select count(*)
into temp_table
from user_tables
where table_name = upper(var_uservalue)
or table_name = lower(var_uservalue); if temp_table >0 then
execute immediate 'drop table '|| var_uservalue;
end if;
dbms_output.put_line('ODCIEnv2');
end if;
end;
--在commend 命令里面执行
set serveroutput on
--1:角色表
call pro_exists('role_info',1);
--怎么没有效果哟??他不能删除role_info 这张表
存储过程里加异常捕捉,看是否发生了异常
控制台有输出?
--into temp_table
from user_tables
where table_name = upper(var_uservalue)
or table_name = lower(var_uservalue);
运行能得到结果?
另外,根据你的过程优化了一下
CREATE OR REPLACE PROCEDURE pro_exists(var_uservalue IN VARCHAR2) AS
temp_type user_objects.OBJECT_TYPE%TYPE;
BEGIN
SELECT object_type INTO temp_type FROM user_objects WHERE object_name = upper(var_uservalue);
IF temp_type IN ('TABLE', 'SEQUENCE') THEN
EXECUTE IMMEDIATE 'drop ' || temp_type || ' ' || var_uservalue;
dbms_output.put_line(temp_type || ':' || var_uservalue || '存在且已删除!');
ELSE
dbms_output.put_line(var_uservalue || '不是表或序列对象!');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('名为' || var_uservalue || '对象不存在!');
END;
你是删除序列了,要删除表的话又把参数1改成其它
-- 当输入参数 var_is_table_seque 为非1时表示删除 的是 TABLE(表);
CREATE OR REPLACE PROCEDURE pro_exists(var_uservalue in varchar2, var_is_table_seque in number)
AS
temp_table integer;
temp_seque integer;
sqlstr VARCHAR2(200);
BEGIN
IF var_is_table_seque=1 THEN
SELECT count(1) INTO temp_seque FROM all_sequences WHERE sequence_name=upper(var_uservalue);
IF temp_seque >0 THEN
sqlstr := 'DROP SEQUENCE '||upper(var_uservalue);
execute immediate sqlstr; --USING var_uservalue;
END IF;
dbms_output.put_line('ODCIEnv1');
ELSE
SELECT count(1) INTO temp_table FROM user_tables WHERE table_name=upper(var_uservalue);
IF temp_table > 0 THEN
sqlstr := 'DROP TABLE '||upper(var_uservalue);
execute immediate sqlstr;
END IF;
dbms_output.put_line('ODCIEnv2');
END IF;
END;
/CREATE TABLE t(cdate date);
CREATE SEQUENCE test_seq;
exec pro_exists('T',0);
exec pro_exists('test_seq',1);