Oracle开发常用知识

一、利用游标实现循环嵌套

在对oracle数据进行操作时我们会经常碰到循环甚至循环嵌套的情况。这个时候游标的作用就体现出来了。

DECLARE
  vId NUMBER(19);
  vDate DATE;
  --a表游标定义
  CURSOR a_cursor IS
    SELECT DISTINCT o.employeeId 
    FROM operations o 
    WHERE o.employeeId IS NULL 
    ORDER BY 1;
  --b表游标定义
  CURSOR b_cursor(eid number) IS
    SELECT DISTINCT trunc(o.startDate) startDate
    FROM operations o 
    WHERE o.employeeId = eid 
    AND o.employeeId IS NULL
    ORDER BY 1;
BEGIN 
  FOR a_cur IN a_cursor LOOP
    vId := a_cur.receiverid;
    FOR b_cur IN b_cursor(vId) LOOP
      vDate := b_cur.startlifecycle;
      Employee_operationSettlement(vDate,vId);--做操作
    END LOOP;
  END LOOP;
END;

二、灵活使用%ROWTYPE

DECLARE   
  V_employee employees%ROWTYPE 
BEGIN   
  SELECT * INTO V_operation
  FROM employees e 
  WHERE e.code = '9999';
  --插入数据
  INSERT INTO employees (code,name,age)
  VALUES ('9998',V_operation.name,V_operation.age);
  --修改数据
  UPDATE employees t
  SET ROW = V_employee
  WHERE e.code = '9999';
END; 

三、根据主键表名查询存在外键关联的表名和字段名，并查询指定主键值在外键表中关联的数据条目

DECLARE
  VSql VARCHAR2(256);
  VCount NUMBER;
  CURSOR a_cursor is
    SELECT DISTINCT 
    B.TABLE_NAME tableName,c.column_name columnName
    FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B, USER_CONS_COLUMNS C  
    WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME  
    AND B.CONSTRAINT_TYPE = 'R'  
    AND a.TABLE_NAME = 'OPERATIONROLES'
    AND c.constraint_name = b.constraint_name
    AND (instr(B.TABLE_NAME,'_')=0 OR instr(B.TABLE_NAME,'_CS')>0)--表名过滤条件（不包含‘_’,或包含‘_CS’）
    ORDER BY 1,2;
BEGIN 
  FOR a_cur IN a_cursor LOOP
    VSql := 'SELECT count('||a_cur.columnName||') FROM '||a_cur.tableName||' WHERE '||a_cur.columnName||' = 40010';--指定值为（40010）
    EXECUTE IMMEDIATE VSql INTO VCount;
    IF VCount > 0 THEN --只看存在数据的条目
      DBMS_OUTPUT.put_line(VSql||'____'||VCount);--输出（SQl____对应数据条数）
    END IF;
  END LOOP;
END;

四、递归查询

--寻根
SELECT o.* 
FROM PartyRoleAssociations p,ORGANIZATIONUNITS o
WHERE p.partyroleid = o.id
AND LEVEL = 1 --层级
START WITH p.associationpartyroleid = '131000755'--开始子节点ID
CONNECT BY p.associationpartyroleid = PRIOR p.partyroleid;


---自顶向下
SELECT o.* 
FROM PartyRoleAssociations p,ORGANIZATIONUNITS o
WHERE p.associationpartyroleid = o.id
START WITH p.associationpartyroleid = '131000161'--父级节点ID
CONNECT BY PRIOR p.associationpartyroleid = p.partyroleid;

五、通过group by和wm_concat来快速定位数据

SELECT o.employeeId, wm_concat(o.name) --行列转换
FROM operations o 
GROUP BY o.employeeId;

六、依照模板数据快速插入数据，特别适用于表字段特别多的情况

DECLARE   
  V_employee employees%ROWTYPE;
  CURSOR a_cursor IS SELECT * FROM employees;
BEGIN   
  SELECT * INTO V_operation
  FROM employees e 
  WHERE e.code = '9999';
  --1
  INSERT INTO employees (code,name,age)
  VALUES ('9998',V_operation.name,V_operation.age);
  --2
  V_operation.code := '9997';
  V_operation.name := '张三';
  INSERT INTO employees
  VALUES V_operation;
  --3
  FOR a_cur IN a_cursor LOOP
    IF a_cur.code = '9999' THEN
       a_cur.code := '9996';
       a_cur.name := '李四';
       INSERT INTO employees 
       VALUES a_cur;
    END IF;
  END LOOP;
  --4
  INSERT INTO Employees
    (code, Name, age)
    SELECT '9995' code, --值,别名（可忽略）
           '王五' name,
           age
    FROM Employees e
    WHERE e.code = '9999';
END; 

七、快速定位存储过程

在项目开发中，经常遇到这样的情况，现在需要改动一个表、函数或者存储过程（简称为对象），但是不知道这个对象被哪些函数、定时任务或存储过程调用，便可利用以下SQL解。

SELECT DISTINCT t.type "类型", t.name "名称"
FROM User_Source t
WHERE t.type = 'PROCEDURE' --FUNCTION(函数),PROCEDURE(存储过程),TRIGGER(触发器)
AND LOWER(t.text) LIKE '%string%' --string英文小写
UNION ALL
SELECT DISTINCT 'JOB' "类型", j.JOB_NAME "名称" FROM user_scheduler_jobs j
WHERE LOWER(j.JOB_ACTION) LIKE '%string%';--查询定时任务ACTION中是否包含

八、中断ORACLE的JOB

网上查了一下，大多是通过dba_jobs_running去查SID，发现查不到，后来发现是可以通过dba_scheduler_running_jobs去查SID的

SELECT t.session_id SID FROM dba_scheduler_running_jobs t;
SELECT SID,SERIAL#  FROM V$Session WHERE SID = '1018';
ALTER SYSTEM KILL SESSION '1018,127';