IT技术网站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)ASLB_SQLCLOB;
LB_COLSCLOB;
LB_CONDCLOB;
LN_ROWSNUMBER;CURSORCUR_TABLEISSELECT*FROMT_IMPORT_TABLEWHEREC_MARK=’2′;BEGINFORLR_TABLEINCUR_TABLELOOPSELECTWM_CONCAT(COLUMN_NAME)INTOLB_COLSFROMUSER_TAB_COLS@LINK_COREWHERETABLE_NAME=’T_’||UPPER(LR_TABLE.C_TABLE)ORDERBYCOLUMN_ID;
LB_COND:=’WHERE’;IF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_COM_ACCT’)THENLB_COND:=LB_COND||’C_REL_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER’)THENLB_COND:=LB_COND||’C_CLNT_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(LR_TABLE.C_TABLE)=’WEB_AUTH_CONFER’)THENLB_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’)THENLB_COND:=LB_COND||’C_SLS_CDEIN(SELECTC_SLS_CDEFROMZSSYS.WEB_CUS_CHAWHEREC_CHA_CDE’;ELSELB_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’)THENLB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||”’)’;ELSELB_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;
触及知识点
1.1oracle中的for循环
FORLR_TABLEINCUR_TABLELOOP
ENDLOOP;
1.2oracle中的条件分支语句
IF()THEN
…;
ELSIF()THEN
…;
ELSE
…;
ENDIF;
1.3存过中入参
1.4游标cursor
Cursor类型包含三种:隐式Cursor，显式Cursor和RefCursor（动态Cursor）。
1).隐式游标：无需界说，Select/Update/Insert/Delete操作，便是隐式Cursor
2).显式游标：
cursor[()]is;
3).动态游标：
Type[Cursortypename]isrefcursor
游标的属性（4个）1.found2.notfound3.rowcount4.isopen
2.存过怎么调试？
2.1怎么打断点
2.2怎么调试
首先在左侧列表中，选中对应存过，右键后点击adddebuginformaintion（添加调试信息），然后在点Test，显现如下页面：
最上面一排是debug相关按键区域，中心为代码显现部分，左下为变量值显现区域（填写变量名称，履行过程中检查变量值）
2.处理方案（版别1.2）
版别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′;
*假如手续费打包时提示：算税渠道找不到该代理人，需联络算税渠道（沈一栋）邵先路，将算税出产环境信息同步到对应环境
\*****************************************************************************/PROCEDUREP_IMPORT_CHA_DATA(IC_C_CHA_CDEVARCHAR2)ASTYPEref_cursor_typeISREFCURSOR;
CUR_TABLEref_cursor_type;
LR_TABLEZSSYS.T_IMPORT_TABLE%ROWTYPE;
LB_SQLCLOB;
LB_COLSCLOB;
LB_CONDCLOB;BEGINFORiIN1..2LOOPIF(i=1)THENOPENCUR_TABLEFORSELECT*FROMZSSYS.T_IMPORT_TABLEWHEREC_MARK=’2’ORDERBYC_TABLE_NODESC;ELSEOPENCUR_TABLEFORSELECT*FROMZSSYS.T_IMPORT_TABLEWHEREC_MARK=’2’ORDERBYC_TABLE_NO;ENDIF;
LOOPFETCHCUR_TABLEINTOLR_TABLE;EXITWHENCUR_TABLE%NOTFOUND;SELECTWM_CONCAT(COLUMN_NAME)INTOLB_COLSFROMDBA_TAB_COLS@LINK_COREWHERETABLE_NAME=’T_’||UPPER(LR_TABLE.C_TABLE)ANDOWNER=’ZSSYS’ORDERBYCOLUMN_ID;
LB_COND:=’WHERE’;IF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_COM_ACCT’)THENLB_COND:=LB_COND||’C_REL_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER’)THENLB_COND:=LB_COND||’C_CLNT_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(LR_TABLE.C_TABLE)=’WEB_AUTH_CONFER’)THENLB_COND:=LB_COND||’C_AGT_AGR_NOIN(SELECTC_AGT_AGR_NOFROMZSSYS.WEB_CUS_CONFER@LINK_COREWHEREC_CLNT_CDE’;
ELSIF(UPPER(LR_TABLE.C_TABLE)=’WEB_ORG_SALES’)THENLB_COND:=LB_COND||’C_SLS_CDEIN(SELECTC_SLS_CDEFROMZSSYS.WEB_CUS_CHA@LINK_COREWHEREC_CHA_CDE’;ELSELB_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’)THENLB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||”’)’;ELSELB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||””;ENDIF;IF(i=1)THENLB_SQL:=’DELETEFROMZSSYS.’||LR_TABLE.C_TABLE||LB_COND;ELSELB_SQL:=’INSERTINTOZSSYS.’||LR_TABLE.C_TABLE||'(‘||LB_COLS||’)SELECT’||LB_COLS||’FROM’||’ZSSYS.’||LR_TABLE.C_TABLE||’@LINK_CORE’||LB_COND;ENDIF;EXECUTEIMMEDIATELB_SQL;ENDLOOP;IFCUR_TABLE%ISOPENTHEN–closecursorCLOSECUR_TABLE;ENDIF;ENDLOOP;ENDP_IMPORT_CHA_DATA;
/*****************************************************************************\
*Name:F_GET_LB_SQL
*PROCEDURE:导出产环境根底数据（代理人信息）
*Return:LB_COND
*Programmer:lyt
*Date:2019/10/24
*Update:
\*****************************************************************************/FUNCTIONF_GET_LB_SQL(C_TABLEINVARCHAR2,–表名IC_C_CHA_CDEINVARCHAR2,–事务员编号LB_COLSINOUTCLOB–表对应字段)RETURNCLOBISLB_CONDCLOB:=”;BEGINSELECTWM_CONCAT(COLUMN_NAME)INTOLB_COLSFROMDBA_TAB_COLS@LINK_COREWHERETABLE_NAME=UPPER(C_TABLE)ANDOWNER=’ZSSYS’ORDERBYCOLUMN_ID;
LB_COND:=’WHERE’;IF(UPPER(C_TABLE)=’WEB_CUS_COM_ACCT’)THENLB_COND:=LB_COND||’C_REL_CDE’;
ELSIF(UPPER(C_TABLE)=’WEB_CUS_CONFER’)THENLB_COND:=LB_COND||’C_CLNT_CDE’;
ELSIF(UPPER(C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(C_TABLE)=’WEB_AUTH_CONFER’)THENLB_COND:=LB_COND||’C_AGT_AGR_NOIN(SELECTC_AGT_AGR_NOFROMZSSYS.WEB_CUS_CONFER@LINK_COREWHEREC_CLNT_CDE’;
ELSIF(UPPER(C_TABLE)=’WEB_ORG_SALES’)THENLB_COND:=LB_COND||’C_SLS_CDEIN(SELECTC_SLS_CDEFROMZSSYS.WEB_CUS_CHA@LINK_COREWHEREC_CHA_CDE’;ELSELB_COND:=LB_COND||’C_CHA_CDE’;ENDIF;IF(UPPER(C_TABLE)=’WEB_CUS_CONFER_DTL’ORUPPER(C_TABLE)=’WEB_AUTH_CONFER’ORUPPER(C_TABLE)=’WEB_ORG_SALES’)THENLB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||”’)’;ELSELB_COND:=LB_COND||’=”’||IC_C_CHA_CDE||””;ENDIF;RETURNLB_COND;
EXCEPTIONWHENNO_DATA_FOUNDTHENdbms_output.put_line(‘表：’||C_TABLE||’找不到’);ENDF_GET_LB_SQL;/*****************************************************************************\
*Name:P_IMPORT_CHA_DATA2
*PROCEDURE:导出产环境根底数据（代理人信息）
*Paramater:IC_C_CHA_CDE代理人代码
*Programmer:lyt
*Date:2019/10/24
*Update:
*需导入表装备：T_IMPORT_TABLEWHEREC_MARK=’2′;
*假如手续费打包时提示：算税渠道找不到该代理人，需联络算税渠道（沈一栋）邵先路，将算税出产环境信息同步到对应环境
\*****************************************************************************/PROCEDUREP_IMPORT_CHA_DATA2(IC_C_CHA_CDEVARCHAR2)ASTYPEref_cursor_typeISREFCURSOR;
CUR_TABLEref_cursor_type;
LR_TABLEZSSYS.T_IMPORT_TABLE%ROWTYPE;
LB_SQLCLOB;
LB_COLSCLOB;
LB_CONDCLOB;BEGINFORiIN1..2LOOP–删除已有数据IF(i=1)THENOPENCUR_TABLEFORSELECT*FROMZSSYS.T_IMPORT_TABLEWHEREC_MARK=’2’ORDERBYC_TABLE_NODESC;
LOOPFETCHCUR_TABLEINTOLR_TABLE;EXITWHENCUR_TABLE%NOTFOUND;
LB_SQL:=’DELETEFROMZSSYS.’||LR_TABLE.C_TABLE||F_GET_LB_SQL(LR_TABLE.C_TABLE,IC_C_CHA_CDE,LB_COLS);EXECUTEIMMEDIATELB_SQL;ENDLOOP;ELSE–插入数据OPENCUR_TABLEFORSELECT*FROMZSSYS.T_IMPORT_TABLEWHEREC_MARK=’2’ORDERBYC_TABLE_NO;
LOOPFETCHCUR_TABLEINTOLR_TABLE;EXITWHENCUR_TABLE%NOTFOUND;
LB_COND:=F_GET_LB_SQL(LR_TABLE.C_TABLE,IC_C_CHA_CDE,LB_COLS);
LB_SQL:=’INSERTINTOZSSYS.’||LR_TABLE.C_TABLE||'(‘||LB_COLS||’)SELECT’||LB_COLS||’FROM’||’ZSSYS.’||LR_TABLE.C_TABLE||’@LINK_CORE’||LB_COND;EXECUTEIMMEDIATELB_SQL;ENDLOOP;ENDIF;IFCUR_TABLE%ISOPENTHEN–closecursorCLOSECUR_TABLE;ENDIF;ENDLOOP;ENDP_IMPORT_CHA_DATA2;
oracle存储过程实例详解
ACCESS_INTO_NULL未界说目标CASE_NOT_FOUNDCASE中若未包括相应的WHEN，并且没有设置ELSE时COLLECTION_IS_NULL集合元素未初始化CURSER_ALREADY_OPEN游标现已打开DUP_VAL_ON_INDEX仅有索引对应的列上有重复的值INVALID_CURSOR在不合法的游标上进行操作INVALID_NUMBER内嵌的SQL句子不能将字符转换为数字NO_DATA_FOUND运用selectinto未返回行，或应用索引表未初始化的TOO_MANY_ROWS履行selectinto时，成果集超越一行ZERO_DIVIDE除数为0SUBSCRIPT_BEYOND_COUNT元素下标超越嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT运用嵌套表或VARRAY时，将下标指定为负数VALUE_ERROR赋值时，变量长度不足以容纳实践数据LOGIN_DENIEDPL/SQL应用程序衔接到oracle数据库时，提供了不正确的用户名或暗码NOT_LOGGED_ONPL/SQL应用程序在没有衔接oralce数据库的情况下访问数据PROGRAM_ERRORPL/SQL内部问题，可能需求重装数据字典＆pl./SQL体系包ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量的返回类型不兼容SELF_IS_NULL运用目标类型时，在null目标上调用目标方法STORAGE_ERROR运行PL/SQL时，超出内存空间SYS_INVALID_ID无效的ROWID字符串TIMEOUT_ON_RESOURCEOracle在等候资源时超时
根本语法
1.根本结构
CREATEORREPLACEPROCEDURE存储进程姓名
(
参数1INNUMBER,
参数2INNUMBER
)IS
变量1INTEGER:=0;
变量2DATE;
BEGIN
–履行体
END存储进程姓名;
2.SELECTINTOSTATEMENT
将select查询的成果存入到变量中，能够一起将多个列存储多个变量中，必须有一条记载，不然抛出反常(如果没有记载抛出NO_DATA_FOUND)
比如：
BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
3.IF判断
IFV_TEST=1THEN
BEGIN
dosomething
END;
ENDIF;
4.while循环
WHILEV_TEST=1LOOP
BEGIN
XXXX
END;
ENDLOOP;
5.变量赋值
V_TEST:=123;
6.用forin运用cursor
CURSORcurISSELECT*FROMxxx;
BEGIN
FORcur_resultincurLOOP
BEGIN
V_SUM:=cur_result.列名1+cur_result.列名2
END;
ENDLOOP;
END;
7.带参数的cursor
CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值);
FETCHC_USERINTOV_NAME;
EXITWHENFETCHC_USER%NOTFOUND;
CLOSEC_USER;
8.用pl/sqldeveloperdebug
衔接数据库后建立一个TestWINDOW,在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
关于oracle存储进程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：
selecta.appnamefromappinfoa;–正确
selecta.appnamefromappinfoasa;–过错
或许，是怕和oracle中的存储进程中的关键字as冲突的问题吧
2.在存储进程中，select某一字段时，后面必须紧跟into，如果select整个记载，运用游标的话就另当别论了。
selectaf.keynodeintoknfromAPPFOUNDATIONaf
whereaf.appid=aidandaf.foundationid=fid;–有into，正确编译
selectaf.keynodefromAPPFOUNDATIONaf
whereaf.appid=aidandaf.foundationid=fid;–没有into，编译报错，提示：CompilationError:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement
3.在运用select…into…语法时，必须先确保数据库中有该条记载，不然会报出”nodatafound”反常。
能够在该语法之前，先运用selectcount(*)from检查数据库中是否存在该记载，如果存在，再运用select…into…
4.在存储进程中，别名不能和字段名称相同，不然虽然编译能够经过，但在运行阶段会报错
–正确
selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;
–过错
selectaf.keynodeintoknfromAPPFOUNDATIONaf
whereaf.appid=appidandaf.foundationid=foundationid;
–运行阶段报错，提示ORA-01422:exactfetchreturnsmorethanrequestednumberofrows
5.在存储进程中，关于呈现null的问题
假设有一个表A，界说如下：
createtableA(
idvarchar2(50)primarykeynotnull,
vcountnumber(8)notnull,
bidvarchar2(50)notnull–外键
);
如果在存储进程中，运用如下句子：
selectsum(vcount)intofcountfromAwherebid=’xxxxxx’;
如果A表中不存在bid=”xxxxxx”的记载，则fcount=null(即使fcount界说时设置了默认值，如：fcountnumber(8):=0仍然无效，fcount仍是会变成null)，这样今后运用fcount时就可能有问题，所以在这里最好先判断一下：
iffcountisnullthen
fcount:=0;
endif;
这样就全部ok了。
6.Hibernate调用oracle存储进程
this.pnumberManager.getHibernateTemplate().execute(
newHibernateCallback(){
publicObjectdoInHibernate(Sessionsession)
throwsHibernateException,SQLException{
CallableStatementcs=session
.connection()
.prepareCall(“{callmodifyapppnumber_remain(?)}”);
cs.setString(1,foundationid);
cs.execute();
returnnull;
}
});
评论前必须登录！
注册