Oracle——疑难杂症总结

1、给已有数据的表新增和添加SYS_GUID主键

--先新增一个可为空PID字段，然后将其更新为行号ROWNUM
UPDATE TABLE SET PID = ROWNUM

--循环所有的行号（其中123为最大行号数），然后将PID更新为SYS_GUID
BEGIN
  FOR I IN 1 .. 123 LOOP
    UPDATE TABLE SET PID = SYS_GUID() WHERE PID = TO_CHAR(I);
  END LOOP;
END;

--最后将PID字段设置为不可为空和主键

2、行转列

--使用pivot方法
select * from Table_A pivot ( max(score) for subject in ('语文','数学','英语') )

3、列转行

--使用unpivot方法
select name,subject,score from Table_B unpivot (score for subject in (chinese,math,english))

4、捕获异常的错误代码、错误行号、错误信息

WHEN OTHERS THEN
  v_ErrorCode := SQLCODE;--异常代码
  v_ErrorRow := DBMS_UTILITY.format_error_backtrace;--出错行号
  v_ErrorText := SUBSTR(SQLERRM, 1, 200); --异常信息

5、查找存储过程的编译错误

select * from SYS.USER_ERRORS where NAME = '存储过程名' and type = 'PROCEDURE'

6、生成动态表名

declare 
  v_date varchar2(8);--定义日期变量（年月日，如20200101）
  v_sql varchar2(2000);--定义动态sql
  v_tablename varchar2(20);--定义动态表名
begin
  select to_char(sysdate,'yyyymmdd') into v_date from dual;--取日期变量
  v_tablename := 'T_'||v_date;--为动态表命名
  v_sql := 'create table '||v_tablename||'
  (id int,
  name varchar2(20))';--为动态sql赋值
  dbms_output.put_line(v_sql);--打印sql语句
  execute immediate v_sql;--执行动态sql
end;

7、更新前100行数据

update top(100) tableName set Column=?

update table set 字段=值 where id in (select top 100 from table)

8、锁表问题的查看和解锁

--查询被锁的会话ID
select session_id from v$locked_object;
--查询结果：SESSION_ID-------9
     
--查询上面会话的详细信息：
SELECT sid, serial#, username, osuser FROM v$session where sid = 9;
--查询结果：serial#------99
     
--将上面锁定的会话关闭：
ALTER SYSTEM KILL SESSION '99';

9、查询导致数据库锁表的SQL

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
 where l.session_id = s.sid
   and s.prev_sql_addr = a.address
 order by sid, s.serial#;

10、复制表结构和备份表数据

--1=2表示赋值表结构（不包含创建主键等信息）
create table 用户名.表名 as select * from 用户名.表名 where 1=2

--1=1表示备份表数据
create table 用户名.表名 as select * from 用户名.表名 where 1=1

11、查看某个表上绑定有哪些触发器

select * from user_triggers where table_owner = 'xxx' and table_name = upper('table_name'); 

12、自治事务：用于记录日志、触发器

declare 
  pragma autonomous_transaction;
  ...
begin
  ...
  commit;（必须）
  ...
end;

13、Job的编写——每日执行一次存储过程

create or replace procedure proc_auto_exec_job as  
begin  
  declare  
    job number;  
    BEGIN  
      DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'proc_test_job;',  /*需要执行的过程或SQL语句*/  
        /*NEXT_DATE => sysdate, */ /*初次执行时间，立刻执行*/  
        /*INTERVAL => 'sysdate+3/(24*60*60)' */ /*执行周期 -每3秒钟*/  
        NEXT_DATE => TRUNC(SYSDATE+1)+(0*60+30)/(24*60),  /*初次执行时间，12点30分*/  
        INTERVAL => 'TRUNC(SYSDATE+1)+(0*60+30)/(24*60)'  /*每天12点30分*/  
      );    
      COMMIT;  
  
      /*dbms_job.submit(job, 'proc_test_job;', sysdate, 'trunc(sysdate,''mi'')+1/(24*60)'); /*执行周期 -每1分钟*/  
      commit;*/  
        
      DBMS_JOB.RUN(job);  
    end;  
end proc_auto_exec_job;  
begin proc_auto_exec_job; end;

14、数据库连接串

--测试<OwnerSqlConn Desp="公用库数据库串"> Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 6521))
      (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = wip2) ) );User ID=meimei;PassWord=123456</OwnerSqlConn>
--正式<OwnerSqlConn Desp="公用库数据库串"> Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 1521))
      (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = wip) ) );User ID=meimei;PassWord=123456</OwnerSqlConn>

15、触发器的创建示例

create or replace trigger TIG_T_RES_JOURNAL
before delete or update
on T_RES_JOURNAL
for each row
declare 
    pragma autonomous_transaction;
begin
    if deleting then
        insert into T_RES_JOURNAL_LOG (select * from T_RES_JOURNAL where id = :old.id);
        commit;
    elsif updating then
        insert into T_RES_JOURNAL_LOG (select * from T_RES_JOURNAL where id = :old.id);
        commit;
    end if;  
end TIG_T_RES_JOURNAL;