oracle存储过程(循环插数据)

oracle存储过程

所谓存储进程(StoredProcedure)，便是一组用于完结特定数据库功用的SQL句子集，该SQL句子集经过
编译后存储在数据库体系中。在运用时分，用户经过指定现已定义的存储进程名字并给出相应的存储进程参数
来调用并履行它，从而完结一个或一系列的数据库操作。
oracle的存储进程优缺点：
1.存储进程能够使得程序履行效率更高、安全性更好，因为进程建立之后现已编译并且储存到数据库，直接写sql就需求先分析再履行因而进程效率更高，直接写sql句子会带来安全性问题，如：sql注入；
2.建立进程不会很耗体系资源，因为进程仅仅在调用才履行；
3.存储进程能够用于下降网络流量，存储进程代码直接存储于数据库中，所以不会产生很多T-sql句子的代码流量；
4.运用存储进程使您能够增强对履行计划的重复运用，由此能够经过运用远程进程调用(RPC)处理服务器上的存储进程而进步性能。RPC封装参数和调用服务器端进程的方法使引擎能够轻松地找到匹配的履行计划，并只需刺进更新的参数值；
5.可维护性高，更新存储进程一般比更改、测验以及从头部署程序集需求较少的时刻和精力；
6.代码精简一致，一个存储进程能够用于应用程序代码的不同方位；
7.增强安全性：
a、经过向用户授予对存储进程（而不是基于表）的访问权限，它们能够供给对特定数据的访问；
b、进步代码安全，避免SQL注入（但未彻底处理，例如，将数据操作语言－－DML，附加到输入参数）；
c、SqlParameter类指定存储进程参数的数据类型，作为深层次防御性战略的一部分，能够验证用户供给的值类型（但也不是万无一失，仍是应该传递至数据库前得到附加验证）。
缺点便是：
1、很多的利用进程，会对数据库服务器压力比较大。
存储进程的创立
参阅网址：https://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html
Oracle存储进程包括三部分：进程声明，履行进程部分，存储进程反常。
（1）无参存储进程语法
仿制代码
createorreplaceprocedureNoParProas//声明
;begin//履行
;
exception//存储进程反常
;end;
仿制代码
仿制代码
–实例–
CREATEORREPLACEPROCEDURE”proc1″(
p_para1VARCHAR2,
p_para2OUTVARCHAR2,
p_para3INOUTVARCHAR2)ASv_namevarchar2(20);BEGINv_name:=’张三丰’;
p_para3:=v_name;
dbms_output.put_line(‘p_para3:’||p_para3);END;
输入参数：
p_para1：李四
p_para3：王五
履行成果：
p_para3:张三丰
P_PARA2=P_PARA3=张三丰
—实例—CREATEORREPLACEPROCEDURE”proc1″(
p_para1VARCHAR2,
p_para2OUTVARCHAR2,
p_para3INOUTVARCHAR2)ASv_namevarchar2(20);BEGIN–v_name:=’张三丰’;–p_para3:=v_name;dbms_output.put_line(‘p_para3:’||p_para3);END;
navacatfororacle履行存储进程：
输入参数：
p_para1：李四
p_para3：王五
履行成果：
p_para3:王五
P_PARA2=P_PARA3=王五
仿制代码
仿制代码
sqleditor中履行存储进程代码：CREATEORREPLACEprocedureproc11(–传输参数的声明，用于接纳外部传入的值和输出存储进程的值给外部p_para1varchar2,
p_para2outvarchar2,
p_para3inoutvarchar2)as–存储进程中声明的变量，仅在存储进程中有用v_namevarchar2(20);begin–p_para1:=’aaa’;p_para2:=’bbb’;
v_name:=’张三丰’;
p_para3:=p_para1;
dbms_output.put_line(‘p_para3:’||p_para3);null;end;
在sqleditor中用命令履行存储进程：DECLARE–关于存储进程中的out方式参数，必定要声明并指定长度，用于接纳存储进程输出变量的值v2VARCHAR2(10);
v3VARCHAR2(10);BEGINproc11(‘aaa’,v2,v3);–字符串连接必定要用’||’，不能够用’+’dbms_output.put_line(‘v2:’||v2);
dbms_output.put_line(‘v3:’||v3);END;
履行成果：
p_para3:aaa
v2:bbb
v3:aaa
【注】
官网社区问题评论贴：https://community.oracle.com/message/91904421.在navicatfororacle客户端中运用命令行履行存储进程弄了良久，便是一向没声明out方式的参数变量；2.今后遇到博客中找不到答案的问题，能够考虑去官网的问题中查找，仍是官网能一针见血的找到问题，便是是英文的，要好好补习英语。
仿制代码
仿制代码
sqleditor中传入参数的默认值：CREATEORREPLACEprocedureproc11(–传入参数设置默认值p_para1varchar2DEFAULT’oooo’,
p_para2outvarchar2,
p_para3inoutvarchar2)asv_namevarchar2(20);beginp_para2:=’bbb’;
v_name:=’张三丰’;
p_para3:=p_para1;
dbms_output.put_line(‘p_para3:’||p_para3);null;end;
sqleditor中履行默认输入参数的存储进程：DECLAREv2VARCHAR2(10);
v3VARCHAR2(10);BEGIN–指定参数的值proc11(p_para2=>v2,p_para3=>v3);
dbms_output.put_line(‘v2:’||v2);
dbms_output.put_line(‘v3:’||v3);END;
履行成果：
p_para3:oooo
v2:bbb
v3:oooo
上面便是一个最简单的存储进程。一个存储进程大体分为这么几个部分:创立句子：createorreplaceprocedure存储进程名假如没有orreplace句子，则仅仅是新建一个存储进程。假如体系存在该存储进程，则会报错。Createorreplaceprocedure假如体系中没有此存储进程就新建一个，假如体系中有此存储进程则把本来删除去，从头创立一个存储进程。存储进程名定义：包括存储进程名和参数列表。参数名和参数类型。参数名不能重复，参数传递方法:IN,OUT,INOUTIN表明输入参数，按值传递方法。OUT表明输出参数，能够理解为按引证传递方法。能够作为存储进程的输出成果，供外部调用者运用。INOUT即可作输入参数，也可作输出参数。参数的数据类型只需求指明类型名即可，不需求指定宽度。参数的宽度由外部调用者决定。进程能够有参数，也能够没有参数变量声明块：紧跟着的as(is)关键字，能够理解为pl/sql的declare关键字，用于声明变量。变量声明块用于声明该存储进程需求用到的变量，它的作用域为该存储进程。另外这里声明的变量有必要指定宽度。遵循PL/SQL的变量声明标准。进程句子块：从begin关键字开端为进程的句子块。存储进程的详细逻辑在这里来实现。反常处理块：关键字为exception，为处理句子产生的反常。该部分为可选结束块：由end关键字成果。存储进程的参数传递方法存储进程的参数传递有三种方法:IN,OUT,INOUT.
IN按值传递，并且它不允许在存储进程中被从头赋值。假如存储进程的参数没有指定存参数传递类型，默以为IN；
OUT参数：作为输出参数，需求注意，当一个参数被指定为OUT类型时，就算在调用存储进程之前对该参数进行了赋值，在存储进程中该参数的值仍然是null；
INOUT是真实的按引证传递参数。即可作为传入参数也能够作为传出参数。
存储进程的参数宽度：
关于IN参数，其宽度是由外部决定。
关于OUT和INOUT参数，其宽度是由存储进程内部决定。
因而，在写存储进程时，对参数的宽度进行阐明对错常有必要的，最正确的方法便是参数的数据类型运用%type。这样两边就达成了一致。
exception类别：
too_many_rows–selectinto句子契合条件的记载有多条回来
no_data_found–selectinto句子没有契合条件的记载回来
dup_val_on_index–关于数据库表中的某一列，该列现已被约束为仅有索引，程序试图存储两个重复的值
value_error–在转换字符类型，截取或长度受限时，会产生该反常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该反常
storage_error–内存溢出
zero_divide–除数为零
case_not_found–关于挑选case句子，没有与之相匹配的条件，一起，也没有else句子捕获其他的条件
cursor_already_open–程序试图翻开一个现已翻开的游标
timeout_on_resource–体系在等待某一资源，时刻超时
others/PRAGMAEXCEPTION_INIT–未命名的内部反常
在子程序中运用EXCEPTION_INIT的语法如下：
PRAGMAEXCEPTION_INIT(exception_name,-Oracle_error_number);
在该语法中，反常名是声明的反常，下例是其用法：
DECLARE
deadlock_detectedEXCEPTION;
PRAGMAEXCEPTION_INIT(deadlock_detected,-60);
BEGIN
…–SomeoperationthatcausesanORA-00060error
EXCEPTION
WHENdeadlock_detectedTHEN
–handletheerror
END;
CREATEORREPLACEPROCEDURE”exceptiontest”(
param1DOUBLEPRECISION,
param2DOUBLEPRECISION,
param3OUTDOUBLEPRECISION)ASBEGINparam3:=param1/param2;
DBMS_OUTPUT.PUT_LINE(‘NavicatforOracle’);
EXCEPTIONWHENzero_divideTHENDBMS_OUTPUT.PUT_LINE(‘除数为零’);END;
输入参数：
param1：5param2：0履行成果：
除数为零
PARAM3=
反常的抛出：
由三种方法抛出反常：
1.经过PL/SQL运行时引擎；
2.运用RAISE句子（显式抛出反常）；
3.调用RAISE_APPLICATION_ERROR存储进程。
当数据库或PL/SQL在运行时产生过错时，一个反常被PL/SQL运行时引擎主动抛出；
RAISE不限于声明了的反常，它能够抛出任何任何反常；
1.声明反常
DECLAREinventory_too_lowEXCEPTION;
2.存储进程的通用格局
其他声明句子…
BEGIN
IForder_rec.qty>inventory_rec.qtyTHEN
RAISEinventory_too_low;
ENDIF
EXCEPTION
WHENinventory_too_lowTHEN
order_rec.staus:=’backordered’;
END;
RAISE_APPLICATION_ERROR内建函数用于抛出一个反常并给反常赋予一个过错号以及过错信息。自定义反常的缺省过错号是+1,缺省信息是User_Defined_Exception。RAISE_APPLICATION_ERROR函数能够在pl/sql程序块的履行部分和反常部分调用，显式抛出带特殊过错号的命名反常。
Raise_application_error(error_number,message[,true,false]))
过错号的范围是-20,000到-20,999。过错信息是文本字符串，最多为2048字节。TRUE和FALSE表明是添加(TRUE)进过错堆(ERRORSTACK)仍是覆盖(overwrite)过错堆(FALSE)。缺省情况下是FALSE。
如下代码所示：
IFproduct_not_foundTHEN
RAISE_APPLICATION_ERROR(-20123,’Invaldproductcode’TRUE);
ENDIF;
反常的处理：
PL/SQL程序块的反常部分包括了程序处理过错的代码，当反常被抛出时，一个反常陷阱就主动产生，程序操控离开履行部分转入反常部分,一旦程序进入反常部分就不能再回到同一块的履行部分。下面是反常部分的一般语法：
EXCEPTION
WHENexception_nameTHEN
Codeforhandingexception_name
[WHENanother_exceptionTHEN
Codeforhandinganother_exception]
[WHENothersTHEN
codeforhandinganyotherexception.]
用户有必要在独立的WHEN子串中为每个反常规划反常处理代码，WHENOTHERS子串有必要放置在最后边作为缺省处理器处理没有显式处理的反常。当反常产生时，操控转到反常部分，ORACLE查找当前反常相应的WHEN..THEN句子，捕捉反常，THEN之后的代码被履行，假如过错陷阱代码仅仅退出相应的嵌套块，那么程序将持续履行内部块END后边的句子。假如没有找到相应的反常陷阱，那么将履行WHENOTHERS。在反常部分WHEN子串没有数量约束。
EXCEPTION
WHENinventory_too_lowTHEN
order_rec.staus:=’backordered’;
replenish_inventory(inventory_nbr=>
inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
WHENdiscontinued_itemTHEN
–codefordiscontinued_itemprocessing
WHENzero_divideTHEN
–codeforzero_divide
WHENOTHERSTHEN
–codeforanyotherexception
END;
当反常抛出后，操控无条件转到反常部分，这就意味着操控不能回到反常产生的方位，当反常被处理和处理后，操控回来到上一层履行部分的下一条句子。
当反常产生时，在块的内部没有该反常处理器时，操控将转到或传达到上一层块的反常处理部分。
反常的传达：
没有处理的反常将沿检测反常调用程序传达到外面，当反常被处理并处理或到达程序最外层传达停止。在声明部分抛出的反常将操控转到上一层的反常部分。
参阅网址：https://www.cnblogs.com/vinsonLu/p/3434030.html
存储进程内部块：
仿制代码
CREATEORREPLACEprocedureinnerBlock(p1varchar2DEFAULT’kkk’)aso1varchar2(10):=’out1′;begindbms_output.put_line(o1);declareinner1varchar2(20);begininner1:=’inner1′;
dbms_output.put_line(inner1);declareinner2varchar2(20);begininner2:=’inner2′;
dbms_output.put_line(inner2);end;
exceptionwhenothersthennull;end;end;
sqleditor履行命令行：BEGINinnerBlock();END;
履行成果：
out1
inner1
inner2
【疑问】如何将外层履行成果传递给内层履行条件？以什么方式传递？
（2）带参存储进程实例
仿制代码
createorreplaceprocedurequeryempname(sfindnoemp.empno%type)assNameemp.ename%type;
sjobemp.job%type;begin….
exception
….end;

循环插数据

procedureInsert_WData(p_CODE1ao_model.code1%type,
p_BRANDIDao_model.brandid%type,
p_CODEvarchar2,
p_CONF_VALvarchar2,
p_DESCvarchar2,
p_CODE2varchar2,
p_DESCvarchar2,
ErrOutinoutvarchar2)is
begin
–参数
declare
startposition1number(10);
len1number(10);
startposition2number(10);
len2number(10);
startposition3number(10);
len3number(10);
output1varchar2(1024);
output2varchar2(1024);
output3varchar2(1024);
numnumber(1);
begin
startposition1:=1;
startposition2:=1;
startposition3:=1;
loop
selectinstr(p_CODE,’|’,startposition1)
intolen1
fromdual;
selectinstr(p_CONF_VAL,’|’,startposition2)
intolen2
fromdual;
selectinstr(p_DESC,’|’,startposition3)
intolen3
fromdual;
iflen1!=0then
begin
selectsubstr(p_CODE,
startposition1,
len1-startposition1)
intooutput1
fromdual;
selectsubstr(p_CONF_VAL,
startposition2,
len2-startposition2)
intooutput2
fromdual;
selectsubstr(p_DESC,
startposition3,
len3-startposition3)
intooutput3
fromdual;
num:=0;
selectcount(*)
intonum
fromao_modela
wherea.c0084_brandid=p_BRANDID
anda.c0001_code1=p_CODE1
anda.c0001_code=output1;
ifnum>=1then
updateao_modelb
setb.conf_val=output2
whereb.c0084_brandid=p_BRANDID
andb.c0001_code1=p_CODE1
andb.c0001_code=output1;
else
insertintoao_model
(C0001_CODE1,
C0084_BRANDID,
C0001_CODE,
CONF_VAL,
DESC)
values
(p_CODE1,
p_BRANDID,
output1||”,
output2||”,
output3||”);
endif;
commit;
end;
else
begin
selectsubstr(p_CODE,startposition1)
intooutput1
fromdual;
selectsubstr(p_CONF_VAL,startposition2)
intooutput2
fromdual;
selectsubstr(p_DESC,startposition3)
intooutput3
fromdual;
num:=0;
selectcount(*)
intonum
fromao_modela
wherea.c0084_brandid=p_BRANDID
anda.c0001_code1=p_CODE1
anda.c0001_code=output1;
ifnum>=1then
updateao_modelb
setb.conf_val=output2
whereb.c0084_brandid=p_BRANDID
andb.c0001_code1=p_CODE1
andb.c0001_code=output1;
else
insertintoao_model
(C0001_CODE1,
C0084_BRANDID,
C0001_CODE,
CONF_VAL,
DESC)
values
(p_CODE1,
p_BRANDID,
output1||”,
output2||”,
output3||”);
endif;
commit;
end;
exit;
endif;
startposition1:=len1+1;
startposition2:=len2+1;
startposition3:=len3+1;
endloop;
end;
EXCEPTION
WHENOTHERSTHEN
ErrOut:=SQLERRM;
rollback;
endInsert_WParamStatusData;