CODEWITHSUNDEEP
sqloracle-databaseplsql
StevenC
StevenC

Reputation: 109

Convert SQL to PL/SQL Block (ORACLE DB)

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!

Upvotes: 0

Views: 2767

Answers (4)

kfinity
kfinity

Reputation: 9091

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.

Upvotes: 2

eifla001
eifla001

Reputation: 1157

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;
/

Upvotes: 1

user5683823
user5683823

Reputation:

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;
/

Upvotes: 0

StevenC
StevenC

Reputation: 109

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;
/

Upvotes: 1

Related Questions