oracle脚本书写
一、建表脚本：
1 DECLARE 2 V_COUNT NUMBER; 3 V_SQL VARCHAR2(8000); 4 BEGIN 5 SELECT COUNT(1) INTO V_COUNT FROM USER_TABLES T WHERE T.TABLE_NAME='TABLE_ORDER'; 6 IF V_COUNT <= 0 THEN 7 V_SQL := 'create table TABLE_ORDER' || 8 '(' || 9 'SID NUMBER(16) not null,' || 10 'ORDER_ID VARCHAR2(20) not null,' || 11 'ORDER_TYPE CHAR(10)' || 12 ')'; 13 EXECUTE IMMEDIATE V_SQL; 14 EXECUTE IMMEDIATE 'comment on table TABLE_ORDER is ''订单表'''; 15 EXECUTE IMMEDIATE 'comment on column TABLE_ORDER.SID is ''序号'''; 16 EXECUTE IMMEDIATE 'comment on column TABLE_ORDER.ORDER_ID is ''订单号'''; 17 EXECUTE IMMEDIATE 'comment on column TABLE_ORDER.ORDER_TYPE is ''订单类型（A-生活用品，B-办公用品）'''; 18 EXECUTE IMMEDIATE 'alter table TABLE_ORDER add constraint PK_TABLE_ORDER primary key (ORDER_ID, ORDER_TYPE)'; 19 DBMS_OUTPUT.PUT_LINE('TABLE_ORDER表创建成功'); 20 ELSE 21 DBMS_OUTPUT.PUT_LINE('TABLE_ORDER已存在'); 22 END IF; 23 24 -- TABLE_ORDER 新增 UPDATETIME 字段 25 SELECT COUNT(1) INTO V_COUNT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='TABLE_ORDER' AND T.COLUMN_NAME='UPDATETIME'; 26 IF V_COUNT <= 0 THEN 27 EXECUTE IMMEDIATE 'ALTER TABLE TABLE_ORDER ADD UPDATETIME VARCHAR2(30) NULL'; 28 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TABLE_ORDER.UPDATETIME IS ''更新日期'''; 29 END IF; 30 31 -- TABLE_ORDER 修改 ORDER_ID 类型和长度 32 SELECT COUNT(1) INTO V_COUNT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='TABLE_ORDER' AND T.COLUMN_NAME='ORDER_ID'; 33 IF V_COUNT > 0 THEN 34 EXECUTE IMMEDIATE 'ALTER TABLE TABLE_ORDER MODIFY (ORDER_ID NUMBER(30))'; 35 END IF; 36 END; 37 /
二、建视图脚本：
1 -- 视图1 2 CREATE OR REPLACE VIEW VIEW_TEACHER AS 3 SELECT 4 'Tom' AS TEACHER_NAME, 5 2 AS TEACHER_CLASS, 6 'CHINA' AS TEACHER_COUNTRY 7 FROM DUAL; 8 /
脚本可以保存到 .sql 文件，方便执行
三、批量删除指定前缀的表：
1 --删除指定前缀的所有表 2 declare 3 begin 4 for vcur in (select t.TABLE_NAME from user_tables t where t.TABLE_NAME like 'PREFIX_NAME%') 5 loop 6 execute immediate 'drop table ' || vcur.table_name; 7 end loop; 8 commit; 9 end;
