对网站二次开发的认识,上海建设厅焊工证查询网站,网站建设分析报告,怎做连接网站一、利用游标实现循环嵌套
在对oracle数据进行操作时我们会经常碰到循环甚至循环嵌套的情况。这个时候游标的作用就体现出来了。
DECLAREvId NUMBER(19);vDate DATE;--a表游标定义CURSOR a_cursor ISSELECT DISTINCT o.employeeId FROM operations o WHERE o.employeeId IS N…一、利用游标实现循环嵌套
在对oracle数据进行操作时我们会经常碰到循环甚至循环嵌套的情况。这个时候游标的作用就体现出来了。
DECLAREvId NUMBER(19);vDate DATE;--a表游标定义CURSOR a_cursor ISSELECT DISTINCT o.employeeId FROM operations o WHERE o.employeeId IS NULL ORDER BY 1;--b表游标定义CURSOR b_cursor(eid number) ISSELECT DISTINCT trunc(o.startDate) startDateFROM operations o WHERE o.employeeId eid AND o.employeeId IS NULLORDER BY 1;
BEGIN FOR a_cur IN a_cursor LOOPvId : a_cur.receiverid;FOR b_cur IN b_cursor(vId) LOOPvDate : b_cur.startlifecycle;Employee_operationSettlement(vDate,vId);--做操作END LOOP;END LOOP;
END;二、灵活使用%ROWTYPE
DECLARE V_employee employees%ROWTYPE
BEGIN SELECT * INTO V_operationFROM employees e WHERE e.code 9999;--插入数据INSERT INTO employees (code,name,age)VALUES (9998,V_operation.name,V_operation.age);--修改数据UPDATE employees tSET ROW V_employeeWHERE e.code 9999;
END;
三、根据主键表名查询存在外键关联的表名和字段名并查询指定主键值在外键表中关联的数据条目
DECLAREVSql VARCHAR2(256);VCount NUMBER;CURSOR a_cursor isSELECT DISTINCT B.TABLE_NAME tableName,c.column_name columnNameFROM 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 OPERATIONROLESAND c.constraint_name b.constraint_nameAND (instr(B.TABLE_NAME,_)0 OR instr(B.TABLE_NAME,_CS)0)--表名过滤条件不包含‘_’,或包含‘_CS’ORDER BY 1,2;
BEGIN FOR a_cur IN a_cursor LOOPVSql : SELECT count(||a_cur.columnName||) FROM ||a_cur.tableName|| WHERE ||a_cur.columnName|| 40010;--指定值为40010EXECUTE 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_operationFROM employees e WHERE e.code 9999;--1INSERT INTO employees (code,name,age)VALUES (9998,V_operation.name,V_operation.age);--2V_operation.code : 9997;V_operation.name : 张三;INSERT INTO employeesVALUES V_operation;--3FOR a_cur IN a_cursor LOOPIF a_cur.code 9999 THENa_cur.code : 9996;a_cur.name : 李四;INSERT INTO employees VALUES a_cur;END IF;END LOOP;--4INSERT INTO Employees(code, Name, age)SELECT 9995 code, --值,别名可忽略王五 name,ageFROM Employees eWHERE 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;
