一、存储过程的使用
以下的文章主要是通过相关的的代码来引出pl/sql中创建Oracle procedure并调用的实际应用方案，如果你想对pl/sql中创建Oracle procedure并调用有想了解的欲望的话，以下的文章就会给你提供相关的知识点。
Create table
create table A
(
USERID NUMBER(38),
PWD VARCHAR2(30)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
创建procedure
create or replace procedure up_sel(cur_test out sys_refcursor)
is
begin
open cur_test for
select * from a;
end;
删除存储过程
drop procedure up_sel
提交
commit
在PL/sql中执行procedure
file-->>new -->>test window
begin
Call the procedure
up_sel(cur_test => :cur_test);
end;
在variable中填入定义的游标名 cur_test，在Type中填入游标类型 Cursor，点击Value 右边的 ...图标即可显示出所要的结果当然也可直接右击存储过程后点TEST，
删除数据的存储过程
create or replace procedure up_del
(userid in varchar2)
is
begin
delete from a where USERID=userid;
end;
要在value中填入要传入的值
增加数据
create or replace procedure up_add
(
userid in varchar2,
pwd in varchar2
)
is
begin
insert into a(USERID,PWD) values(userid,pwd);
commit;
end;
执行
declare
begin
up_add(11,'222');
end;
二、游标的使用
1、第一种定义方法（显式游标）：
在大多数时候我们在设计程序的时候都遵循下面的步骤： 1、声明游标 2、打开游标 3、开始循环 4、从游标中取值 5、检查那一行被返回 6、处理 7、关闭循环 8、关闭游标 。
举例如下：
CURSOR C_Cus_Cur is
SELECT REAL_NAME 客户名,
to_char(CUSTOMER_ID) 客户号
from t_customer@cclink
where CUSTOMER_ID = dd;
begin
dd:=customer_id;
OPEN C_Cus_Cur;
loop
FETCH C_Cus_Cur
INTO vCUS_NAM ,
vCUS_ALI_ID
EXIT WHEN C_Cus_Cur%NOTFOUND;
select to_char(customer_id) into vCUS_ALI_ID_T from dual;
select count(*) into vCount from c_cus_bas where cus_ali_id = vCUS_ALI_ID_T;
if vCount = 0 then
--插入基本信息表
select C_CUS_BAS_ID_SEQ.Nextval into vCUS_ID_SEQ from dual;
insert into c_cus_bas(
CUS_ID,
CUS_TYP_ID,
OPE_DAT,
CUS_NAM)
values(
vCUS_ID_SEQ,
4,
to_char(sysdate,'YYYY-MM-DD'),
vCUS_NAM);
end if;
end loop;
CLOSE C_Cus_Cur;
2、第二种定义方法（隐式游标，当使用for循环时，oracle会隐含地打开游标、提取游标数据并关闭游标。游标for循环是使用游标最简单的方式）
游标FOR循环的语法如下：
FOR record_name IN corsor_name LOOP
Statements1；
Statements2；
END LOOP;
record_name是Oracle隐含定义的记录变量名(不用在定义部分定义，直接使用即可)， corsor_name 是已定义的游标名。
如果在使用游标FOR循环时不需要使用任何游标属性，那么直接在游标FOR循环中使用子查询。语法如下：
FOR record_name IN (SELECT column1,column2…FROM…[WHERE …]) LOOP
Statement1；
Statement2；
…
END LOOP；
如例：
FOR C_Deskchange_Answer_Cur IN (
select BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME
from C_DESKCHANGE_ANSWER_INFO_TEMP
where READ_FLAG = '0'
AND CERTI_CODE = c_certi_code.certi_code
AND ROWNUM = 1
)
LOOP
BRANCH_ORGAN_ID := C_Deskchange_Answer_Cur.BRANCH_ORGAN_ID;
BRANCH_ORGAN_NAME := C_Deskchange_Answer_Cur.BRANCH_ORGAN_NAME;
--插入基本信息表
insert into C_DESKCHANGE_ANSWER_INFO
(INFO_ID,
BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME)
VALUES(
DESK_ANSWER_SEQ.Nextval,
BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME);
END LOOP;
三、例子
下面举一包含存储过程和游标的完整例子：游标嵌套循环
create or replace procedure HZ_AGT_DESK_ANSWER_MOVE
as
str2 varchar2(2000):='';
str VARCHAR2(8000) :='';
v varchar2(8000) :='';
INFO_ID NUMBER(10);
BRANCH_ORGAN_ID VARCHAR2(40);
BRANCH_ORGAN_NAME VARCHAR2(100);
ORGAN_ID VARCHAR2(40);
ORGAN_NAME VARCHAR2(100);
CHANNEL_ID CHAR(1);
CHANNEL_NAME VARCHAR2(20);
APPLY_QUALIFY_NAME VARCHAR2(200);
APPLY_QUALIFY_BIRTHDAY DATE;
APPLY_QUALIFY_CELLER VARCHAR2(40);
APPLICANT_ID NUMBER(10);
APPLICANT_REAL_NAME VARCHAR2(100);
APPLICANT_GENDER CHAR(1);
APPLICANT_BIRTHDAY DATE;
INSURED_ID NUMBER(10);
INSURED_REAL_NAME VARCHAR2(100);
INSURED_BIRTHDAY DATE;
APPLY_ID NUMBER(10);
APPLY_NAME VARCHAR2(100);
APPLY_CELLER VARCHAR2(40);
APPLY_TYPE CHAR(1);
APPLY_TYPE_NAME VARCHAR2(30);
APPLY_CONTENT VARCHAR2(1000);
CERTI_CODE VARCHAR2(50);
HANDLER_ID NUMBER(10);
HANDLER_NAME VARCHAR2(200);
READ_FLAG CHAR(1);
NEW_CONTRACT_CELLER VARCHAR2(40);
BEGIN
FOR c_certi_code IN ( -------------------遍历身份证号
SELECT a.certi_code from C_DESKCHANGE_ANSWER_INFO_TEMP a where READ_FLAG = '0'
group by a.certi_code
)
LOOP
v:='';
FOR c_policy_code IN ( -------------------遍历保单号
SELECT b.policy_code from C_DESKCHANGE_ANSWER_INFO_TEMP b WHERE READ_FLAG = '0' and b.certi_code = c_certi_code.certi_code
group by b.policy_code
)
LOOP
str2:='';--每次清空保单号
str2 := c_policy_code.policy_code || '('; --接收policy_id
str:='';
FOR c_service_name IN ( -------------------遍历保全
SELECT c.service_name from C_DESKCHANGE_ANSWER_INFO_TEMP c
WHERE c.policy_code = c_policy_code.policy_code AND c.certi_code = c_certi_code.certi_code AND c.read_flag = '0'
)
LOOP
str := str|| c_service_name.service_name || '/';
--dbms_output.put_line('str:' || str);
END LOOP;
str2 := str2 || substr(str,1,length(str)-1) || ')';
v:=v || str2 || ',';
update C_DESKCHANGE_ANSWER_INFO_TEMP set READ_FLAG = '1' where READ_FLAG = '0' and policy_code = c_policy_code.policy_code;
END LOOP;
v:=substr(v,1,length(v)-1);
--dbms_output.put_line('final: '||v);
select DESK_ANSWER_SEQ.Nextval into INFO_ID from dual;
FOR C_Deskchange_Answer_Cur IN (
select BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME,
ORGAN_ID,
ORGAN_NAME,
CHANNEL_ID,
CHANNEL_NAME,
POLICY_CODE,
APPLY_QUALIFY_NAME,
APPLY_QUALIFY_BIRTHDAY,
APPLY_QUALIFY_CELLER,
APPLICANT_ID,
APPLICANT_REAL_NAME,
APPLICANT_GENDER,
APPLICANT_BIRTHDAY,
INSURED_ID,
INSURED_REAL_NAME,
INSURED_BIRTHDAY,
APPLY_ID,
APPLY_NAME,
CERTI_CODE,
APPLY_CELLER,
HANDLER_ID,
HANDLER_NAME,
APPLY_TYPE,
APPLY_TYPE_NAME,
READ_FLAG,
NEW_CONTRACT_CELLER
from C_DESKCHANGE_ANSWER_INFO_TEMP
where READ_FLAG = '0'
AND CERTI_CODE = c_certi_code.certi_code
AND ROWNUM = 1
)
LOOP
BRANCH_ORGAN_ID := C_Deskchange_Answer_Cur.BRANCH_ORGAN_ID;
BRANCH_ORGAN_NAME := C_Deskchange_Answer_Cur.BRANCH_ORGAN_NAME;
ORGAN_ID := C_Deskchange_Answer_Cur.ORGAN_ID;
ORGAN_NAME := C_Deskchange_Answer_Cur.ORGAN_NAME;
CHANNEL_ID := C_Deskchange_Answer_Cur.CHANNEL_ID;
CHANNEL_NAME := C_Deskchange_Answer_Cur.CHANNEL_NAME;
APPLY_QUALIFY_NAME := C_Deskchange_Answer_Cur.APPLY_QUALIFY_NAME;
APPLY_QUALIFY_BIRTHDAY := C_Deskchange_Answer_Cur.APPLY_QUALIFY_BIRTHDAY;
APPLY_QUALIFY_CELLER := C_Deskchange_Answer_Cur.APPLY_QUALIFY_CELLER;
APPLICANT_ID := C_Deskchange_Answer_Cur.APPLICANT_ID;
APPLICANT_REAL_NAME := C_Deskchange_Answer_Cur.APPLICANT_REAL_NAME;
APPLICANT_GENDER := C_Deskchange_Answer_Cur.APPLICANT_GENDER;
APPLICANT_BIRTHDAY := C_Deskchange_Answer_Cur.APPLICANT_BIRTHDAY;
INSURED_ID := C_Deskchange_Answer_Cur.INSURED_ID;
INSURED_REAL_NAME := C_Deskchange_Answer_Cur.INSURED_REAL_NAME;
INSURED_BIRTHDAY := C_Deskchange_Answer_Cur.INSURED_BIRTHDAY;
APPLY_ID := C_Deskchange_Answer_Cur.APPLY_ID;
APPLY_NAME := C_Deskchange_Answer_Cur.APPLY_NAME;
CERTI_CODE := C_Deskchange_Answer_Cur.CERTI_CODE;
APPLY_CELLER := C_Deskchange_Answer_Cur.APPLY_CELLER;
HANDLER_ID := C_Deskchange_Answer_Cur.HANDLER_ID;
HANDLER_NAME := C_Deskchange_Answer_Cur.HANDLER_NAME;
APPLY_TYPE := C_Deskchange_Answer_Cur.APPLY_TYPE;
APPLY_TYPE_NAME := C_Deskchange_Answer_Cur.APPLY_TYPE_NAME;
READ_FLAG := C_Deskchange_Answer_Cur.READ_FLAG;
NEW_CONTRACT_CELLER := C_Deskchange_Answer_Cur.NEW_CONTRACT_CELLER;
--插入基本信息表
insert into C_DESKCHANGE_ANSWER_INFO
(INFO_ID,
BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME,
ORGAN_ID,
ORGAN_NAME,
CHANNEL_ID,
CHANNEL_NAME,
APPLY_QUALIFY_NAME,
APPLY_QUALIFY_BIRTHDAY,
APPLY_QUALIFY_CELLER,
APPLICANT_ID,
APPLICANT_REAL_NAME,
APPLICANT_GENDER,
APPLICANT_BIRTHDAY,
INSURED_ID,
INSURED_REAL_NAME,
INSURED_BIRTHDAY,
APPLY_ID,
APPLY_NAME,
APPLY_CONTENT,
CERTI_CODE,
APPLY_CELLER,
HANDLER_ID,
HANDLER_NAME,
APPLY_TYPE,
APPLY_TYPE_NAME,
NEW_CONTRACT_CELLER)
VALUES(
DESK_ANSWER_SEQ.Nextval,
BRANCH_ORGAN_ID,
BRANCH_ORGAN_NAME,
ORGAN_ID,
ORGAN_NAME,
CHANNEL_ID,
CHANNEL_NAME,
APPLY_QUALIFY_NAME,
APPLY_QUALIFY_BIRTHDAY,
APPLY_QUALIFY_CELLER,
APPLICANT_ID,
APPLICANT_REAL_NAME,
APPLICANT_GENDER,
APPLICANT_BIRTHDAY,
INSURED_ID,
INSURED_REAL_NAME,
INSURED_BIRTHDAY,
APPLY_ID,
APPLY_NAME,
v,
CERTI_CODE,
APPLY_CELLER,
HANDLER_ID,
HANDLER_NAME,
APPLY_TYPE,
APPLY_TYPE_NAME,
NEW_CONTRACT_CELLER);
END LOOP;
update C_DESKCHANGE_ANSWER_INFO_TEMP set READ_FLAG = '1' where READ_FLAG = '0';
commit;
END LOOP;
commit;
END;