存储过程，存储过程循环，一张表整列插入另一张表

最新推荐文章于 2022-04-17 17:02:16 发布
最新推荐文章于 2022-04-17 17:02:16 发布
阅读量922 收藏
点赞数
文章标签： 存储过程 存储过程遍历 存储过程一张表一列插入另一张表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/an341221/article/details/48158877
版权
create or replace package body Prc_Stat is


  Procedure Prc_Stat_yuebao(card_type    IN TMP_ZXBB_YB.qc%type,
                              card_guige      IN TMP_ZXBB_YB.qc%type,
                                 p_rc1           OUT pkg_sql.MyRsType) is
  strSql    VARCHAR2(1000);
  tmp_wdid_current VARCHAR2(20);
    fund01    number(16,6);
    fund02    number(16,6);
    fund03    number(16,6);
    fund04    number(16,6);
    fund05    number(16,6);
    fund06    number(16,6);
    fund07    number(16,6);
    fund08    number(16,6);
    fund09    number(16,6);
    fund10    number(16,6);
    fund11    number(16,6);
    fund12    number(16,6);
    fund13    number(16,6);

   Begin
   
        insert into TMP_ZXBB_YB (wdid,pid,wdname)  SELECT ID wdid, PID, FULLNAME wdname  FROM TORG WHERE TYPE = '4' ORDER BY ORGBH;

        
        
     declare    
     cursor cur_Dtl9 is select wdid  from TMP_ZXBB_YB;   
     begin
     OPEN cur_Dtl9;
     loop
     fetch cur_Dtl9 into tmp_wdid_current;
     exit when(cur_Dtl9%notfound);
     --期初库存
          SELECT  SUM(ZPJC + CPJC) into fund01
                FROM T_ZKJC
               WHERE WZGGID in (24, 12, 2)
                 AND TO_CHAR(RQ, 'YYYY/MM/DD') =
                     TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE('2015-08-01',
                                                         'YYYY/MM/DD'),
                                                 -1)),
                             'YYYY/MM/DD')
              and ORGID=tmp_wdid_current;

          
          
       --期末库存
          SELECT SUM(ZPJC + CPJC)  into fund02
                FROM T_ZKJC
               WHERE WZGGID in (24, 12, 2)
                 AND TO_CHAR(RQ, 'YYYY/MM/DD') =
                     TO_CHAR(SYSDATE, 'YYYY/MM/DD')
               and  ORGID= tmp_wdid_current;

         
     
           --其他入库
           
                 SELECT count(RKD.ORGID) into fund03 FROM T_RKMX RKMX , T_RKD RKD where RKD.RKDID = RKMX.RKDID
                          AND RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 7
                       AND RKMX.WZGGID in (24, 12, 2)
                       AND RKD.ORGID=tmp_wdid_current;

                
           
           
           
           ----调拨入库
           
           SELECT COUNT(RKD.ORGID) into fund04
                        FROM T_RKMX RKMX
                        , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 6
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               AND  RKD.ORGID=tmp_wdid_current;

          
           
           
           --盘点入库
            SELECT count(RKD.ORGID) into fund05
                        FROM T_RKMX RKMX
                       , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 20
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               AND  RKD.ORGID=tmp_wdid_current;

              
               
               --次品退回入库
               SELECT count(RKD.ORGID)  into fund06
                        FROM T_RKMX RKMX
                        , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 10
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               AND RKD.ORGID=tmp_wdid_current;

           
                  --次品入库
                   SELECT COUNT(*) into fund07
                FROM V_CPDJD V
               WHERE V.CPDJWZZLID = 2
                 AND V.CPDJWZGGID in (24, 12, 2)
                 AND V.CPDJCSSJ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                 AND V.CPDJCSSJ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                 AND V.IFSHOW = '1'
               AND V.ORGID=tmp_wdid_current;

                
                --次品上交出库
               SELECT count(CKD.ORGID) into  fund08
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE
                           CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 4
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

               
          --盘点出库
         SELECT count(CKD.ORGID)  into fund09
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 20
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

               
               
             --调账出库
             SELECT COUNT(CKD.ORGID) into fund10
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 6
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

               
               
               --销售出库
               SELECT COUNT(CKD.ORGID)  into fund11
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 1
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

               
               
               --赠送出库
               SELECT COUNT(CKD.ORGID)  into fund12
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 2
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

              
         
         
               --其他发出
              SELECT COUNT(CKD.ORGID) into  fund13
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 3
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) AND CKD.ORGID=tmp_wdid_current;

                update  TMP_ZXBB_YB set qc=fund01,qm=fund02,RK_QT=fund03,RK_DB=fund04,RK_PD=fund05,RK_CPTH=fund06,
                RK_CP=fund07,CK_CPSJ=fund08,CK_PD=fund09,CK_TZ=fund10,CK_XS=fund11,CK_ZS=fund12,CK_QTFC=fund13 where wdid=tmp_wdid_current;

                
         
         
         
         
         
          
          
          
     end loop;

     
     close cur_Dtl9;
     end;

     
     
     
     
     
     
         strSql := 'select * from TMP_ZXBB_YB';
         OPEN p_rc1 FOR strSql;
    End;  
    
    
    
    
    
    
    
    
    
    
  Procedure Prc_Stat_yuebaoNew(card_type    IN TMP_ZXBB_YB.qc%type,
                              card_guige      IN TMP_ZXBB_YB.qc%type,
                                 p_rc1           OUT pkg_sql.MyRsType) is
  strSql    VARCHAR2(1000);
  tmp_wdid_current VARCHAR2(20);


   Begin
   
        insert into TMP_ZXBB_YB (wdid,pid,wdname)  SELECT ID wdid, PID, FULLNAME wdname  FROM TORG WHERE TYPE = '4' ORDER BY ORGBH;

        
        
         ----调拨入库
        insert into TMP_ZXBB_YB_PART (wdid,sl)  SELECT  RKD.ORGID wdid ,COUNT(RKD.ORGID) sl
                        FROM T_RKMX RKMX
                        , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 6
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               group by  RKD.ORGID ;

                  
                   update  TMP_ZXBB_YB a set a.RK_DB=(
           
           SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

                 
           --期初库存
           delete from TMP_ZXBB_YB_PART ;

          insert into TMP_ZXBB_YB_PART (wdid,sl) SELECT  ORGID wdid,SUM(ZPJC + CPJC) sl
                FROM T_ZKJC
               WHERE WZGGID in (24, 12, 2)
                 AND TO_CHAR(RQ, 'YYYY/MM/DD') =
                     TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE('2015-08-01',
                                                         'YYYY/MM/DD'),
                                                 -1)),
                             'YYYY/MM/DD')
              group by ORGID ;

              update  TMP_ZXBB_YB a set a.QC=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );   
              
               --期末库存
               delete from TMP_ZXBB_YB_PART ;

          insert into TMP_ZXBB_YB_PART (wdid,sl) SELECT ORGID wdid,SUM(ZPJC + CPJC) sl
                FROM T_ZKJC
               WHERE WZGGID in (24, 12, 2)
                 AND TO_CHAR(RQ, 'YYYY/MM/DD') =
                     TO_CHAR(SYSDATE, 'YYYY/MM/DD')
               group by   ORGID;   
              update  TMP_ZXBB_YB a set a.QM=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );  
               
               --其他入库
            delete from TMP_ZXBB_YB_PART ;


          insert into TMP_ZXBB_YB_PART (wdid,sl)
                 SELECT RKD.ORGID wdid , count(RKD.ORGID) sl  FROM T_RKMX RKMX , T_RKD RKD where RKD.RKDID = RKMX.RKDID
                          AND RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 7
                       AND RKMX.WZGGID in (24, 12, 2)
                       group by RKD.ORGID;

               
          
           update  TMP_ZXBB_YB a set a.RK_QT=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );  
               
                --盘点入库
                delete from TMP_ZXBB_YB_PART ;



          insert into TMP_ZXBB_YB_PART (wdid,sl)
            SELECT RKD.ORGID wdid , count(RKD.ORGID) sl  
                        FROM T_RKMX RKMX
                       , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 20
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               group by  RKD.ORGID;

               
               update  TMP_ZXBB_YB a set a.RK_PD=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );
               --次品退回入库
                delete from TMP_ZXBB_YB_PART ;




          insert into TMP_ZXBB_YB_PART (wdid,sl)
               SELECT RKD.ORGID adid,count(RKD.ORGID) sl
                        FROM T_RKMX RKMX
                        , T_RKD RKD
                          WHERE RKD.RKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND RKD.RKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND RKD.RKXZ = 10
                         AND RKD.RKDID = RKMX.RKDID
                       AND RKMX.WZGGID in (24, 12, 2)
               group by RKD.ORGID;

               
               update  TMP_ZXBB_YB a set a.RK_CPTH=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

                --次品入库
                 delete from TMP_ZXBB_YB_PART ;





          insert into TMP_ZXBB_YB_PART (wdid,sl)
                   SELECT V.ORGID wdid,COUNT(*) sl
                FROM V_CPDJD V
               WHERE V.CPDJWZZLID = 2
                 AND V.CPDJWZGGID in (24, 12, 2)
                 AND V.CPDJCSSJ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                 AND V.CPDJCSSJ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                 AND V.IFSHOW = '1'
               group by  V.ORGID;

               
                update  TMP_ZXBB_YB a set a.RK_CP=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

               
                --次品上交出库
                 delete from TMP_ZXBB_YB_PART ;






          insert into TMP_ZXBB_YB_PART (wdid,sl)
               SELECT CKD.ORGID wdid,count(CKD.ORGID) sl
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE
                           CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 4
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by CKD.ORGID;

               
               update  TMP_ZXBB_YB a set a.CK_CPSJ=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

               --盘点出库
                delete from TMP_ZXBB_YB_PART ;




          insert into TMP_ZXBB_YB_PART (wdid,sl)
         SELECT CKD.ORGID wdid,count(CKD.ORGID) sl  
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 20
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by CKD.ORGID;

               update  TMP_ZXBB_YB a set a.CK_CPSJ=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

               --调账出库
               delete from TMP_ZXBB_YB_PART ;





          insert into TMP_ZXBB_YB_PART (wdid,sl)
             SELECT CKD.ORGID wdid,COUNT(CKD.ORGID) sl
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 6
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by  CKD.ORGID;

                       
                update  TMP_ZXBB_YB a set a.CK_CPSJ=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

             
             
              
               --销售出库
                delete from TMP_ZXBB_YB_PART ;






          insert into TMP_ZXBB_YB_PART (wdid,sl)
               SELECT CKD.ORGID wdid,COUNT(CKD.ORGID) sl
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 1
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by CKD.ORGID;

                       
                        update  TMP_ZXBB_YB a set a.CK_XS=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

               
               
               --赠送出库
               delete from TMP_ZXBB_YB_PART ;







          insert into TMP_ZXBB_YB_PART (wdid,sl)
               SELECT CKD.ORGID wdid,COUNT(CKD.ORGID)  sl
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 2
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by CKD.ORGID;

                       
                       update  TMP_ZXBB_YB a set a.CK_ZS=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

              
         
         
               --其他发出
                delete from TMP_ZXBB_YB_PART ;








          insert into TMP_ZXBB_YB_PART (wdid,sl)
              SELECT CKD.ORGID wdid,COUNT(CKD.ORGID) sl
                        FROM T_CKDMX CKMX
                        , T_CKD CKD
                          WHERE CKD.CKRQ < TO_DATE('2015-09-01', 'YYYY/MM/DD')
                         AND CKD.CKRQ >= TO_DATE('2015-08-01', 'YYYY/MM/DD')
                         AND CKD.CKXZ = 3
                         AND CKD.CKDID = CKMX.CKDID
                       AND CKMX.WZGGID in (24, 12, 2) group by CKD.ORGID;


                 update  TMP_ZXBB_YB a set a.CK_QTFC=(
               SELECT sl from  TMP_ZXBB_YB_PART
               where  TMP_ZXBB_YB_PART.WDID=a.wdid );

             
             
             
             
             
             
             
             
             
             
             
             
             
             
             
                 
                 
     
         strSql := 'select * from TMP_ZXBB_YB order by pid ';
         OPEN p_rc1 FOR strSql;
    End;                    
end Prc_Stat;

欣赏月光
关注
【大数据 OLAP ClickHouse 引擎】ClickHouse 系统架构和存储引擎实现原理 : 为什么 ClickHouse 这么快？ Why is ClickHouse so fast?
程序员光剑
03-01 4万+
ClickHouse 是一款由俄罗斯 Yandex 公司开发的 C++ 开源高性能 OLAP 组件。在 Yandex 内部, ClickHouse 主要用于在线流量分析产品 Yandex Metrica，类似于 Google Analytics 或者百度统计。ClickHouse是一款MPP架构的列式存储数据库，但MPP和列式存储并不是什么"稀罕"的设计。拥有类似架构的其他数据库产品也有很多，但是为什么偏偏只有ClickHouse的性能如此出众呢？
MySql技术内 幕：InnoDB存储引擎 读书笔记
qq_33275348的博客
12-06 558
书名 《MySql技术内幕：InnoDB存储引擎》 作者 姜承尧 书摘 第一章：MySQL体系结构和存储引擎 定义数据库和实例： 定义数据库和实例 数据库：文件的集合，frm、MYD、MYI、ibd等 实例：是有后台线程以及一个共享内存区组成。操作数据库文件。单进程...
sqlserver 循环临时表插入数据到另一张表
11-27
sqlserver 循环临时表插入数据到另一张表 -- 声明变量 DECLARE @SupCode as varchar(100), @ProdCode as varchar(50), @PackLayer as varchar(50), @CodeStatus as varchar(50), @ProductId as varchar(50), @subTypeNo as varchar(50), @PackageSpecID as varchar(50), @UpdateTime as varchar(50), @errorCount as int=0;
存储过程（一张表中的数据插入到另一张表）
zhangMY12138的博客
08-15 1185
USE [xcoa] GO /****** Object: StoredProcedure [dbo].[cunchu] Script Date: 2020/8/15 11:17:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[cunchu] as --声明变量（定义变量） declare @jsr varchar(255) declare @fid varchar(255) --建立游标...
写一个存储过程，将一个数据表中的数据定时存入另一个表中，能够进行实时更新表中的数据，如果表中没有会直接插入新数据...
weixin_33826268的博客
09-04 255
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`() BEGIN DECLARE chkc INT; DECLARE id_tmp INT; DECLARE age_tmp INT; DECLARE name_tmp VARCHAR(32); DECLARE flag INT DEFAULT TRUE;//定义一个fla...
存储过程循环插入表中
奋斗不息
06-04 300
查出角色表T_SYS_ROLE，循环然后插入角色资源表T_SYS_ROLE_RESOURCE declare --游标,从角色表T_SYS_ROLE取出所有数据 cursor ROLE_ID_cursor is SELECT ROLE_ID from T_SYS_ROLE; new_ROLE_ID ROLE_ID_cursor%rowtype; BEGIN --DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buf
使用存储过程循环写入数据
10-13 2231
declare @i intset @i=1while @ibegininsert into Table(字段信息)values(值信息)set @i=@i+1end 
MySql基础，MySql视图&索引&存储过程&触发器
ghb741649543的博客
08-28 668
文章目录视图是什么视图的优点1) 定制用户数据，聚焦特定的数据2) 简化数据操作3) 提高数据的安全性4) 共享所需数据5) 更改数据格式6) 重用 SQL 语句MySQL创建视图基本语法查询视图MySQL查看视图查看视图的字段信息查看视图的详细信息MySQL修改视图基本语法修改视图内容MySQL删除视图基本语法索引是什么为什么要使用索引顺序访问索引访问索引的优缺点优点缺点MySQL索引类型详解存储方式区分B-树索引哈希索引逻辑区分普通索引唯一索引主键索引空间索引全文索引实际使用区分单列索引多列索引MySQ
使用EasyExcel导入表格来进行批量插入数据----后端实现
qq_45968950的博客
04-17 2083
1.创建一个实体类对应表格中的字段： @Data @EqualsAndHashCode public class RepositoryExcel { private Long safeTypeId; private Integer quesType; private String quesContent; private String options; private String answer; } 2.创建数据监听器： 注意：需要使用set方法把Sp
ClickHouse特性及底层存储原理
uuuyy_的博客
12-12 725
ClickHouse的特性 ClickHouse是一款MPP架构的列式存储数据库，但MPP和列式存储并不是什么"稀罕"的设计。拥有类似架构的其他数据库产品也有很多，但是为什么偏偏只有ClickHouse的性能如此出众呢？ClickHouse发展至今的演进过程一共经历了四个阶段，每一次阶段演进，相比之前都进一步取其精华去其糟粕。可以说ClickHouse汲取了各家技术的精髓，将每一个细节都做到了极致。接下来将介绍ClickHouse的一些核心特性，正是这些特性形成的合力使得ClickHouse如此优秀。
将count(*)值写入另一个表中的方法
01-19
一、写入同一个数据库的另一个表中：   代码如下:use db1   go   create table t1(   Item   varchar(100),   Prov   varchar(100),   Qty    int);   insert into t1(item,prov,qty)      select ‘原名’,’xx省’,   (select count(*)    from updata where id=’A0101′);  二、写入另一个数据库的表中：   代码如下:use db2   go   create table t2(   Item   varchar(1
SQL语句将一个表的数据写入到另一个表中
dianwulai9939的博客
04-03 1760
在日常的数据库运维过程中，有时候需要将Select查询出来的数据集写入到另一个数据表中，其中一种方式是通过存储过程循环写入数据，另一种简便的方式是直接使用Insert Into语句后面跟上Select结果查询语句即可将数据写入。通过Insert Into和Select语句连用可以很快的将一个表的数据写入到另一个数据表中。 例如我们需要将订单表Order_2中的type=1的数据写入到订...
sql语句循环插入表
ccxcuixia
04-20 729
sql： DROP PROCEDURE IF EXISTS proc_initData; DELIMITER $ （诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号。在这里是规定mysql结束符号是$） CREATE PROCEDURE proc_initData() BEGIN DECLARE i INT DEFAULT 1; ...
循环向表中插入记录
owen_008的专栏
08-27 957
create table e( a numeric, b varchar2(50));方法1：declaretemp varchar2(20):=aaaa;i number;begin for i in 1..100 loop insert into e(a,b) values(i,temp); end loop;en
oracle 存储过程--每日统计一个表中的数据，并插入另一个表中
wzhiju
07-13 1278
根据 LOGINFO 和 MEDICALINFO 表中的数据，根据DocID 和 PROCTIME 字段，查询记录，然后根据ACTION 字段的值，将查询出的数据插入到另外一个表中。 create or replace PROCEDURE PROCEDURE1 AS CURSOR LOG_CURSOR IS SELECT TO_CHAR(PROCTIME,'YYYY-MM-DD H...
postgresql 存储过程 把一个表的部分数据插入另一个表中（条件：传入的某些参数作为检索条件并且可能为空值）
IT_ziliang的博客
10-27 4782
postgresql 存储过程
存储过程如何向一张表中循环插入1000条数据和字段二进制转换
hero_girl的专栏
06-14 2398
declare @tb table(id int identity(0,1),date datetime) declare @count int,@i float set @count=0 while(@count <=1000)   begin     insert into @tb values(dateadd(ms,@count,00:00:00.000))   
数据库复习（九）——存储过程
学习笔记
07-22 2500
  9.1  游标的使用        使用游标（CURSOR）在需要一行一行处理时，游标十分有用。游标可以打开一个结果集合（按照指定的标准选择的行），并提供在结果集中一行一行处理的功能。基于游标的类型，可以对其进行回滚或者前进。 一．游标的声明    用DECLARE语句对游标进行声明，有两种方法可以指定一个游标。 SQL-92 语法 DECLARE cursor_name [ IN...
存储过程批量从一个表插入到另一个表
fengchao1000的专栏
06-02 2317
create proc YSCX_insert @SCUserId int ,@SCYY nvarchar(500),@JMBH varchar(50)  ,@result int outputas  declare @sqlInsert varchar(500),          @sqlDelete varchar(500)  begin  tran           select @sqlInsert += 'insert into dbo.YSCX select JMBH, WJH, LWDW,
MySQL存储过程：遍历多表数据并插入注册表
"这篇教程详细讲解了MySQL存储过程在遍历多表记录并插入第三方表中的应用，旨在帮助读者理解如何使用存储过程解决实际问题。文章以一个具体的项目背景为出发点，探讨了如何在现有项目中实现设备的统一管理。" 在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值