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

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

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

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

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

5、R發(fā)行量STOCK_ISSUE_PRICENUMBER發(fā)行價VARCHAR2(10)STOCK_PARVALUENUMBER股票面值,股面價格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)股東編號PREFERRED _STOCKHOLDER_NAMEVARCHAR2(10)股東姓名PREFERRED _STOCKHOLDER_MONEYVARCHAR2(10)開戶金額PREFERRED _STOCKHOLDER_JOINTIMEVARCHAR2(10)開戶時間PREFERRED _STOCKHOLDER_BNOVARCHAR2(10)經(jīng)紀人編號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)股票編號STOCK_EXCHANGE_SHNOVARCHAR2(10)股民編號STOCK_EXCHANGE_HANDNUMBER交易手數(shù)，每手100股STOCK_EXCHANGE_IOVARCHAR2(10)買入或賣出STOCK_EXCHANGE_PRICEVARCHAR2(10)交易價STOCK_EXCHANGE_TIMEVARCHAR2(20)交易時間STOCK_EX

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

9、CK_BORKER_REMARKVARCHAR2(50)經(jīng)紀人備注創(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; 授權用戶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ù)對象，說明股民持有的股票編號（STOCK_NO）和數(shù)量（STOCK_COUNT）CREATE OR REPLACE TYPE STOCKHOLERTYPE IS OBJECT( STOCK_NO VARCHAR2(10), STOCK_COUNT NUMBER)創(chuàng)建嵌套表類型，該表對象將嵌套于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;<將對象型轉換為關系型查看SELECT * FROM TABLE(SELECT T.STOCK FROM PREFERRED_STOCKHOLDER_TABLE T); >4、 STOCK_EXCHANGE_TABLE股票交易表，紀錄交易信息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)紀人表，及管理員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設計一、各表主鍵自增設計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)紀人表序列CREATE SEQUENCE

21、STOCKBROKERSEQUENCEINCREMENT BY 1START WITH 1經(jīng)紀人表主鍵自增觸發(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;二、功能包設計1、 增加優(yōu)先股東（開戶）某股東買入賣出股票包說明：本包ADDPREFERRED1函數(shù)實現(xiàn)增加股民 當該編號的股民存在，則增加失敗 ADDPREF

22、ERRED2函數(shù)實現(xiàn)股民買賣股票時所持有股票變動情況，當買入股票是，COUNT1參數(shù)接受正值，當賣出股票時，COUNT1參數(shù)接受負數(shù)當股民有該股票時，該股票數(shù)量更新當股民沒有該股票時，向該嵌套表增加該股票信息當交易股票時，該持股人銀行存款余額發(fā)生變化 DELETEPREFERRED函數(shù)實現(xiàn)刪除某股民，當該股民所持股票為空時，有權刪除,該股民不存在或還持有股票時刪除失敗。 UPDATEPREFERRED函數(shù)實現(xià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ù)測試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ù)實現(xiàn)股票增加功能，當該編號的股票存在，則增加失敗 DELETESTOCK函數(shù)實現(xiàn)刪除股票功能

37、SHOWSTOCK函數(shù)實現(xiàn)顯示所有股票功能 UPDATESTOCK函數(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;增加股票測試begindbms_output.put_line(STOCK_TABLEPACKAGE.ADDSTOCK(2,1,1,1,1,1,1,1,1);end;刪除股票測試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ù)測試BEGIN DBMS_OUTPUT.put_line(STOCK_EXCHANGE_PACKAGE.ADDSTOCKEXCHANGE(2,1,1,

48、1,1,1);END;3、 經(jīng)紀人注冊表說明：本包ADDBROKER函數(shù)實現(xiàn)經(jīng)紀人注冊，當該編號經(jīng)紀人存在，并且用戶名存在，則注冊失敗 LOGINBROKER函數(shù)實現(xiàn)經(jī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、 顯示股民和相應的股票信息說明：本包函數(shù)RETURNSTOCK_PRO實現(xiàn)現(xiàn)實某經(jīng)紀人所有（客戶）股民的信息，有引用游標實現(xiàn)。在股民表中的經(jīng)紀人字段標識該股民所屬經(jīng)紀人。聲明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;開盤價視圖create or replace view showtq asselect r.stock_exchange_stockno 股票編號,min(TO_DATE(r.stock_exchange_time,YYYY-MM-DD HH24:MI:SS) 最后時間 from stock_exchange_table r