Converting SQL Select Query to PL/SQL Anonymous Block (ORACLE)

Question

I have written a standard SQL Select Query to select the zip code in which the largest number of sales were. I now need to convert it to an anonymous PL/SQL block, however I'm still very "green" with PL/SQL and really don't have much of an idea as to how to accomplish this. Also, I need to incorporate a LIMIT into the PL/SQL anonymous block that will only display the lowest numeric zip code in the event of a tie.

Here are the tables w/some data:

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerZip     VARCHAR(15) NOT NULL); 

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID));

INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (1, '20636');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (2, '20619');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (3, '20670');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (4, '20670');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (5, '20636');

INSERT INTO SALES (saleID, customerID) VALUES (1, 1);
INSERT INTO SALES (saleID, customerID) VALUES (2, 2);
INSERT INTO SALES (saleID, customerID) VALUES (3, 3);
INSERT INTO SALES (saleID, customerID) VALUES (4, 4);
INSERT INTO SALES (saleID, customerID) VALUES (5, 5);

And here's the SQL query I wrote:

SELECT C.customerZip, COUNT (*) AS "MOST_SALES_byZIP"
FROM SALES S
    INNER JOIN CUSTOMERS C
        ON S.customerID = C.customerID
GROUP BY C.customerZip
HAVING COUNT (*) >= ALL
    (SELECT COUNT(*)
        FROM SALES S
            INNER JOIN CUSTOMERS C
                    ON S.customerID = C.customerID
        GROUP BY C.customerZip)
        ORDER BY C.customerZip;

Basically, I first need to know how to "convert" this into a PL/SQL anonymous block. Then, I need to know how I can limit the results to only show the lowest numeric zip code if there is a tie between two or more.

I have an SQL fiddle Schema built here, if it helps: http://sqlfiddle.com/#!4/ca18bf/2

Thank you!

Answer 1

80% of good PL/SQL programming is good SQL coding.

In your problem: first, in SQL, to select the lowest numeric zip code from among those tied for most sales, you can do a join followed by aggregation by zip code, as you did already - and then use the aggregate LAST function. Like so:

select   min(customerzip) keep (dense_rank last order by count(*)) as selected_zip
from     sales inner join customers using (customerid)
group by customerzip
;

SELECTED_ZIP  
---------------
20636   

Now it is easy to use this in an anonymous block (if you have to - for whatever reason). SET SERVEROUTPUT ON is not part of the PL/SQL code; it is a command to the interface program, to instruct it to display the content of the output buffer on screen.

set serveroutput on

declare
  selected_zip integer;
begin
  select   min(customerzip) keep (dense_rank last order by count(*))
    INTO   selected_zip                              -- this is the PL/SQL part!
  from     sales inner join customers using (customerid)
  group by customerzip
  ;
  dbms_output.put_line('Selected zip is: ' || selected_zip);
end;
/

PL/SQL procedure successfully completed.

Selected zip is: 20636

Answer 2

Here's an option. Create a function since an anonymous block can only print to STDOUT, it can't return something into a variable

The having clause is remove and simply order by count,zip so that top count wins then top count + top zip based on the order. Added in fetch first 1 rows ONLY to only get the 1 row then returned it from the function.

SQL> CREATE OR REPLACE FUNCTION getlowest RETURN NUMBER AS
        l_ret   NUMBER;
    BEGIN
        FOR r IN (
            SELECT
                c.customerzip,
                COUNT(*) AS "MOST_SALES_byZIP"
            FROM
                sales s
               INNER JOIN customers c ON s.customerid = c.customerid
           GROUP BY
               c.customerzip
           order by
               COUNT(*), c.customerzip
           fetch first 1 rows ONLY
       ) LOOP
           l_ret := r.customerzip;
       END LOOP;

       RETURN l_ret;
   END;
   /
SQL> show errors;
SQL> 
SQL> select getlowest from dual
  2  /
20619
SQL> 

Answer 3

If the aim is to return a result set, then the PL/SQL block to do that would be

-- [Declare the host ref cursor according to the calling tool/language]
-- e.g. in SQL*Plus
var resultset refcursor

begin
    open :resultset for
        select c.customerzip, count(*) as most_sales_byzip
        from   sales s
               join customers c on s.customerid = c.customerid
        group  by c.customerzip
        having count(*) >= all
               ( select count(*) from sales s
                        join customers c on s.customerid = c.customerid
                 group by c.customerzip )
        order by c.customerzip;
end;

From Oracle 12.1 onwards you can use implicit result sets:

declare
    rc sys_refcursor;
begin
    open rc for
        open :resultset for
            select c.customerzip, count(*) as most_sales_byzip
            from   sales s
                   join customers c on s.customerid = c.customerid
            group  by c.customerzip
            having count(*) >= all
                   ( select count(*) from sales s
                            join customers c on s.customerid = c.customerid
                     group by c.customerzip )
            order by c.customerzip;

    dbms_sql.return_result(rc);
end;

However we already have SQL to do this so it seems a bit pointless.