oracle存储过程(oracle存储过程实例详解)

oracle存储过程

存储进程，百度百科上是这样解说的，存储进程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功用的SQL句子集，存储在数据库中，经过第一次编译后再次调用不需求再次编译，用户经过指定存储进程的名字并给出参数（如果该存储进程带有参数）来调用存储进程。
简略的说便是专门干一件事一段sql句子。
能够由数据库自己去调用，也能够由java程序去调用。
在oracle数据库中存储进程是procedure。
二.为什么要写存储进程
1.效率高
存储进程编译一次后，就会存到数据库，每次调用时都直接履行。而普通的sql句子咱们要保存到其他地方（例如：记事本上），都要先剖析编译才会履行。所以想对而言存储进程效率更高。
2.降低网络流量
存储进程编译好会放在数据库，咱们在长途调用时，不会传输大量的字符串类型的sql句子。
3.复用性高
存储进程往往是针对一个特定的功用编写的，当再需求完成这个特定的功用时，能够再次调用该存储进程。
4.可维护性高
当功用要求产生小的变化时，修正之前的存储进程比较容易，花费精力少。
5.安全性高
完成某个特定功用的存储进程一般只要特定的用户能够运用，具有运用身份约束，更安全。
三.存储进程基础
1.存储进程结构
(1).根本结构
Oracle存储进程包含三部分：进程声明，履行进程部分，存储进程反常（可写可不写，要增强脚本的容错性和调试的方便性那就写上反常处理）
(2).无参存储进程
CREATEORREPLACEPROCEDUREdemoAS/IS
变量2DATE;
变量3NUMBER;
BEGIN
–要处理的事务逻辑
EXCEPTION–存储进程反常
END
这里的as和is相同任选一个，在这里没有区别，其间demo是存储进程称号。
(3).有参存储进程
a.带参数的存储进程
CREATEORREPLACEPROCEDURE存储进程称号(param1student.id%TYPE)
AS/IS
namestudent.name%TYPE;
agenumber:=20;
BEGIN
–事务处理…..
END
上面脚本中，
第1行：param1是参数，类型和student表id字段的类型相同。
第3行：声明变量name，类型是student表name字段的类型（同上）。
第4行：声明变量age，类型数数字，初始化为20
b.带参数的存储进程并且进行赋值
CREATEORREPLACEPROCEDURE存储进程称号(
s_noinvarchar,
s_nameoutvarchar,
s_agenumber)AS
totalNUMBER:=0;
BEGIN
SELECTCOUNT(1)INTOtotalFROMstudentsWHEREs.age=s_age;
dbms_output.put_line(‘契合该年纪的学生有’||total||’人’);
EXCEPTION
WHENtoo_many_rowsTHEN
DBMS_OUTPUT.PUT_LINE(‘返回值多于1行’);
END
上面脚本中：
其间参数IN表明输入参数，是参数的默认模式。
OUT表明返回值参数，类型能够运用任意Oracle中的合法类型。
OUT模式界说的参数只能在进程体内部赋值，表明该参数能够将某个值传递回调用他的进程
INOUT表明该参数能够向该进程中传递值，也能够将某个值传出去
第7行：查询句子，把参数s_age作为过滤条件，INTO关键字，把查到的成果赋给total变量。
第8行：输出查询成果，在数据库中“||”用来衔接字符串
第9—11行：做反常处理

oracle存储过程实例详解

业务系统的生产环境，有专人配置基础数据，有运维人员管理。而测试环境没有人员维护，基础配置信息数据过期后，导致系统无法正常使用。
以往的解决办法：
1.联系外围管理系统（销售管理系统）负责人协助配置。
2.配置信息，审核通过后同步到核心，确认后可以使用。
下面进入正题，存过实现迁移生产环境数据到测试环境
1.解决方案（版本1.1）
工具：数据库存过
思路：需要导入数据的表放在数据库中。遍历这些表，在遍历过程中先删除表中符合条件的数据，然后将生产环境符合条件的数据迁入到当前环境中
/*****************************************************************************\
*Name:P_IMPORT_CHA_DATA
*PROCEDURE:导生产环境基础数据（代理人信息）
*Paramater:IC_C_CHA_CDE代理人代码
*Programmer:lyt
*Date:2019/10/12
*Update:
*需导入表配置：T_IMPORT_TABLEWHEREC_MARK=’2′;
*维护dbquery中间库表结构：SELECT’CREATETABLET_’||C_TABLE||’ASSELECT*FROMZSSYS.’||C_TABLE||’WHEREROWNUM=0;’,A.*FROMT_IMPORT_TABLEA；
*如果手续费打包时提示：算税平台找不到该代理人，需联系算税平台（沈一栋）邵先路，将算税生产环境信息同步到对应环境
\*****************************************************************************/
PROCEDUREP_IMPORT_CHA_DATA(IC_C_CHA_CDEVARCHAR2)AS
LB_SQLCLOB;
LB_COLSCLOB;
LB_CONDCLOB;
LN_ROWSNUMBER;
CURSORCUR_TABLEIS
SELECT*FROMT_IMPORT_TABLEWHEREC_MARK=’2′;
BEGIN
FORLR_TABLEINCUR_TABLELOOP
SELECTWM_CONCAT(COLUMN_NAME)
INTOLB_COLS
FROMUSER_TAB_COLS@LINK_CORE
WHERETABLE_NAME=’T_’||UPPER(LR_TABLE.C_TABLE)
ORDERBYCOLUMN_ID;
LB_COND:=’WHERE’;
IF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_COM_ACCT’)THEN
LB_COND:=LB_COND||’C_REL_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER’)THEN
LB_COND:=LB_COND||’C_CLNT_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(LR_TABLE.C_TABLE)=’WEB_AUTH_CONFER’)THEN
LB_COND:=LB_COND||’C_AGT_AGR_NOIN(SELECTC_AGT_AGR_NOFROMZSSYS.WEB_CUS_CONFERWHEREC_CLNT_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_ORG_SALES’)THEN
LB_COND:=LB_COND||’C_SLS_CDEIN(SELECTC_SLS_CDEFROMZSSYS.WEB_CUS_CHAWHEREC_CHA_CDE’;
ELSE
LB_COND:=LB_COND||’C_CHA_CDE’;
ENDIF;
IF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(LR_TABLE.C_TABLE)=’WEB_AUTH_CONFER’ORUPPER(LR_TABLE.C_TABLE)=’WEB_ORG_SALES’)THEN
LB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||”’)’;
ELSE
LB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||””;
ENDIF;
LB_SQL:=’DELETEFROMZSSYS.’||LR_TABLE.C_TABLE||LB_COND;
EXECUTEIMMEDIATELB_SQL;
LB_SQL:=’INSERTINTOZSSYS.’||LR_TABLE.C_TABLE||'(‘||LB_COLS||
‘)SELECT’||LB_COLS||’FROM’||’ZSSYS.’||
LR_TABLE.C_TABLE||’@LINK_CORE’||LB_COND;
EXECUTEIMMEDIATELB_SQL;
ENDIF;
ENDLOOP;
–COMMIT;
ENDP_IMPORT_CHA_DATA;