ORACLE1.24 Banking system operations and cursors

CREATE TABLE Bank_user (
card_id varchar2 () primary key, --ID number
User_name VARCHAR2 (+), ---name
Ye number, ---balance (redundant)
Status Number ---State 1 Normal 0 lock
);
drop table Deal_record;
CREATE TABLE Deal_record (
ID number primary Key,
Deal_type number, --1. Save 2. Take the money 3. Transfer 4. Transfer to
card_id varchar2, --trader
Money number,--Transaction amount
dest_card_id varchar2 --turn out the object
);

Select * FROM Bank_user
Select * from Deal_record where card_id= ' 123 '
-- Reasonable redundancy (repetition) is a scientific expression
--Science in Space-time
INSERT into bank_user values (' 123 ' , ' xiaoming ', 100, 1);
INSERT into Bank_user values (' 456 ', ' Little Red ', 300, 1);
--3 pen transaction
1. Xiao Ming Save 2000 yuan
Insert into Deal_record values ("123", 2000,null);
Update bank_user set ye=ye+2000 where card_id= ' 123 ';
Commit;
2. Xiao Ming takes money 300 yuan
INSERT into Deal_record values (2,2, ' 123 ', 300,null);
Update Bank_user set ye=ye-300 where card_id= ' 123 ';
Commit;
3. Xiao Ming turn money to Little Red, 1500 USD
INSERT into Deal_record VALUES (3,3, ' 123 ',, ' 456 ');
INSERT into Deal_record VALUES (bis, ' 456 ', 1500,null);
UPDATE bank_user SET ye=ye-1500 WHERE card_id = ' 123 ';
UPDATE bank_user SET ye=ye+1500 WHERE card_id = ' 456 ';
COMMIT

SELECT * from Deal_record where card_id = ' 456 ';
Select Myseq.nextval from dual
--The stored procedure of saving money
Create or replace procedure P_add
(
USER_ID in Bank_user.card_id%type,
Add_money in Deal_record.money%type
)
As
Begin
INSERT into Deal_record values (myseq.nextval,1,user_id,add_money,null);
Update Bank_user set Ye=ye+add_money where card_id=user_id;
End
--The stored procedure for taking money
Create or replace procedure P_min
(
USER_ID in Bank_user.card_id%type,
Min_money in Deal_record.money%type
)
As
Begin
INSERT into Deal_record values (myseq.nextval,2,user_id,min_money,null);
Update Bank_user set Ye=ye-min_money where card_id=user_id;
End
----
--Transfer of stored procedures
Create or replace procedure P_tran
(
User_id1 in Bank_user.card_id%type ,--turn out people
User_id2 in Bank_user.card_id%type,--Transfer to Person
Money in Deal_record.money%type
)
As
Begin
INSERT into Deal_record VALUES (MYSEQ.NEXTVAL,3,USER_ID1,MONEY,USER_ID2);
INSERT into Deal_record VALUES (myseq.nextval,4,user_id2,money,null);
UPDATE bank_user SET ye=ye-money WHERE card_id = user_id1;
UPDATE bank_user SET ye=ye+money WHERE card_id = User_id2;
End

--PL/SQL's own cursors
--Querying business records
--1. User ID 2. Business type (1. Check all Revenue 2. Query all points 3. All)
USER_ID in Bank_user.card_id%type,--turn out people
Query_type in number

Create or replace procedure P_query
Is
Mydeal Deal_record%rowtype;
Cursor MyCursor is a select * from Deal_record;
Begin
--Open cursor
Open mycursor;
Loop
Fetch mycursor into mydeal;
Exit when Mycursor%notfound;
Dbms_output.put_line (mydeal.card_id| | ' ' | | Mydeal.money);
End Loop;
End

--Cursors called by Java

ORACLE1.24 Banking system operations and cursors