存储过程

最新推荐文章于 2022-11-04 15:29:03 发布

xfyjg

最新推荐文章于 2022-11-04 15:29:03 发布

阅读量88

点赞数

分类专栏： oracle 文章标签： C C++ C# SQL Server SQL

本文链接：https://blog.csdn.net/xfyjg/article/details/83739342

版权

oracle 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文介绍了一个Oracle PL/SQL触发器示例，该触发器在插入到srm_t_runtime_session表后执行，用于记录播放日志。触发器通过查询获取资产ID、客户端ID等信息，并将这些信息插入到mc.report_play_log表中。

摘要由CSDN通过智能技术生成

create or replace trigger reportTrigger
after insert on urm.srm_t_runtime_session
for each row

declare
v_eqamIp varchar2(16);
v_movName varchar2(128);
v_assetId varchar2(50);
v_clientId  varchar2(40);
v_ipqamName varchar2(100);
begin

v_assetId := hextostring(:new.asset_id);
v_clientId := clientidtostbsn(:new.client_id);
 begin
  select qam.video_ip1 into v_eqamIp from urm.srm_t_ipqam qam where qam.ipqam_id = (
         select p.ipqam_id from urm.srm_t_qam_port p where p.qam_port_id=(
                select c.qam_port_id from urm.srm_t_vod_channel c where c.qam_channel_id=:new.qam_channel_id
         )
  );

  select i.ipqam_name into v_ipqamName from srm_t_ipqam i where ipqam_id= (
         select p.ipqam_id from srm_t_qam_port p where p.qam_port_id=(
         select c.qam_port_id from srm_t_vod_channel c where c.qam_channel_id=:new.qam_channel_id
         )
  );

  select a.assetname into v_movName from bms.asset a where a.assetid=v_assetId;

  insert into mc.report_play_log(session_id, start_time, ssp_server_id, op_type_name, user_id, stb_ip_add ,stb_ip_port ,rtsp_agent_id ,qam_channel_id, asset_id ,release_request
  , update_time, client_id, bitrate, eqamip, assetname,id,ipqam_Name) 
  values (:new.session_id, :new.start_time, :new.ssp_server_id, :new.op_type_name, :new.user_id, :new.stb_ip_add ,:new.stb_ip_port ,:new.rtsp_agent_id ,
  :new.qam_channel_id, v_assetid ,:new.release_request, :new.update_time, v_clientId, :new.bitrate, v_eqamIp ,v_movName,SEQ_REPORT_PLAY_LOG.NEXTVAL,v_ipqamName) ;

 exception
 when others then --抛出异常 others 可以是异常名称
   null;--null 什么都不做
 end;
end;


declare    
 begin   
  proc_test();  
 end;