oracle 创建存储过程查询历史交易明细 提高查询效率

最新推荐文章于 2022-05-19 15:48:22 发布
最新推荐文章于 2022-05-19 15:48:22 发布
阅读量1.2k 收藏
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiehui_top/article/details/9905025
版权
create or replace package tomorrow as
  type p_cursor is ref Cursor;
end;


create or replace package pro_query_postxnjnl is
  ---办事处，业务员，商户，查询历史交易明细
  procedure query_postxnjnl_his_info(curpage     in varchar2,
                                     unitpage    in varchar2,
                                     stldt       in varchar2,
                                     stldtstr    in varchar2,
                                     stldtend    in varchar2,
                                     txncd       in varchar2,
                                     txnsts      in varchar2,
                                     agtorg      in varchar2,
                                     txnamtstr   in varchar2,
                                     txnamtend   in varchar2,
                                     mercid      in varchar2,
                                     mernam      in varchar2,
                                     srefno      in varchar2,
                                     osrefno     in varchar2,
                                     logno       in varchar2,
                                     ologno      in varchar2,
                                     termid      in varchar2,
                                     flag        in varchar2,
                                     mercid_oper in varchar2,
                                     agentid     in varchar2,
                                     tlrid       in varchar2,
                                     v_cursor    out tomorrow.p_cursor);
  procedure query_postxnjnl_his_count(stldt       in varchar2,
                                      stldtstr    in varchar2,
                                      stldtend    in varchar2,
                                      txncd       in varchar2,
                                      txnsts      in varchar2,
                                      agtorg      in varchar2,
                                      txnamtstr   in varchar2,
                                      txnamtend   in varchar2,
                                      mercid      in varchar2,
                                      mernam      in varchar2,
                                      srefno      in varchar2,
                                      osrefno     in varchar2,
                                      logno       in varchar2,
                                      ologno      in varchar2,
                                      termid      in varchar2,
                                      flag        in varchar2,
                                      mercid_oper in varchar2,
                                      agentid     in varchar2,
                                      tlrid       in varchar2,
                                      v_cursor    out tomorrow.p_cursor);
  procedure query_postxnjnl_his_amttotal(stldt       in varchar2,
                                         stldtstr    in varchar2,
                                         stldtend    in varchar2,
                                         txncd       in varchar2,
                                         txnsts      in varchar2,
                                         agtorg      in varchar2,
                                         txnamtstr   in varchar2,
                                         txnamtend   in varchar2,
                                         mercid      in varchar2,
                                         mernam      in varchar2,
                                         srefno      in varchar2,
                                         osrefno     in varchar2,
                                         logno       in varchar2,
                                         ologno      in varchar2,
                                         termid      in varchar2,
                                         flag        in varchar2,
                                         mercid_oper in varchar2,
                                         agentid     in varchar2,
                                         tlrid       in varchar2,
                                         v_cursor    out tomorrow.p_cursor);

end;

create or replace package body pro_query_postxnjnl is
  ---办事处，业务员，商户，查询历史交易明细
  procedure query_postxnjnl_his_info(curpage     in varchar2,
                                     unitpage    in varchar2,
                                     stldt       in varchar2,
                                     stldtstr    in varchar2,
                                     stldtend    in varchar2,
                                     txncd       in varchar2,
                                     txnsts      in varchar2,
                                     agtorg      in varchar2,
                                     txnamtstr   in varchar2,
                                     txnamtend   in varchar2,
                                     mercid      in varchar2,
                                     mernam      in varchar2,
                                     srefno      in varchar2,
                                     osrefno     in varchar2,
                                     logno       in varchar2,
                                     ologno      in varchar2,
                                     termid      in varchar2,
                                     flag        in varchar2,
                                     mercid_oper in varchar2,
                                     agentid     in varchar2,
                                     tlrid       in varchar2,
                                     v_cursor    out tomorrow.p_cursor) as
 
    startpos            number;
    endpos              number;
    tj                  varchar2(500);
    tj2                 varchar2(100);
    tj3                 varchar2(100);
    sqlstr              varchar2(300);
    query_postxnjnl_his varchar2(3000);
  begin
    tj     := '';
    tj2    := '';
    tj3    := '';
    sqlstr := '';
    --商户号
    if (mercid is not null) then
      tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid || '%' || '''';
    end if;
    --商户名称
    if (mernam is not null) then
      tj := tj || ' AND I.MERNAM LIKE ' || '''' || '%' || mernam || '%' || '''';
    end if;
    --系统参考号
    if (srefno is not null) then
      tj := tj || ' AND P.SREFNO LIKE ' || '''' || '%' || srefno || '%' || '''';
    end if;
    --原系统参考号
    if (osrefno is not null) then
      tj := tj || ' AND P.TSREFNO LIKE ' || '''' || '%' || osrefno || '%' || '''';
    end if;
    --交易日期
    if (stldt is not null) then
      tj := tj || ' AND P.SYSDAT =''' || stldt || '''';
    end if;
    tj := tj || ' AND P.SYSDAT BETWEEN ''' || stldtstr || ''' AND ''' ||
          stldtend || '''';
    --交易类型
    if (txncd is not null) then
      tj := tj || ' AND P.TXNCD =''' || txncd || '''';
    end if;
    --交易状态
    if (txnsts is not null) then
      tj := tj || ' AND P.TXNSTS =''' || txnsts || '''';
    end if;
    --合作机构
    if (agtorg is not null) then
      tj := tj || ' AND P.AGTORG =''' || agtorg || '''';
    end if;
    --交易金额起
    if (txnamtstr is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)>=TO_NUMBER(''' || txnamtstr || '''' ||
            ')*100';
    end if;
    --交易金额终
    if (txnamtend is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)<=TO_NUMBER(''' || txnamtend || '''' ||
            ')*100';
    end if;
    --原交易流水号和交易流水号
    if ((logno is null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND (P.LOGNO LIKE ' || '''' || '%' || ologno || '%' || '''' ||
                'OR P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' ||
                ''')';
    end if;
    if ((logno is not null) and (ologno is null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''';
    end if;
    if ((logno is not null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''' ||
                'AND P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' || '''';
    end if;
    --终端号
    if (termid is not null) then
      sqlstr := sqlstr || ' AND P.TERMID LIKE ' || '''' || '%' || termid || '%' || '''';
    end if;
    --分页计算
    if ((curpage is not null) and (unitpage is not null)) then
      startpos := (curpage - 1) * unitpage + 1;
      endpos   := curpage * unitpage;
    end if;
    --判断不同角色登录查询历史交易明细
    if (flag = '2') then
      --商户角色
      if (mercid_oper is not null) then
        tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid_oper || '%' || '''';
      end if;
    end if;
    if (flag = '3') then
      --办事处角色
      if (agentid is not null) then
        tj2 := tj2 || ' WHERE MI.AGENTID=''' || agentid || '''';
        tj3 := tj3 || ' WHERE MD.AGENTID=''' || agentid || '''';
      end if;
    end if;
    if (flag = '1') then
      --业务员角色     通过办事处编号和当前操作员id查找 
      if (tlrid is not null) then
        tj2 := tj2 || ' WHERE MI.OPERID =''' || tlrid ||
               ''' OR MI.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
        tj3 := tj3 || ' WHERE MD.OPERID=''' || tlrid ||
               ''' OR MD.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
      end if;
    end if;
 
    --查询历史交易明细sql
    query_postxnjnl_his := 'SELECT DISTINCT I.MERNAM,I.MERCID,I.MERCNUM,B.BRANAM,T.TERMID,P.SYSDAT,P.TXNTM,P.LOGNO,P.TXNTYP,
        P.CRDNO,P.TXNAMT,P.SREFNO,P.OLOGNO,P.TSREFNO OTSREFNO,P.TXNSTS,P.CRDFLG,P.TXNCD,U.ISSNAM,N.ORGNAM,P.FEE,(P.TXNAMT-DECODE(TRIM(P.FEE),' || '''''' || ',0,P.FEE)) JSAMT
        FROM POSTXNJNLHIS P INNER JOIN
        (SELECT TRIM(MI.MERNAM) MERNAM,TRIM(MI.MERCID) MERCID,TRIM(MI.MERCNUM) MERCNUM FROM POSMERCINF MI ' || tj2 || ' union
         SELECT  DISTINCT TRIM(MD.MERNAM) MERNAM,TRIM(MD.MERCID) MERCID,TRIM(MD.MERCNUM) MERCNUM FROM POSMERCINFMOD MD ' || tj3 || '
        )I ON P.MERCID=I.MERCID
        INNER JOIN POSTERMINF T ON P.TERMID=T.TERMID LEFT JOIN POSBRAINF B ON TRIM(T.BRAID)=TRIM(B.BRAID)
        LEFT JOIN  UNIONFITNAME U ON TRIM(P.ISSINO)=TRIM(U.ISSNO)
        LEFT JOIN POSNODINF N ON P.AGTORG=N.AGTORG
        WHERE P.TXNTYP!=''C'' ' || sqlstr || '  ' || tj || '
        ORDER BY P.SYSDAT DESC,P.TXNTM DESC,I.MERCID  DESC';
    query_postxnjnl_his := 'select  * from (select  B.*,rownum rid from (' || query_postxnjnl_his || ')B where rownum<=' || endpos || ')A where A.rid>=' || startpos;
    open v_cursor for query_postxnjnl_his;
  Exception
    when others then
      dbms_output.put_line('数据库出现异常！');
      raise;
  end query_postxnjnl_his_info;
  --统计总个数
  procedure query_postxnjnl_his_count(stldt       in varchar2,
                                      stldtstr    in varchar2,
                                      stldtend    in varchar2,
                                      txncd       in varchar2,
                                      txnsts      in varchar2,
                                      agtorg      in varchar2,
                                      txnamtstr   in varchar2,
                                      txnamtend   in varchar2,
                                      mercid      in varchar2,
                                      mernam      in varchar2,
                                      srefno      in varchar2,
                                      osrefno     in varchar2,
                                      logno       in varchar2,
                                      ologno      in varchar2,
                                      termid      in varchar2,
                                      flag        in varchar2,
                                      mercid_oper in varchar2,
                                      agentid     in varchar2,
                                      tlrid       in varchar2,
                                      v_cursor    out tomorrow.p_cursor) as
 
    tj                    varchar2(500);
    tj2                   varchar2(100);
    tj3                   varchar2(100);
    sqlstr                varchar2(300);
    query_postxnjnl_count varchar2(3000);
  begin
    tj     := '';
    tj2    := '';
    tj3    := '';
    sqlstr := '';
    --商户号
    if (mercid is not null) then
      tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid || '%' || '''';
    end if;
    --商户名称
    if (mernam is not null) then
      tj := tj || ' AND I.MERNAM LIKE ' || '''' || '%' || mernam || '%' || '''';
    end if;
    --系统参考号
    if (srefno is not null) then
      tj := tj || ' AND P.SREFNO LIKE ' || '''' || '%' || srefno || '%' || '''';
    end if;
    --原系统参考号
    if (osrefno is not null) then
      tj := tj || ' AND P.TSREFNO LIKE ' || '''' || '%' || osrefno || '%' || '''';
    end if;
    --交易日期
    if (stldt is not null) then
      tj := tj || ' AND P.SYSDAT =''' || stldt || '''';
    end if;
    tj := tj || ' AND P.SYSDAT BETWEEN ''' || stldtstr || ''' AND ''' ||
          stldtend || '''';
    --交易类型
    if (txncd is not null) then
      tj := tj || ' AND P.TXNCD =''' || txncd || '''';
    end if;
    --交易状态
    if (txnsts is not null) then
      tj := tj || ' AND P.TXNSTS =''' || txnsts || '''';
    end if;
    --合作机构
    if (agtorg is not null) then
      tj := tj || ' AND P.AGTORG =''' || agtorg || '''';
    end if;
    --交易金额起
    if (txnamtstr is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)>=TO_NUMBER(''' || txnamtstr || '''' ||
            ')*100';
    end if;
    --交易金额终
    if (txnamtend is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)<=TO_NUMBER(''' || txnamtend || '''' ||
            ')*100';
    end if;
    --原交易流水号和交易流水号
    if ((logno is null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND (P.LOGNO LIKE ' || '''' || '%' || ologno || '%' || '''' ||
                'OR P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' ||
                ''')';
    end if;
    if ((logno is not null) and (ologno is null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''';
    end if;
    if ((logno is not null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''' ||
                'AND P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' || '''';
    end if;
    --终端号
    if (termid is not null) then
      sqlstr := sqlstr || ' AND P.TERMID LIKE ' || '''' || '%' || termid || '%' || '''';
    end if;
    --判断不同角色登录查询历史交易明细
    if (flag = '2') then
      --商户角色
      if (mercid_oper is not null) then
        tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid_oper || '%' || '''';
      end if;
    end if;
    if (flag = '3') then
      --办事处角色
      if (agentid is not null) then
        tj2 := tj2 || ' WHERE MI.AGENTID=''' || agentid || '''';
        tj3 := tj3 || ' WHERE MD.AGENTID=''' || agentid || '''';
      end if;
    end if;
    if (flag = '1') then
      --业务员角色     通过办事处编号和当前操作员id查找 
      if (tlrid is not null) then
        tj2 := tj2 || ' WHERE MI.OPERID =''' || tlrid ||
               ''' OR MI.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
        tj3 := tj3 || ' WHERE MD.OPERID=''' || tlrid ||
               ''' OR MD.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
      end if;
    end if;

    --查询历史交易明细sql
    query_postxnjnl_count := 'SELECT P.LOGNO
        FROM POSTXNJNLHIS P INNER JOIN
        (SELECT TRIM(MI.MERNAM) MERNAM,TRIM(MI.MERCID) MERCID,TRIM(MI.MERCNUM) MERCNUM FROM POSMERCINF MI ' || tj2 || ' union
         SELECT  DISTINCT TRIM(MD.MERNAM) MERNAM,TRIM(MD.MERCID) MERCID,TRIM(MD.MERCNUM) MERCNUM FROM POSMERCINFMOD MD ' || tj3 || '
        )I ON P.MERCID=I.MERCID WHERE P.TXNTYP!=''C'' ' || sqlstr || '  ' || tj || '';
    query_postxnjnl_count := 'SELECT COUNT(1) AS TOLCNT FROM (' || query_postxnjnl_count || ')';
    open v_cursor for query_postxnjnl_count;
  Exception
    when others then
      dbms_output.put_line('数据库出现异常！');
      raise;
  end query_postxnjnl_his_count;
  --统计交易总金额
  procedure query_postxnjnl_his_amttotal(stldt       in varchar2,
                                         stldtstr    in varchar2,
                                         stldtend    in varchar2,
                                         txncd       in varchar2,
                                         txnsts      in varchar2,
                                         agtorg      in varchar2,
                                         txnamtstr   in varchar2,
                                         txnamtend   in varchar2,
                                         mercid      in varchar2,
                                         mernam      in varchar2,
                                         srefno      in varchar2,
                                         osrefno     in varchar2,
                                         logno       in varchar2,
                                         ologno      in varchar2,
                                         termid      in varchar2,
                                         flag        in varchar2,
                                         mercid_oper in varchar2,
                                         agentid     in varchar2,
                                         tlrid       in varchar2,
                                         v_cursor    out tomorrow.p_cursor) as
 
    tj                       varchar2(500);
    tj2                      varchar2(100);
    tj3                      varchar2(100);
    sqlstr                   varchar2(300);
    query_postxnjnl_amttotal varchar2(3000);
  begin
    tj     := '';
    tj2    := '';
    tj3    := '';
    sqlstr := '';
    --商户号
    if (mercid is not null) then
      tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid || '%' || '''';
    end if;
    --商户名称
    if (mernam is not null) then
      tj := tj || ' AND I.MERNAM LIKE ' || '''' || '%' || mernam || '%' || '''';
    end if;
    --系统参考号
    if (srefno is not null) then
      tj := tj || ' AND P.SREFNO LIKE ' || '''' || '%' || srefno || '%' || '''';
    end if;
    --原系统参考号
    if (osrefno is not null) then
      tj := tj || ' AND P.TSREFNO LIKE ' || '''' || '%' || osrefno || '%' || '''';
    end if;
    --交易日期
    if (stldt is not null) then
      tj := tj || ' AND P.SYSDAT =''' || stldt || '''';
    end if;
    tj := tj || ' AND P.SYSDAT BETWEEN ''' || stldtstr || ''' AND ''' ||
          stldtend || '''';
    --交易类型
    if (txncd is not null) then
      tj := tj || ' AND P.TXNCD =''' || txncd || '''';
    end if;
    --交易状态
    if (txnsts is not null) then
      tj := tj || ' AND P.TXNSTS =''' || txnsts || '''';
    end if;
    --合作机构
    if (agtorg is not null) then
      tj := tj || ' AND P.AGTORG =''' || agtorg || '''';
    end if;
    --交易金额起
    if (txnamtstr is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)>=TO_NUMBER(''' || txnamtstr || '''' ||
            ')*100';
    end if;
    --交易金额终
    if (txnamtend is not null) then
      tj := tj || ' AND TO_NUMBER(P.TXNAMT)<=TO_NUMBER(''' || txnamtend || '''' ||
            ')*100';
    end if;
    --原交易流水号和交易流水号
    if ((logno is null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND (P.LOGNO LIKE ' || '''' || '%' || ologno || '%' || '''' ||
                'OR P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' ||
                ''')';
    end if;
    if ((logno is not null) and (ologno is null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''';
    end if;
    if ((logno is not null) and (ologno is not null)) then
      sqlstr := sqlstr || ' AND P.LOGNO LIKE ' || '''' || '%' || logno || '%' || '''' ||
                'AND P.OLOGNO LIKE ' || '''' || '%' || ologno || '%' || '''';
    end if;
    --终端号
    if (termid is not null) then
      sqlstr := sqlstr || ' AND P.TERMID LIKE ' || '''' || '%' || termid || '%' || '''';
    end if;
    --判断不同角色登录查询历史交易明细
    if (flag = '2') then
      --商户角色
      if (mercid_oper is not null) then
        tj := tj || ' AND I.MERCNUM LIKE ' || '''' || '%' || mercid_oper || '%' || '''';
      end if;
    end if;
    if (flag = '3') then
      --办事处角色
      if (agentid is not null) then
        tj2 := tj2 || ' WHERE MI.AGENTID=''' || agentid || '''';
        tj3 := tj3 || ' WHERE MD.AGENTID=''' || agentid || '''';
      end if;
    end if;
    if (flag = '1') then
      --业务员角色     通过办事处编号和当前操作员id查找 
      if (tlrid is not null) then
        tj2 := tj2 || ' WHERE MI.OPERID =''' || tlrid ||
               ''' OR MI.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
        tj3 := tj3 || ' WHERE MD.OPERID=''' || tlrid ||
               ''' OR MD.AGENTID IN (SELECT AGENTID FROM POSAGTINF WHERE TLRID=''' ||
               tlrid || ''')';
      end if;
    end if;
 
    --查询历史交易明细sql
    query_postxnjnl_amttotal := 'SELECT (  SUM (xftxnamt) + SUM (ysqwctxnamt) - SUM (thtxnamt))
        AS sumtoal FROM (  SELECT TXNCD, SUM (DECODE (TRIM (TXNCD), 0200000000, TXNAMT, 0))AS XFTXNAMT,
        SUM (DECODE (TRIM (TXNCD),0200200000, TXNAMT, 0))AS XFCXTXNAMT,SUM (DECODE (TRIM (TXNCD),0220200000, TXNAMT, 0))AS thtxnamt,
        SUM (DECODE (TRIM (TXNCD),0200010000, TXNAMT, 0))AS YSQWCTXNAMT,SUM (DECODE (TRIM (TXNCD), 0200210000, TXNAMT, 0))AS YSQCXTXNAMT
        FROM (SELECT TXNCD, SUM (TXNAMT) AS TXNAMT FROM POSTXNJNLHIS P INNER JOIN
        (SELECT TRIM(MI.MERNAM) MERNAM,TRIM(MI.MERCID) MERCID,TRIM(MI.MERCNUM) MERCNUM FROM POSMERCINF MI ' || tj2 || ' UNION
         SELECT  DISTINCT TRIM(MD.MERNAM) MERNAM,TRIM(MD.MERCID) MERCID,TRIM(MD.MERCNUM) MERCNUM FROM POSMERCINFMOD MD ' || tj3 || '
        )I ON P.MERCID=I.MERCID INNER JOIN POSTERMINF T ON P.TERMID=T.TERMID LEFT JOIN POSBRAINF B ON TRIM(T.BRAID)=(B.BRAID)
        WHERE  P.TXNSTS  = ''S'' ' || sqlstr || '  ' || tj ||
                                ' GROUP BY TXNCD, txnsts ) GROUP BY TXNCD)';
    open v_cursor for query_postxnjnl_amttotal;
  Exception
    when others then
      dbms_output.put_line('数据库出现异常！');
      raise;
  end query_postxnjnl_his_amttotal;
end;

xiehui_top
关注
专栏目录
提高Oracle数据库查询效率
09-21
下面是一个采用联接查询的SQL语句， select * from employss where first_name||''||last_name ='Beill Cliton';
订单事务-存储过程
bruceangel的专栏
08-22 1934
create proc Createorder @orderId nvarchar(50),--订单号 @userId int,--用户编号 @address nvarchar(255),--收货人地址 @totalMoney money output --总金额 as  begin declare @error int set @error=0 begin transactio
mysql 交易 存储过程_Mysql中的存储过程
weixin_32892145的博客
02-17 135
-- ------------------------------ Procedure structure for `proc_adder`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_adder`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc...
oracle 提高sql查询效率
yanhui007的专栏
06-13 348
数据量比较大 进行数据迁移 提高效率 alter table t_Uums_Upg_User_Dept_Sysrole_t nologging; 　　insert  /*+ append */ into  t_Uums_Upg_User_Dept_Sysrole_t   select * from t_Uums_Upg_User_Dept_Sysrole_t@UUMS_TO_E
ChainDesk：交易数据的存储
weixin_43970764的博客
01-12 566
            作者：ChainDesk韩小东 ChainDesk官网：http://www.chaindesk.cn/?20190112jinritoutiaomeiti 目标 1.了解账本数据组成结构 2.了解区块本身及事务数据的组成结构 3.理解账本数据的存储过程 任务实现 9.1.1 区块链账本数据 分类账本中保存着所有交易变化的记录，具有有序和防篡...
提高Oracle查询效率
04-12
正确选择索引类型（如B树、位图、函数索引等），并合理创建复合索引，可以减少全表扫描，提高查询速度。 - **执行计划分析**：理解SQL的执行计划，包括表的访问方式、连接顺序和排序操作，可以帮助识别性能瓶颈并...
oracle 存储过程、函数和触发器用法实例详解
09-08
总之，Oracle的存储过程、函数和触发器是数据库开发中的重要工具，它们可以增强数据库的功能，提高代码的效率和安全性，同时也是实现业务逻辑的关键手段。在实际应用中，根据需求选择合适的方式来编写和调用这些对象...
ORACLE库存快照创建和查询工具
08-14
总之，“ORACLE库存快照创建和查询工具”是Oracle数据库在库存管理领域的一个强大应用，它通过存储过程和库存快照技术，为企业提供了准确、及时的库存信息，有利于提升供应链的效率和决策的准确性。同时，提供的查询...
基于Oracle数据库存储过程与触发器的应用.pdf
10-10
《基于Oracle数据库存储过程与触发器的应用》这篇文章深入探讨了如何在Oracle数据库中利用存储过程和触发器来提升系统性能和实现自动化管理。存储过程是预编译的SQL语句集合，可以理解为一个可重复使用的代码模块，...
oracle pl/sql 存储过程和函数与触发器
04-20
在Oracle中，创建存储过程使用`CREATE PROCEDURE`语句。存储过程的优点包括提高性能（因为编译只需一次）、降低网络流量、增强安全性（通过权限控制访问）以及简化复杂的数据库操作。 二、函数 函数类似于存储过程...
订单表---存储过程
不忘初心~
03-09 2231
订单表---存储过程
可疑交易报表存储过程
龙塘湾
06-12 201
create or replace procedure rp_doubtfultransferdet(in_orgcode varchar2, in_startdate varchar2, ...
一个账单存储过程
叶落枫亭
12-23 731
-------------------------------------------------------- Export file for user PRM ---- Created by Administrator on 2008-12-17, 15:30:58 ----------------------------------------
ORACLE查最近的一条明细
weixin_33911824的博客
10-27 109
2019独角兽企业重金招聘Python工程师标准>>> ...
历史数据统计-存储过程的应用
它山之石，可以攻玉
04-20 1622
存储过程可以用流控制语句编写，具有很强的灵活性，可以完成复杂的判断和运算，可以根据条件执行不通SQL语句。在大型数据处理中复杂的数据统计使用存储过程执行，能够产生很好的查询效果，根据周期计划统计如日最大值、日最小值、日平均值和日负荷值等，以便在数据查询中根据标志量区别提高效率。
SqlServer典型存储过程订单表和明细表
chenggong9527的博客
05-19 976
--使用订单表和订单明细表 --存储过程 带参数和无参数 --1 无参数的存储过程 -- 查询订单表里面的名字，地址信息。和订单明细表中的金额信息和发生订单的时间数据值信息内容. create Procedure Lh_Dingdan as begin select a.ID,a.dname,a.Address,b.DDprice,b.DDtime from LHDD a ,DDMXB b where a.DDMXID=b.ID order ...
【SQL】查询一段时期内的交易流水记录
欲买桂花同载酒，终不似，少年游。
08-01 7218
#当前月 上个月 前三个月 十个月 SELECT * FROM timetest WHERE year(time)=year(NOW()) and month(time)=(month(NOW())); SELECT * FROM timetest WHERE month(time)!=month(NOW()) and (time BETWEEN (date_sub(NOW(),interval 1 month)) and NOW()); SELECT * FROM timetest WHERE mo
java中使用sql的存储过程,提高查询效率
打造核心知识库
03-24 1771
曾几何时,在java中写一个老长的查询 语句--sql=select * from where ........................",哇塞,简直是折磨,用PreparedStatement 吧,虽然对于N个参数查询时确实是高效的,但是还是要写N长的代码,后来看sql时,发现有个存储过程可以省略很长的代码,还有带参的存储过程可以使用,大大简化了查询的语句,对于软件中固定查询模式的sql
包含头和明细的接口存储过程（从中间表读取数据到系统）
Tryndamere-K的博客
06-09 627
存储过程三： 一.procedures create or replace procedure PR_BOM is /* * 功能:从中间表获取BOM及其明细信息 * 作者：KAIQI.ZHAO * 创建日期 2019-06-06 * 最后修改时间 2019-06-06 14:35:29 */ v_con number; V_MSG varchar2(1000...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值