Oracle存储过程

最新推荐文章于 2024-10-12 23:21:07 发布

zhangzikui

最新推荐文章于 2024-10-12 23:21:07 发布

阅读量613

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： oracle 存储 query insert exception class

本文链接：https://blog.youkuaiyun.com/zhangzikui/article/details/6561486

数据库专栏收录该内容

19 篇文章

订阅专栏

create or replace procedure ossBusiInfoImport
(ossBusiId in varchar2, reqId in varchar2, log_accept in varchar2, ele_key in varchar2, ele_val in varchar2)

is
    busiId pd_busi_instance_info.busi_id%TYPE;  --业务信息ID

    classid or_req_info.class_id%TYPE;

    element_val pd_element_instance_info.element_value%TYPE;

    --带参数游标
    cursor query_z(log_acc pd_element_instance_info.loginaccept%TYPE, b_id pd_element_instance_info.busi_id%TYPE, e_id pd_element_instance_info.element_id%TYPE)
    is select element_value from pd_element_instance_info where loginaccept=log_acc and busi_id=b_id and element_id=e_id;

    --带参数游标
    cursor query_m(log_acc pd_element_instance_mid.loginaccept%TYPE, b_id pd_element_instance_mid.busi_id%TYPE, e_id pd_element_instance_mid.element_id%TYPE)
    is select element_value from pd_element_instance_mid where loginaccept=log_acc and busi_id=b_id and element_id=e_id;

begin
    --根据reqId查询class_id
    select class_id into classid from or_req_info where req_id=reqId and login_accept=log_accept;

    --变更业务类型，更新中间表
    if(classid='10') then
        --根据一站式业务信息ID，查询对应的busiId
        select busi_id into busiId from pd_busi_instance_inter where oss_busi_id=ossBusiId;
    
        --更新电路代号
        if(ele_key='8077') then
            update pd_busi_instance_inter set BUSI_PHY_NUMBER=ele_val where busi_id=busiId and loginaccept=log_accept;
        end if;    

        --检查元素值是否存在，有则更新，无则插入
        if not query_m%ISOPEN then
            open query_m(log_accept, busiId, ele_key);
        end if;

        fetch query_m into element_val;

        if not query_m%FOUND then
            insert into pd_element_instance_mid values(log_accept, '', busiId, ele_key, ele_val, '');
        else
            update pd_element_instance_mid set element_value=ele_val where loginaccept=log_accept and busi_id=busiId and element_id=ele_key;
        end if;

        close query_m;
    --其它类型，更新正表
    else
        --根据一站式业务信息ID，查询对应的busiId
        select busi_id into busiId from pd_busi_instance_info where oss_busi_id=ossBusiId;
        
        --更新电路代号
        if(ele_key='8077') then
            update pd_busi_instance_info set BUSI_PHY_NUMBER=ele_val where busi_id=busiId and loginaccept=log_accept;
        end if;

        --检查元素值是否存在，有则更新，无则插入
        if not query_z%ISOPEN then
            open query_z(log_accept, busiId, ele_key);
        end if;

        fetch query_z into element_val;

        if not query_z%FOUND then
            insert into pd_element_instance_info values(log_accept, '', busiId, ele_key, ele_val);
        else
            update pd_element_instance_info set element_value=ele_val where busi_id=busiId and element_id=ele_key and loginaccept=log_accept;
        end if;

        close query_z;
    end if;

    commit;

    exception when others then
        DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);
        DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);    
        rollback;

end ossBusiInfoImport;

ibatis中调用：

</parameterMap>

{ call ossupdateNumbers(?, ?, ?, ?, ?, ?) }

</procedure>