, , , , :
Look_at_more:=True;
EXEC SQL OPEN CURSOR Londonsales;
while Look_at_more and SQLCODE = O do
begin
EXEC SQL FETCH Londonsales
INTO:id_num,:Salesperson,:loc,:comm;
writeln (id_num, Salesperson, loc, comm);
writeln ('Do you want to see more data? (Y/N)');
readln (response);
If response = 'N' then Look_at_more:=False;
end;
EXEC SQL CLOSE CURSOR Londonsales;
WHENEVER
. , , , . , SQL GOTO. , SQL , GOTO , SQLCODE. WHENEVER. :
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
SQLERROR , SQLCODE < 0; NOT FOUND , SQLCODE = 100. ( SQLWARNING.)
Error_handler , , (GOTO ). , , , ( ). , .
CONTINUE - SQLCODE. , WHENEVER, SQLCODE. , , , () . , INSERT, , , - , , . , :
EXEC SQL WHENEVER NOT FOUND GOTO No_rows;
No_rows , . , , :
EXEC SQL WHENEVER NOT FOUND CONTINUE;
, , , .
, , . , UPDATE DELETE. , , . 16, SQL , :
|
|
|
EXEC SQL DELETE FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, , , , DELETE . :
EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);
, :
EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL OPEN CURSOR Belowavg;
while not SOLCODE = 100 do
begin
EXEC SOL FETCH Belowavg INTO:a,:b,:c,:d,:e;
EXEC SOL DELETE FROM Customers
WHERE CURRENT OF Belowavg;
end;
EXEC SOL CLOSE CURSOR Belowavg;
WHERE CURRENT OF , DELETE , . , , DELETE , , , .
. , (. 21). , ORDER BY UNION, , , . , , . FETCH. UPDATE .
, =300, . :
EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300);
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = 0 do
begin
EXEC SOL FETCH High_cust
INTO:id_num,:salesperson,:loc,:comm;
EXEC SQL UPDATE Salespeople
SET comm = comm +.01
WHERE CURRENT OF High_cust;
end;
EXEC SQL CLOSE CURSOR High_cust;
: , , UPDATE . FOR UPDATE <column list>. High_cust , UPDATE comm, :
EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;
, .
INDICATOR
(NULLS) SQL. . NULL , NULL SQL, . NULL , , : SQLCODE , . , . , NULL , . , , NULL . , , indicator ().
|
|
|
indicator, SQL . , SQL. , , NULL , indicator, . indicator SQL , , - , , , INDICATOR.
indicator 0. , NULL, indicator . indicator, , NULL. , city comm, , NOT NULL, SQL , i_a i_b.
, indicator. indicator, indicator.
:
EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = O do
begin
EXEC SQL FETCH High_cust
INTO:id_num,:salesperson,:loc,:i_a,:comm INDlCATOR,:i_b;
If i_a >= 0 and i_b >= 0
then {no NULLs produced}
begin
EXEC SQL UPDATE Salespeople
SET comm = comm +.01
WHERE CURRENT OF Hlgh_cust
end {then}
else {one or both NULL}
begin
If i_a < 0 then writeln('salesperson ', id_num, ' has no city');
If i_b < 0 then writeln('salesperson ', id_num, ' has no commission')
end {else}
end; {while}
EXEC SQL CLOSE CURSOR High_cust;
, , INDICATOR , , , . , UPDATE NULL .
NULL , , , NULL .
: indicator , , WHERE SQL.
, .
