点点滴滴(Oracle)

insert into
    select * into t_dest from t_src; -- 要求目标表不存在
    insert into t_dest(a, b) select a, b from t_src; -- 要求目标表已存在

动态SQL
    execute immediate 'select ' || sq_serialnum || '.nextval from dual' into i_serialnum;

按照拼音,部首,笔画排序
    下面的方法需要ORACLE9i和以上的版本才支持.
    Oracle9i之前, 中文是按照二进制编码进行排序的.
    在oracle9i中新增了按照拼音,部首,笔画排序功能. 设置NLS_SORT值
    SCHINESE_RADICAL_M 按照部首(第一顺序),笔划(第二顺序)排序
    SCHINESE_STROKE_M 按照笔划(第一顺序),部首(第二顺序)排序
    SCHINESE_PINYIN_M 按照拼音排序, 系统的默认排序方式为拼音排序
    select * from table_name order by nlssort(field_name, 'nls_sort=SCHINESE_STROKE_M');

取毫秒(oracle 9i +)
    select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff3') from dual;

like查询下划线本身
    select * from test where columnname like 'a\_%' escape '\';
    -- 表示将紧跟在escape字符后的字符解释为字符本身, 而非转义字符

日期减去一秒
    select sysdate,sysdate - interval '1' second from dual;

重命名
    rename oldname to newname; -- 重命名table/sequence/view

修改表结构
    alter table t_employee add (remark varchar2(50)); -- 增加列 
    alter table t_employee modify (sex default 0); -- 修改列的默认值
    alter table t_employee modify (remark null); -- 修改列可为空
    alter table t_employee rename to t_employee_new; -- 修改表名
    alter table t_employee rename column remark to description; --修改列名
    alter table t_employee add constraint unq_code unique(code); -- 增加唯一索引
    alter table t_employee add constraint pk_employee_id primary key (employee_id); -- 增加主键
    alter table t_employee drop constraint pk_employee_id; -- 删除约束

增加注释
    comment on table <table_name> is '<comment>'; -- 为表增加注释
    select table_name, comments from user_tab_comments; -- 'user_tab_comments'为系统表
    comment on column <table_name.column_name> is '<comment>'; -- 为表的列增加注释
    select table_name, column_name, comments from user_col_comments; -- 'user_col_comments'为系统表

统计表记录
    declare
        cnt number;
    begin
        dbms_output.put_line(rpad('table_name',35)||rpad('records',20));
        dbms_output.put_line(rpad('---------------',35)||rpad('--------------',20) );
        for rec in(select table_name from user_tables order by table_name) loop
            execute immediate 'select count(1) from '||rec.table_name into cnt;
            dbms_output.put_line(rpad(lower(rec.table_name),35)||rpad(cnt,20));
        end loop;
    end;
    /

杀进程
    select * from v$session where username='NAME'; -- 查询sid,serial#
    alter system kill session 'sid,serial#';

返回码变换
    return case v_rtn when 1 then 11 when 2 then 21 else v_rtn end;

判断日期格式
    create or replace function ...
        ( i_issuetime in varchar2)
    return ...
    as
        fmt constant varchar2(100) := 'yyyy-mm-dd hh24:mi:ss'; -- 声明常量
        v_issuetime date;
    begin
        begin
            v_issuetime := to_date(i_issuetime, fmt);
        exception
            when others then return -1; -- 解析发布时间出现异常
        end;
        ...
    exception
        ...
    end ...;

创建JOB并运行
    declare
        jobnum dba_jobs.job%type;
    begin
        sys.dbms_job.submit(:jobnum,
            'p_clear_expired_data(''t_receiverlog'',''logtime'',100);',
            sysdate,
            'trunc(sysdate+1,''dd'')+2/24' -- 每天凌晨2点运行1次
        );
        sys.dbms_job.run(:jobnum);
    end;
    /

修改JOB
    declare
        jobnum dba_jobs.job%type;
    begin
        select min(job) into jobnum from dba_jobs where what like 'p_clear_expired_data%';
        sys.dbms_job.change(jobnum,
            'p_clear_expired_data(''t_receiverlog'',''logtime'',100);' ||
            'p_clear_expired_data(''t_operatelog'', ''logtime'',100);',
            sysdate,
            'trunc(sysdate+1,''dd'')+2/24' -- 每天凌晨2点运行1次
        );
        commit;
    end;
    /

PL/SQL登录项配置
    $oracle$/ora90/network/admin/tnsnames.ora文件:
    colorring =
    (description =
        (address_list =
            (address = (protocol = tcp)(host = 192.168.1.1)(port = 1521))
        )
        (connect_data =
            (server = dedicated)
            (service_name = xxx)
        )
    )