There are 3 tables:

CREATE TABLE "SYSTEM"."ID_DOWNLOADS" ( "LOADID" NUMBER NOT NULL ENABLE, "DATELOAD" DATE NOT NULL ENABLE, CONSTRAINT "ID_DOWNLOADS_PK" PRIMARY KEY ("LOADID") ); CREATE TABLE "SYSTEM"."CAR_BRAND" ( "CARBRANDID" NUMBER NOT NULL ENABLE, "BRANDNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, CONSTRAINT "CAR_BRAND_PK" PRIMARY KEY ("CARBRANDID") ); CREATE TABLE "SYSTEM"."SALECAR" ( "CARBRANDID" NUMBER NOT NULL ENABLE, "CARMODELID" NUMBER NOT NULL ENABLE, "COST" NUMBER NOT NULL ENABLE, "SALEDATE" DATE NOT NULL ENABLE, "LOADID" NUMBER

);

You need to write a procedure in which the date is submitted. For this date, you need to take loadid from the first table and assign it to a variable. Then, by the value of this variable, output the table in which there will be BRANDNAME and the amount COST for this brand.

I wrote the following

 CREATE OR REPLACE Procedure SummSale ( date_in IN date ) IS no_loadid1 EXCEPTION; Loadid1 number; TYPE SummSale IS RECORD ( Brandname VARCHAR2(30), Cost1 NUMBER (20) ); summ_sale SummSale; begin SELECT loadid INTO loadid1 from id_downloads where dateload = date_in; IF loadid1 = NULL THEN RAISE no_loadid1; ELSE SELECT Br.BRANDNAME, SUM(Sc.COSTT) AS COSTT INTO Summ_Sale.Brandname, Summ_Sale.Costt FROM CAR_BRAND Br INNER JOIN SALECAR Sc ON Br.CARBRANDID = Sc.CARBRANDID WHERE (((loadid1) = Sc.loadid)) GROUP BY Br.BRANDNAME; DBMS_OUTPUT.PUT_LINE('Brandname: ' || Summ_Sale.Brandname); DBMS_OUTPUT.PUT_LINE('Cost: ' || Summ_Sale.Cost1); END if; exception WHEN no_loadid1 THEN raise_application_error (-20001,'load id not found in order to submit.'); end; END SummSale; / show errors declare date1 date; begin date1:=28/01/2018; SummSale(date1); end; /

The question is as follows. Whether select after else adequately written and why swears:

Encountered the symbol "END"

at the end of the procedure?

  • one
    "Whether select is adequately written" - no. select ... into ... from . Or use cursors, bulk collect into . Table description is not complete, add create table ... The no_loadid1 exception no_loadid1 not needed. - 0xdb
  • one
    You have an extra end in the procedure, so it swears. Format the code, and such problems will be immediately visible. - fori1ton
  • @ 0xdb I cannot understand a lot of the time table, I need to describe it, it will consist of 2 fields, there is no similar table structure. It turns out that you can only create it and write it there already? - dima babin
  • And where is your "time table" in the question? Or what do you mean by? - 0xdb
  • one
    Aaa, a record type variable, then yes. It is necessary to declare a record with two fields, then a variable. Or use an implicit cursor . - 0xdb

1 answer 1

Taking into account the corrected request in the related question and discussions in the comments, the working version of the procedure is as follows:

 create or replace procedure printSumSale (dateLoad date) is loadId number; cursor getSumCostPerBrand (loadId number) is SELECT br.BrandName, SUM(sc.cost) Costs FROM car_brand br INNER JOIN salecar sc ON br.carBrandId = sc.carBrandId WHERE sc.loadId = getSumCostPerBrand.loadId GROUP BY br.BrandName ; sumCostPerBrand getSumCostPerBrand%rowtype; function getLoadId (dateLoad date) return number is ret number; found boolean := false; begin for r in ( SELECT loadid FROM id_downloads WHERE dateLoad = getLoadId.dateLoad) loop if not found then ret := r.loadId; found := true; else raise_application_error ( -20001, 'fatal: dateLoad='||dateLoad||' more then one rows found ('||ret||','||r.loadId||')'); end if; end loop; if found then return ret; else raise_application_error ( -20002, 'fatal: loadId='||loadId||' does not exist yet'); end if; end getLoadId; begin loadId := getLoadId (dateLoad); for r in getSumCostPerBrand (loadId) loop dbms_output.put_line ('Brandname: '||r.BrandName||' Costs: '||r.Costs); end loop; exception when others then dbms_output.put_line (sqlerrm||chr(10)||dbms_utility.format_error_backtrace()); raise; end printSumSale; / declare loadDate date := date'2018-01-28'; begin printSumSale (loadDate); end; /