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

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