PL/SQL ORA-01422选择进入Oracle Anonymous Block(NOVA环境)

Question

获取以下错误：ORA-01422：精确的获取返回大于请求的行数ORA-6512

新的PL/SQL，尽管我知道应该通过标准SQL进行此查询，但我正在尝试使用PL/SQL弄清楚如何完成此问题。

我需要查询才能通过ZIP代码'20636'

返回客户的销售总数

这是表/输入：

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, '20650');
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);

这是我为PL/SQL匿名块编写的代码：

DECLARE
customerZip INTEGER;
totalSales INTEGER;
BEGIN
SELECT customerID INTO customerZip from CUSTOMERS where customerZip = '20636';
SELECT COUNT(*) INTO totalSales from SALES where customerID = customerZip;
DBMS_OUTPUT.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || customerZip ||'.');
END;
/

如果我不在" 20636"设置两个Customerzip属性而运行此操作，则可以正常工作。一旦我输入一个以上的客户记录" 20636"，我会遇到错误。

您能解释一下我在这里做错了什么以及如何解决它？谢谢！

SQL小提琴链接如果有帮助：http：//sqlfiddle.com/#！4/10fc1

Answer 1

您可以使用这样的语句：

SQL> set serveroutput on;
SQL> DECLARE
  v_customerZip CUSTOMERS.customerZip%type:='20636';
  v_customerID  CUSTOMERS.customerID%type;
  totalSales    INTEGER:=0;
  Sales         INTEGER;
BEGIN
 for c in ( SELECT customerID from CUSTOMERS where customerZip = v_customerZip )
 loop
  v_customerID := c.customerID;
  SELECT COUNT(1) INTO Sales from SALES where customerID = v_customerID;
  totalSales := totalSales + Sales;
 end loop; 
  dbms_output.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || v_customerZip ||'.');
END;
/
We sold 2 Cars to customers in Zip Code 20636.

和更改变量v_customerzip的价值，无论您从声明部分所需的任何内容。

，或者您可以创建一个完成此任务的过程：

SQL> CREATE OR REPLACE PROCEDURE GET_SALES( v_customerZip CUSTOMERS.customerZip%type ) IS
  v_customerID  CUSTOMERS.customerID%type;
  totalSales    INTEGER:=0;
  Sales         INTEGER;
BEGIN
 for c in ( SELECT customerID from CUSTOMERS where customerZip = v_customerZip )
 loop
  v_customerID := c.customerID;
  SELECT COUNT(1) INTO Sales from SALES where customerID = v_customerID;
  totalSales := totalSales + Sales;
 end loop; 
  dbms_output.put_line('We sold ' || totalSales || ' Cars to customers in Zip Code ' || v_customerZip ||'.');
END;
/
SQL> exec get_sales('&cZ');
Enter value for cz: 20636
We sold 2 Cars to customers in Zip Code 20636.