股票交易系統(tǒng)需求分析

1、股票管理系統(tǒng)需求分析摘要 本系統(tǒng)為股票經(jīng)紀(jì)人（管理人員）提供交易平臺(tái)，其中包括提供給經(jīng)濟(jì)人的各基本功能，如：經(jīng)紀(jì)人登錄，注冊(cè)，買賣股票，增加，刪除，更新，查詢股民，查詢股票信息。提供給政府部門的功能有增加股票的功能。開發(fā)語(yǔ)言：JAVA數(shù)據(jù)庫(kù)：ORACLE開發(fā)工具：ECLIPSE開發(fā)環(huán)境：JDK1.5一、 功能介紹1、 交易員登陸注冊(cè)功能A、交易員登陸功能：管理者通過輸入正確的ID和密碼進(jìn)入后臺(tái)管理系統(tǒng)。如果密碼或者ID錯(cuò)誤則給出應(yīng)用的錯(cuò)誤提示。當(dāng)管理者進(jìn)入系統(tǒng)后，進(jìn)入股票交易界面。B、交易員注冊(cè)功能：交易員通過鍵入個(gè)人信息可以注冊(cè)。如果注冊(cè)的用戶已經(jīng)存在，則給出注冊(cè)失?。ㄓ脩舸嬖冢┨崾?；若輸入

2、的信息不全，則給出注冊(cè)失?。ㄐ畔⒉蝗┑奶崾?、用戶管理功能：A、添加用戶信息：若用戶存在，則顯示（用戶存在）信息，若不存在，則填加成功。B、刪除用戶信息：若用戶不存在，則無法刪除，若存在，則刪除成功。C、修改用戶信息：改變?cè)杏脩粜畔?。D、顯示用戶信息：個(gè)人信息和所持有的股票信息。3、股票信息顯示功能：A、顯示股票的信息;B、添加股票的信息;C、更改股票的信息:D、刪除股票的信息;E、顯示股票交易量功能4、用戶股票交易功能 二、設(shè)計(jì)表空間名stock_exchange文件名STOCK_EXCHANGE.ora路徑H:stockexchangesystem空間大小50M用戶名：stock密 碼

3、：stock21表設(shè)計(jì)股東表 common_stockholder_table 普通股股東股票表stock_table股民表preferred_stockholder_table 優(yōu)先股股東交易表 stockexchange_table股票經(jīng)紀(jì)人stockbroker_table1、 COMMON_STOCKHOLDER_TABLE 普通股股東表CSH_ID VARCHAR2(10)自增列主鍵COMMON_STOCKHOLDER_NOVARCHAR2(10)股東編號(hào)COMMON_STOCKHOLDER_NAMEVARCHAR2(10)股東姓名COMMON_STOCKHOLDER_JOINTIM

4、EVARCHAR2(10)入股時(shí)間COMMON_STOCKHOLDER_STOCK_NOVARCHAR2(10)股票編號(hào) COMMON_STOCKHOLDER_HOLDCOUNTNUMBER持股數(shù)量COMMON_STOCKHOLDER_REMARKVARCHAR2(500)備注說明2、 STOCK_TABLE股票表S_IDVARCHAR2(10)自增列主鍵STOCK_NAMEVARCHAR2(10)股票名STOCK_NOVARCHAR2(10)股票編號(hào)STOCK_ISSUE_TIMEVARCHAR2(10)發(fā)行時(shí)間VARCHAR2(10)STOCK_ISSUE_CIRCULATIONNUMBE

5、R發(fā)行量STOCK_ISSUE_PRICENUMBER發(fā)行價(jià)VARCHAR2(10)STOCK_PARVALUENUMBER股票面值,股面價(jià)格VARCHAR2(10)STOCK_DIVIDENTNUMBER股息VARCHAR2(10)STOCK_ISSUERVARCHAR2(10)發(fā)行人VARCHAR2(10)STOCK_BOARDVARCHAR2(10)所屬板塊VARCHAR2(10)STOCK_REMARKVARCHAR2(10)備注說明VARCHAR2(50)3、 PREFERRED_STOCKHOLDER_TABLE 優(yōu)先股股東表PSH_ID VARCHAR2(10)自增列PREFER

6、RED _STOCKHOLDER_NOVARCHAR2(10)股東編號(hào)PREFERRED _STOCKHOLDER_NAMEVARCHAR2(10)股東姓名PREFERRED _STOCKHOLDER_MONEYVARCHAR2(10)開戶金額PREFERRED _STOCKHOLDER_JOINTIMEVARCHAR2(10)開戶時(shí)間PREFERRED _STOCKHOLDER_BNOVARCHAR2(10)經(jīng)紀(jì)人編號(hào)OBJECT< PREFERRED _STOCKHOLDER>OBJECT嵌套持股表PREFERRED _STOCKHOLDER_REMARKVARCHAR2(50

7、)備注說明4、 STOCK_EXCHANGE_TABLE 股票交易表SE_ID （PRIMARY KEY NOT NULL）VARCHAR2(10)自增列主鍵STOCK_EXCHANGE_STOCKNOVARCHAR2(10)股票編號(hào)STOCK_EXCHANGE_SHNOVARCHAR2(10)股民編號(hào)STOCK_EXCHANGE_HANDNUMBER交易手?jǐn)?shù)，每手100股STOCK_EXCHANGE_IOVARCHAR2(10)買入或賣出STOCK_EXCHANGE_PRICEVARCHAR2(10)交易價(jià)STOCK_EXCHANGE_TIMEVARCHAR2(20)交易時(shí)間STOCK_EX

8、CHANGE_STOCKBROKERVARCHAR2(10)經(jīng)紀(jì)人STOCK_EXCHANGE_REMARKVARCHAR2(50)備注說明5、STOCK_BROKER_TABLE 經(jīng)紀(jì)人表SB_IDVARCHAR2(10)自增列主鍵STOCK_BROKER_TRUENAMEVARCHAR2(10)經(jīng)紀(jì)人姓名STOCK_BROKER_NOVARCHAR2(10)經(jīng)紀(jì)人編號(hào)STOCK_BROKER_USERNAMEVARCHAR2(10)經(jīng)紀(jì)人用戶名STOCK_BROKER_PASSWORDVARCHAR2(10)經(jīng)紀(jì)人密碼STOCK_BROKER_TIMEVARCHAR2(10)注冊(cè)時(shí)間STO

9、CK_BORKER_REMARKVARCHAR2(50)經(jīng)紀(jì)人備注創(chuàng)建表空間：CREATE TABLESPACE "STOCK_EXCHANGE" LOGGING DATAFILE ' D:stocksysemSTOCK_EXCHANGE.ora' SIZE 50MEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 創(chuàng)建用戶：create user stock identified by stock default tablespace stock_exchange; 授權(quán)用戶GRANT CONNECT

10、,RESOURCE TO STOCK;創(chuàng)建表1、COMMON_STOCKHOLDER_TABLECREATE TABLE COMMON_STOCKHOLDER_TABLE (CSH_ID VARCHAR2(10) PRIMARY KEY NOT NULL, COMMON_STOCKHOLDER_NO VARCHAR2(10) NOT NULL, COMMON_STOCKHOLDER_NAME VARCHAR2(20) NOT NULL, COMMON_STOCKHOLDER_JOINTIME VARCHAR2(20) NOT NULL, COMMON_STOCKHOLDER_STOCK_NO

11、VARCHAR2(10) NOT NULL, COMMON_STOCKHOLDER_HOLDCOUNT NUMBER NOT NULL, COMMON_STOCKHOLDER_REMARK VARCHAR2(200) NULL) 2、STOCK_TABLE股票表CREATE TABLE STOCK_TABLE(S_ID VARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_NAME VARCHAR2(10) NOT NULL, STOCK_NO VARCHAR2(10) NOT NULL, STOCK_ISSUE_TIME VARCHAR2(20) NOT NULL

12、, STOCK_ISSUE_CIRCULATION NUMBER NOT NULL, STOCK_ISSUE_PRICE NUMBER NOT NULL, STOCK_PARVALUE NUMBER NOT NULL, STOCK_DIVIDENT NUMBER NOT NULL, STOCK_ISSUER VARCHAR2(20) NOT NULL, STOCK_BOARD VARCHAR2(10) NOT NULL, STOCK_REMARK VARCHAR2(200) NULL);3、 PREFERRED_STOCKHOLDER_TABLE股民表(本表嵌套表以說明某股民持有多種股票) 創(chuàng)

13、建基類型數(shù)據(jù)對(duì)象，說明股民持有的股票編號(hào)（STOCK_NO）和數(shù)量（STOCK_COUNT）CREATE OR REPLACE TYPE STOCKHOLERTYPE IS OBJECT( STOCK_NO VARCHAR2(10), STOCK_COUNT NUMBER)創(chuàng)建嵌套表類型，該表對(duì)象將嵌套于PREFERRED_STOCKHOLDER_TABLE表的STOCK字段CREATE OR REPLACE TYPE STOCKHOLDERTABLE AS TABLE OF STOCKHOLERTYPE創(chuàng)建嵌套表CREATE TABLE PREFERRED_STOCKHOLDER_TABLE

14、(PSH_ID VARCHAR2(10) PRIMARY KEY NOT NULL, PREFERRED_STOCKHOLDER_NO VARCHAR2(10) NOT NULL, PREFERRED_STOCKHOLDER_NAME VARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_MONEY VARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_JOINTIME VARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_BNO VARCHAR2(20) NOT NULL, STOCK

15、 STOCKHOLDERTABLE) NESTED TABLE STOCK STORE AS STOCKTABLE;<將對(duì)象型轉(zhuǎn)換為關(guān)系型查看SELECT * FROM TABLE(SELECT T.STOCK FROM PREFERRED_STOCKHOLDER_TABLE T); >4、 STOCK_EXCHANGE_TABLE股票交易表，紀(jì)錄交易信息CREATE TABLE STOCK_EXCHANGE_TABLE(SE_ID VARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_EXCHANGE_STOCKNO VARCHAR2(10) NOT

16、NULL, STOCK_EXCHANGE_SHNO VARCHAR2(10) NOT NULL, STOCK_EXCHANGE_BHNO NUMBER NOT NULL, STOCK_EXCHANGE_PRICE VARCHAR2(10) NOT NULL, STOCK_EXCHANGE_TIME VARCHAR2(20) NOT NULL, STOCK_EXCHANGE_STOCKBROKER VARCHAR2(20) NOT NULL, STOCK_EXCHANGE_REMARK VARCHAR2(200) NULL);5、STOCK_BROKER_TABLE經(jīng)紀(jì)人表，及管理員CREATE

17、 TABLE STOCK_BROKER_TABLE(SB_ID VARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_BROKER_TRUENAME VARCHAR2(20) NOT NULL,STOCK_BROKER_NO VARCHAR2(20) NOT NULL, STOCK_BROKER_USERNAME VARCHAR2(20) NOT NULL, STOCK_BROKER_PASSWORD VARCHAR2(10) NOT NULL, STOCK_BROKER_TIME VARCHAR2(20),STOCK_BORKER_REMARK VARCHAR2(

18、200) NULL);Pl/sql設(shè)計(jì)一、各表主鍵自增設(shè)計(jì)1、創(chuàng)建優(yōu)先股東表序列STOCKHOLDERSQUEENCREATE SEQUENCE STOCKHOLDERSQUEENINCREMENT BY 1START WITH 1創(chuàng)建優(yōu)先股東表主鍵自增觸發(fā)器PREFERRED_STOCKHOLDERCREATE OR REPLACE TRIGGERPREFERRED_STOCKHOLDERBEFORE INSERT or update or delete ON PREFERRED_STOCKHOLDER_TABLEFOR EACH ROWBEGIN if inserting then SEL

19、ECT STOCKHOLDERSQUEEN.NEXTVAL INTO :NEW.PSH_ID FROM DUAL; end if;END;2、創(chuàng)建股票表序列STOCK_TABLE_SEQUEENCREATE SEQUENCE STOCK_TABLE_SEQUEENINCREMENT BY 1START WITH 1創(chuàng)建股票表主鍵自增觸發(fā)器STOCK_TABLE_TRIGGERCREATE OR REPLACE TRIGGER STOCK_TABLE_TRIGGERBEFORE INSERT ON STOCK_TABLEFOR EACH ROWBEGIN SELECT STOCK_TABLE_S

20、EQUEEN.NEXTVAL INTO :NEW.S_ID FROM DUAL;END;3、交易表序列CREATE SEQUENCE EXCHANGESEQUENCEINCREMENT BY 1START WITH 1交易表自增觸發(fā)器CREATE OR REPLACE TRIGGER EXCHANGE_TRIGGERBEFORE INSERT ON STOCK_EXCHANGE_TABLEFOR EACH ROWBEGIN SELECT EXCHANGESEQUENCE.NEXTVAL INTO :NEW.SE_ID FROM DUAL;END;4、經(jīng)紀(jì)人表序列CREATE SEQUENCE

21、STOCKBROKERSEQUENCEINCREMENT BY 1START WITH 1經(jīng)紀(jì)人表主鍵自增觸發(fā)器CREATE OR REPLACE TRIGGER STOCKBROKER_TRIGGERBEFORE INSERT ON STOCK_BROKER_TABLE FOR EACH ROWBEGIN SELECT STOCKBROKERSEQUENCE.NEXTVAL INTO :NEW.SB_ID FROM DUAL;END;二、功能包設(shè)計(jì)1、 增加優(yōu)先股東（開戶）某股東買入賣出股票包說明：本包ADDPREFERRED1函數(shù)實(shí)現(xiàn)增加股民 當(dāng)該編號(hào)的股民存在，則增加失敗 ADDPREF

22、ERRED2函數(shù)實(shí)現(xiàn)股民買賣股票時(shí)所持有股票變動(dòng)情況，當(dāng)買入股票是，COUNT1參數(shù)接受正值，當(dāng)賣出股票時(shí)，COUNT1參數(shù)接受負(fù)數(shù)當(dāng)股民有該股票時(shí)，該股票數(shù)量更新當(dāng)股民沒有該股票時(shí)，向該嵌套表增加該股票信息當(dāng)交易股票時(shí)，該持股人銀行存款余額發(fā)生變化 DELETEPREFERRED函數(shù)實(shí)現(xiàn)刪除某股民，當(dāng)該股民所持股票為空時(shí)，有權(quán)刪除,該股民不存在或還持有股票時(shí)刪除失敗。 UPDATEPREFERRED函數(shù)實(shí)現(xiàn)更新股東部分?jǐn)?shù)據(jù)。聲明CREATE OR REPLACE PACKAGE ADDPREFERRED ISFUNCTION ADDPREFERRED1(PREFERRED_STOCKHOLD

23、ER_NO1 PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_NO%TYPE,PREFERRED_STOCKHOLDER_NAME1 PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_NAME%TYPE, PREFERRED_STOCKHOLDER_MONEY1 PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_MONEY%TYPE,PREFERRED_STOCKHOLDER_BNO1 PREFERRED_STOCKHOLDER_TABLE.

24、PREFERRED_STOCKHOLDER_BNO%TYPE,STOCK1 PREFERRED_STOCKHOLDER_TABLE.STOCK%TYPE) RETURN NUMBER;FUNCTION ADDPREFERRED2(NO VARCHAR2,NUM VARCHAR2,COUNT1 NUMBER, PRICE NUMBER) RETURN NUMBER;FUNCTION DELETEPREFERRED(NO VARCHAR2) RETURN NUMBER;FUNCTION UPDATEPREFERRED(NO VARCHAR2,NAME VARCHAR2,BNO VARCHAR2)

25、RETURN NUMBER;END ADDPREFERRED;主體CREATE OR REPLACE PACKAGE body ADDPREFERRED ISFUNCTION ADDPREFERRED1(PREFERRED_STOCKHOLDER_NO1PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_NO%TYPE,PREFERRED_STOCKHOLDER_NAME1 PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_NAME%TYPE,PREFERRED_STOCKHOLDER_MONEY

26、1 PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_MONEY%TYPE,PREFERRED_STOCKHOLDER_BNO1PREFERRED_STOCKHOLDER_TABLE. PREFERRED_STOCKHOLDER_BNO %TYPE,STOCK1 PREFERRED_STOCKHOLDER_TABLE.STOCK%TYPE)RETURN NUMBERas ICOUNT INT; begin SELECT COUNT(*) INTO ICOUNT FROM PREFERRED_STOCKHOLDER_TABLEWHERE PREF

27、ERRED_STOCKHOLDER_NO = PREFERRED_STOCKHOLDER_NO1; IF ICOUNT = 0 THEN INSERT INTO PREFERRED_STOCKHOLDER_TABLE(PREFERRED_STOCKHOLDER_NO,PREFERRED_STOCKHOLDER_NAME,PREFERRED_STOCKHOLDER_MONEY,PREFERRED_STOCKHOLDER_JOINTIME,PREFERRED_STOCKHOLDER_BNO,STOCK)VALUES(PREFERRED_STOCKHOLDER_NO1,PREFERRED_STOCK

28、HOLDER_NAME1, PREFERRED_STOCKHOLDER_MONEY1,TO_CHAR(SYSDATE), PREFERRED_STOCKHOLDER_BNO1,STOCK1);Commit; return sql%rowcount; ELSE RETURN 0; END IF; end ADDPREFERRED1;FUNCTION ADDPREFERRED2(NO VARCHAR2,NUM VARCHAR2,COUNT1 NUMBER,PRICE NUMBER) RETURN NUMBERasICOUNT INT;COUNT INT;MONEY INT; beginSELECT

29、 COUNT(*) into icount FROM TABLE(select t.stock from PREFERRED_STOCKHOLDER_TABLE t WHERE PREFERRED_STOCKHOLDER_NO = NO) WHERE STOCK_NO = NUM;SELECT PREFERRED_STOCKHOLDER_MONEY INTO MONEY FROM PREFERRED_STOCKHOLDER_TABLE WHERE PREFERRED_STOCKHOLDER_NO = NO;IF MONEY < COUNT1 * PRICE THENRETURN 3;EL

30、SIFICOUNT = 0 THEN INSERT INTO table(select t.stock from PREFERRED_STOCKHOLDER_TABLE t where PREFERRED_STOCKHOLDER_NO = NO) sVALUES(num,count1);UPDATE PREFERRED_STOCKHOLDER_TABLE TSET T. PREFERRED_STOCKHOLDER_MONEY = T. PREFERRED_STOCKHOLDER_MONEY +COUNT1 * PRICE WHERE PREFERRED_STOCKHOLDER_NO = NO;

31、Commit;return 1;ELSE UPDATE table(select t.stock from PREFERRED_STOCKHOLDER_TABLE t where PREFERRED_STOCKHOLDER_NO = NO) SET STOCK_COUNT = STOCK_COUNT + COUNT1 WHERE STOCK_NO = NUM;UPDATE PREFERRED_STOCKHOLDER_TABLE TSET T. PREFERRED_STOCKHOLDER_MONEY = T. PREFERRED_STOCKHOLDER_MONEY +COUNT1 * PRICE

32、 WHERE PREFERRED_STOCKHOLDER_NO = NO;Commit; RETURN 0;END IF;END ADDPREFERRED2;FUNCTION DELETEPREFERRED(NO VARCHAR2) RETURN NUMBERASICOUNT INT;ICOUNT2 INT;BEGINSELECT COUNT(*) INTO ICOUNT FROM TABLE(SELECT T.STOCK FROM PREFERRED_STOCKHOLDER_TABLE T WHERE PREFERRED_STOCKHOLDER_NO = NO);SELECT COUNT(*

33、) INTO ICOUNT2 FROM PREFERRED_STOCKHOLDER_TABLE WHERE PREFERRED_STOCKHOLDER_NO = NO;IF ICOUNT = 1 AND ICOUNT2 > 0 THENDELETE PREFERRED_STOCKHOLDER_TABLE WHERE PREFERRED_STOCKHOLDER_NO = NO;Commit;RETURN 1;ELSERETURN 0;END IF;END DELETEPREFERRED;FUNCTION UPDATEPREFERRED(NO VARCHAR2,NAME VARCHAR2,B

34、NO VARCHAR2) RETURN NUMBERASBEGINUPDATE PREFERRED_STOCKHOLDER_TABLE SET PREFERRED_STOCKHOLDER_NAME=NAME, PREFERRED_STOCKHOLDER_BNO = BNO WHERE PREFERRED_STOCKHOLDER_NO = NO;Commit; Return 1;END UPDATEPREFERRED;END ADDPREFERRED;增加優(yōu)先股東數(shù)據(jù)測(cè)試BEGINdbms_output.put_line(ADDPREFERRED.ADDPREFERRED1('P007&

35、#39;,'1231','2000', 1,STOCKHOLDERTABLE(STOCKHOLERTYPE(11,22);end;更新優(yōu)先股東持股數(shù)據(jù)update PREFERRED_STOCKHOLDER_TABLE t set t.stock = STOCKHOLDERTABLE(STOCKHOLERTYPE('b0010',1000) where PREFERRED_STOCKHOLDER_NAME = 'liujie'刪除股民數(shù)據(jù)BEGINdbms_output.put_line(ADDPREFERRED.DELETEPR

36、EFERRED('P003');end;更新股民數(shù)據(jù)BEGINdbms_output.put_line(ADDPREFERRED. UPDATEPREFERRED('P003',劉杰,12);end;向某股民增加股票數(shù)據(jù)BEGINDBMS_OUTPUT.put_line(ADDPREFERRED.ADDPREFERRED2('P004','77777',2010,100);END;2、 增加刪除顯示更新股票包說明：本包ADDSTOCK函數(shù)實(shí)現(xiàn)股票增加功能，當(dāng)該編號(hào)的股票存在，則增加失敗 DELETESTOCK函數(shù)實(shí)現(xiàn)刪除股票功能

37、SHOWSTOCK函數(shù)實(shí)現(xiàn)顯示所有股票功能 UPDATESTOCK函數(shù)實(shí)現(xiàn)更新股票功能聲明CREATE OR REPLACE PACKAGE STOCK_TABLEPACKAGE ISTYPE SHOWSTOCKCURSOR IS REF CURSOR;FUNCTION ADDSTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER,PRICE NUMBER,PARVALUE NUMBER,DIVIDENT NUMBER,ISSUER VARCHAR2,BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBER;FUNCT

38、ION DELETESTOCK(NO VARCHAR2) RETURN NUMBER;PROCEDURE SHOWSTOCK(SHOWSTOCK IN OUT SHOWSTOCKCURSOR);PROCEDURE SHOWSTOCKDETAIL(SHOWSTOCK IN OUT SHOWSTOCKCURSOR,NAME VARCHAR2,NO VARCHAR2);FUNCTION UPDATESTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER, BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBER;END ST

39、OCK_TABLEPACKAGE;主體CREATE OR REPLACE PACKAGE BODY STOCK_TABLEPACKAGE ISFUNCTION ADDSTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER,PRICE NUMBER,PARVALUE NUMBER,DIVIDENT NUMBER,ISSUER VARCHAR2,BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBERASICOUNT INT;BEGINSELECT COUNT(*) INTO ICOUNT FROM STOCK_TABLE

40、 WHERE STOCK_NO = NO;IF ICOUNT = 0 THENINSERT INTO STOCK_TABLE(STOCK_NAME, STOCK_NO,STOCK_ISSUE_TIME, STOCK_ISSUE_CIRCULATION,STOCK_ISSUE_PRICE,STOCK_PARVALUE,STOCK_DIVIDENT,STOCK_ISSUER,STOCK_BOARD,STOCK_REMARK)VALUES(NAME,NO,TO_CHAR(SYSDATE),CIRCULATION,PRICE,PARVALUE,DIVIDENT,ISSUER,BOARD,REMARK)

41、; Commit;RETURN 1;ELSERETURN 0;END IF;END ADDSTOCK;FUNCTION DELETESTOCK(NO VARCHAR2) RETURN NUMBERASICOUNT NUMBER;BEGINSELECT COUNT(*) INTO ICOUNT FROM STOCK_TABLE WHERE STOCK_NO = NO;IF ICOUNT > 0 THEN DELETE STOCK_TABLE WHERE STOCK_NO = NO;Commit;RETURN 1;ELSERETURN 0;END IF;END DELETESTOCK;PRO

42、CEDURE SHOWSTOCK(SHOWSTOCK IN OUT SHOWSTOCKCURSOR)ASBEGIN OPEN SHOWSTOCK FOR SELECT * FROM showend;END SHOWSTOCK;PROCEDURE SHOWSTOCKDETAIL(SHOWSTOCK IN OUT SHOWSTOCKCURSOR,NAME VARCHAR2,NO VARCHAR2)ASBEGIN OPEN SHOWSTOCK FOR SELECT * FROM STOCK_TABLE WHERE STOCK_NO = NO OR STOCK_NAME = NAME;END SHOW

43、STOCKDETAIL;FUNCTION UPDATESTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER, BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBERASBEGIN UPDATE STOCK_TABLE SET STOCK_NAME=NAME,STOCK_ISSUE_CIRCULATION=CIRCULATION,STOCK_BOARD= BOARD,STOCK_REMARK= REMARK WHERE STOCK_NO = NO;Commit;RETURN 1;END UPDATESTOCK;END

44、 STOCK_TABLEPACKAGE;增加股票測(cè)試begindbms_output.put_line(STOCK_TABLEPACKAGE.ADDSTOCK(2,1,1,1,1,1,1,1,1);end;刪除股票測(cè)試begindbms_output.put_line(STOCK_TABLEPACKAGE.DELETESTOCK(1);end;顯示所有股票信息var a refcursor;exec STOCK_TABLEPACKAGE.SHOWSTOCK(:A);更新某股票信息 begin dbms_output.put_line(STOCK_TABLEPACKAGE.UPDATESTOCK

45、('1','1',1,1,'1'); end;2、交易表增加數(shù)據(jù)包聲明CREATE OR REPLACE PACKAGE STOCK_EXCHANGE_PACKAGE ISFUNCTION ADDSTOCKEXCHANGE(STOCKNO VARCHAR2,SHNO VARCHAR2,BHNO VARCHAR2,PRICE VARCHAR2,STOCKBROKER VARCHAR2,REMARK VARCHAR2)RETURN NUMBER;END STOCK_EXCHANGE_PACKAGE;主體CREATE OR REPLACE PACKAG

46、E body STOCK_EXCHANGE_PACKAGE ISFUNCTION ADDSTOCKEXCHANGE(STOCKNO VARCHAR2,SHNO VARCHAR2,BHNO VARCHAR2,PRICE VARCHAR2,STOCKBROKER VARCHAR2,REMARK VARCHAR2)RETURN NUMBERASBEGIN INSERT INTO STOCK_EXCHANGE_TABLE(STOCK_EXCHANGE_STOCKNO, STOCK_EXCHANGE_SHNO, STOCK_EXCHANGE_BHNO, STOCK_EXCHANGE_PRICE, STO

47、CK_EXCHANGE_TIME, STOCK_EXCHANGE_STOCKBROKER, STOCK_EXCHANGE_REMARK) VALUES(STOCKNO,SHNO,BHNO,PRICE, TO_CHAR (SYSDATE,YYYY-MM-DD HH24:MI:SS),STOCKBROKER,REMARK);RETURN 1; END ADDSTOCKEXCHANGE;END STOCK_EXCHANGE_PACKAGE;增加交易數(shù)據(jù)測(cè)試BEGIN DBMS_OUTPUT.put_line(STOCK_EXCHANGE_PACKAGE.ADDSTOCKEXCHANGE(2,1,1,

48、1,1,1);END;3、 經(jīng)紀(jì)人注冊(cè)表說明：本包ADDBROKER函數(shù)實(shí)現(xiàn)經(jīng)紀(jì)人注冊(cè)，當(dāng)該編號(hào)經(jīng)紀(jì)人存在，并且用戶名存在，則注冊(cè)失敗 LOGINBROKER函數(shù)實(shí)現(xiàn)經(jīng)紀(jì)人登錄，當(dāng)用戶名或密碼不匹配，則登錄失敗聲明CREATE OR REPLACE PACKAGE STOCK_BROKERPACKAGEISFUNCTION ADDBROKER(TRUENAME VARCHAR2,NO VARCHAR2, USERNAME VARCHAR2, PASSWORD VARCHAR2, REMARK VARCHAR2) RETURN NUMBER ;FUNCTION LOGINBROKER(USERN

49、AME VARCHAR2,PASSWORD VARCHAR2) RETURN NUMBER;END STOCK_BROKERPACKAGE;主體CREATE OR REPLACE PACKAGE body STOCK_BROKERPACKAGEISFUNCTION ADDBROKER(TRUENAME VARCHAR2,NO VARCHAR2, USERNAME VARCHAR2, PASSWORD VARCHAR2,REMARK VARCHAR2) RETURN NUMBER asICOUNT NUMBER;BEGINSELECT COUNT(*) INTO ICOUNT FROM STOC

50、K_BROKER_TABLE WHERE STOCK_BROKER_NO = NO OR STOCK_BROKER_USERNAME = USERNAME;IF ICOUNT = 0 THENINSERT INTO STOCK_BROKER_TABLE(STOCK_BROKER_TRUENAME,STOCK_BROKER_NO, STOCK_BROKER_USERNAME, STOCK_BROKER_PASSWORD, STOCK_BROKER_TIME,STOCK_BORKER_REMARK) VALUES(TRUENAME,NO, USERNAME, PASSWORD, TO_CHAR(S

51、YSDATE),REMARK); RETURN 1;ELSERETURN 0;END IF;END ADDBROKER;FUNCTION LOGINBROKER(USERNAME VARCHAR2,PASSWORD VARCHAR2) RETURN NUMBERASICOUNT NUMBER;BEGINSELECT COUNT(*) INTO ICOUNT FROM STOCK_BROKER_TABLE WHERE STOCK_BROKER_USERNAME = USERNAMEAND STOCK_BROKER_PASSWORD = PASSWORD;IF ICOUNT > 0 THEN

52、RETURN 1;ELSE RETURN 0;END IF;END LOGINBROKER;END STOCK_BROKERPACKAGE;4、 顯示股民和相應(yīng)的股票信息說明：本包函數(shù)RETURNSTOCK_PRO實(shí)現(xiàn)現(xiàn)實(shí)某經(jīng)紀(jì)人所有（客戶）股民的信息，有引用游標(biāo)實(shí)現(xiàn)。在股民表中的經(jīng)紀(jì)人字段標(biāo)識(shí)該股民所屬經(jīng)紀(jì)人。聲明CREATE OR REPLACE PACKAGE SHOW IS TYPE RETURNSTOCKHOLDER_CURSOR IS REF CURSOR;TYPE HOLDERSTOCK_CURSOR IS REF CURSOR;PROCEDURE RETURNSTOCK_PRO

53、(SHOWSTOCKER IN OUT RETURNSTOCKHOLDER_CURSOR,BROKERNO PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_BNO%TYPE);PROCEDURE RETURNSTOCK_CURSOR(SHOWSTOCK IN OUT HOLDERSTOCK_CURSOR,HOLDERNO VARCHAR2);END SHOW;主體CREATE OR REPLACE PACKAGE BODY SHOW ISPROCEDURE RETURNSTOCK_PRO(SHOWSTOCKER IN OUT RETURNST

54、OCKHOLDER_CURSOR,BROKERNO PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_BNO%TYPE) ASBEGINOPEN SHOWSTOCKER FOR SELECT * FROM PREFERRED_STOCKHOLDER_TABLEWHERE PREFERRED_STOCKHOLDER_BNO = BROKERNO;END RETURNSTOCK_PRO;PROCEDURE RETURNSTOCK_CURSOR(SHOWSTOCK IN OUT HOLDERSTOCK_CURSOR,HOLDERNO VARCHAR2

55、)ASBEGINOPEN SHOWSTOCK FOR SELECT * FROM TABLE(SELECT T.STOCK FROM PREFERRED_STOCKHOLDER_TABLE T WHERE PREFERRED_STOCKHOLDER_NO = HOLDERNO);END RETURNSTOCK_CURSOR;END SHOW;開盤價(jià)視圖create or replace view showtq asselect r.stock_exchange_stockno 股票編號(hào),min(TO_DATE(r.stock_exchange_time,YYYY-MM-DD HH24:MI:SS) 最后時(shí)間 from stock_exchange_table r