oracle、sqlserver创建表、删除表、创建索引的sql

最新推荐文章于 2024-04-17 17:53:36 发布

转角向右捡爱

最新推荐文章于 2024-04-17 17:53:36 发布

阅读量247

点赞数

本文链接：https://blog.csdn.net/zyp630998758/article/details/106716723

版权

----oracle创建表
create table TEST_TABLE01 (
           comp_code varchar2(20) not null,
           scheme_code varchar2(20) not null,
           edit_code varchar2(20) not null,
           drgs_code varchar2(20) not null,
           dept_discharge_code varchar2(20) not null,
           wk_dept_id int default 0,
           charge_detail_id int default 0 not null,
           cost_type_id char(2) not null,
           charge_kind_id int default 0 not null,
           num numeric(18,2) default 0,
           income numeric(18,4) default 0,
           cost numeric(18,6) default 0,
           c_cost numeric(18,6) default 0,
           k_cost numeric(18,6) default 0
           )

           ----SqlServer创建表
           create table TEST_TABLE02 (
           comp_code nvarchar(20) not null,
           scheme_code nvarchar(20) not null,
           his_patient_id nvarchar(40) not null,
           wk_dept_id int null,
           charge_detail_id int null,
           charge_kind_id int null,
           med_income numeric(18,6) null default 0,
           med_cost numeric(18,6) null default 0,
           med_c_cost numeric(18,6) null default 0,
           med_k_cost numeric(18,6) null default 0,
           drug_income numeric(18,6) null default 0,
           drug_cost numeric(18,6) null default 0,
           cost_num numeric(18,6) null default 0,
           mate_income numeric(18,6) null default 0,
           mate_cost numeric(18,6) null default 0
           )

       ----oracle删除表
       declare
          v_count number;
       begin
          select count(1) into v_count from user_tables t where t.TABLE_NAME = upper('TEST_TABLE02');
       if v_count > 0 then
              execute immediate 'drop table TEST_TABLE02';
          end if;
       end;

       -----SqlServer删除表
       if exists (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WITH (nolock) WHERE TABLE_NAME = 'TEST_TABLE01')
       drop table TEST_TABLE01

       ---orale创建索引
       DECLARE
               num NUMBER;
           BEGIN
               select count(1) into num from user_indexes where table_name = UPPER('TEST_TABLE01') AND index_name = 'IND_DRGS_PCD2_MUL1';
               IF num = 0 THEN
                   EXECUTE IMMEDIATE 'CREATE INDEX IND_DRGS_PCD2_MUL1 ON TEST_TABLE01(comp_code, scheme_code, his_patient_id)';
               END IF;
           END;

   ---SqlServer创建索引
   if not exists (select * from sys.sysobjects a, sys.sysindexes b where a.id = b.id and a.name = 'TEST_TABLE01' and b.name = 'IND_DRGS_PCD2_MUL1')
           CREATE CLUSTERED INDEX IND_DRGS_PCD2_MUL1 ON TEST_TABLE01(comp_code, scheme_code, his_patient_id)