0

I have the following SQL code:

INSERT INTO TIMES (saleDay, dayType)
SELECT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday'
    WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend'
        ELSE 'Weekday' END dayType
 FROM SALES s LEFT JOIN 
     (SELECT '01.01' hd FROM DUAL UNION ALL
      SELECT '15.01' FROM DUAL UNION ALL
      SELECT '19.01' FROM DUAL UNION ALL
      SELECT '28.05' FROM DUAL UNION ALL
      SELECT '04.07' FROM DUAL UNION ALL
      SELECT '08.10' FROM DUAL UNION ALL
      SELECT '11.11' FROM DUAL UNION ALL
      SELECT '22.11' FROM DUAL UNION ALL
      SELECT '25.12' FROM DUAL) h
ON h.hd = TO_CHAR(s.saleDate, 'dd.mm');

And I need to convert it to a PL/SQL block. I initially just turned the code above into a create or replace procedure and then called it, but I've been asked to complete the code above in PL/SQL.

Unfortunately, I've really struggled in this area and am still trying to grasp the concept of PL/SQL, especially in situations like this where it doesn't make much sense to conduct the INSERT code via PL/SQL. Any pointers/instruction on what the best way to convert this to PL/SQL is?

Thank you!

Improve this question
2
  • I just went through the same experience, and - shame of all shames - it was in a course offered by Oracle itself. Why instructors feel it necessary to require students to practice idiotic habits such as inserting data row-by-row in a PL/SQL loop is beyond me. Surely PL/SQL wasn't invented in order for us to do things in much worse ways than we can in SQL? There must be some legitimate uses for loops, cursors, EXECUTE IMMEDIATE etc. - why don't instructors show us THOSE use cases? (Alas, I suspect that in many cases it is because the instructors themselves don't have a clue.) Commented Feb 20, 2018 at 15:27
  • @mathguy...Yep - Totally feel the same way. At this point, I'm just trying to get through my current course and then plan to find/take another SQL course that will hopefully elaborate more in-depth on the ACTUAL requirements for PL/SQL. It seems like most cases when I've been asked to use PL/SQL "reinvent the wheel" of SQL. I'd much rather learn of cases where PL/SQL is actually required or more efficient. So on that note, from what I understand - you think I should handle this with a PL/SQL loop? Commented Feb 20, 2018 at 15:32

4 Answers 4

Reset to default
2

I agree, this is better as plain SQL. If this is just for a course, I'd do it with a simple loop and a collection. If you're dealing with a high-volume production environment, a BULK COLLECT .. FORALL approach will have much better performance. 

declare
  vDayType varchar2(10);
  TYPE Holidays is table of varchar2(5);
  hd Holidays := Holidays('01.01','15.01','19.01','28.05','04.07','08.10','11.11','22.11','25.12');
begin
  for s in (select distinct saleDate from Sales) loop
    vDayType := case when TO_CHAR(s.saleDate, 'dd.mm') member of hd then 
        'Holiday'
    when to_char(s.saleDate, 'd') IN (1,7) then
        'Weekend'
    else
        'Weekday'
    end;
    insert into times (saleDay, dayType) values (s.saleDate, vDayType);
  end loop;
end;
/

I feel like PL/SQL procedures like this are a good choice when so much processing needs to be done for each record that it would be awkward, less readable, or impossible to do in SQL. Maybe you're building HTML or modifying a DOCX file in a clob column. I don't know. Honestly, I don't come across use cases for this kind of thing very often in my work.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

This is awesome. Thank you! Like you, I can see when they would be a good choice, however for this certain case - it seems silly for me to make a PL/SQL conversion for something that works well in SQL. THANK YOU anyways! Always great knowledge here on Stack Overflow.
I'm getting an error "ERROR at line 1: ORA-00001: unique constraint (CM420A01.SYS_C002205100) violated ORA-06512: at line 16" On this when I put it into my actual program (I was using SQL Fiddle to test earlier. Any thoughts on why I'm getting this error? I understand it has to do with the fact that there is a UNIQUE constraint, but I'm having troubles with resolving.
If you look at the constraint (with e.g. select * from user_constraints where constraint_name = 'SYS_C002205100' ), you should see which column(s) are causing the problem. I'm guessing that duplicate saleDays are being inserted. You might also check to make sure that the TIMES table is empty before starting.
Ok, Will check this afternoon. I have a DELETE FROM TIMES; Statement in there, so the table should be empty. I'll see what constraint is throwing the error.
Eureka! I see what's happening here. Basically there are several occurrences where two or more vehicles were sold on the same date, therefore the code you provided is trying to insert multiple instances of the same saleDate into the saleDay Primary Key column. Basically, this code needs to only add one instance of each saleDate to the saleDay column in TIMES. Any thoughts on how to do this? @kfinity
|
1

Thinking I may just go with this...Although I'm not sure if it'll be taken as PL/SQL.

Better solutions are still welcomed!

BEGIN

INSERT INTO TIMES (saleDay, dayType)
SELECT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday'
    WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend'
        ELSE 'Weekday' END dayType
 FROM SALES s LEFT JOIN 
     (SELECT '01.01' hd FROM DUAL UNION ALL
      SELECT '15.01' FROM DUAL UNION ALL
      SELECT '19.01' FROM DUAL UNION ALL
      SELECT '28.05' FROM DUAL UNION ALL
      SELECT '04.07' FROM DUAL UNION ALL
      SELECT '08.10' FROM DUAL UNION ALL
      SELECT '11.11' FROM DUAL UNION ALL
      SELECT '22.11' FROM DUAL UNION ALL
      SELECT '25.12' FROM DUAL) h
ON h.hd = TO_CHAR(s.saleDate, 'dd.mm');

END;
/
Improve this answer

Comments

1

if TIMES table has only two columns (salesDay and DayType), you can also do it like this,

BEGIN
    FOR rec IN 
        (SELECT saleDate, CASE WHEN h.hd IS NOT NULL THEN 'Holiday'
                WHEN to_char(saleDate, 'd') IN (1,7) THEN 'Weekend'
                ELSE 'Weekday' END dayType
           FROM SALES s LEFT JOIN 
          (SELECT '01.01' hd FROM DUAL UNION ALL
           SELECT '15.01' FROM DUAL UNION ALL
           SELECT '19.01' FROM DUAL UNION ALL
           SELECT '28.05' FROM DUAL UNION ALL
           SELECT '04.07' FROM DUAL UNION ALL
           SELECT '08.10' FROM DUAL UNION ALL
           SELECT '11.11' FROM DUAL UNION ALL
           SELECT '22.11' FROM DUAL UNION ALL
           SELECT '25.12' FROM DUAL) h
          ON h.hd = TO_CHAR(s.saleDate, 'dd.mm')))     
    LOOP
         INSERT INTO TIMES VALUES rec;
    END LOOP;
END;
/
Improve this answer

Comments

0

I don't have your tables, but I would do something like I am showing below - creating a table to accept the inserts (similar to yours) and using HIREDATE from the EMP table in the SCOTT schema.

I see that kfinity just posted a similar answer; I am still posting mine because I feel it is cleaner, but it's really the same answer.

Also: keeping the dates in a nested table, as kfinity has done, is the correct approach (declare all the "magic things" at the top of your code, so they are easy to find and modify if/when needed); however, it is possible that in your course you haven't covered collections yet. In my solution I hard-coded the values directly where they were needed.

drop table my_table purge;
create table my_table ( hireday date, daytype varchar2(20) );

begin
  for r in ( select hiredate from scott.emp )
  loop
    insert into my_table ( hireday, daytype ) values
      ( r.hiredate,
        case when to_char(r.hiredate, 'dd.mm') in (
                    '01.01', '15.01', '19.01', '28.05', '04.07',
                    '08.10', '11.11', '22.11', '25.12')  then 'Holiday'
             when to_char(r.hiredate, 'd') in ('1', '7') then 'Weekend'
             else 'Weekday' end
      );
  end loop;
end;
/
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.