Oracle 动态游标和静态游标区别（性能解析效率打开速度联接速度）

朴灿烈づ我的快乐病毒、 2022-04-02 09:47 290阅读 0赞

# 静态游标和动态游标 #

2012年01月17日 23:33:42 [孔乙已][Link 1] 阅读数：6821 标签： [数据库][Link 2][insert][][delete][][c][][table][][null][] 更多

个人分类： [SQL SERVER][]

静态游标在打开时会将数据集存储在**tempdb中，因此显示的数据与游标打开时的数据集保持一致，在游标打开以后对数据库的更新不会显示在游标中。**

** **

**动态游标在打开后会反映对数据库的更改。所有**UPDATE、INSERT 和 DELETE 操作都会显示在游标的结果集中，结果集中的行数据值、顺序和成员在每次提取时都会改变。

在定义游标的时候如果不指定STATIC关键字，默认是DYNAMIC的。

动态游标的打开速度比静态游标的打开速度快。当打开静态游标时，必须生成内部临时工作表，而动态游标则不需要。

在联接中，静态游标的速度可能比动态游标的速度快。因为动态游标在滚动时反应对结果集内的各行数据所做的更改，它会消耗资源去检测基表的更改，因此对于复杂的查询，且不需要反映基表的更新的游标的处理应将其定义为静态游标。

如果要进行绝对提取，必须使用由键集驱动的游标或静态游标。

在使用动态游标时，应该注意行数据的更新所导致的死循环等问题，如下表所示：

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER ON

CREATE TABLE \[dbo\].\[Test\](

\[ID\] \[int\] NOT NULL,

CONSTRAINT \[PK\_Test\] PRIMARY KEY CLUSTERED (\[ID\] ASC)) ON \[PRIMARY\]

INSERT INTO Test VALUES(1)

DECLARE C$Cursor Cursor

FOR

SELECT ID From Test Where ID < 10

DECLARE @id INT

OPEN C$Cursor

Fetch  C$Cursor INTO @id

WHILE @@FETCH\_STATUS = 0

BEGIN

UPDATE Test SET ID = ID + 1 WHERE id = @id

Fetch  C$Cursor INTO @id

END

CLOSE C$Cursor

DEALLOCATE C$Cursor

执行完后表中的数值变成了10，代表更新行执行了多次。如果把游标的定义改成如下：

DECLARE C$Cursor Cursor STATIC

FOR

SELECT ID From Test Where ID < 10

则执行结果为2

[https://blog.csdn.net/liu\_1983/article/details/7208144][https_blog.csdn.net_liu_1983_article_details_7208144]

# 数据库里面的游标,动态游标和静态游标区别 #

2016年12月08日 19:28:18 [SCQshine][] 阅读数：1811 标签： [sql][][游标][Link 3][数据库][Link 2][oracle][][java][] 更多

个人分类： [Oracle+][Oracle]

## 游标 ##

静态游标指的是程序执行的时候不需要再去解析sql语言，对于sql语句的解析在编译的时候就可以完成的。动态游标由于含有参数，对于sql语句的解析必须要等到参数确定的时候才能完成。从这个角度来说，静态游标的效率也比动态游标更高一些。   
静态游标又分为显式游标和隐式游标:简单来说静态游标,在程序执行的时候,就已经编译完了sql语句,所以在执行该游标里面的sql语句的时间就不需要再编译sql语句了,   
动态游标分强类型游标和弱类型游标,这个由于里面带参数,所以sql语句不会在一开始就编译,要等到调用这个游标的时间,参数确定后才编译,相比效率是底了一点,但是用户一般查询的时候都是需要参数的,

– 声明游标；CURSOR cursor\_name IS select\_statement

–For 循环游标   
–（1）定义游标   
–（2）定义游标变量   
–（3）使用for循环来使用这个游标   
declare   
–类型定义   
cursor c\_job   
is   
select empno,ename,job,sal   
from emp   
where job=’MANAGER’;   
–定义一个游标变量v\_cinfo c\_emp%ROWTYPE ，该类型为游标c\_emp中的一行数据类型   
c\_row c\_job%rowtype;   
begin   
for c\_row in c\_job loop   
dbms\_output.put\_line(c\_row.empno||’-‘||c\_row.ename||’-‘||c\_row.job||’-‘||c\_row.sal);   
end loop;   
end;

–Fetch游标   
–使用的时候必须要明确的打开和关闭

declare   
–类型定义   
cursor c\_job   
is   
select empno,ename,job,sal   
from emp   
where job=’MANAGER’;   
–定义一个游标变量   
c\_row c\_job%rowtype;   
begin   
open c\_job;   
loop   
–提取一行数据到c\_row   
fetch c\_job into c\_row;   
–判读是否提取到值，没取到值就退出   
–取到值c\_job%notfound 是false   
–取不到值c\_job%notfound 是true   
exit when c\_job%notfound;   
dbms\_output.put\_line(c\_row.empno||’-‘||c\_row.ename||’-‘||c\_row.job||’-‘||c\_row.sal);   
end loop;   
–关闭游标   
close c\_job;   
end;

–1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。   
begin   
update emp set ENAME=’ALEARK’ WHERE EMPNO=7469;   
if sql%isopen then   
dbms\_output.put\_line(‘Openging’);   
else   
dbms\_output.put\_line(‘closing’);   
end if;   
if sql%found then   
dbms\_output.put\_line(‘游标指向了有效行’);–判断游标是否指向有效行   
else   
dbms\_output.put\_line(‘Sorry’);   
end if;   
if sql%notfound then   
dbms\_output.put\_line(‘Also Sorry’);   
else   
dbms\_output.put\_line(‘Haha’);   
end if;   
dbms\_output.put\_line(sql%rowcount);   
exception   
when no\_data\_found then   
dbms\_output.put\_line(‘Sorry No data’);   
when too\_many\_rows then   
dbms\_output.put\_line(‘Too Many rows’);   
end;   
declare   
empNumber emp.EMPNO%TYPE;   
empName emp.ENAME%TYPE;   
begin   
if sql%isopen then   
dbms\_output.put\_line(‘Cursor is opinging’);   
else   
dbms\_output.put\_line(‘Cursor is Close’);   
end if;   
if sql%notfound then   
dbms\_output.put\_line(‘No Value’);   
else   
dbms\_output.put\_line(empNumber);   
end if;   
dbms\_output.put\_line(sql%rowcount);   
dbms\_output.put\_line(‘————-‘);

select EMPNO,ENAME into  empNumber,empName from emp where EMPNO=7499;
                 dbms_output.put_line(sql%rowcount);
    
                if sql%isopen then
                dbms_output.put_line('Cursor is opinging');
                else
                dbms_output.put_line('Cursor is Closing');
                end if;
                 if sql%notfound then
                 dbms_output.put_line('No Value');
                 else
                 dbms_output.put_line(empNumber);
                 end if;
                 exception 
                   when no_data_found then
                     dbms_output.put_line('No Value');
                     when too_many_rows then
                       dbms_output.put_line('too many rows');
                       end;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19

–2,使用游标和loop循环来显示所有部门的名称   
–游标声明   
declare   
cursor csr\_dept   
is   
–select语句   
select DNAME   
from Depth;   
–指定行指针,这句话应该是指定和csr\_dept行类型相同的变量   
row\_dept csr\_dept%rowtype;   
begin   
–for循环   
for row\_dept in csr\_dept loop   
dbms\_output.put\_line(‘部门名称:’||row\_dept.DNAME);   
end loop;   
end;

–3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）   
declare   
–游标声明   
cursor csr\_TestWhile   
is   
–select语句   
select LOC   
from Depth;   
–指定行指针   
row\_loc csr\_TestWhile%rowtype;   
begin   
–打开游标   
open csr\_TestWhile;   
–给第一行喂数据   
fetch csr\_TestWhile into row\_loc;   
–测试是否有数据，并执行循环   
while csr\_TestWhile%found loop   
dbms\_output.put\_line(‘部门地点：’||row\_loc.LOC);   
–给下一行喂数据   
fetch csr\_TestWhile into row\_loc;   
end loop;   
close csr\_TestWhile;   
end;   
select \* from emp

–4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)   
–CURSOR cursor\_name\[(parameter\[,parameter\],…)\] IS select\_statement;   
–定义参数的语法如下:Parameter\_name \[IN\] data\_type\[\{:=|DEFAULT\} value\]

declare   
CURSOR   
c\_dept(p\_deptNo number)   
is   
select \* from emp where emp.depno=p\_deptNo;   
r\_emp emp%rowtype;   
begin   
for r\_emp in c\_dept(20) loop   
dbms\_output.put\_line(‘员工号：’||r\_emp.EMPNO||’员工名：’||r\_emp.ENAME||’工资：’||r\_emp.SAL);   
end loop;   
end;   
select \* from emp   
–5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)   
declare   
cursor   
c\_job(p\_job nvarchar2)   
is   
select \* from emp where JOB=p\_job;   
r\_job emp%rowtype;   
begin   
for r\_job in c\_job(‘CLERK’) loop   
dbms\_output.put\_line(‘员工号’||r\_job.EMPNO||’ ‘||’员工姓名’||r\_job.ENAME);   
end loop;   
end;   
SELECT \* FROM EMP

–6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来   
create table emp1 as select \* from emp;

declare   
cursor   
csr\_Update   
is   
select \* from emp1 for update OF SAL;   
empInfo csr\_Update%rowtype;   
saleInfo emp1.SAL%TYPE;   
begin   
FOR empInfo IN csr\_Update LOOP   
IF empInfo.SAL<1500 THEN   
saleInfo:=empInfo.SAL\*1.2;   
elsif empInfo.SAL<2000 THEN   
saleInfo:=empInfo.SAL\*1.5;   
elsif empInfo.SAL<3000 THEN   
saleInfo:=empInfo.SAL\*2;   
END IF;   
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr\_Update;   
END LOOP;   
END;

–7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)   
declare   
cursor   
csr\_AddSal   
is   
select \* from emp1 where ENAME LIKE ‘A%’ OR ENAME LIKE ‘S%’ for update OF SAL;   
r\_AddSal csr\_AddSal%rowtype;   
saleInfo emp1.SAL%TYPE;   
begin   
for r\_AddSal in csr\_AddSal loop   
dbms\_output.put\_line(r\_AddSal.ENAME||’原来的工资:’||r\_AddSal.SAL);   
saleInfo:=r\_AddSal.SAL\*1.1;   
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr\_AddSal;   
end loop;   
end;   
–8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500   
declare   
cursor   
csr\_AddComm(p\_job nvarchar2)   
is   
select \* from emp1 where JOB=p\_job FOR UPDATE OF COMM;   
r\_AddComm emp1%rowtype;   
commInfo emp1.comm%type;   
begin   
for r\_AddComm in csr\_AddComm(‘SALESMAN’) LOOP   
commInfo:=r\_AddComm.COMM+500;   
UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr\_AddComm;   
END LOOP;   
END;

–9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）   
–（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）   
declare   
cursor crs\_testComput   
is   
select \* from emp1 order by HIREDATE asc;   
–计数器   
top\_two number:=2;   
r\_testComput crs\_testComput%rowtype;   
begin   
open crs\_testComput;   
FETCH crs\_testComput INTO r\_testComput;   
while top\_two>0 loop   
dbms\_output.put\_line(‘员工姓名：’||r\_testComput.ENAME||’ 工作时间：’||r\_testComput.HIREDATE);   
–计速器减一   
top\_two:=top\_two-1;   
FETCH crs\_testComput INTO r\_testComput;   
end loop;   
close crs\_testComput;   
end;

–10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，   
–如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)   
declare   
cursor   
crs\_UpadateSal   
is   
select \* from emp1 for update of SAL;   
r\_UpdateSal crs\_UpadateSal%rowtype;   
salAdd emp1.sal%type;   
salInfo emp1.sal%type;   
begin   
for r\_UpdateSal in crs\_UpadateSal loop   
salAdd:= r\_UpdateSal.SAL\*0.2;   
if salAdd>300 then   
salInfo:=r\_UpdateSal.SAL;   
dbms\_output.put\_line(r\_UpdateSal.ENAME||’: 加薪失败。’||’薪水维持在：’||r\_UpdateSal.SAL);   
else   
salInfo:=r\_UpdateSal.SAL+salAdd;   
dbms\_output.put\_line(r\_UpdateSal.ENAME||’: 加薪成功.’||’薪水变为：’||salInfo);   
end if;   
update emp1 set SAL=salInfo where current of crs\_UpadateSal;   
end loop;   
end;

–11:将每位员工工作了多少年零多少月零多少天输出出来   
–近似   
–CEIL(n)函数：取大于等于数值n的最小整数   
–FLOOR(n)函数：取小于等于数值n的最大整数

declare   
cursor   
crs\_WorkDay   
is   
select ENAME,HIREDATE, trunc(months\_between(sysdate, hiredate) / 12) AS SPANDYEARS,   
trunc(mod(months\_between(sysdate, hiredate), 12)) AS months,   
trunc(mod(mod(sysdate - hiredate, 365), 12)) as days   
from emp1;   
r\_WorkDay crs\_WorkDay%rowtype;   
begin   
for r\_WorkDay in crs\_WorkDay loop   
dbms\_output.put\_line(r\_WorkDay.ENAME||’已经工作了’||r\_WorkDay.SPANDYEARS||’年,零’||r\_WorkDay.months||’月,零’||r\_WorkDay.days||’天’);   
end loop;   
end;

–12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来   
– deptno raise(%)   
– 10 5%   
– 20 10%   
– 30 15%   
– 40 20%   
– 加薪比例以现有的sal为标准   
–CASE expr WHEN comparison\_expr THEN return\_expr   
–\[, WHEN comparison\_expr THEN return\_expr\]… \[ELSE else\_expr\] END   
declare   
cursor   
crs\_caseTest   
is   
select \* from emp1 for update of SAL;   
r\_caseTest crs\_caseTest%rowtype;   
salInfo emp1.sal%type;   
begin   
for r\_caseTest in crs\_caseTest loop   
case   
when r\_caseTest.DEPNO=10   
THEN salInfo:=r\_caseTest.SAL\*1.05;   
when r\_caseTest.DEPNO=20   
THEN salInfo:=r\_caseTest.SAL\*1.1;   
when r\_caseTest.DEPNO=30   
THEN salInfo:=r\_caseTest.SAL\*1.15;   
when r\_caseTest.DEPNO=40   
THEN salInfo:=r\_caseTest.SAL\*1.2;   
end case;   
update emp1 set SAL=salInfo where current of crs\_caseTest;   
end loop;   
end;

–13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。   
–AVG(\[distinct|all\] expr) over (analytic\_clause)   
—作用：   
–按照analytic\_clause中的规则求分组平均值。   
–分析函数语法:   
–FUNCTION\_NAME(,…)   
–OVER   
–()   
–PARTITION子句   
–按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组   
select \* from emp1   
DECLARE   
CURSOR   
crs\_testAvg   
IS   
select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP\_AVG   
FROM EMP1 for update of SAL;   
r\_testAvg crs\_testAvg%rowtype;   
salInfo emp1.sal%type;   
begin   
for r\_testAvg in crs\_testAvg loop   
if r\_testAvg.SAL>r\_testAvg.DEP\_AVG then   
salInfo:=r\_testAvg.SAL-50;   
end if;   
update emp1 set SAL=salInfo where current of crs\_testAvg;   
end loop;   
end;

[https://blog.csdn.net/shaniqng/article/details/53523113][https_blog.csdn.net_shaniqng_article_details_53523113]

[Link 1]: https://me.csdn.net/liu_1983
[Link 2]: http://so.csdn.net/so/search/s.do?q=%E6%95%B0%E6%8D%AE%E5%BA%93&t=blog
[insert]: http://so.csdn.net/so/search/s.do?q=insert&t=blog
[delete]: http://so.csdn.net/so/search/s.do?q=delete&t=blog
[c]: http://so.csdn.net/so/search/s.do?q=c&t=blog
[table]: http://so.csdn.net/so/search/s.do?q=table&t=blog
[null]: http://so.csdn.net/so/search/s.do?q=null&t=blog
[SQL SERVER]: https://blog.csdn.net/liu_1983/article/category/800831
[https_blog.csdn.net_liu_1983_article_details_7208144]: https://blog.csdn.net/liu_1983/article/details/7208144
[SCQshine]: https://me.csdn.net/shaniqng
[sql]: http://so.csdn.net/so/search/s.do?q=sql&t=blog
[Link 3]: http://so.csdn.net/so/search/s.do?q=%E6%B8%B8%E6%A0%87&t=blog
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[java]: http://so.csdn.net/so/search/s.do?q=java&t=blog
[Oracle]: https://blog.csdn.net/shaniqng/article/category/6564440
[https_blog.csdn.net_shaniqng_article_details_53523113]: https://blog.csdn.net/shaniqng/article/details/53523113