number(10), warehouseno char(2), shipdate date);
Table created.
CONSTRAINTS ADDED TO TABLES:
SQL> alter table corder10 add constraint fk_cust2 foreign key(custno) references customer10(custno);
Table altered.
SQL> alter table orderitem10 add constraint fk1_ord foreign key(orderno) references corder10(orderno);
Table altered.
SQL> alter table orderitem10 add constraint fk3_itm foreign key(itemno) references item10(itemno);
Table altered.
TABLE DESCRIPTIONS:
SQL> desc customer10;
Name Null? Type
------------------ ---------------- -----------------------------------
CUSTNO NOT NULL NUMBER(10)
CNAME NOT NULL VARCHAR2(25)
CITY NOT NULL VARCHAR2(10)
SQL> desc corder10;
Name Null? Type
------------------------ ------------------ ----------------------------
ORDERNO NOT NULL NUMBER(10)
ORDERDATE NOT NULL DATE
CUSTNO NUMBER(10)
ORDMAT NUMBER(8,2)
SQL> desc orderitem10;
Name Null? Type
------------------ ---------------- ----------------------------
ORDERNO NOT NULL NUMBER(10)
ITEMNO NOT NULL NUMBER(10)
QTY NOT NULL NUMBER(10)
SQL> desc item10;
Name Null? Type
-------------------- ---------------- ----------------------------
ITEMNO NOT NULL NUMBER(10)
UNITPRICE NUMBER(8,2)
SQL> desc shipment10;
Name Null? Type
------------------------ -------- ------------------------
ORDERNO NUMBER(10)
WAREHOUSENO CHAR(2)
SHIPDATE DATE
INSERTING VALUES IN CUSTOMER10 TABLE:
SQL> insert into customer10 values(1001, 'roots', 'coimbatore');
1 row created.
SQL> insert into customer10 values(1002, 'pricol', 'erode');
1 row created.
SQL> insert into customer10 values(1003, 'lmw', 'chennai');
1 row created.
INSERTING VALUES IN CORDER10 TABLE:
SQL> insert into corder10 values(1, '15-jan-1999', 1001, 1500.75);
1 row created.
SQL> insert into corder10 values(2, '5-july-1998', 1002, 1750.23);
1 row created.
SQL> insert into corder10 values(4, '20-dec-1999', 1002, 359.75);
1 row created.
INSERTING VALUES IN ITEM10 TABLE:
SQL> insert into item10(itemno, unitprice)values(501,400.25);
1 row created.
SQL> insert into item10(itemno, unitprice)values(205,220.75);
1 row created.
SQL> insert into item10(itemno, unitprice)values(503,4550.75);
1 row created.
SQL> insert into item10(itemno, unitprice)values(505,100.20);
1 row created.
INSERTING VALUES IN ORDERITEM10 TABLE:
SQL> insert into orderitem10 values(1, 501, 55);
1 row created.
SQL> insert into orderitem10 values(2, 205, 50);
1 row created.
SQL> insert into orderitem10 values(4, 501, 78);
1 row created.
INSERTING VALUES IN SHIPMENT10 TABLE:
SQL> insert into shipment10 values(1, 'w2', '25-mar-1995');
1 row created.
SQL> insert into shipment10 values(2, 'w2', '1-jan-1998');
1 row created.
SQL> insert into shipment10 values(3, 'w2', '20-sep-2000');
1 row created.
SQL> insert into shipment10 values(4, 'w2', '16-feb-2005');
1 row created.
SQL> insert into shipment10 values(5, 'w2', '15-aug-1998');
1 row created.
DISPLAYING TABLE CONTENTS:
SQL> select * from customer10;
CUSTNO CNAME CITY
----------- ----------------- --------------------
1001 roots coimbatore
1002 pricol erode
1003 lmw chennai
SQL> select * from corder10;
ORDERNO ORDERDATE CUSTNO ORDMAT
------------- ------------------ ------------- --------------
1 15-JAN-99 1001 1500.75
2 05-JUL-98 1002 1750.23
4 20-DEC-99 1002 359.75
SQL> select * from orderitem10;
ORDERNO ITEMNO QTY
-------------- --------------- ----------
1 501 55
2 205 50
4 501 78
SQL> select * from item10;
ITEMNO UNITPRICE
------------ ---------------
501 400.25
205 220.75
503 4550.75
505 100.2
SQL> select * from shipment10;
ORDERNO WA SHIPDATE
------------- ----- -------------------
1 w2 25-MAR-95
2 w2 01-JAN-98
3 w2 20-SEP-00
4 w2 16-FEB-05
5 w2 15-AUG-98
WRITE A PL/SQL PACKAGE WITH
i. A PROCEDURE TO UPDATE THE DETAILS INTO THE ITEM10 TABLE.
ii. A FUNCTION WHICH TAKES ITEMNO AS INPUT AND RETURNS THE NUMBER OF
ORDERS FOR THAT ITEM.
SQL> create or replace package ex2_pkg
2 as procedure upd_itms(ino number, prc number, ono number);
3 function get_orders(ino number)return number; end ex2_pkg;
4 /
Package created.
SQL> create or replace package body ex2_pkg as
2 procedure upd_itms(ino number, prc number, ono number) is
3 begin update item10 set itemno=ino, unitprice=prc
4 where itemno=ono;
5 end upd_itms;
6 function get_orders(ino number) return number is
7 ct number;
8 begin
9 select count(*) into ct
10 from orderitem10 where itemno=ino;
11 return ct;
12 end get_orders;
13 end ex2_pkg;
14 /
Package body created.
BEFORE EXECUTION:
SQL> select * from item10;
ITEMNO UNITPRICE
------------ ---------------
501 400.25
205 220.75
503 4550.75
505 100.2
PACKAGE EXECUTION:
SQL> begin ex2_pkg.upd_itms(501, 175.97, 501);end;
2 /
PL/SQL procedure successfully completed.
AFTER PACKAGE EXECUTION:
SQL> select * from item10;
ITEMNO UNITPRICE
------------ ---------------
501 175.97
205 220.75
503 4550.75
505 100.2
SQL> declare
2 res number;
3 begin
4 res:=ex2_pkg.get_orders(501);
5 dbms_output.put_line('The number of orders for '
6 ||'501,'||'is'||res); end;
7 /
The number of orders for 501, is2
PL/SQL procedure successfully completed.
Comments