Hello, there are two tables, the firm and the firm_pe.

CREATE TABLE firm (fNum NUMBER(4), fName VARCHAR2(50) NOT NULL, fProductType VARCHAR2(50) NOT NULL, fBudget NUMBER(7,3) NOT NULL), CONSTRAINT firm_pk_snum PRIMARY KEY (fnum) ); CREATE TABLE firm_pe ( fpeNum NUMBER(4), fpeName VARCHAR2(50) NOT NULL, fpeProductType VARCHAR2(50) NOT NULL, fpeBudget NUMBER(7,3) NOT NULL, CONSTRAINT fpe_pk PRIMARY KEY (fpeNum) );

Put such a task in the table firm_pe to find a company with the lowest budget and transfer it to the firm table. In this case, you need to use only implicit cursors. But the company should be transferred only if there are no peripheral equipment suppliers in the firm table (that is, the fProductType column does not contain Peripheral). Solved the problem in this way:

 DECLARE fpe_num number(4); fpe_name VARCHAR2(20); fpe_type VARCHAR2(20); fpe_budget NUMBER(7,3); BEGIN SELECT fpenum, fpename, fpeproducttype, fpebudget INTO fpe_num, fpe_name, fpe_type, fpe_budget FROM firm_pe WHERE fpebudget = (SELECT min(fpebudget) FROM firm_pe); DBMS_OUTPUT.PUT_LINE(fpe_num||' '||fpe_name||' '||fpe_type||' '||fpe_budget); UPDATE firm SET fnum = fpe_num WHERE fproducttype = 'Peripheral'; IF(SQL%NOTFOUND) THEN INSERT INTO firm(fnum, fname, fproducttype , fbudget) VALUES(fpe_num, fpe_name, fpe_type, fpe_budget); DBMS_OUTPUT.PUT_LINE('Добавлена ' || SQL%ROWCOUNT || ' фирма ' || fpe_name); END IF; COMMIT; END;

Data is added,
enter image description here

but when you call again, an error should be generated, and the information is simply updated. Apparently not correctly composed line:

UPDATE firm SET fnum = fpe_num WHERE fproducttype = 'Peripheral';

Tell me how to rewrite and where I was wrong

  • And in what place and why should an error be generated? - Viktorov
  • IF (SQL% NOTFOUND) THEN this condition is always true - harima
  • Although in the first query the error "request returned more than one line" may appear, if in the database there will be more than one record with the same fpebudget and it will be minimal - Mike
  • You understand that by the request UPDATE firm SET fnum = fpe_num WHERE fproducttype = 'Peripheral'; you change all the entries in the firm's table where fproducttype is equal to Peripheral, and you are trying to change the primary key of this table, which in general is of course allowed, but in practice this does not occur. So I did not understand what you are trying to achieve with this update (and yes, an error on update could be the same if more than one row in the firm’s table has fproducttype = 'Peripheral') - Mike
  • one
    I suspect you had to write select count(1) into CNT from firm where fproducttype = 'Peripheral' and then do IF CNT = 0 THEN - Mike

1 answer 1

  1. It is completely incomprehensible why you need two identical tables, differing only in name. So far it looks like a design error. Merge the tables into one and add a column in which the characteristic by which you now distinguish firms will be stored.

  2. As already said in the comments, your first request is rich in potential errors. Rewrite it like this:

     select fpenum, fpename, fpeproducttype, fpe_budget into fpe_num, fpe_name, fpe_type, fpe_budget from (select fpenum, fpename, fpeproducttype, fpe_budget, row_number() over (order by fpebudget) rn from firm_pe) where rn = 1;

  3. The requirement “the company should be transferred only if there are no peripheral equipment suppliers in the firm table” looks strange. Do you need exactly one supplier per table? In any case, your code does not solve this problem.

  4. The error you expect when you restart is ORA-00001 when inserted? Do not wait, the second time you run SQL%NOTFOUND will be false , and there will be no insert.

  5. Completely incomprehensible UPDATE . Maybe you wanted to say

     UPDATE firm SET fproducttype = 'Peripheral' WHERE fnum = fpe_num;

    In this form, it is a much more meaningful expression.

  6. It is much easier to MERGE row from one table and insert it into another - with one MERGE . For example:

     merge firm f using (select fpenum, fpename, fpeproducttype, fpe_budget from (select fpenum, fpename, fpeproducttype, fpe_budget, row_number() over (order by fpebudget) rn from firm_pe) where rn = 1) fpe on fpe.fnum = f.fnum when matched then update set fproducttype = 'Peripheral' when not matched then insert (fnum, fname, fproducttype , fbudget) values (fpe.fpenum, fpe.fpenum, fpe.fpeproducttype, fpe.fpe_budget);