oraclecursorsql
㈠ oracle数据库运行sql很卡很慢很顿，看等待事件都是cursor:pin s on x,这是啥
详解cursor: pin S wait on X等待事件 ‘cursor: pin * events’等待事件 该类等待事件一般是为了pin相关的子游标 ‘Cursor: pin S on X’ 最常见的等待事件， 进程为了共享操作例如执行pin游标而以SHRD S mode申请mutex， 但是未立即获得。原因是该游标被其他进程以EXCL X mode 持有了。 实际该 cursor: pin S wait on X等待事件往往是由于其他因素诱发的。Mutex争用仅仅是问题的症状，但根本原因需要Database Consultant 进一步挖掘。 下面我们列出一些已知的常见案例， 在这些例子中可以看到 我上面提到的 Mutex的争用仅仅是伪争用: 过多的子游标 High Version Counts 过多的子游标版本Version Count可能导致Mutex 争用，一般一个SQL的Version Count不要高于500。 检查High Version Count很简单， 在AWR里就有SQL ordered by High Version Count，也可以写SQL查V$SQL、V$SQLAREA 昂贵的X$、V$视图查询 一些对于V$、X$视图的查询，需要访问X$KGL*之类的fixed table，可能触发Mutex争用。 Mutex持有者得不到CPU Mutex持有者若得不到足够的CPU片可能一直阻塞他人，直到它拿到需要的CPU。 这种情况可能由于OS操作系统的实际情况或者使用Resource Manager而引起。需要配合AWR中的Host CPU、Instance CPu一起看。 已经被KILLED的SESSION仍持有Mutex 当session正持有Mutex，而其对应的Process被强制KILL掉， 则直到PMON彻底清理掉该Dead Process并释放Mutex，其他session才能不再等待。 诊断该类问题，最好能检查PMON的TRACE。 当然也存在部分BUG会导致PMON清理过程非常慢。 举例来说，bug 9312879描述了一种场景：PMON 需要获得某个Mutex以便清理某个dead process，但是该Mutex又被其他进程持有，则PMON甚至无法开始真正清理并释放Mutex。 如果自己搞不定可以找ASKMACLEAN专业ORACLE优化团队成员帮您搞定!
㈡ Oracle下如何用sql创建游标
For 循环游标
（1）定义游标
（2）定义游标变量
（3）使用for循环来使用这个游标
declare
--类型定义
cursorc_job
is
selectempno,ename,job,sal
fromemp
wherejob='MANAGER';
--定义一个游标变量v_cinfoc_emp%ROWTYPE，该类型为游标c_emp中的一行数据类型
c_rowc_job%rowtype;
begin
forc_rowinc_jobloop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
endloop;
end;
Fetch游标
使用的时候必须要明确的打开和关闭
declare
--类型定义
cursorc_job
is
selectempno,ename,job,sal
fromemp
wherejob='MANAGER';
--定义一个游标变量
c_rowc_job%rowtype;
begin
openc_job;
loop
--提取一行数据到c_row
fetchc_jobintoc_row;
--判读是否提取到值，没取到值就退出
--取到值c_job%notfound是false
--取不到值c_job%notfound是true
exitwhenc_job%notfound;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
endloop;
--关闭游标
closec_job;
end;
㈢ oracle存储过程循环执行SQL语句
实现方式错了，批量移动数据应该使用Cursor，而不是像分页那样每次都查询。
每次都查询可能会导致重复数据。
正确方式应该是打开一个Cursor，循环Cursor来插入，使用计数器来控制每次COMMIT的行数：
declare
TYPE R_CURSOR IS REF CURSOR;
i number;
a1_cursor R_CURSOR;
a1_row A1%ROWTYPE;
begin
open a1_cursor FOR
select ID, NAME from A1;
i := 0;
loop
fetch a1_cursor
into a1_row;
exit when a1_cursor%notfound;
INSERT INTO A2 VALUES a1_row;
i := i + 1;
if i >= 5 then
commit;
i := 0;
end if;
end loop;
close a1_cursor;
commit;
end;
㈣ Oracle游标sql语句代码块的优化
游标操作的优化：
-- 有一个表格，存储的是用户的名字，将 emp 表中所有的用户名转换成小写，再写入这个表格
create table ename_emp(
ename varchar2(50)
);
-- 下面这个游标的操作，需要 43s时间才能完成
declare
begin
for i in (select ename from emp_liebiao) loop
insert into ename_emp values(lower(i.ename));
commit;
end loop;
end;
-- 因为游标是以行为单位进行数据的操作的，所有游标的效率是比较慢的，而且游标需要消耗的内存也是比较多的，我们需要将游标以行进行操作的方式，修改成一次性操作所有数据的批量的方式。
批量操作在游标里面 叫做 bulk collect
第一步，创建一个表类型
type 表类型的名字 is table of 表名.列名 %type;
变量名 表类型的名字;
第三步，创建一个游标，读取某个查询的结果
cursor 游标名字 is select 查询语句 ;
第四步，打开游标
open 游标名字;
第五步，捕获游标的数据，将内容给到表类型的变量进行保存
fetch 游标名字 bulk collect into 变量名字 ;
第六步，使用 forall 语句，对数据进行批量的操作
forall i in 变量 .first .. 变量 .last DML 语句操作 ;
第七步，关闭游标
close 游标名字 ;
declare
-- 创建表类型
type biao is table of emp_liebiao.ename%type;
b biao;
-- 创建游标
cursor m is select ename from emp_liebiao;
begin
-- 打开游标
open m;
-- 将游标的内容批量的给到变量
fetch m bulk collect into b;
-- 使用forall批量修改数据
forall i in b.first .. b.last insert into ename_emp values(lower(b(i)));
commit;
-- 关闭游标
close m;
end;
练习：批量修改用户的编号，让编号+工资等级+部门，形成一个新的编号，存入下面的表格中，使用 bulk collect 来实现。
2000以下是 C ，2000-3000是 B ，3000以上是 A ，
例如 SMITH ， 新编号应该是 7369_C_20
create table empno_emp(
empno varchar2(50)
);
declare
type biao is table of emp_liebiao%rowtype;
b biao;
cursor m is select * from emp_liebiao;
begin
open m;
fetch m bulk collect into b;
forall i in b.first..b.last
insert into empno_emp values(
b(i).empno||'_'||decode(sign(b(i).sal-2000)+sign(b(i).sal-3000),-2,'C',2,'A','B')||'_'||b(i).deptno
);
commit;
close m;
end;