Oracle 汇总

1. 循环执行SQL

declare   
i integer;  
  begin  
  i:=0;  
  for i in 1..10 loop  
//SQL...
  end loop;     
end;  

3. 计算时间差, 向上取整

    ceil(SYSDATE - TO_DATE(TO_CHAR('2021-04-01','yyyy-mm-dd'), 'yyyy-mm-dd'))

4. 查看查询计划

   explain plan for SQL...;
   select * from table(dbms_xplan.display);

5. where in() 不能大于1000, 大于1000时, 报ORA-01795异常

6. DBLINK  数据库A想要访问数据库B中的表, 可以创建DBLINK  XXX, 然后可以使用 表名@XXX访问

7. 同义词  可以通过创建同义词访问其他用户的表, 但DBLINK是前提

8. 常用函数

(1) nvl(e1, e2) 函数, e1为null时, 返回e2

(2) start with connect by 树结构查询, 递归查询
    start with  起始条件, connect by prior 连接条件
    prior跟父节点parentid放在一起，往父节点方向遍历
    prior跟子节点subid放在一起，则往叶子节点方向遍历

(3) trunc 截取时间
    trunc(sysdate,'yyyy')，2020-1-1
    trunc(sysdate,'mm')，2020-12-1
    trunc(sysdate,'dd')，2020-12-20
    trunc(sysdate,'hh')或HH24，2020-12-19 14:00:00
    trunc(sysdate,'mi')，2020-12-19 14:30:00

(4) sysdate  获取当前时间
    sysdate+1  加一天
    sysdate+1/24  加1小时
    sysdate+1/(24*60)  加1分钟
    sysdate+1/(24*60*60)  加1秒钟

(5) numtodsinterval  时间计算,参数(day, hour, minute, second)
    select sysdate + numtodsinterval(1,'day')   from dual 加一天

(6) numtoyminterval  时间计算,参数(year, month)
    select sysdate + numtoyminterval(1,'year')  from dual 加一年

(7) nulls first  排序时, 将null值排在最前

(8) nulls last   排序时, 将null值排在最后

(9) listagg(字段名,分隔符) within group (ORDER BY 字段名)
    eg: 对应多个手机号时, 可查出多个手机号, 在一个字段中返回

(10)decode(字段/条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

(11)sign(字段)   0, 正数, 负数，分别返回0、1、-1

9. 选择表批量执行SQL语句

declare
  --需要执行的SQL字符串
  v_alter_sqlstr  varchar2(500);
  -- 游标
  cursor c_result is 
  select '
          update ' || t.OBJECT_NAME  || ' set CREATE_TIME=sysdate;
          alter table ' || t.OBJECT_NAME  || ' add UPDATE_TIME TIMESTAMP;
          comment on column ' || t.OBJECT_NAME ||'.CREATE_TIME is ''创建日期''; 
        ' as alter_sqlstr
    from user_objects t where t.OBJECT_TYPE='TABLE' and t.object_name in();
 
  -- 定义一个与游标一样的变更
  v_result c_result%rowtype;
begin
  open c_result;
    loop
      fetch c_result into v_result;  
      exit when c_result%notfound;
      v_alter_sqlstr := v_result.alter_sqlstr;
      dbms_output.put_line(v_alter_sqlstr); -- 可单独将SQL从output窗口提取出来执行
      
      -- 执行修改
      --execute immediate v_alter_sqlstr;   
    end loop;
  close c_result;
exception
  when others then
    begin
      dbms_output.put_line('异常:' || 'sqlcode:' || sqlcode || ' sqlerrm : ' ||sqlerrm );
    end;  
end

10.数据恢复

   (1)通过SCN恢复删除且已提交的数据

        select current_scn from v$database    获得当前数据库的SCN号

        select * from 表名 as of scn scn号   查询当前SCN号之前的数据

        flashback table 表名 to scn   恢复删除且已提交的数据

    (2)通过时间恢复删除且已提交的数据

         select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual   查询当前系统时间

         select * from 表名 as of timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd                 hh24:mi:ss')   查询删除数据的时间点的数据

         flashback table 表名 to timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd                     hh24:mi:ss')恢复删除且已提交的数据

出现错误, 可尝试执行  alter table 表名 enable row movement

11.修改密码

 (1) sqlplus /nolog

 (2) conn / as sysdba

 (3) alter user sys identified by 新密码