Home » SQL & PL/SQL » SQL & PL/SQL » Cursor (PL/SQL 10g)
Cursor [message #576281] Sat, 02 February 2013 06:10 Go to next message
derivat
Messages: 1
Registered: January 2013
Junior Member
Hallo,

It should be determine all days between 14.07.09 and 16.07.09
with the number of driving lesson in the morning (between 9,00 and 12,00 o'clock)
and in the afternoon (between 15,00 and 18,00 o'clock)from the table driving
with a CURSOR, according the following illustration, including the text string
'-no driving-' for zero drivings.
******
14.07.09: Number of driving lesson in the morning: 1
14.07.09: Number of driving lesson in afternoon: 1
******
15.07.09: Number of driving lesson in the morning: 2
15.07.09: Number of driving lesson in afternoon: -no driving-
******

CREATE TABLE driving
(
  dno     CHAR(8),
  dep_date    DATE,
  typ     CHAR(10),
  dep_time  NUMBER(4,2)
);

INSERT INTO driving
     VALUES ('DR-56', '14.07.09', 'TF', '9,15');

INSERT INTO driving
     VALUES ('DR-13','14.07.09', 'TM', '15,15');

INSERT INTO driving
     VALUES ('DR-13','15.07.09', 'TF', '10,15');

INSERT INTO driving
     VALUES ('DR-19','15.07.09', 'GF', '10,45');

INSERT INTO driving
     VALUES ('DR-56', '18.07.09', 'BCA', '10,45');


select * from driving;
			
DNO	DEP_DATE	TYP	DEP_TIME
DR-56 	14.07.09	TF 	9,15
DR-13 	14.07.09	TM 	15,15
DR-13 	15.07.09	TF 	10,15
DR-19 	15.07.09	GF 	10,45
DR-56 	18.07.09	BCA 	10,45



What are the possibilities of a cursor in that case?

I'm still at the beginning of PL/SQL programming.

Many thanks in advance.

[Updated on: Sat, 02 February 2013 06:58]

Report message to a moderator

Re: Cursor [message #576286 is a reply to message #576281] Sat, 02 February 2013 08:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
First of all you have design issue. Keeping date and time in separate columns is bad. Secondly '14.07.09' is a string, not a date. Anyway:

with t1 as (
            select  distinct dep_date dep_date
              from  driving
           ),
     t2 as (
            select  dep_date,
                    case lvl
                      when 1 then 9
                      else 15
                    end dep_time
              from  t1,
                    (
                     select  level lvl
                       from  dual
                       connect by level <= 2
                    )
           )
select  to_char(t2.dep_date,'dd.mm.rr') || ' Number of driving lesson in the ' ||
        case t2.dep_time
          when 9 then 'morning: '
          else 'afternoon: '
        end ||
        case count(d.dep_time)
          when 0 then '-no driving-'
          else to_char(count(d.dep_time))
        end lesson_count
  from      t2
        left join
            driving d
          on (
                  d.dep_date = t2.dep_date
              and
                  d.dep_time between t2.dep_time and t2.dep_time + 3
             )
  group by t2.dep_date,
           t2.dep_time
  order by t2.dep_date,
           t2.dep_time
/

LESSON_COUNT
-------------------------------------------------------------------
14.07.09 Number of driving lesson in the morning: 1
14.07.09 Number of driving lesson in the afternoon: 1
15.07.09 Number of driving lesson in the morning: 2
15.07.09 Number of driving lesson in the afternoon: -no driving-
18.07.09 Number of driving lesson in the morning: 1
18.07.09 Number of driving lesson in the afternoon: -no driving-

6 rows selected.

SQL>


SY.
Re: Cursor [message #576288 is a reply to message #576286] Sat, 02 February 2013 09:10 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is obviously a homework and our policy is to not give solution to homework or to people that learn SQL or PL/§QL.
Please follow our policy.

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Previous Topic: Logic to find table names and data in oracle
Next Topic: Logic to compare date with standard timestamp
Goto Forum:
  


Current Time: Mon Dec 22 20:03:31 CST 2014

Total time taken to generate the page: 0.08011 seconds