MENU

创建表

// 从查询中创建
create table [table_name] as
select [字段]
from [table_name]
where [条件]
// 根据字段创建空表
create table tb_ljt_tmp(序号 varchar2(20), 姓名 varchar2(128), 部门 varchar2(128))

基本操作

  • 修改表字段类型
alter table table_name modify 字段名 字段类型;
  • 创建存储过程自动更新表
CREATE OR REPLACE PROCEDURE name
as

drop与delete的区别

drop table 是删除表定义

delete table 是删除表中的数据

多个字段的模糊匹配

REGEXP_LIKE(字段, '条件1|条件2')
-- 相当于
(字段 like '%条件1%' or 字段 like '%条件2%')

一个例子

CREATE OR REPLACE PROCEDURE update_some_table
as
    beg_time date;
    v_code varchar2(800);
    v_msg varchar2(800);
    v_sql  varchar2(1800);
    v_count number ;
begin
    beg_time := SYSDATE;

select count(1) into v_count from user_tables where table_name ='table_a';
if v_count > 0 then 
    v_sql := 'drop table table_a';
    execute immediate v_sql;
end if;

v_sql := '
    create table table_a as
      select a.some_id,
             a.id,
             a.some_code,
             b.accept_date,
             b.depart_id,
             b.staff_id
        from table_b a, table_c b, table_d c
       where a.some_id = b.some_id(+)
         and a.some_code = c.some_code(+)
         and a.some_code in ('' A '', '' B '', '' C '', '' D '')
         and to_char(b.accept_date, '' yyyymmdd '') >=
             to_char(sysdate - 3, '' yyyymmdd '')';

execute immediate v_sql;
insert into log_work(v_module, sub_mod, remarks, oper_date, end_time)
    values('update_some_table', 'table_a', '临时表', beg_time, sysdate);
commit;
beg_time := SYSDATE;
v_sql := '
    insert into tb_discnt_user_gjhd
      select aa.*
        from (select distinct d.*,
                              b.discnt_name,
                              c.serial_number,
                              e.depart_name,
                              f.staff_name
                from table_d b,
                     table_f c,
                     table_a d,
                     table_g e,
                     table_h f
               where d.some_code = b.some_code(+)
                 and d.id = c.user_id(+)
                 and d.depart_id = e.depart_id(+)
                 and d.staff_id = f.staff_id(+)) aa,
             tb_discnt_user_gjhd bb
       where aa. some_id = bb.some_id(+)
         and bb.some_id is null';

execute immediate v_sql;
commit;
insert into log_work
  (v_module, sub_mod, remarks, oper_date, end_time)
values
  ('update_some_table', 'table_a', '临时表', beg_time, sysdate);
commit;

exception
    when others then
    v_code := SQLCODE;
    v_msg := SQLERRM;
    ROLLBACK;
    insert into err_log(err_proc, err_code, err_msg, err_time) 
    values('update_some_table', v_code, v_msg, sysdate);
    commit;
    -- ErrorPkg.HandleAll(False);
    Raise;
end;

判断一个表是否存在,并删除

select count(1) into v_count from user_tables
where table_name ='table_name';
if v_count > 0 then
    v_sql := 'drop table table_name';
    execute immediate v_sql;
end if;

剔重

delete from table_name
 where rowid in (select row_id
                   from (select rowid row_id,
                                row_number() over(partition by 字段名 order by rowid) rn
                           from table_name)
                  where rn <> 1);