--给个例子 declare num number; begin select count(1) into num from all_tables where table_name='TESTA'; if num>0 then execute immediate 'drop table TESTA'; end if; execute immediate 'create table TESTA as select * from dept'; end; /
SET serveroutput ON; declare i integer; BEGIN i:=0; SELECT count(*) INTO i FROM user_tables WHERE table_name='TB'; IF i=1 THEN EXECUTE IMMEDIATE 'drop table tb'; dbms_output.put_line('删除成功'); ELSE dbms_output.put_line('删除失败'); END IF; end;
drop table TESTA where exists(select * from TESTA) create table TESTA as select * from dept
--单纯的查看表,建立表,就没必要整什么存储过程,直接来下面的,简单,易懂: 1.desc table_name[or view_name] SQL> desc t; Object t does not exist.2.来个查询 SQL> select * from t; ORA-00942: table or view does not exist3.若上面两步其中之一成立,就建表 SQL> create table t 2 as 3 select * from scott.emp 4 where 1=0; Table created
SQL> desc t; Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y MGR NUMBER(4) Y HIREDATE DATE Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y DEPTNO NUMBER(2) Y SQL> drop table t; Table droppedSQL> create table t 2 as 3 select * from scott.dept 4 / Table createdSQL> select * from t; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL code --给个例子 declare num number; begin select count(1) into num from user_tables where table_name='TESTA'; --num>o表示表已经存在,先drop表 if num>0 then execute immediate 'drop table TESTA'; end if; execute immediate 'create table TESTA ....; end; /
可以这么做:CREATE OR REPLACE PROCEDURE p_test AUTHID CURRENT_USER AS BEGIN BEGIN EXECUTE IMMEDIATE 'drop table t_test'; EXCEPTION WHEN OTHERS THEN NULL; END; EXECUTE IMMEDIATE 'create table t_tes as select * from t_temp' END;
--给个例子
declare
num number;
begin
select count(1) into num from all_tables where table_name='TESTA';
if num>0 then
execute immediate 'drop table TESTA';
end if;
execute immediate 'create table TESTA as select * from dept';
end;
/
SET serveroutput ON;
declare
i integer;
BEGIN
i:=0;
SELECT count(*) INTO i FROM user_tables WHERE table_name='TB';
IF i=1 THEN
EXECUTE IMMEDIATE 'drop table tb';
dbms_output.put_line('删除成功');
ELSE
dbms_output.put_line('删除失败');
END IF;
end;
create table TESTA as select * from dept
--单纯的查看表,建立表,就没必要整什么存储过程,直接来下面的,简单,易懂:
1.desc table_name[or view_name]
SQL> desc t;
Object t does not exist.2.来个查询
SQL> select * from t;
ORA-00942: table or view does not exist3.若上面两步其中之一成立,就建表
SQL> create table t
2 as
3 select * from scott.emp
4 where 1=0;
Table created
SQL> desc t;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y SQL> drop table t;
Table droppedSQL> create table t
2 as
3 select * from scott.dept
4 /
Table createdSQL> select * from t;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL code
--给个例子
declare
num number;
begin
select count(1) into num from user_tables where table_name='TESTA';
--num>o表示表已经存在,先drop表
if num>0 then
execute immediate 'drop table TESTA';
end if;
execute immediate 'create table TESTA ....;
end;
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table t_test';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table t_tes as
select * from t_temp'
END;