Yurttas/PL/DBL/oracle/F/02/Bank/Borrower-def.sp

 1/*
 2REM
 3REM
 4REM Borrower-def.sp
 5REM
 6REM
 7*/
 8
 9DECLARE
10
11  cursor_handle INTEGER;
12
13  borrower_created INTEGER;
14
15BEGIN
16
17  cursor_handle := DBMS_SQL.OPEN_CURSOR;
18
19  DBMS_SQL.PARSE(cursor_handle,
20                 'CREATE TABLE Borrower(branchname VARCHAR2(16) NOT NULL,         '||
21                                       'loannumber NUMBER(4) NOT NULL,            '||
22                                       'customername VARCHAR2(16) NOT NULL,       '||
23                                       'amount NUMBER(8),                         '||
24                           'PRIMARY KEY(loannumber),                              '||
25                           'FOREIGN KEY(branchname) REFERENCES Branch(branchname),'||
26                           'FOREIGN KEY(customername) REFERENCES Customer(customername))',
27                 DBMS_SQL.V7);
28
29  borrower_created := DBMS_SQL.EXECUTE(cursor_handle);
30
31  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
32
33  DBMS_OUTPUT.PUT_LINE('Table ''Borrower'' created Successfully');
34
35EXCEPTION
36  WHEN OTHERS
37  THEN
38    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
39    DBMS_OUTPUT.PUT_LINE('Table ''Borrower'' cannot be created;');
40    DBMS_OUTPUT.PUT_LINE('Table ''Borrower'' might be already existing.');
41
42END;
43/