BankingAppScript.SQL

-- This is my testing ground for building my app tables.
CREATE SEQUENCE ACCOUNTKEY
MINVALUE 1
START WITH 1000
INCREMENT BY 1;

CREATE SEQUENCE USERKEY
MINVALUE 1
START WITH 1000
INCREMENT BY 1;

CREATE SEQUENCE ADMINKEY
MINVALUE 1
START WITH 1000
INCREMENT BY 1;

CREATE TABLE ACCOUNTS
( BANK_ACCOUNT_ID NUMBER PRIMARY KEY,
ACCOUNTNUM NUMBER UNIQUE,
BALANCE NUMBER(10,2),
USERID VARCHAR2(21));

ALTER TABLE ACCOUNTS
ADD CONSTRAINT OVERDRAFT
CHECK (BALANCE >= 0);

CREATE TABLE USERS
( USERID NUMBER PRIMARY KEY,
USERNAME VARCHAR2(21),
PASS VARCHAR2(21),
FULLNAME VARCHAR2(41),
SSN NUMBER,
APPROVED NUMBER(1,0));

CREATE TABLE ADMINS (
ADMINID NUMBER PRIMARY KEY,
USERNAME VARCHAR2(21),
PASSWORD VARCHAR2(21) );

--
CREATE OR REPLACE PROCEDURE NEWADMIN(USR VARCHAR2, PW VARCHAR2)
AS
BEGIN
    INSERT INTO ADMINS VALUES (ADMINKEY.NEXTVAL,USR,PW);
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE DELADMIN(USR VARCHAR2, PW VARCHAR2)
AS
BEGIN
    DELETE FROM ADMINS WHERE USERNAME = USR AND PASSWORD = PW;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE NEWUSER(USR VARCHAR2, PW VARCHAR2, REALNAME VARCHAR2, SSN NUMBER)
AS
BEGIN
    INSERT INTO USERS VALUES (USERKEY.NEXTVAL, USR, PW, REALNAME, SSN, 0);
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE APPROVEUSR(USR VARCHAR2)
AS
BEGIN
    UPDATE USERS
    SET APPROVED = 1 WHERE USERNAME = USR;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE EDITUSR(USR VARCHAR2, PW VARCHAR2, NEWNAME VARCHAR2, SSN VARCHAR2, APPROVED NUMBER)
AS
BEGIN
    UPDATE USERS
    SET PASS = PW, FULLNAME = NEWNAME, SSN = SSN, APPROVED = APPROVED WHERE USERNAME = USER;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE DELUSR(USR VARCHAR2)
AS
BEGIN
    DELETE FROM USERS WHERE USERNAME=USR;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE NEWACCOUNT(ACC_NUM NUMBER, USR VARCHAR2)
AS
BEGIN
    INSERT INTO ACCOUNTS VALUES (ACCOUNTKEY.NEXTVAL,ACC_NUM,0.00,USR);
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE WITHDRAW(ACC_NUM NUMBER, AMOUNT NUMBER)
AS
BEGIN
    UPDATE ACCOUNTS
    SET BALANCE = BALANCE - AMOUNT WHERE accountnum = ACC_NUM;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE DEPOSIT(ACC_NUM NUMBER, AMOUNT NUMBER)
AS
BEGIN
    UPDATE ACCOUNTS
    SET BALANCE = BALANCE + AMOUNT WHERE accountnum = ACC_NUM;
    COMMIT;
END;

CREATE OR REPLACE PROCEDURE DELACCOUNT(BAID NUMBER)
AS
BEGIN
    DELETE FROM ACCOUNTS WHERE BANK_ACCOUNT_ID = BAID;
    COMMIT;
END;
EXECUTE DELACCOUNT(1001);
--
commit;