Castell - Notes

Castell - Notes

SET SERVEROUTPUT ON

Chapter 2

Chall_2-1
DECLARE
lv_billing_date DATE := '21-OCT-12';
lv_lastname_txt VARCHAR2(20);
lv_creditbal_num NUMBER(6,2) := 1000;
BEGIN
lv_lastname_txt := 'Brown';
DBMS_OUTPUT.PUT_LINE(lv_billing_date);
DBMS_OUTPUT.PUT_LINE(lv_lastname_txt);
DBMS_OUTPUT.PUT_LINE(lv_creditbal_num);
END;
21-OCT-12
Brown
1000
Chall_2-2
Run sepparetely
DECLARE
lv_news_txt CHAR(1) NOT NULL := 'Y';
lv_balance_num NUMBER(6,2) := 1200;
lv_rate_num CONSTANT NUMBER(2,2) := .05;
lv_minpay_num NUMBER(6,2);
BEGIN
lv_minpay_num := lv_balance_num * lv_rate_num;
lv_news_txt := 'N';
DBMS_OUTPUT.PUT_LINE(lv_news_txt);
DBMS_OUTPUT.PUT_LINE(lv_balance_num);
DBMS_OUTPUT.PUT_LINE(lv_rate_num);
DBMS_OUTPUT.PUT_LINE(lv_minpay_num);
END;
======
DECLARE
lv_news_txt CHAR(1) NOT NULL := 'Y';
lv_balance_num NUMBER(6,2) := 1200;
lv_rate_num CONSTANT NUMBER(2,2) := .05;
lv_minpay_num NUMBER(6,2);
BEGIN
lv_rate_num := .07; -- constant value – cannot be re define
lv_minpay_num := lv_balance_num * lv_rate_num;
lv_news_txt := 'N';
DBMS_OUTPUT.PUT_LINE(lv_news_txt);
DBMS_OUTPUT.PUT_LINE(lv_balance_num);
DBMS_OUTPUT.PUT_LINE(lv_rate_num);
DBMS_OUTPUT.PUT_LINE(lv_minpay_num);
END;
Chall_2-3
DECLARE
lv_promo_txt CHAR(6) := 'A0807X';
lv_mth_txt CHAR(2);
lv_year_txt CHAR(2);
BEGIN
lv_mth_txt := SUBSTR(lv_promo_txt,2,2);
lv_year_txt := SUBSTR(lv_promo_txt,4,2);
DBMS_OUTPUT.PUT_LINE(lv_mth_txt);
DBMS_OUTPUT.PUT_LINE(lv_year_txt);
END;
08
07
Chall2_6 – run separately
DECLARE
lv_promo_txt CHAR(1);
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2);
BEGIN
IF lv_promo_txt = 'A' THEN
lv_disc_num := lv_total_num * .05;
ELSIF lv_promo_txt = 'B' THEN
lv_disc_num := lv_total_num * .10;
ELSIF lv_promo_txt = 'C' THEN
lv_disc_num := lv_total_num * .15;
ELSE
lv_disc_num := lv_total_num * .02;
END IF;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
======
DECLARE
lv_promo_txt CHAR(1);
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2) := 0;
BEGIN
IF lv_promo_txt = 'A' THEN
lv_disc_num := lv_total_num * .05;
ELSIF lv_promo_txt = 'B' THEN
lv_disc_num := lv_total_num * .10;
ELSIF lv_promo_txt = 'C' THEN
lv_disc_num := lv_total_num * .15;
END IF;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
DECLARE
lv_promo_txt CHAR(1);
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2);
BEGIN
lv_promo_txt := 'A'; -- added this line
IF lv_promo_txt = 'A' THEN
lv_disc_num := lv_total_num * .05;
ELSIF lv_promo_txt = 'B' THEN
lv_disc_num := lv_total_num * .10;
ELSIF lv_promo_txt = 'C' THEN
lv_disc_num := lv_total_num * .15;
ELSE
lv_disc_num := lv_total_num * .02;
END IF;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
5
DECLARE
lv_promo_txt CHAR(1);
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2) := 0;
BEGIN
lv_promo_txt := 'B'; -- Added this line
IF lv_promo_txt = 'A' THEN
lv_disc_num := lv_total_num * .05;
ELSIF lv_promo_txt = 'B' THEN
lv_disc_num := lv_total_num * .10;
ELSIF lv_promo_txt = 'C' THEN
lv_disc_num := lv_total_num * .15;
END IF;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
10
Chall_2-7
DECLARE
lv_promo_txt CHAR(1) := 'B';
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2) := 0;
BEGIN
IF lv_promo_txt IN('A','E') THEN
lv_disc_num := lv_total_num * .05;
ELSIF lv_promo_txt IN('B','C') THEN
lv_disc_num := lv_total_num * .10;
ELSIF lv_promo_txt = 'D' THEN
lv_disc_num := lv_total_num * .15;
END IF;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
10
Chall_2-8
DECLARE
lv_promo_txt CHAR(1) := 'B';
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2);
BEGIN
CASE lv_promo_txt
WHEN 'A' THEN lv_disc_num := lv_total_num * .05;
WHEN 'B' THEN lv_disc_num := lv_total_num * .10;
WHEN 'C' THEN lv_disc_num := lv_total_num * .15;
ELSE lv_disc_num := lv_total_num * .02;
END CASE;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
10
Chall_2-9
DECLARE
lv_promo_txt CHAR(1) := 'B';
lv_total_num NUMBER(6,2) := 100;
lv_disc_num NUMBER(5,2);
BEGIN
lv_disc_num := CASE lv_promo_txt
WHEN 'A' THEN lv_total_num * .05
WHEN 'B' THEN lv_total_num * .10
WHEN 'C' THEN lv_total_num * .15
ELSE lv_total_num * .02
END;
DBMS_OUTPUT.PUT_LINE(lv_disc_num);
END;
Chall_2-10
DECLARE
lv_one_num NUMBER(3) := 11;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(lv_one_num);
lv_one_num := lv_one_num - 2;
EXIT WHEN lv_one_num < 2;
END LOOP;
END;
11
9
7
5
3
Chall_2-11
DECLARE
lv_one_num NUMBER(3) := 11;
BEGIN
WHILE lv_one_num >= 2 LOOP
DBMS_OUTPUT.PUT_LINE(lv_one_num);
lv_one_num := lv_one_num - 2;
END LOOP;
END;
11
9
7
5
3
Chall_2-12
DECLARE
lv_one_num NUMBER(3) := 11;
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(lv_one_num);
lv_one_num := lv_one_num - 2;
END LOOP;
END;
11
9
7
5
3

Chapter 3

ScalarA03
DECLARE
lv_basket_num NUMBER(3);
lv_created_date DATE;
lv_qty_num NUMBER(2);
lv_sub_num NUMBER(5,2);
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
lv_basket_nu bb_basket.idBasket%Type; ------Add this line
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO lv_basket_nu, lv_created_date, lv_qty_num, lv_sub_num
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - lv_created_date;
DBMS_OUTPUT.PUT_LINE(lv_basket_num||' * '||lv_created_date||' * '||
lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
END;
ScalarB03
DECLARE
lv_basket_num NUMBER(3);
lv_created_date DATE;
lv_qty_num NUMBER(2);
lv_sub_num NUMBER(5,2);
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - lv_created_date; --- put “:” in front of “=”
DBMS_OUTPUT.PUT_LINE(lv_basket_num||' * '||lv_created_date||' * '||
lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
END;
ScalarC03
DECLARE
lv_basket_num NUMBER(3);
lv_created_date DATE;
lv_qty_num NUMBER(2);
lv_sub_num NUMBER(5,2);
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num– “lv_basket_num” instead of “lv_basket_nu”
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - lv_created_date;
DBMS_OUTPUT.PUT_LINE(lv_basket_num||' * '||lv_created_date||' * '||
lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
END;
ScalarD03
DECLARE
lv_basket_num NUMBER(3);
lv_created_date DATE;
lv_qty_num NUMBER(2);
lv_sub_num NUMBER(5,2);
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - lv_created_date; --- put “;” at the end of line
DBMS_OUTPUT.PUT_LINE(lv_basket_num||' * '||lv_created_date||' * '||
lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
END;
ScalarD03
DECLARE
lv_basket_num NUMBER(3);
lv_created_date DATE;
lv_qty_num NUMBER(2);
lv_sub_num NUMBER(5,2);
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 33;-- 33 is not in the DB
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - lv_created_date;
DBMS_OUTPUT.PUT_LINE(lv_basket_num||' * '||lv_created_date||' * '||
lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
END;
Idshopper in db = 21, 22, 23, 24, 25, 26, 27
Chall_3-1
DECLARE
lv_card#_txt VARCHAR2(20);
lv_cardtype_txt CHAR(1);
BEGIN
SELECT cardnumber, cardtype
INTO lv_card#_txt, lv_cardtype_txt
FROM bb_basket
WHERE idbasket = 10;
DBMS_OUTPUT.PUT_LINE('Card #: '||lv_card#_txt);
DBMS_OUTPUT.PUT_LINE('Card Type: '||lv_cardtype_txt);
END;
This example, “Cardtype” is blank in DB.
Use %TYPE
DECLARE
lv_card#_txt bb_basket.cardnumber%type;
lv_cardtype_txt bb_basket.cardtype%type;
BEGIN
SELECT cardnumber, cardtype
INTO lv_card#_txt, lv_cardtype_txt
FROM bb_basket
WHERE idbasket = 10;
DBMS_OUTPUT.PUT_LINE('Card #: '||lv_card#_txt);
DBMS_OUTPUT.PUT_LINE('Card Type: '||lv_cardtype_txt);
END;
Chall_3_2 – answer for Chall_3_1
DECLARE
lv_card#_txtbb_basket.cardnumber%TYPE;
lv_cardtype_txtbb_basket.cardtype%TYPE;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardnumber, cardtype
INTO lv_card#_txt, lv_cardtype_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
DBMS_OUTPUT.PUT_LINE('Card #: '||lv_card#_txt);
DBMS_OUTPUT.PUT_LINE('Card Type: '||lv_cardtype_txt);
END;
Chall_3_3
DECLARE
lv_cardtype_txtbb_basket.cardtype%TYPE;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardtype
INTO lv_cardtype_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
IF lv_cardtype_txt = 'V' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Visa');
ELSIF lv_cardtype_txt = 'X' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Amerian Express');
ELSIF lv_cardtype_txt = 'M' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Master Card');
ELSE
DBMS_OUTPUT.PUT_LINE('No credit card type indicated');
END IF;
END;
DECLARE
lv_cardtype_txtbb_basket.cardtype%TYPE;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardtype
INTO lv_cardtype_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
lv_cardtype_txt := 'V'; -- Added this line
IF lv_cardtype_txt = 'V' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Visa');
ELSIF lv_cardtype_txt = 'X' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Amerian Express');
ELSIF lv_cardtype_txt = 'M' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Master Card');
ELSE
DBMS_OUTPUT.PUT_LINE('No credit card type indicated');
END IF;
END;
Chall_3_4
DECLARE
lv_cardtype_txtbb_basket.cardtype%TYPE;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardtype
INTO lv_cardtype_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
CASE lv_cardtype_txt
WHEN 'V' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Visa');
WHEN 'X' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Amerian Express');
WHEN 'M' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Master Card');
ELSE
DBMS_OUTPUT.PUT_LINE('No credit card type indicated');
END CASE;
END;
DECLARE
lv_cardtype_txtbb_basket.cardtype%TYPE;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardtype
INTO lv_cardtype_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
lv_cardtype_txt := 'V'; -- Add this line
CASE lv_cardtype_txt
WHEN 'V' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Visa');
WHEN 'X' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Amerian Express');
WHEN 'M' THEN
DBMS_OUTPUT.PUT_LINE('Shopper used credit card type of Master Card');
ELSE
DBMS_OUTPUT.PUT_LINE('No credit card type indicated');
END CASE;
END;
Chall_3_4
DECLARE
lv_sub_numbb_basket.subtotal%TYPE;
lv_state_txtbb_basket.shipstate%TYPE;
lv_tax_num NUMBER(4,2) := 0;
lv_bask_num NUMBER(3) := 13;
BEGIN
SELECT subtotal, shipstate
INTO lv_sub_num, lv_state_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
IF lv_state_txt = 'VA' THEN
lv_tax_num := lv_sub_num * .06;
ELSIF lv_state_txt = 'ME' THEN
lv_tax_num := lv_sub_num * .05;
ELSIF lv_state_txt = 'NY' THEN
lv_tax_num := lv_sub_num * .07;
ELSE
lv_tax_num := lv_sub_num * .04;
END IF;
DBMS_OUTPUT.PUT_LINE('State = '||lv_state_txt);
DBMS_OUTPUT.PUT_LINE('Subtotal = '||lv_sub_num);
DBMS_OUTPUT.PUT_LINE('Tax amount = '||lv_tax_num);
UPDATE bb_basket
SET tax = lv_tax_num
WHERE idbasket = lv_bask_num;
COMMIT;
END;
State = -- No state info.
Subtotal = 20
Tax amount = .8
DECLARE
lv_sub_numbb_basket.subtotal%TYPE;
lv_state_txtbb_basket.shipstate%TYPE;
lv_tax_num NUMBER(4,2) := 0;
lv_bask_num NUMBER(3) := 13;
BEGIN
SELECT subtotal, shipstate
INTO lv_sub_num, lv_state_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
lv_state_txt := 'VA'; -- Added this line
IF lv_state_txt = 'VA' THEN
lv_tax_num := lv_sub_num * .06;
ELSIF lv_state_txt = 'ME' THEN
lv_tax_num := lv_sub_num * .05;
ELSIF lv_state_txt = 'NY' THEN
lv_tax_num := lv_sub_num * .07;
ELSE
lv_tax_num := lv_sub_num * .04;
END IF;
DBMS_OUTPUT.PUT_LINE('State = '||lv_state_txt);
DBMS_OUTPUT.PUT_LINE('Subtotal = '||lv_sub_num);
DBMS_OUTPUT.PUT_LINE('Tax amount = '||lv_tax_num);
UPDATE bb_basket
SET tax = lv_tax_num
WHERE idbasket = lv_bask_num;
COMMIT;
END;
anonymous block completed
State = VA
Subtotal = 20
Tax amount = 1.2
Chall_3_5
DECLARE
lv_sub_numbb_basket.subtotal%TYPE;
lv_state_txtbb_basket.shipstate%TYPE;
lv_tax_num NUMBER(4,2) := 0;
lv_bask_num NUMBER(3) := 13;
BEGIN
SELECT subtotal, shipstate
INTO lv_sub_num, lv_state_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
lv_state_txt := 'ME';
IF lv_state_txt = 'VA' THEN
lv_tax_num := lv_sub_num * .06;
ELSIF lv_state_txt = 'ME' THEN
lv_tax_num := lv_sub_num * .05;
ELSIF lv_state_txt = 'NY' THEN
lv_tax_num := lv_sub_num * .07;
ELSE
lv_tax_num := lv_sub_num * .04;
END IF;
DBMS_OUTPUT.PUT_LINE('State = '||lv_state_txt);
DBMS_OUTPUT.PUT_LINE('Subtotal = '||lv_sub_num);
DBMS_OUTPUT.PUT_LINE('Tax amount = '||lv_tax_num);
UPDATE bb_basket
SET tax = lv_tax_num
WHERE idbasket = lv_bask_num;
COMMIT;
END;
State =
Subtotal = 20
Tax amount = .8
DECLARE
lv_sub_numbb_basket.subtotal%TYPE;
lv_state_txtbb_basket.shipstate%TYPE;
lv_tax_num NUMBER(4,2) := 0;
lv_bask_num NUMBER(3) := 13;
BEGIN
SELECT subtotal, shipstate
INTO lv_sub_num, lv_state_txt
FROM bb_basket
WHERE idbasket = lv_bask_num;
lv_state_txt := 'ME'; -- Added this line
IF lv_state_txt = 'VA' THEN
lv_tax_num := lv_sub_num * .06;
ELSIF lv_state_txt = 'ME' THEN
lv_tax_num := lv_sub_num * .05;
ELSIF lv_state_txt = 'NY' THEN
lv_tax_num := lv_sub_num * .07;
ELSE
lv_tax_num := lv_sub_num * .04;
END IF;
DBMS_OUTPUT.PUT_LINE('State = '||lv_state_txt);
DBMS_OUTPUT.PUT_LINE('Subtotal = '||lv_sub_num);
DBMS_OUTPUT.PUT_LINE('Tax amount = '||lv_tax_num);
UPDATE bb_basket
SET tax = lv_tax_num
WHERE idbasket = lv_bask_num;
COMMIT;
END;
State = ME
Subtotal = 20
Tax amount = 1
Chall_3_6 --- Run this three separate times
DECLARE
lv_first_txt VARCHAR2(15) := 'Jeffrey';
lv_last_txt VARCHAR2(20) := 'Brand';
lv_email_txt VARCHAR2(25) := '';
BEGIN
INSERT INTO bb_shopper (idshopper, firstname, lastname, email)
VALUES (bb_shopper_seq.NEXTVAL,lv_first_txt,lv_last_txt,lv_email_txt);
COMMIT;
END;
SELECT idshopper, firstname, lastname, email
FROM bb_shopper
WHERE lastname = 'Brand';
DECLARE
lv_id_num NUMBER(3) := 30;
BEGIN
DELETE FROM bb_shopper
WHERE idshopper = lv_id_num;
COMMIT;
END;
======
Show the sequence number
SQL> select bb_shopper_seq.NEXTVAL from dual;
Chall_3-7
DECLARE
lv_card#_txt VARCHAR2(20);
lv_cardtype_txt CHAR(1);
TYPE type_cardIS RECORD (
card# bb_basket.cardnumber%TYPE,
type bb_basket.cardtype%TYPE);
rec_cardtype_card;
lv_bask_num NUMBER(3) := 10;
BEGIN
SELECT cardnumber, cardtype
INTO rec_card
FROM bb_basket
WHERE idbasket = lv_bask_num;
DBMS_OUTPUT.PUT_LINE('Card #: '||rec_card.card#);
DBMS_OUTPUT.PUT_LINE('Card Type: '||rec_card.type);
END;
DECLARE
TYPE type_basketIS RECORD (
basket bb_basket.idBasket%TYPE,
created bb_basket.dtcreated%TYPE,
qtybb_basket.quantity%TYPE,
sub bb_basket.subtotal%TYPE);
rec_baskettype_basket;
lv_days_num NUMBER(3);
lv_shopper_num NUMBER(3) := 25;
BEGIN
SELECT idBasket, dtcreated, quantity, subtotal
INTO rec_basket
FROM bb_basket
WHERE idShopper = lv_shopper_num
AND orderplaced = 0;
lv_days_num := TO_DATE('02/28/12','mm/dd/yy') - rec_basket.created;
DBMS_OUTPUT.PUT_LINE(rec_basket.basket || ' * ' || rec_basket.created || ' * ' ||
rec_basket.qty || ' * ' || rec_basket.sub || ' * ' || lv_days_num);
END;
Output: 12 * 19-FEB-12 * 7 * 72.4 * 9
Chall_3_8
DECLARE
rec_baskitembb_basketitem%ROWTYPE;
lv_item_numbb_basketitem.idbasketitem%TYPE := 15;
BEGIN
SELECT *
INTO rec_baskitem
FROM bb_basketitem
WHERE idbasketitem = lv_item_num;
DBMS_OUTPUT.PUT_LINE(rec_baskitem.idproduct);
DBMS_OUTPUT.PUT_LINE(rec_baskitem.price);
END;
SQL> descbb_basketitem;
Name Null? Type
------
IDBASKETITEM NOT NULL NUMBER(2)
IDPRODUCT NUMBER(2)
PRICE NUMBER(6,2)
QUANTITY NUMBER(2)
IDBASKET NUMBER(5)
OPTION1 NUMBER(2)
OPTION2 NUMBER(2)
SQL>
Ibtable03
DECLARE
TYPE type_roast IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
tbl_roasttype_roast;
lv_tot_num NUMBER := 0;
lv_cnt_num NUMBER := 0;
lv_avg_num NUMBER;
lv_samp1_num NUMBER(5,2) := 6.22;
lv_samp2_num NUMBER(5,2) := 6.13;
lv_samp3_num NUMBER(5,2) := 6.27;
lv_samp4_num NUMBER(5,2) := 6.16;
lv_samp5_num NUMBER(5,2);
BEGIN
tbl_roast(1) := lv_samp1_num;
tbl_roast(2) := lv_samp2_num;
tbl_roast(3) := lv_samp3_num;
tbl_roast(4) := lv_samp4_num;
tbl_roast(5) := lv_samp5_num;
FOR i IN 1..tbl_roast.COUNT LOOP
IF tbl_roast(i) IS NOT NULL THEN
lv_tot_num := lv_tot_num + tbl_roast(i);
lv_cnt_num := lv_cnt_num + 1;
END IF;
END LOOP;
lv_avg_num := lv_tot_num / lv_cnt_num;
DBMS_OUTPUT.PUT_LINE(lv_tot_num);
DBMS_OUTPUT.PUT_LINE(lv_cnt_num);
DBMS_OUTPUT.PUT_LINE(tbl_roast.COUNT);
DBMS_OUTPUT.PUT_LINE(lv_avg_num);
END;

Ans:

anonymous block completed

24.78

4

5

6.195

Assignment03-01
DECLARE
lv_ship_datebb_basketstatus.dtstage%TYPE;
lv_shipper_txtbb_basketstatus.shipper%TYPE;
lv_ship_numbb_basketstatus.shippingnum%TYPE;
lv_bask_numbb_basketstatus.idbasket%TYPE := 3;
BEGIN
SELECT dtstage, shipper, shippingnum
INTO lv_ship_date, lv_shipper_txt, lv_ship_num
FROM bb_basketstatus
WHERE idbasket = lv_bask_num
AND idstage = 5;
DBMS_OUTPUT.PUT_LINE('Date Shipped: '||lv_ship_date);
DBMS_OUTPUT.PUT_LINE('Shipper: '||lv_shipper_txt);
DBMS_OUTPUT.PUT_LINE('Shipping #: '||lv_ship_num);
END;
Date Shipped: 25-JAN-12
Shipper: UPS
Shipping #: ZW845584GD89H569
Assignment03-02
DECLARE
rec_shipbb_basketstatus%ROWTYPE;
lv_bask_numbb_basketstatus.idbasket%TYPE := 3;
BEGIN
SELECT *
INTO rec_ship
FROM bb_basketstatus
WHERE idbasket = lv_bask_num
AND idstage = 5;
DBMS_OUTPUT.PUT_LINE('Date Shipped: '||rec_ship.dtstage);
DBMS_OUTPUT.PUT_LINE('Shipper: '||rec_ship.shipper);
DBMS_OUTPUT.PUT_LINE('Shipping #: '||rec_ship.shippingnum);
DBMS_OUTPUT.PUT_LINE('Notes: '||rec_ship.notes);
END;
Date Shipped: 25-JAN-12
Shipper: UPS
Shipping #: ZW845584GD89H569
Notes: Customer called to confirm shipment
Assignment03-02 – no idea!!
DECLARE
lv_total_num NUMBER(6,2);
lv_rating_txt VARCHAR2(4);
lv_shop_numbb_basket.idshopper%TYPE := 22;
BEGIN
SELECT SUM(total)
FROM bb_basket
WHERE idShopper =
AND orderplaced = 1
GROUP BY idshopper;
IF lv_total_num > 200 THEN
END IF;
DBMS_OUTPUT.PUT_LINE('Shopper '||:g_shopper||' is rated '||lv_rating_txt);
END;

1