Statement processed 003 seconds 2 DECLARE CURSOR curshopper IS SELECT

Statement processed 003 seconds 2 declare cursor

This preview shows page 17 - 26 out of 42 pages.

Statement processed. 0.03 seconds
Image of page 17
2 – DECLARE CURSOR cur_shopper IS SELECT a.idShopper, a.promo, b.total FROM bb_shopper a, (SELECT b.idShopper, SUM(bi.quantity*bi.price) total FROM bb_basketitem bi, bb_basket b WHERE bi.idBasket = b.idBasket GROUP BY idShopper) b WHERE a.idShopper = b.idShopper FOR UPDATE OF a.idShopper NOWAIT; lv_promo_txt CHAR(1); BEGIN FOR rec_shopper IN cur_shopper LOOP lv_promo_txt := 'X'; IF rec_shopper.total > 100 THEN lv_promo_txt := 'A'; END IF; IF rec_shopper.total BETWEEN 50 AND 99 THEN lv_promo_txt := 'B'; END IF; IF lv_promo_txt <> 'X' THEN UPDATE bb_shopper SET promo = lv_promo_txt WHERE CURRENT OF cur_shopper; END IF; END LOOP; COMMIT;
Image of page 18
END; 1 row(s) updated. 0.03 seconds Select idshopper, s.promo, sum(bi.quantity * bi.price) Total from bb_shopper s join bb_basket b using(idshopper) join bb_basketitem bi using(idbasket) group by idshopper, s.promo order by idshopper; Results Explain Describe Saved SQL History IDSHOPPER PROMO TOTAL 21 B 55.1
Image of page 19
22 A 202.39 23 B 64.8 24 - 48.9 25 B 72.4 26 - 20 27 - 32.4 7 rows returned in 0.02 seconds Download --Rows have been updated and the total is obtained from the multiplication of quantity and price
Image of page 20
3 - UPDATE bb_shopper SET promo = NULL; 7 row(s) updated. 0.01 seconds UPDATE bb_shopper SET promo = 'B' WHERE idShopper IN (21,23,25); 3 row(s) updated. 0.01 seconds UPDATE bb_shopper SET promo = 'A' WHERE idShopper = 22; 1 row(s) updated. 0.01 seconds BEGIN UPDATE bb_shopper SET promo = NULL WHERE promo IS NOT NULL; IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows updated'); ELSE
Image of page 21
DBMS_OUTPUT.PUT_LINE('No rows changed'); END IF; END; 4 rows updated 1 row(s) updated. 0.01 seconds 4 – DECLARE lv_tax_num NUMBER(2,2); BEGIN CASE 'NJ' WHEN 'VA' THEN lv_tax_num := .04; WHEN 'NC' THEN lv_tax_num := .02; WHEN 'NY' THEN lv_tax_num := .06; END CASE; DBMS_OUTPUT.PUT_LINE('tax rate = '||lv_tax_num); EXCEPTION When CASE_NOT_FOUND then DBMS_OUTPUT.PUT_LINE('NO Tax'); END; NO Tax Statement processed. 0.00 seconds
Image of page 22
5 – DECLARE rec_shopper bb_shopper%ROWTYPE; BEGIN SELECT * INTO rec_shopper FROM bb_shopper WHERE idShopper = 99; Exception WHEN NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('Invalid shopper ID'); END; Invalid shopper ID Statement processed. 0.00 seconds 6 – ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity < 20); Table altered. 0.02 seconds Declare ex_bitems_ck EXCEPTION; PRAGMA EXCEPTION_INIT(ex_bitems_ck, -02290);
Image of page 23
BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); EXCEPTION WHEN ex_bitems_ck THEN DBMS_OUTPUT.PUT_LINE('Check Constraint Violated'); END; Check Constraint Violated 1 row(s) inserted. 0.00 seconds 7 – Declare lv_new_num NUMBER(3) :=4; lv_old_num NUMBER(3) :=30; ex_id_fk EXCEPTION; PRAGMA EXCEPTION_INIT(ex_id_fk, -02291); BEGIN UPDATE bb_basketitem SET idBasket = lv_old_num WHERE idBasket = lv_new_num; EXCEPTION WHEN ex_id_fk THEN DBMS_OUTPUT.PUT_LINE('Invalid original basket ID'); END;
Image of page 24
Invalid original basket ID 1 row(s) updated. 0.00 seconds 8 – DECLARE CURSOR cur_emp IS SELECT employee_id, salary, salary*12 AnSal, salary*12*.06 Raise FROM employees WHERE employee_id <> 100 FOR UPDATE NOWAIT; rec_emp cur_emp%rowtype; new_sal number(12,2); BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO rec_emp; EXIT WHEN cur_emp%NOTFOUND; IF rec_emp.employee_id <> 100 THEN new_sal := rec_emp.salary*12 * 0.6;
Image of page 25
Image of page 26

You've reached the end of your free preview.

Want to read all 42 pages?

  • Spring '16
  • Mary Gable
  • Salary, Annual Salary, new salary, TimesTen

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture