title: How to write a loop statement in Oracle SQL
body: <p>I need a query for collecting information of check-in status for a group of staff in a particular department everyday. </p>
  
  <p>People I need to collect are saved in a simple table named <code>LEADER</code>, like:</p>
  
  <pre><code> - LEADERNAME    -NUM-
  
   - TOM             1
   - BEN             2
   - LUCY            3
   - ERIC            4
  </code></pre>
  
  <p>The check-in status of all staff in my company is saved in a table named <code>TB_DAYWORK</code>. We know that everyday, once you checked in, the table will update with a new line, including your name and the check-in time. </p>
  
  <p>I've composed a SQL for ONE person's check-in information. the SQL is like this:</p>
  
  <pre><code>select m.* ,n.starttime,n.peoplename 
  from ( 
  select 
  everyDay,
  to_char(everyday,'dy') as weekday,
  lpad(to_char(everyday,'w'),6) as weekinmonth,
  lpad(to_char(everyday,'ww'),6) as weekinyear 
  from 
    (select to_date('20150901','yyyymmdd') + level - 1 as everyDay from dual connect by level &lt;= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))) m 
  left join 
  
  (
  select distinct STARTTIME,
  Peoplename
  from TB_DAYWORK where CREATETIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') 
  and PEOPLENAME in (select leadername from leader where num=1) ) n on m.EVERYDAY=n.STARTTIME
  order by 1;
  </code></pre>
  
  <p>The query result is just like this:</p>
  
  <p><img src="https://i.stack.imgur.com/SDYbc.png" alt="sql preview"></p>
  
  <p>From the image you can see that for one person, all status has been found  by a left join with a dynamic calendar. If the data in <code>PEOPLENAME</code> and <code>STARTTIME</code> is not null that means they had checked-in for that day, or if the column is null, it means he was absent.</p>
  
  <p>Since all people need to be selected here are  in the table <code>LEADER</code> and sorted by the column <code>NUM</code>. I think there should be a way of using a loop statement to collect all people's status we need by using one SQL. Is there a way to do this?</p>
  
  <p>/**********************UPDATE********************************/</p>
  
  <p>@APC thank you for your help, I've just tested your code and the last code I use is like this:</p>
  
  <pre><code>    select m.* , n.starttime, n.peoplename 
  from ( 
          select 
              everyDay,
              to_char(everyday,'dy') as weekday,
              lpad(to_char(everyday,'w'),6) as weekinmonth,
              lpad(to_char(everyday,'ww'),6) as weekinyear 
          from 
            (select to_date('20150901','yyyymmdd') + level - 1 as everyDay 
             from dual 
             connect by level &lt;= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))
         ) m 
      left join 
          (
          select  t.STARTTIME,
                  t.Peoplename,
                  l.num
          from leader l
               join TB_DAYWORK t
                    on t.PEOPLENAME = leadername
          where t.STARTTIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
          order by l.num, t.Starttime
          ) n 
  on m.EVERYDAY=n.STARTTIME
  order by n.num,m.everyDay ;
  </code></pre>
  
  <p>here's the result I got:</p>
  
  <p>[<img src="https://i.stack.imgur.com/82ZvB.jpg" alt="results of revised query"></p>
  
  <p>Perfectly ordered by <code>everyday</code> and <code>leadername</code>, but as highlighted, you can see that the everyday column has missed data, here it lost 9/12 and 9/13.</p>
  
  <p>I am a little confused, because as far as I know the <code>LEFT JOIN</code> will keep all data at the left side, if so the right side -- <code>STARTTIME</code> column and <code>PEPOPENAME</code> column will have some null lines, which will be easy to see (you can see it on the first pic), and that is the reason I use it. Why are they missing now?</p>
  
  <p>/////***********************UPDATE********************//////</p>
  
  <p>the cross join way results is like this:
  <a href="https://i.stack.imgur.com/Rdocc.jpg" rel="nofollow noreferrer">cross join</a></p>
  
  <p>///////////////************UPDATE SAMPLE DATA**************///////////////</p>
  
  <p>only 2 tables are involved here: <code>TB_DAYWORK</code> and <code>LEADER</code>  , I prepared sample tables for further explore. same data to the original tables, but all columns with chinese characters are replaced by roman letters, just in case for the character issue.</p>
  
  <p>for table <code>LEADER</code> which is very simple, please run below code to build a sample:</p>
  
  <pre><code>create table leader(leadername varchar2(50), num number);
  
  insert into leader values ('mazeping','1');
  insert into leader values ('zhangyi','2');
  insert into leader values ('taoshengfa','3');
  </code></pre>
  
  <p>for table <code>TB_DAYWORK</code> I got its metadata:</p>
  
  <pre><code>CREATE TABLE "JSHXBJY"."TB_DAYWORK"
      (       "ROW_ID" VARCHAR2(32) NOT NULL ENABLE,
      "CREATETIME" DATE,
       "CREATEUSERID" VARCHAR2(32),
       "UPDATETIME" DATE,
       "UPDATEUSERID" VARCHAR2(32),
       "DELFLAG" NUMBER(10,0),
       "DEPTID" VARCHAR2(255),
       "STARTTIME" DATE,
       "STARTTIMESTR" VARCHAR2(255),
       "WORK_CONTENT" VARCHAR2(3000),
       "PEOPLENAME" VARCHAR2(255),
       "WORK_CONTENT_INFO" VARCHAR2(4000),
       "WORK_REMARK" VARCHAR2(255),
       "PEOPLE_SORT" NUMBER(10,0),
       "LOGINID" VARCHAR2(255),
       "HEAD_PHOTO_PATH" VARCHAR2(255)
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
     TABLESPACE "JSHXBJY"
  </code></pre>
  
  <p>also I exported some raw data from the original table, please download from:
  <a href="http://pan.baidu.com/s/1mgtRiBI" rel="nofollow noreferrer">http://pan.baidu.com/s/1mgtRiBI</a> (press the 2nd button on the top-right corner to download,<em>please let me know if this link cannot be reached</em>).</p>
  
  <p>and the control file for sqlloader is:</p>
  
  <pre><code>LOAD DATA
  INFILE 'c:\oracle\tb_daywork_data1.txt'
  APPEND
  into TABLE TB_DAYWORK
  
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  trailing nullcols
  (ROW_ID,CREATETIME date "yyyy-mm-dd hh24:mi:ss",CREATEUSERID,UPDATETIME date "yyyy-mm-dd",UPDATEUSERID,DELFLAG,DEPTID,STARTTIME date "yyyy-mm-dd",STARTTIMESTR,WORK_CONTENT,PEOPLENAME,WORK_CONTENT_INFO,WORK_REMARK,PEOPLE_SORT,LOGINID,HEAD_PHOTO_PATH)
  </code></pre>
  
  <p>and the command is :</p>
  
  <pre><code>C:\&gt;sqlldr jshxbjy/jshxbjy control=c:\oracle\sqlldr.txt discard=c:\oracle\tb_daywork_dis.txt bad=c:\oracle\tb_daywork_bad.txt log=c:\oracle\tb_daywork_log.txt
  </code></pre>
  
  <p>I've tested by myself, these codes should be ok to build up sample tables, which including 3 people's check-in data in this September.</p>