将SQL选择查询转换为PL/SQL匿名块(ORACLE) [英] Converting SQL Select Query to PL/SQL Anonymous Block (ORACLE)

问题描述

我编写了一个标准的SQL Select查询，以选择销量最大的邮政编码.现在，我需要将其转换为匿名PL/SQL块，但是我对PL/SQL仍然很绿色"，对于如何完成此操作确实没有太多想法.另外，我需要将 LIMIT 合并到PL/SQL匿名块中，该块仅在出现平局时显示最低的邮政编码.

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

这是我编写的SQL查询:

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;

基本上，我首先需要知道如何将其转换"为PL/SQL匿名块.然后，我需要知道如何限制结果，以仅在两个或两个以上之间存在平局时才显示最低的邮政编码.

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.

如果有帮助，我在此处构建了一个SQL提琴模式: http://sqlfiddle. com/#！4/ca18bf/2

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

谢谢！

推荐答案

80％的良好PL/SQL编程是良好的SQL编码.

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

问题所在:首先，在SQL中，要从与大多数销售额相关的数字中选择最低的数字邮政编码，您可以先进行联接，然后再按邮政编码进行汇总-然后使用汇总LAST功能.像这样:

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   

现在，可以很容易地在匿名块中使用它(如果需要，无论出于何种原因). SET SERVEROUTPUT ON不属于PL/SQL代码；这是接口程序的命令，用于指示它在屏幕上显示输出缓冲区的内容.

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

这篇关于将SQL选择查询转换为PL/SQL匿名块(ORACLE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！