简介面向数据集成开发与 ETL 实施人员这份 Informatica 调用存储过程图文流程文档聚焦在 Mapping 中通过目标表属性配置 pre SQL 调用存储过程的常见做法适合需要补齐存储过程与 Informatica 配合使用方法、快速查找操作要点的初、中级读者。压缩包仅含 1 个 docx 文件大小约 180KB篇幅精炼便于随查随用。内容围绕新建 Mapping、编辑目标表属性、在 pre SQL 栏写入调用脚本、使用变量替换入参以及保存运行等环节展开并补充存储过程的概念说明、优点与数据迁移、数据同步、报表生成等应用场景可帮助读者理解调用逻辑、梳理配置顺序与参数传递思路。已有 977 人学习适合作为日常开发中的流程参考。1. Informatica 调存储过程翻车点几乎都不在 SQL 本身在 Informatica 里调用存储过程很多人的第一次都是照着三步走完就上生产新建 Mapping、右键目标表 Edit、在 pre SQL 里贴上脚本。会话跑完显示成功去库表一查数据没变、过程没跑、日志里干干净净。这条流程本身没写错错在它只说了一半——pre SQL 到底在什么时刻触发、入参怎么从会话传进来、替换进 SQL 之后引号还在不在、失败了会话是红的还是绿的这几件事才是决定成败的地方。适合已经能跑通简单 Mapping、却被“存储过程到底执行了没有”卡住的一线 ETL 开发。2. 调用位置先定下来pre SQL、post SQL 与 Stored Procedure 转换的边界2.1 pre SQL 与 post SQL 到底在哪一刻执行目标实例的 Pre SQL 语义是向这张目标表写第一条记录之前由 Integration Service 把这个文本发到目标库执行一次。Post SQL 则是这张目标表的数据全部写完之后执行一次。注意“一次”这个词有前提——它是对着目标表算的不是对着行算的。所以在 Insert 模式下十万行数据也只触发一次 pre SQL这正是大家愿意用它来调存储过程的原因只付一次网络往返过程内部用集合操作批量处理比逐行走转换快一个量级。但边界条件得说清楚。目标类型必须是 relationalflat file 目标属性里根本没有 Pre SQL / Post SQL 这一栏硬找是找不到的目标写入模式如果是 Update else InsertInformatica 会拆成 update 阶段和 insert 阶段两段执行pre SQL 和 post SQL 也会各跑一遍这一点在日志里能数出来也是后面避坑一章要说的重复批次问题根源如果会话开了分区pre SQL 是对整张目标表执行一次不按分区复制。还有一条容易忽略Target Load Type 选 Bulk 时走的是数据库侧的批量装载通道pre SQL 依然会执行但中间那条 DML 路径不受 Informatica 事务保护回滚语义会变。我一般把 pre SQL 当成“装载前的准备动作”用它做三件事按批次号清掉上一轮的残留数据、调用装载包、刷新一张中间汇总表。所有依赖“数据已经写进去”的动作都不放这里放 Post SQL。2.2 Stored Procedure 转换的两种接入方式除了 pre SQLInformatica 还提供 Stored Procedure 转换它有两种接入形态行为差别很大。Connected 形态是把转换直接拖进数据流和上下游连线转换上带输入端口和返回端口。它默认对流的每一行调用一次存储过程。数据量小的时候没感觉一旦源表几十万行就是几十万次过程调用每次都要走一次数据库往返会话会被拖到超时。想让它只跑一次得在转换属性里把Call Text的执行方式从默认的“每行调用”改成“执行一次”或者干脆不用 Connected 形态。Unconnected 形态是把转换丢在 Mapping 里不连线然后在 Expression 转换的输出端口里写:SP.过程名(参数1, 参数2)来调用返回值赋给输出端口。这种写法适合拿过程的返回值做分支判断比如过程返回 0 表示校验通过、非 0 表示数据有问题后面接一个 Router 转换分流。缺点是参数类型必须严格对齐日期、数值、字符串三种类型的隐式转换规则和你手写 SQL 时不完全一样报错信息还特别绕。还有一种位置是会话级的在 Session 属性里配置预加载存储过程在整个会话开始前跑一次与目标表无关。做过批量调度的同行一般用它来做批次初始化把“过程调用”和“目标写入”这两件事彻底解耦。2.3 一张对照表按场景反推该用哪个选型不用纠结把执行次数、执行时机、能不能拿返回值三件事列出来答案基本就固定了。位置触发时机执行次数能否取返回值适用场景Target Pre SQL写目标表之前每张目标表一次Update else Insert 模式两次不能批量装载、批次清理、刷新中间表Target Post SQL目标数据写完之后同上不能汇总、状态回写、发通知Stored Procedure 转换Connected数据流中随行默认每行一次能逐行校验、逐行取码值Stored Procedure 转换Unconnected表达式求值处按调用点能分支判断、单次取标量Session 预加载过程会话开始前一次不能批次初始化、参数落库判断口诀很直白如果过程内部自己会做集合操作一次能把整批数据搞定就走 pre SQL别用转换如果必须逐行拿到返回值参与后续逻辑才用 Stored Procedure 转换并且优先考虑 Unconnected减少调用次数。原文给的 pre SQL 路线覆盖的正是最常见的那 80% 场景——批量装载。3. 照着走一遍建 Mapping 到 pre SQL 落脚本的完整动作3.1 建 Mapping 前先把目标定义和连接对齐打开 Mapping Designer新建一个 Mapping把源表和目标表拖进来这是原文的第一步但真正决定后面能不能跑通的动作在拖进来之前。目标表必须先在 Source Analyzer 或 Target Designer 里从数据库导入一次字段名、字段类型、长度和库里保持一致。我见过太多情况是 pre SQL 里的存储过程跑得好好的转头在 insert 阶段报字段类型不匹配最后发现目标定义是三个月前手工建的库里加了一列。导入时选“从数据库导入”把同义词展开成实表别让目标定义挂在同义词上。连接对象要单独确认。Mapping 用的连接和工作流里会话实际使用的连接可能不是同一个开发环境用的账号往往有 DBA 权限生产环境给的账号只有目标表的 INSERT 权限没有存储过程的 EXECUTE 权限。上线前拿生产账号在库里手工执行一次EXECUTE 过程名(参数)能过再往下走这一步花五分钟省掉一次上线回滚。3.2 目标实例的 Edit 里哪些属性可写原文说“右键目标表 → Edit”这里的右键对象是 Mapping 画布里的目标实例不是 Repository 里的表定义。点开之后有一个多标签的编辑框切到 Properties 选项卡Pre SQL 和 Post SQL 是两段多行文本能直接敲脚本也支持从外部粘贴。这个框里有几个隐性限制文本长度有上限脚本特别长的时候我一般把逻辑封进存储过程框里只留一行调用不支持注释掉的整段脚本保留原样发给数据库某些驱动会因为前导注释解析失败稳妥做法是注释只写在行尾框里不要出现 SQL*Plus 的斜杠终结符那个符号是客户端的东西Informatica 会原样发给驱动直接换来一个语法错误。同一个 Properties 页里还有 target 的 update override 之类的选项和 pre SQL 不是一回事别混着改。改完记得在 Mapping 里保存Designer 不会自动落库。3.3 pre SQL 里的调用脚本怎么写不同数据库的调用语法差别不小脚本形态必须跟着目标库走不能照抄。-- 目标库为 Oracle用匿名块调用带入参的装载过程 BEGIN dw_pkg.prc_load_sales( p_batch_date TO_DATE(:P_BATCHDATE, YYYY-MM-DD), p_src_system CRM ); END;这段脚本里几个点值得逐个说。BEGIN ... END;是必须的匿名块没有它数据库不知道这是一个 PL/SQL 块会按普通 SQL 解析然后报错。END后面的分号不能省省了就是语法错误。参数位置的:P_BATCHDATE是 Mapping 参数引用写法Informatica 在发给数据库之前会做文本替换把参数值原样贴进去所以外层的单引号必须自己加——这是全文最容易翻车的一处参数值是字符串替换后引号丢了数据库看到的是一个裸的日期字面量直接报标识符无效。末尾那个分号是语句结束符匿名块本身需要一个。如果目标库是 SQL Server写法换成过程名前加EXEC不需要 BEGIN/END 包裹如果是 PostgreSQL用CALL 过程名(参数)。跨库迁移 Mapping 的时候这一段是必须重写的别指望兼容。参数引用有个坑不同版本对前缀的识别不完全一致:前缀在 Pre SQL 里通常按 Mapping 参数解析$$前缀更多见于参数文件和会话参数场景。我一般的做法是先写一个只含参数的极简脚本跑一次去会话日志里看打印出来的实际 SQL 长什么样确认替换正确了再往里加逻辑。日志里能看到替换前和替换后两条这是最可靠的黑匣子。3.4 保存、校验与会话运行Mapping 保存之后到 Workflow Manager 里建 SessionSession 的 Mapping 页签选中刚建的那个连接赋值页签把源和目标的连接都指定上源连接和目标连接分开赋值别指望它自动继承。跑之前先做一次 Mapping 校验Designer 里点校验主要看端口有没有悬空、参数有没有未定义。参数未定义在 Designer 里可能只是警告跑到会话里就直接失败日志里写得很含糊所以校验这一步不要跳。跑会话可以用 Monitor 手工启动也可以走命令行。批量调度场景下命令行更常见# 触发工作流并把批次日期作为参数覆盖进去 pmcmd startworkflow \ -sv IS_PROD \ -d DOMAIN_PROD \ -u $INFA_USER -p $INFA_PWD \ -f DW_FOLDER \ -wait wf_load_sales \ -paramfile /opt/infa/params/sales_20240501.par-sv指 Integration Service 名-d指域-f指工作流所在的文件夹-wait让命令阻塞到工作流结束再返回调度脚本靠这个返回值判断成功失败不写-wait会立刻返回后面的判断全是假的。-paramfile指定参数文件优先级高于 Session 里保存的默认值这条链路是自动化批次的标配。4. 入参换成变量参数文件、作用域与引号拼接的写法4.1 硬编码入参为什么必须换成变量脚本里写死p_batch_date TO_DATE(2024-05-01)当天的批次能跑第二天就得改 Mapping、重新保存、重新校验、重新部署。一天一改两周之内必然有人改错版本。把入参抽成变量之后同一份 Mapping 每天用不同参数文件调用代码零改动这才是可运维的形态。原文提到“如果用到变量将入参替换为变量即可”这句话实操起来比看上去复杂因为 Informatica 里有三层变量作用域Mapping 参数定义在 Mapping 层面随 Mapping 一起走Session 参数定义在 Session 属性里可以覆盖 Mapping 参数工作流变量定义在 Workflow 层面只在工作流里可见。Pre SQL 里最稳的是用 Mapping 参数然后在 Session 里覆盖它的值工作流变量在 Pre SQL 里引用容易踩空。4.2 参数文件的结构与会话覆盖参数文件是纯文本节名定位到具体对象参数名带$$前缀值不带引号。# /opt/infa/params/sales_20240501.par # 节名层级文件夹.工作流.会话 [DW_FOLDER.wf_load_sales.s_load_sales] $$P_BATCHDATE2024-05-01 $$P_SRCSYSTEMCRM节名写成三层最保险只写会话名在多数版本里也能匹配但同一文件夹下会话重名的时候会取错排查起来非常费劲。参数名大小写要和 Mapping 里定义的完全一致Informatica 对参数名大小写的处理在不同版本上表现不同统一大写是最省事的约定。值的引号问题要单独强调参数文件里的值不加引号加了引号引号会被当成值的一部分传进去最终拼到 SQL 里变成两个引号数据库直接报错。我见过有人为了“保险”在参数文件里写成$$P_BATCHDATE2024-05-01结果 pre SQL 里再套一层单引号拼出三层引号报错信息完全看不出原因。4.3 日期与字符串拼接的写法这节是血泪经验最集中的地方。Informatica 的 pre SQL 参数替换是纯文本替换不是数据库绑定变量。这意味着参数值是什么样贴进脚本就是什么样类型安全、转义、格式化全部由写脚本的人负责。字符串参数脚本里要自己加单引号。p_src_system :P_SRCSYSTEM是正确写法外面那层引号必须有。日期参数不要让数据库做隐式转换。TO_DATE(:P_BATCHDATE, YYYY-MM-DD)显式指定格式参数统一用YYYY-MM-DD传。隐式转换依赖会话的 NLS 设置开发机和生产库的 NLS 不一致时同一份脚本在开发环境正常在生产环境把日期解析成完全不同的值或者直接抛格式错误。这种问题在测试环境复现不了只能靠显式格式串规避。数值参数一般不需要引号但如果参数文件里没赋值、参数为空替换后会变成空字符串拼出来的 SQL 片段是残缺的报错指向的位置和真正原因差很远。稳妥做法是在 Mapping 里给参数一个默认值参数没传时用默认值兜底。-- 三种类型参数的完整写法字符串加引号日期显式转数值直接放 BEGIN dw_pkg.prc_load_sales( p_batch_date TO_DATE(:P_BATCHDATE, YYYY-MM-DD), p_src_system :P_SRCSYSTEM, p_max_rows :P_MAXROWS ); END;跑之前去会话日志里搜一下最终执行的 SQL 文本三种参数的替换结果一眼就能看出来对不对比在库里猜快得多。4.4 参数优先级与调度传参三层来源同时存在时生效顺序是命令行或参数文件覆盖 Session 里保存的值Session 里保存的值覆盖 Mapping 里定义的默认值。排错时如果发现参数值不是预期的那个从上往下逐层排除先看调度脚本传了什么再看参数文件里写了什么最后看 Session 属性里存了什么。调度脚本里临时传一个参数用-param直接给键值对比临时生成一个参数文件快固定批次走参数文件便于留痕和回溯。两种方式混用的时候以命令行为准我一般在调度模板里统一只用参数文件减少一层不确定性。5. 避坑六条踩坑记录与现象-原因-解决5.1 会话绿灯过程没跑现象Monitor 里会话状态成功目标表数据也进去了但存储过程负责的那张汇总表一行没变。原因目标实例类型不是 relational或者脚本贴到了 Source Qualifier 的 SQL Override 里。前者根本没有 Pre SQL 属性后者是查询语句不是执行语句数据库把它当 SELECT 解析运气好报错运气不好什么都不做。解决确认目标是从数据库导入的 relational 目标Pre SQL 文本框里有内容且保存成功。跑完之后去会话日志里搜PRE SQL关键字日志里会打印准备执行的语句搜不到就说明这一段压根没被加载。5.2 Update else Insert 目标上过程跑了两遍现象过程内部的批次日志表里同一个批次号出现了两条开始记录或者清理逻辑把刚插进去的数据又删了一遍。原因Update else Insert 模式下Informatica 分 update 和 insert 两个阶段执行pre SQL 和 post SQL 每个阶段各跑一次一共两次。解决把过程写成幂等进过程先按批次号删自己范围内的数据再插跑两次结果一样或者把清理逻辑挪到 Post SQL写入后的动作不受阶段拆分的重复影响。这个坑不看日志根本发现不了血泪经验。5.3 参数没被替换报标识符无效现象数据库报ORA-00904: 标识符无效或者语法错误错误信息里赫然出现:P_BATCHDATE这个原始字符串。原因参数名拼写和 Mapping 里定义的不一致或者参数只定义在 Session 层面、Pre SQL 里用:前缀引用不到。解决参数名统一大写并复制粘贴不要手敲先在 Mapping 层定义参数再用 Session 覆盖值。日志里打印的替换后 SQL 是最终判据看到原始占位符就说明没替换成功。5.4 日期出错但只在生产环境复现现象测试环境跑得好好的生产环境报日期格式错误或者数据落进去日期差了几个月。原因参数是字符串替换脚本里没写显式 TO_DATE数据库按会话的 NLS 日期格式做隐式转换两套环境的 NLS 设置不一样。解决所有日期参数一律显式TO_DATE加格式串参数统一用YYYY-MM-DD传不依赖任何环境配置。这条改完之后跨环境迁移基本不会再因为日期翻车。5.5 过程内 COMMIT 把会话事务拆了现象会话中途失败目标表里残留了一部分数据回滚不干净重跑之后数据重复。原因存储过程内部自己写了 COMMIT提交动作脱离了 Informatica 的事务边界会话认为数据还没写完数据库已经把它落盘了。解决过程内不做提交提交交给会话结束时的正常流程如果业务上必须分段提交就在过程里做幂等重跑前按批次号清干净。这条规则我在所有项目上都强制写进开发规范里。5.6 分区会话里过程被调了多次现象会话开了两个分区过程日志显示被执行了两次清理逻辑互相干扰。原因分区会话下每个分区有自己的数据库连接目标侧的 pre SQL 执行次数会随配置和行为变化不能默认只有一次。解决把会话分区数降到 1 再验证过程逻辑是否正确确认没问题后再开分区并且把过程写成对重复执行不敏感。或者把过程调用从 pre SQL 挪到会话级预加载过程与分区彻底解耦。6. 验证与幂等重跑三条自查线索和一个批次号技巧过程跑没跑成功不能只看 Monitor 的绿灯那是 Informatica 自己的状态不代表数据库里的过程执行成功。我一般同时看三条线索会话日志里PRE SQL打印的替换后语句确认参数、引号、格式都对存储过程内部维护的批次日志表记录批次号、开始时间、结束时间、影响行数目标表按批次号 count 一次和源表对得上才算完。线索看什么判断依据会话日志替换后的 pre SQL 文本参数已替换、引号完整过程日志表批次号与影响行数每个批次号只有一条成功记录目标表按批次号 count与源侧过滤后的行数一致进阶一点的做法是把批次号做成过程的第一个入参过程开头先删本批次数据再插结尾写日志表。这样无论过程被调一次还是两次无论重跑几轮结果都收敛到同一个状态。下面这段是过程内幂等骨架的示意逻辑比语法更重要-- 过程内幂等骨架按批次号自清自杀 CREATE OR REPLACE PROCEDURE prc_load_sales ( p_batch_date IN DATE, p_src_system IN VARCHAR2 ) IS BEGIN -- 先清本批次重跑多少遍都不会重复 DELETE FROM dw_sales_fact WHERE batch_date p_batch_date AND src_system p_src_system; -- 再做装载内部不提交 INSERT INTO dw_sales_fact (batch_date, src_system, amount) SELECT p_batch_date, p_src_system, SUM(amount) FROM stg_sales WHERE stat_date p_batch_date GROUP BY 1, 2; END;参数部分只需要盯住两点批次日期用 DATE 类型接收调用侧用 TO_DATE 显式转换源系统名用 VARCHAR2 接收调用侧保证外层单引号存在。过程内部不写 COMMIT提交交给会话。这个骨架改一改就能套到绝大多数装载场景上比起每次现写脚本省下的是反复排错的时间。从那以后我每次新增一个调存储过程的 Mapping都强制走一遍“日志里看替换后 SQL、过程日志表看批次记录、目标表按批次对行数”这三步一次不落。希望帮到你。本文还有配套的精品资源点击获取
