Yurttas/PL/DBL/oracle/F/02/SPJ/Shipments-def.sp
Jump to navigation
Jump to search
1/*
2REM
3REM Shipments-def.sp
4REM
5*/
6
7DECLARE
8
9 cursor_handle INTEGER;
10
11 parts_created INTEGER;
12
13BEGIN
14
15 cursor_handle :=DBMS_SQL.OPEN_CURSOR;
16
17 DBMS_SQL.PARSE(cursor_handle,
18 'CREATE TABLE Shipments(sn VARCHAR2(3) NOT NULL,'||
19 'pn VARCHAR2(3) NOT NULL,'||
20 'jn VARCHAR2(3) NOT NULL,'||
21 'quantity INTEGER,'||
22 'PRIMARY KEY(sn,pn,jn),'||
23 'FOREIGN KEY(sn) REFERENCES Suppliers(SN),'||
24 'FOREIGN KEY(pn) REFERENCES Parts(pn),'||
25 'FOREIGN KEY(jn) REFERENCES Projects(jn))',
26 DBMS_SQL.V7);
27
28 parts_created := DBMS_SQL.EXECUTE(cursor_handle);
29
30 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
31
32 DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' created Successfully');
33
34EXCEPTION
35 WHEN OTHERS THEN
36 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
37 DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' cannot be created');
38 DBMS_OUTPUT.PUT_LINE('Table ''Shipments'' might be already existing');
39
40END;
41/