Oracle存储过程详解(四)-Oracle中Cursor介绍_存储过程cursor用法

参考资料：这里写链接内容

一 概念

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

二 类型

Cursor类型包含三种: 隐式Cursor，显式Cursor和Ref Cursor（动态Cursor）。
1． 隐式Cursor:
1).对于Select …INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DML Sql语句，就是隐式Cursor。例如：Select /Update / Insert/Delete操作。
2)作用：可以通过隐式Cusor的属性来了解操作的状态和结果，从而达到流程的控制。Cursor的属性包含：
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
3) 隐式Cursor是系统自动打开和关闭Cursor.
下面是一个Sample：

Set Serveroutput on;   

begin  
    update t_contract_master set liability_state = 1 where policy_code = '123456789';   

    if SQL%Found then  
       dbms_output.put_line('the Policy is updated successfully.');   
       commit;   
    else  
      dbms_output.put_line('the policy is updated failed.');   
    end if;   

end;   

/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Set Serveroutput on;  
begin  
update t_contract_master set liability_state = 1 where policy_code = '123456789';  
if SQL%Found then  
dbms_output.put_line('the Policy is updated successfully.');  
commit;  
else  
dbms_output.put_line('the policy is updated failed.');  
end if;  
end;  
/  
1
2
3
4
5
6
7
8
9
10
11

在PL/SQL中run:

SQL>    

the policy is updated failed.   

PL/SQL procedure successfully completed  
[SQL] view plain copy
SQL>  
the policy is updated failed.  
PL/SQL procedure successfully completed  
1
2
3
4
5
6
7
8
9

2． 显式Cursor：

（1） 对于从数据库中提取多行数据，就需要使用显式Cursor。显式Cursor的属性包含：
游标的属性 返回值类型 意 义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真，否则为假

（2） 对于显式游标的运用分为四个步骤：
 定义游标—Cursor [Cursor Name] IS;
 打开游标—Open [Cursor Name];
 操作数据—Fetch [Cursor name]
 关闭游标—Close [Cursor Name],这个Step绝对不可以遗漏。
（3）以下是三种常见显式Cursor用法。
1）

Set serveroutput on;   

declare    
    ---define Cursor   
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
     from t_contract_master cm   
     where cm.liability_state = 2   
     and cm.policy_type = 1   
     and cm.policy_cate in ('2','3','4')   
     and rownum < 5   
     order by cm.policy_code desc;   
    curPolicyInfo cur_policy%rowtype;---定义游标变量   
Begin  
   open cur_policy; ---open cursor   
   Loop    
     --deal with extraction data from DB   
     Fetch cur_policy into curPolicyInfo;   
     Exit when cur_policy%notfound;   

     Dbms_Output.put_line(curPolicyInfo.policy_code);   
   end loop;   
   Exception    
     when others then  
         close cur_policy;   
         Dbms_Output.put_line(Sqlerrm);   

   if cur_policy%isopen then     
    --close cursor    
      close cur_policy;   
   end if;   
end;   

/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Set serveroutput on;  
declare  
---define Cursor  
Cursor cur_policy is  
select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
from t_contract_master cm  
where cm.liability_state = 2  
and cm.policy_type = 1  
and cm.policy_cate in ('2','3','4')  
and rownum < 5  
order by cm.policy_code desc;  
curPolicyInfo cur_policy%rowtype;---定义游标变量  
Begin  
open cur_policy; ---open cursor  
Loop  
--deal with extraction data from DB  
Fetch cur_policy into curPolicyInfo;  
Exit when cur_policy%notfound;  
Dbms_Output.put_line(curPolicyInfo.policy_code);  
end loop;  
Exception  
when others then  
close cur_policy;  
Dbms_Output.put_line(Sqlerrm);  
if cur_policy%isopen then  
--close cursor  
close cur_policy;  
end if;  
end;  
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

2）

Set serveroutput on;   

declare    
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
     from t_contract_master cm   
     where cm.liability_state = 2   
     and cm.policy_type = 1   
     and cm.policy_cate in ('2','3','4')   
     and rownum < 5   
     order by cm.policy_code desc;   
     v_policyCode t_contract_master.policy_code%type;   
     v_applicantId t_contract_master.applicant_id%type;   
     v_periodPrem t_contract_master.period_prem%type;   
     v_bankCode t_contract_master.bank_code%type;   
     v_bankAccount t_contract_master.bank_account%type;   
Begin  
   open cur_policy;   
   Loop    
     Fetch cur_policy into v_policyCode,   
                           v_applicantId,   
                           v_periodPrem,   
                           v_bankCode,   
                           v_bankAccount;   
     Exit when cur_policy%notfound;   

     Dbms_Output.put_line(v_policyCode);   
   end loop;   
   Exception    
     when others then  
         close cur_policy;   
         Dbms_Output.put_line(Sqlerrm);   

   if cur_policy%isopen then      
      close cur_policy;   
   end if;   
end;   
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

Set serveroutput on;  
declare  
Cursor cur_policy is  
select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
from t_contract_master cm  
where cm.liability_state = 2  
and cm.policy_type = 1  
and cm.policy_cate in ('2','3','4')  
and rownum < 5  
order by cm.policy_code desc;  
v_policyCode t_contract_master.policy_code%type;  
v_applicantId t_contract_master.applicant_id%type;  
v_periodPrem t_contract_master.period_prem%type;  
v_bankCode t_contract_master.bank_code%type;  
v_bankAccount t_contract_master.bank_account%type;  
Begin  
open cur_policy;  
Loop  
Fetch cur_policy into v_policyCode,  
v_applicantId,  
v_periodPrem,  
v_bankCode,  
v_bankAccount;  
Exit when cur_policy%notfound;  
Dbms_Output.put_line(v_policyCode);  
end loop;  
Exception  
when others then  
close cur_policy;  
Dbms_Output.put_line(Sqlerrm);  
if cur_policy%isopen then  
close cur_policy;  
end if;  
end;  
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

3）

Set serveroutput on;   

declare    
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
     from t_contract_master cm   
     where cm.liability_state = 2   
     and cm.policy_type = 1   
     and cm.policy_cate in ('2','3','4')   
     and rownum < 5   
     order by cm.policy_code desc;   
Begin  
   For rec_Policy in cur_policy loop   
       Dbms_Output.put_line(rec_policy.policy_code);   
   end loop;   
   Exception    
     when others then  
         Dbms_Output.put_line(Sqlerrm);   

end;   

/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

Set serveroutput on;  
declare  
Cursor cur_policy is  
select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
from t_contract_master cm  
where cm.liability_state = 2  
and cm.policy_type = 1  
and cm.policy_cate in ('2','3','4')  
and rownum < 5  
order by cm.policy_code desc;  
Begin  
For rec_Policy in cur_policy loop  
Dbms_Output.put_line(rec_policy.policy_code);  
end loop;  
Exception  
when others then  
Dbms_Output.put_line(Sqlerrm);  
end;  
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

run pl/sql,执行结果如下：

SQL>    

8780203932   
8780203227   
8780203218   
8771289268   

PL/SQL procedure successfully completed  
[sql] view plain copy
SQL>  
8780203932  
8780203227  
8780203218  
8771289268  
PL/SQL procedure successfully completed  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3． Ref Cursor（动态游标）：

1） 与隐式Cursor,显式Cursor的区别：Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor，是静态的，在编译期间就决定数据结果集。
2） Ref cursor的使用:
 Type [Cursor type name] is ref cursor
 Define 动态的Sql语句
 Open cursor
 操作数据—Fetch [Cursor name]
 Close Cursor
下面是一个Sample：

Set serveroutput on;   

Declare  
    ---define cursor type name   
    type cur_type is ref cursor;   
    cur_policy cur_type;   
    sqlStr varchar2(500);   
    rec_policy t_contract_master%rowtype;   
begin  
   ---define 动态Sql   
   sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm   
     where cm.liability_state = 2    
     and cm.policy_type = 1    
     and cm.policy_cate in (2,3,4)    
     and rownum < 5    
     order by cm.policy_code desc ';   
---Open Cursor   
  open cur_policy for sqlStr;   
  loop   
       fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;   
       exit when cur_policy%notfound;   

       Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);   

  end loop;   
close cur_policy;       

end;   
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Set serveroutput on;  
Declare  
---define cursor type name  
type cur_type is ref cursor;  
cur_policy cur_type;  
sqlStr varchar2(500);  
rec_policy t_contract_master%rowtype;  
begin  
---define 动态Sql  
sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm  
where cm.liability_state = 2  
and cm.policy_type = 1  
and cm.policy_cate in (2,3,4)  
and rownum < 5  
order by cm.policy_code desc ';  
---Open Cursor  
open cur_policy for sqlStr;  
loop  
fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;  
exit when cur_policy%notfound;  
Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);  
end loop;  
close cur_policy;  
end;  
/  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.常见Exception

1. 错 误 名 称 错误代码 错 误 含 义
2. CURSOR_ALREADY_OPEN ORA_06511 试图打开已经打开的游标
3. INVALID_CURSOR ORA_01001 试图使用没有打开的游标
4. DUP_VAL_ON_INDEX ORA_00001 保存重复值到惟一索引约束的列中
5. ZERO_DIVIDE ORA_01476 发生除数为零的除法错误
6. INVALID_NUMBER ORA_01722 试图对无效字符进行数值转换
7. ROWTYPE_MISMATCH ORA_06504 主变量和游标的类型不兼容
8. VALUE_ERROR ORA_06502 转换、截断或算术运算发生错误
9. TOO_MANY_ROWS ORA_01422 SELECT…INTO…语句返回多于一行的数据
10. NO_DATA_FOUND ORA_01403 SELECT…INTO…语句没有数据返回
11. TIMEOUT_ON_RESOURCE ORA_00051 等待资源时发生超时错误
12. TRANSACTION_BACKED_OUT ORA_00060 由于死锁，提交失败
13. STORAGE_ERROR ORA_06500 发生内存错误
14. PROGRAM_ERROR ORA_06501 发生PL/SQL内部错误
15. NOT_LOGGED_ON ORA_01012 试图操作未连接的数据库
16. LOGIN_DENIED ORA_01017 在连接时提供了无效用户名或口令
    函数里面进行update操作，发现这样子的错误会抛以下的DML错误，如下：
    ORA-14551: cannot perform. a DML operation inside a query -

查询资料发现DML的问题：

对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的函数,是无法简单的用SQL来调用的.

解决办法如下，只需在声明中添加 PRAGMA AUTONOMOUS_TRANSACTION

CREATE OR REPLACE FUNCTION FUN_SET_PROJECT_COST 
 (
  V_BUDGET_OBJECT_ID  IN VARCHAR2,
  V_ADJUST_ID  IN VARCHAR2
 )   
RETURN VARCHAR2;   
IS   
PRAGMA AUTONOMOUS_TRANSACTION;  
    CREATE OR REPLACE FUNCTION FUN_SET_PROJECT_COST 
     (
      V_BUDGET_OBJECT_ID  IN VARCHAR2,
      V_ADJUST_ID  IN VARCHAR2
     );
    RETURN VARCHAR2;
IS
PRAGMA AUTONOMOUS_TRANSACTION;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

PRAGMA AUTONOMOUS_TRANSACTION自治事务

当前的存储过程作为已有事务的子事务运行，子事务的commit，rollback操作不影响父事务的状态

在你的一个事务（外层事务）中可以定义一个或几个自治事务。自治事务可以独立commit,不对外层事务产生影响，同样外层事务的 rollback 也对自治事务没有影响。通常可以考虑将自治事务定义成一个过程，在外层的事务中调用。

---

1、去掉重复记录 保留一条

DELETE FROM A_TEST
WHERE UNAME IN ( SELECT UNAME
FROM A_TEST
GROUP BY UNAME
HAVING COUNT(UNAME) > 1 )
AND ROWID NOT IN ( SELECT MIN(ROWID)
FROM A_TEST
GROUP BY UNAME
HAVING COUNT(UNAME) > 1 )

2、替换字段根据指定的字符

update QF_KEYWORDS set KEYWORD=REPLACE(KEYWORD, ‘,’ , ”)

3、oracle job时间

每天运行一次 ‘SYSDATE + 1’
每小时运行一次 ‘SYSDATE + 1/24’
每10分钟运行一次 ‘SYSDATE + 10/（60*24）’
每30秒运行一次 ‘SYSDATE + 30/(60*24*60)’
每隔一星期运行一次 ‘SYSDATE + 7’

每天午夜12点 ‘TRUNC(SYSDATE + 1)’
每天早上8点30分 ‘TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)’
每星期二中午12点 ‘NEXT_DAY(TRUNC(SYSDATE ), ”TUESDAY” ) + 12/24’
每个月第一天的午夜12点 ‘TRUNC(LAST_DAY(SYSDATE ) + 1)’
每个季度最后一天的晚上11点 ‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24’
每星期六和日早上6点10分 ‘TRUNC(LEAST(NEXT_DAY(SYSDATE, ”SATURDAY”), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60）’

每天凌晨0点执行
TRUNC(sysdate+1)
每天凌晨1点执行
TRUNC(sysdate+1)+1/24
每天早上8点30分执行
TRUNC(SYSDATE+1)+(8*60+30)/(24*60)

3、每周定时执行
例如：
每周一凌晨2点执行
TRUNC(next_day(sysdate,1))+2/24
TRUNC(next_day(sysdate,’星期一’))+2/24
每周二中午12点执行
TRUNC(next_day(sysdate,2))+12/24
TRUNC(next_day(sysdate,’星期二’))+12/24

4、每月定时执行
例如：
每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE)+1)
每月1日凌晨1点执行
TRUNC(LAST_DAY(SYSDATE)+1)+1/24

5、每季度定时执行
每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),’q’)
每季度的第一天凌晨2点执行
TRUNC(ADD_MONTHS(SYSDATE,3),’q’)+2/24
每季度的最后一天的晚上11点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),’q’)-1/24

6、每半年定时执行
例如：
每年7月1日和1月1日凌晨1点执行
ADD_MONTHS(TRUNC(sysdate,’yyyy’),6)+1/24

7、每年定时执行
例如：
每年1月1日凌晨2点执行
ADD_MONTHS(TRUNC(sysdate,’yyyy’),12)+2/24
根据规律来算，其实就非常简单了。

4、oracle查询表名：

select table_name from all_tables t where table_name like ‘%SEND%’

当库中的表中太多太多的时候，可以查询所有库中的表名

5、创建索引：
CREATE INDEX “IPIQXT”.”TZHS10_SUBMIT” ON “IPIQXT”.”TZHS10_SUBMIT”
(
“ID”
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE “MBOP” ;

–两种方法 三十分钟执行一次存储过程DAYBAKDATA

1、VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, ‘DAYBAKDATA;’, sysdate , ‘SYSDATE+30/1440’);
commit;
END;

2、DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => ‘DAYBAKDATA;’
,next_date => SYSDATE+30/1440
,interval => ‘SYSDATE+30/1440’
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ’ || to_char(x));
COMMIT;
7、打印

dbms_output.put_line(‘归档了一条彩信发送状态’);

8、创建索引：
CREATE INDEX “MY_SUBMIT4” ON “MY_SUBMIT4”
(
“ID”
)

10、创建触发器
create or replace
TRIGGER bjcbsend
AFTER INSERT ON a_SUBMIT
FOR EACH ROW
DECLARE
BEGIN

–sql操作 以上是插入数据时候的触发器 以下注释的是如果你想要修改某个字段的时候的触发器 只需把INSERT改成UPDATE 然后加上以下代码
– if updating(‘字段’) and :NEW.字段> ‘100’ then
– 执行sql操作
– end if;
–END;

9、创建序列
CREATE SEQUENCE “IPIQXT”.”SEQ_ABLEPHOTO”
MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 20000 CACHE 20 NOORDER NOCYCLE ;

10、创建函数
–从表T_a 中取出phone 条件是AREANO =传入的参数areano1
create or replace
function vphone (areano1 in varchar2)
return varchar2
is
phone1 varchar2(20);
begin
select phone into phone1 from T_a where rownum=1 and AREANO = areano1 and flag
return(phone1);
end ;

1. 11.