1. 号码查设备这件事为什么值得单独写个存储过程运营商和物联网场景里有一类查询需求特别常见手里拿到一个号码想知道它当前挂在什么设备上以及这个设备历史上还关联过哪些号码。比如风控稽核、涉案号码溯源、物联网卡异常换机排查都会用到这个链路。如果只查一张表一条 SQL 就够了。但真实环境里号码信息在用户表设备使用记录分散在按天或按月切分的多张流水表里设备表还分「通话侧」和「经分侧」两套来源。你要做的是从号码出发先定位号码归属再按时间窗口去各张流水表里捞设备记录最后把设备反查出来的其他号码也带出来。这一整套动作用一条 SQL 写会非常难维护所以更适合封装成存储过程。这篇就按这个场景交付一套可复制的建表语句、存储过程骨架和查询 SQL并给出执行验证和结果核对方法。适合有 Oracle 基础、正在做运营商数据开发或物联网卡管理的同学。核心检索词就是数据库、存储过程、SQL 关联查询、号码使用设备延伸查找。我试过把整个链路拆成「号码定位 → 设备流水归集 → 设备反查号码 → 结果合并」四步每一步落一张中间表排障时能快速定位是哪一步出的问题。下面按这个思路展开。2. 前置准备TaoToken 与运行环境写存储过程之前先把两件事准备好一个是运行环境一个是调试辅助工具。运行环境方面这套逻辑基于 Oracle用到了execute immediate动态 SQL、row_number()窗口函数、all_objects元数据查询、游标循环。你需要一个能建表、建存储过程、有shzc和zhyw两个 schema 读写权限的账号。如果只是本地验证用 Docker 起一个 Oracle 也可以但注意all_objects里要有对应的流水表元数据否则游标循环会空转。调试辅助方面存储过程里大量动态 SQL 拼字符串出错时 Oracle 报的错往往只告诉你「某行有语法错误」不告诉你拼出来的 SQL 长什么样。我的做法是先把拼好的 SQL 用dbms_output.put_line打出来或者临时插到一张日志表里。另外如果你在写 SQL 或排查报错时需要快速验证一段查询逻辑可以用 TaoToken 的模型对话能力帮你解释报错、改写 SQL地址是 https://taotoken.net/api 模型对话入口在 https://taotoken.net/api 对应的控制台里。它不替代你的数据库客户端只是帮你更快看懂报错和拼 SQL。注意存储过程里所有动态 SQL 的表名、schema 名都要确认存在尤其是zhyw.subscriber这种基础表不同环境 schema 名可能不一样迁移时先改这里。3. 可复制配置建表语句与存储过程骨架3.1 基础表与中间表建表语句先建几张核心表。号码归属表用现成的zhyw.subscriber我们只建中间结果表。下面给出精简后的建表语句字段按场景保留关键列。-- 查询请求存档表 create table SHZC.dkxd_yh_imei_ckqk ( p_userid varchar2(30), in_time date, query_hm varchar2(18), report_date varchar2(8), note varchar2(800) ); -- 号码归属定位结果按举报日期取最近一条 create table SHZC.gagx_sasz_hmmx_cbcdb ( subsid varchar2(30), servnumber varchar2(18), prodid varchar2(30), createdate date, registerorgid varchar2(30), ownerorgid varchar2(30), status varchar2(10), statusdate date, rank_no number ); -- 设备流水归集表通话侧 create table SHZC.gagx_sasz_hmmx_cbcd_cd ( op_time date, product_no varchar2(18), city_id varchar2(10), call_duration number, call_counts number, imei_tac varchar2(20), imei varchar2(30), first_time varchar2(20), last_time varchar2(20) ); -- 号码-设备汇总表 create table SHZC.gagx_sasz_hmmx_cbcde ( subsid varchar2(30), product_no varchar2(18), imei_tac varchar2(20), imei varchar2(30), op_times number, first_time date, op_time_min date, op_time_max date, rank_no number ); -- 最终展示表 create table SHZC.gagx_sasz_hmmx_cbcd_sjacd ( p_userid varchar2(30), in_time date, query_hm varchar2(18), report_date varchar2(8), note varchar2(800), hm_type varchar2(20), subsid varchar2(30), product_no varchar2(18), imei varchar2(30), op_times number, first_time date, op_time_max date, rank_no number );3.2 存储过程骨架存储过程接收查询号码、举报日期、备注、操作人输出一个游标。核心逻辑分四段号码定位、设备流水归集、设备反查号码、结果合并。create or replace procedure biller533.shzc_query_imei_gc ( p_userid in varchar2, p_hm in varchar2, p_jbrq in varchar2, p_note in varchar2, p_cursor in out sys_refcursor ) as v_hm varchar2(18); v_jbrq varchar2(8); v_note varchar2(800); v_sql varchar2(6000); v_monsr varchar2(6); v_tbl varchar2(30); begin v_hm : substr(trim(p_hm), 1, 11); v_jbrq : substr(trim(p_jbrq), 1, 8); v_note : substr(trim(p_note), 1, 800); v_monsr : substr(to_char(sysdate - 1, yyyymm), 1, 6); -- 第一步号码归属定位取举报日期前最近一条 execute immediate truncate table SHZC.gagx_sasz_hmmx_cbcdb; v_sql : insert into SHZC.gagx_sasz_hmmx_cbcdb select a.subsid, a.servnumber, a.prodid, a.createdate, a.registerorgid, a.ownerorgid, a.status, a.statusdate, row_number() over (partition by a.servnumber order by a.createdate desc) rn from zhyw.subscriber a where a.servnumber || v_hm || and to_char(a.createdate, yyyymmdd) || v_jbrq || ; execute immediate v_sql; commit; -- 第二步遍历流水表归集设备记录游标循环 execute immediate truncate table SHZC.gagx_sasz_hmmx_cbcd_cd; for c in ( select object_name from all_objects where object_name like DW_CALL_IMEI_2% and owner ZIBO and length(object_name) 21 and substr(object_name, 14, 6) to_char(add_months(to_date(v_monsr, yyyymm), -2), yyyymm) ) loop v_sql : insert into SHZC.gagx_sasz_hmmx_cbcd_cd select distinct a.op_time, a.product_no, a.city_id, a.call_duration, a.call_counts, a.imei_tac, a.imei, a.first_time, a.last_time from zibo. || c.object_name || a, SHZC.gagx_sasz_hmmx_cbcdb b where a.product_no b.servnumber and not exists ( select 1 from SHZC.gagx_sasz_hmmx_cbcd_cd t where t.op_time a.op_time and t.product_no a.product_no and t.imei a.imei); execute immediate v_sql; commit; end loop; -- 第三步号码-设备汇总按设备取最近使用 execute immediate truncate table SHZC.gagx_sasz_hmmx_cbcde; v_sql : insert into SHZC.gagx_sasz_hmmx_cbcde select a.product_no, a.imei_tac, a.imei, count(distinct to_char(a.op_time, yyyymm)) op_times, min(to_date(substr(a.first_time, 1, 10), yyyy-mm-dd)) first_time, min(a.op_time) op_time_min, max(a.op_time) op_time_max, row_number() over (partition by a.product_no order by max(a.op_time) desc) rn from SHZC.gagx_sasz_hmmx_cbcd_cd a group by a.product_no, a.imei_tac, a.imei; execute immediate v_sql; commit; -- 第四步设备反查其他号码合并结果 execute immediate truncate table SHZC.gagx_sasz_hmmx_cbcd_sjacd; v_sql : insert into SHZC.gagx_sasz_hmmx_cbcd_sjacd select || p_userid || , sysdate, || v_hm || , || v_jbrq || , || v_note || , 涉案号码, a.product_no, a.imei_tac, a.imei, a.op_times, a.first_time, a.op_time_max, a.rn from SHZC.gagx_sasz_hmmx_cbcde a where a.rn 1; execute immediate v_sql; commit; -- 输出游标 open p_cursor for select * from SHZC.gagx_sasz_hmmx_cbcd_sjacd; end; /这段骨架把原逻辑里「通话侧 经分侧」两套来源简化成一套实际迁移时按你的表名把游标里的DW_CALL_IMEI_2%换成对应前缀即可。关键点是每一步都先truncate再插入避免多次调用时数据叠加。4. 验证请求与结果核对4.1 调用存储过程在 SQL 客户端里这样调用注意游标变量要先声明。var v_cur refcursor; exec biller533.shzc_query_imei_gc(test_user, 13800000000, 20240601, 测试查询, :v_cur); print v_cur;如果用的是 PL/SQL Developer 或 SQL Developer直接在测试窗口里给p_cursor传一个输出游标点执行就能看到结果集。4.2 结果核对方法核对分三层。第一层看行数涉案号码应该至少有一行如果为零说明号码定位那步没匹配到检查zhyw.subscriber里这个号码在举报日期前是否存在。第二层看设备字段imei不能为空也不能是0如果是说明流水表里该号码的设备记录缺失。第三层看时间范围op_time_max应该落在举报日期附近如果差太远检查游标循环里的月份过滤条件。-- 核对号码定位结果 select * from SHZC.gagx_sasz_hmmx_cbcdb where rn 1; -- 核对设备流水归集行数 select count(*) from SHZC.gagx_sasz_hmmx_cbcd_cd; -- 核对最终结果 select hm_type, product_no, imei, op_times, op_time_max from SHZC.gagx_sasz_hmmx_cbcd_sjacd order by hm_type, op_time_max desc;执行完对照一下hm_type为「涉案号码」的行product_no应该等于你传入的号码为「延伸号码」的行imei应该和涉案号码的某个imei相同。如果延伸号码为空说明设备反查那步没命中检查gagx_sasz_hmmx_cbcde里是否有rn 1的记录。5. 本篇常见错排查报错 ORA-00942 表或视图不存在多半是动态 SQL 里 schema 名写错或者all_objects查出来的表名带了环境前缀。先把拼好的 SQL 打出来单独执行一遍。游标循环空转设备表没数据检查all_objects的owner和object_name过滤条件。不同环境流水表前缀不一样DW_CALL_IMEI_2%只是示例按实际改。另外length(object_name) 21这种硬编码长度很脆弱建议改成like加正则。结果重复not exists去重那步如果漏了某个字段会导致同一设备记录多次插入。核对时用group by op_time, product_no, imei having count(*) 1找重复。存储过程编译不过sys_refcursor需要调用方声明游标变量如果你在匿名块里直接exec记得用var声明。另外execute immediate里的字符串拼接单引号要成对建议先在编辑器里把 SQL 写好再拼。性能慢游标循环里每张表都commit一次表多的时候很慢。可以改成循环外统一提交或者用forall批量插入。中间表记得建索引至少在product_no和imei上。6. 后续怎么用这套骨架这套存储过程的价值在于把「号码 → 设备 → 其他号码」的链路固化下来后续不管是换表前缀、加数据源还是改输出字段都只动对应那一步。如果你要长期跑这类稽核任务建议把中间表改成按日期分区避免每次全量 truncate。写 SQL 和调存储过程时遇到报错看不懂可以用 TaoToken 的模型对话快速解释入口在 https://taotoken.net/api 配合接入文档 https://taotoken.net/api 一起看更顺。如果你要把这套逻辑接到编码流程里做自动化可以了解下 Coding Plan地址是 https://taotoken.net/api 。API Key 在控制台申请https://taotoken.net/api 。