Oracle-PLSQL-银行管理系统简单实现.
时间: 2023-10-30 14:07:51 浏览: 158
log4oracle-plsql:Oracle PLSQL 的日志记录框架
银行管理系统是一个非常复杂的系统,由于篇幅有限,我只能提供一个简单的实现,基于Oracle-PLSQL语言。我们将实现以下功能:
1. 用户账户管理:包括开户、销户、查询、存款、取款等操作。
2. 账户转账:允许用户在不同账户之间进行转账操作。
3. 查询交易记录:用户可以查询历史交易记录。
下面是实现步骤:
1. 创建表格:
我们需要创建以下表格:
- 用户信息表(包括账户信息)
- 交易记录表
2. 编写存储过程:
我们需要编写一系列存储过程来实现用户账户管理、账户转账和查询交易记录。以下是一些基本存储过程的示例:
- 开户:
```
CREATE PROCEDURE create_account(
p_name VARCHAR2,
p_balance NUMBER,
p_branch VARCHAR2
) AS
BEGIN
INSERT INTO account_info(name, balance, branch)
VALUES(p_name, p_balance, p_branch);
END;
```
- 销户:
```
CREATE PROCEDURE delete_account(
p_account_no VARCHAR2
) AS
BEGIN
DELETE FROM account_info WHERE account_no = p_account_no;
DELETE FROM transaction_history WHERE account_no = p_account_no;
END;
```
- 查询余额:
```
CREATE PROCEDURE check_balance(
p_account_no VARCHAR2,
p_balance OUT NUMBER
) AS
BEGIN
SELECT balance INTO p_balance FROM account_info WHERE account_no = p_account_no;
END;
```
- 存款:
```
CREATE PROCEDURE deposit(
p_account_no VARCHAR2,
p_amount NUMBER
) AS
BEGIN
UPDATE account_info SET balance = balance + p_amount WHERE account_no = p_account_no;
INSERT INTO transaction_history(account_no, transaction_type, amount)
VALUES(p_account_no, 'DEPOSIT', p_amount);
END;
```
- 取款:
```
CREATE PROCEDURE withdraw(
p_account_no VARCHAR2,
p_amount NUMBER
) AS
BEGIN
UPDATE account_info SET balance = balance - p_amount WHERE account_no = p_account_no;
INSERT INTO transaction_history(account_no, transaction_type, amount)
VALUES(p_account_no, 'WITHDRAW', p_amount);
END;
```
- 转账:
```
CREATE PROCEDURE transfer(
p_sender_account_no VARCHAR2,
p_receiver_account_no VARCHAR2,
p_amount NUMBER
) AS
BEGIN
UPDATE account_info SET balance = balance - p_amount WHERE account_no = p_sender_account_no;
UPDATE account_info SET balance = balance + p_amount WHERE account_no = p_receiver_account_no;
INSERT INTO transaction_history(account_no, transaction_type, amount)
VALUES(p_sender_account_no, 'TRANSFER', -p_amount);
INSERT INTO transaction_history(account_no, transaction_type, amount)
VALUES(p_receiver_account_no, 'TRANSFER', p_amount);
END;
```
- 查询交易记录:
```
CREATE PROCEDURE check_transaction_history(
p_account_no VARCHAR2
) AS
BEGIN
SELECT * FROM transaction_history WHERE account_no = p_account_no;
END;
```
3. 测试:
我们可以使用以下代码来测试我们的系统:
```
DECLARE
v_balance NUMBER;
BEGIN
-- 创建账户
create_account('Alice', 1000, 'Beijing');
create_account('Bob', 2000, 'Shanghai');
-- 查询余额
check_balance('1001', v_balance);
DBMS_OUTPUT.PUT_LINE('Alice balance: ' || v_balance);
check_balance('1002', v_balance);
DBMS_OUTPUT.PUT_LINE('Bob balance: ' || v_balance);
-- 存款
deposit('1001', 500);
-- 取款
withdraw('1002', 1000);
-- 转账
transfer('1001', '1002', 300);
-- 查询交易记录
check_transaction_history('1001');
check_transaction_history('1002');
-- 销户
delete_account('1001');
delete_account('1002');
END;
```
这只是一个简单的实现,真正的银行管理系统要比这个复杂得多。但这个例子应该可以帮助你了解如何使用Oracle-PLSQL来实现银行管理系统。
阅读全文