股票交易系统需求分析

股票管理系统需求分析

摘要

本系统为股票经纪人（管理人员）提供交易平台，其中包括提供给经济人的各基本功能，如：经纪人登录，注册，买卖股票，增加，删除，更新，查询股民，查询股票信息。提供给政府部门的功能有增加股票的功能。

开发语言：JA V A

数据库：ORACLE

开发工具：ECLIPSE

开发环境：JDK1.5

一、功能介绍

1、交易员登陆注册功能

A、交易员登陆功能：管理者通过输入正确的ID和密码进入后台管理系统。如果密码或者ID错误则给出应用的错误提

示。当管理者进入系统后，进入股票交易界面。

B、交易员注册功能：交易员通过键入个人信息可以注册。如果注册的用户已经存在，则给出注册失败（用户存在）提示；

若输入的信息不全，则给出注册失败（信息不全）的提示

2、用户管理功能：

A、添加用户信息：若用户存在，则显示（用户存在）信息，若不存在，则填加成功。

B、删除用户信息：若用户不存在，则无法删除，若存在，则删除成功。

C、修改用户信息：改变原有用户信息。

D、显示用户信息：个人信息和所持有的股票信息。

3、股票信息显示功能：

A、显示股票的信息;

B、添加股票的信息;

C、更改股票的信息:

D、删除股票的信息;

E、显示股票交易量功能

4、用户股票交易功能

二、设计

表空间名stock_exchange

文件名STOCK_EXCHANGE.ora

路径H:\stockexchangesystem\

空间大小50M

用户名：stock

密码：stock

2．1表设计

股东表common_stockholder_table 普通股股东

股票表stock_table

股民表preferred_stockholder_table 优先股股东

交易表stockexchange_table

股票经纪人stockbroker_table

1、COMMON_STOCKHOLDER_TABLE 普通股股东表

2、STOCK_TABLE股票表

3、PREFERRED_STOCKHOLDER_TABLE 优先股股东表

创建表空间：

CREATE TABLESPACE "STOCK_EXCHANGE"

LOGGING

DA TAFILE ' D:\stocksysem\STOCK_EXCHANGE.ora' SIZE 50M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

创建用户：

create user stock

identified by stock

default tablespace stock_exchange;

授权用户

GRANT CONNECT,RESOURCE TO STOCK;

创建表

1、COMMON_STOCKHOLDER_TABLE

CREATE 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 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 V ARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_NAME V ARCHAR2(10) NOT NULL, STOCK_NO V ARCHAR2(10) NOT NULL, STOCK_ISSUE_TIME V ARCHAR2(20) NOT NULL, STOCK_ISSUE_CIRCULATION NUMBER NOT NULL, STOCK_ISSUE_PRICE NUMBER NOT NULL, STOCK_PARV ALUE NUMBER NOT NULL, STOCK_DIVIDENT NUMBER NOT NULL, STOCK_ISSUER V ARCHAR2(20) NOT NULL, STOCK_BOARD V ARCHAR2(10) NOT NULL, STOCK_REMARK V ARCHAR2(200) NULL);

3、PREFERRED_STOCKHOLDER_TABLE股民表(本表嵌套表以说明某股民持有多种股票)

创建基类型数据对象，说明股民持有的股票编号（STOCK_NO）和数量（STOCK_COUNT）

CREATE OR REPLACE TYPE STOCKHOLERTYPE IS OBJECT

( STOCK_NO VARCHAR2(10),

STOCK_COUNT NUMBER

)

创建嵌套表类型，该表对象将嵌套于PREFERRED_STOCKHOLDER_TABLE表的STOCK字段

CREATE OR REPLACE TYPE STOCKHOLDERTABLE AS TABLE OF STOCKHOLERTYPE

创建嵌套表

CREATE TABLE PREFERRED_STOCKHOLDER_TABLE(PSH_ID V ARCHAR2(10) PRIMARY KEY NOT NULL, PREFERRED_STOCKHOLDER_NO V ARCHAR2(10) NOT NULL, PREFERRED_STOCKHOLDER_NAME V ARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_MONEY V ARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_JOINTIME V ARCHAR2(20) NOT NULL, PREFERRED_STOCKHOLDER_BNO V ARCHAR2(20) NOT NULL, STOCK 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 V ARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_EXCHANGE_STOCKNO V ARCHAR2(10) NOT NULL, STOCK_EXCHANGE_SHNO V ARCHAR2(10) NOT NULL, STOCK_EXCHANGE_BHNO NUMBER NOT NULL, STOCK_EXCHANGE_PRICE V ARCHAR2(10) NOT

NULL, STOCK_EXCHANGE_TIME V ARCHAR2(20) NOT NULL, STOCK_EXCHANGE_STOCKBROKER V ARCHAR2(20) NOT NULL, STOCK_EXCHANGE_REMARK V ARCHAR2(200) NULL);

5、STOCK_BROKER_TABLE经纪人表，及管理员

CREATE TABLE STOCK_BROKER_TABLE(SB_ID V ARCHAR2(10) PRIMARY KEY NOT NULL, STOCK_BROKER_TRUENAME V ARCHAR2(20) NOT NULL,STOCK_BROKER_NO V ARCHAR2(20) NOT NULL, STOCK_BROKER_USERNAME V ARCHAR2(20) NOT NULL, STOCK_BROKER_PASSWORD V ARCHAR2(10) NOT NULL, STOCK_BROKER_TIME V ARCHAR2(20),STOCK_BORKER_REMARK V ARCHAR2(200) NULL);

Pl/sql设计

一、各表主键自增设计

1、创建优先股东表序列STOCKHOLDERSQUEEN

CREATE SEQUENCE STOCKHOLDERSQUEEN

INCREMENT BY 1

START WITH 1

创建优先股东表主键自增触发器PREFERRED_STOCKHOLDER

CREATE OR REPLACE TRIGGER PREFERRED_STOCKHOLDER

BEFORE INSERT or update or delete ON PREFERRED_STOCKHOLDER_TABLE

FOR EACH ROW

BEGIN

if inserting then

SELECT STOCKHOLDERSQUEEN.NEXTVAL INTO :NEW.PSH_ID FROM DUAL;

end if;

END;

2、创建股票表序列STOCK_TABLE_SEQUEEN

CREATE SEQUENCE STOCK_TABLE_SEQUEEN

INCREMENT BY1

START WITH 1

创建股票表主键自增触发器STOCK_TABLE_TRIGGER

CREATE OR REPLACE TRIGGER STOCK_TABLE_TRIGGER

BEFORE INSERT ON STOCK_TABLE

FOR EACH ROW

BEGIN

SELECT STOCK_TABLE_SEQUEEN.NEXTVAL INTO :NEW.S_ID FROM DUAL;

END;

3、交易表序列

CREATE SEQUENCE EXCHANGESEQUENCE INCREMENT BY1

START WITH 1

交易表自增触发器

CREATE OR REPLACE TRIGGER EXCHANGE_TRIGGER BEFORE INSERT ON STOCK_EXCHANGE_TABLE

FOR EACH ROW

BEGIN

SELECT EXCHANGESEQUENCE.NEXTVAL INTO :NEW.SE_ID FROM DUAL; END;

4、经纪人表序列

CREATE SEQUENCE STOCKBROKERSEQUENCE INCREMENT BY1

START WITH 1

经纪人表主键自增触发器

CREATE OR REPLACE TRIGGER STOCKBROKER_TRIGGER BEFORE INSERT ON STOCK_BROKER_TABLE

FOR EACH ROW

BEGIN

SELECT STOCKBROKERSEQUENCE.NEXTVAL INTO :NEW.SB_ID FROM DUAL; END;

二、功能包设计

1、增加优先股东（开户）某股东买入卖出股票包

说明：本包ADDPREFERRED1函数实现增加股民

当该编号的股民存在，则增加失败 ADDPREFERRED2函数实现股民买卖股票时所持有股票变动情况，当买入股票是，COUNT1参数接受正值，

当卖出股票时，COUNT1参数接受负数

当股民有该股票时，该股票数量更新

当股民没有该股票时，向该嵌套表增加该股票信息

当交易股票时，该持股人银行存款余额发生变化DELETEPREFERRED函数实现删除某股民，当该股民所持股票为空时，有权删除, 该股民不存在或还持有股票时删除失败。 UPDATEPREFERRED函数实现更新股东部分数据。声明

CREATE OR REPLACE PACKAGE ADDPREFERRED IS

FUNCTION ADDPREFERRED1(

PREFERRED_STOCKHOLDER_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. 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) RETURN NUMBER

;

END ADDPREFERRED;

主体

CREATE OR REPLACE PACKAGE body ADDPREFERRED IS

FUNCTION ADDPREFERRED1(

PREFERRED_STOCKHOLDER_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. PREFERRED_STOCKHOLDER_BNO %TYPE,

STOCK1 PREFERRED_STOCKHOLDER_TABLE.STOCK%TYPE

)RETURN NUMBER

as

ICOUNT INT;

begin

SELECT COUNT(*) INTO ICOUNT FROM PREFERRED_STOCKHOLDER_TABLE

WHERE PREFERRED_STOCKHOLDER_NO = PREFERRED_STOCKHOLDER_NO1;

IF ICOUNT = 0THEN

INSERT INTO PREFERRED_STOCKHOLDER_TABLE

(PREFERRED_STOCKHOLDER_NO,PREFERRED_STOCKHOLDER_NAME,PREFERRED_STOCKHOLDER_MONEY,PREFERRED_STOCKHOLDER_JOINT IME,PREFERRED_STOCKHOLDER_BNO,STOCK)

VALUES(PREFERRED_STOCKHOLDER_NO1,PREFERRED_STOCKHOLDER_NAME1, PREFERRED_STOCKHOLDER_MONEY1,TO_CHAR(SYSDATE), PREFERRED_STOCKHOLDER_BNO1,STOCK1);

Commit;

return sql%rowcount;

ELSE

RETURN0;

END IF;

end ADDPREFERRED1;

FUNCTION ADDPREFERRED2(NO VARCHAR2,NUM VARCHAR2,COUNT1 NUMBER,PRICE NUMBER) RETURN NUMBER

as

ICOUNT INT;

COUNT INT;

MONEY INT;

begin

SELECT 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 THEN

RETURN3;

ELSIF

ICOUNT = 0THEN

INSERT INTO table(select t.stock from PREFERRED_STOCKHOLDER_TABLE t where PREFERRED_STOCKHOLDER_NO = NO) s

VALUES(num,count1);

UPDATE PREFERRED_STOCKHOLDER_TABLE T

SET T. PREFERRED_STOCKHOLDER_MONEY = T. PREFERRED_STOCKHOLDER_MONEY +COUNT1 * PRICE WHERE PREFERRED_STOCKHOLDER_NO = NO;

Commit;

return1;

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 T

SET T. PREFERRED_STOCKHOLDER_MONEY = T. PREFERRED_STOCKHOLDER_MONEY +COUNT1 * PRICE WHERE PREFERRED_STOCKHOLDER_NO = NO;

Commit;

RETURN0;

END IF;

END ADDPREFERRED2;

FUNCTION DELETEPREFERRED(NO VARCHAR2) RETURN NUMBER

AS

ICOUNT INT;

ICOUNT2 INT;

BEGIN

SELECT COUNT(*) INTO ICOUNT FROM TABLE(SELECT T.STOCK FROM PREFERRED_STOCKHOLDER_TABLE T WHERE PREFERRED_STOCKHOLDER_NO = NO);

SELECT COUNT(*) INTO ICOUNT2 FROM PREFERRED_STOCKHOLDER_TABLE WHERE PREFERRED_STOCKHOLDER_NO = NO;

IF ICOUNT = 1AND ICOUNT2 > 0THEN

DELETE PREFERRED_STOCKHOLDER_TABLE WHERE PREFERRED_STOCKHOLDER_NO = NO;

Commit;

RETURN1;

ELSE

RETURN0;

END IF;

END DELETEPREFERRED;

FUNCTION UPDATEPREFERRED(NO VARCHAR2,NAME VARCHAR2,BNO VARCHAR2) RETURN NUMBER

AS

BEGIN

UPDATE PREFERRED_STOCKHOLDER_TABLE SET PREFERRED_STOCKHOLDER_NAME=NAME, PREFERRED_STOCKHOLDER_BNO = BNO WHERE PREFERRED_STOCKHOLDER_NO = NO;

Commit;

Return1;

END UPDATEPREFERRED;

END ADDPREFERRED;

增加优先股东数据测试

BEGIN

dbms_output.put_line(ADDPREFERRED.ADDPREFERRED1('P007','1231','2000',

1,STOCKHOLDERTABLE(STOCKHOLERTYPE(11,22))));

end;

更新优先股东持股数据

update PREFERRED_STOCKHOLDER_TABLE t set t.stock = STOCKHOLDERTABLE(STOCKHOLERTYPE('b0010',1000)) where PREFERRED_STOCKHOLDER_NAME = 'liujie';

删除股民数据

BEGIN

dbms_output.put_line(ADDPREFERRED.DELETEPREFERRED('P003'));

end;

更新股民数据

dbms_output.put_line(ADDPREFERRED. UPDATEPREFERRED('P003',’刘杰’,’12’));

end;

向某股民增加股票数据

BEGIN

DBMS_OUTPUT.put_line(ADDPREFERRED.ADDPREFERRED2('P004','77777',2010,100));

END;

2、增加删除显示更新股票包

说明：本包ADDSTOCK函数实现股票增加功能，当该编号的股票存在，则增加失败

DELETESTOCK函数实现删除股票功能

SHOWSTOCK函数实现显示所有股票功能

UPDATESTOCK函数实现更新股票功能

声明

CREATE OR REPLACE PACKAGE STOCK_TABLEPACKAGE IS

TYPE 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; FUNCTION 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 STOCK_TABLEPACKAGE;

主体

CREATE OR REPLACE PACKAGE BODY STOCK_TABLEPACKAGE IS

FUNCTION ADDSTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER,PRICE NUMBER,PARVALUE NUMBER,DIVIDENT NUMBER,ISSUER VARCHAR2,BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBER AS

ICOUNT INT;

BEGIN

SELECT COUNT(*) INTO ICOUNT FROM STOCK_TABLE WHERE STOCK_NO = NO;

IF ICOUNT = 0 THEN

INSERT INTO STOCK_TABLE(STOCK_NAME, STOCK_NO,STOCK_ISSUE_TIME, STOCK_ISSUE_CIRCULATION,STOCK_ISSUE_PRICE,STOCK_PARVALUE,STOCK_DIVIDENT,STOCK_ISSU ER,STOCK_BOARD,STOCK_REMARK)

VALUES(NAME,NO,TO_CHAR(SYSDATE),CIRCULATION,PRICE,PARVALUE,DIVIDENT,ISSUER,BOARD,R EMARK);

RETURN 1;

ELSE

RETURN 0;

END IF;

END ADDSTOCK;

FUNCTION DELETESTOCK(NO VARCHAR2) RETURN NUMBER

AS

ICOUNT NUMBER;

BEGIN

SELECT COUNT(*) INTO ICOUNT FROM STOCK_TABLE WHERE STOCK_NO = NO;

IF ICOUNT > 0 THEN

DELETE STOCK_TABLE WHERE STOCK_NO = NO;

Commit;

RETURN 1;

ELSE

RETURN 0;

END IF;

END DELETESTOCK;

PROCEDURE SHOWSTOCK(SHOWSTOCK IN OUT SHOWSTOCKCURSOR)

AS

BEGIN

OPEN SHOWSTOCK FOR SELECT * FROM showend;

END SHOWSTOCK;

PROCEDURE SHOWSTOCKDETAIL(SHOWSTOCK IN OUT SHOWSTOCKCURSOR,NAME VARCHAR2,NO VARCHAR2) AS

BEGIN

OPEN SHOWSTOCK FOR SELECT * FROM STOCK_TABLE WHERE STOCK_NO = NO OR STOCK_NAME = NAME;

END SHOWSTOCKDETAIL;

FUNCTION UPDATESTOCK(NAME VARCHAR2,NO VARCHAR2,CIRCULATION NUMBER, BOARD NUMBER,REMARK VARCHAR2) RETURN NUMBER

AS

BEGIN

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 STOCK_TABLEPACKAGE;

增加股票测试

begin

dbms_output.put_line(STOCK_TABLEPACKAGE.ADDSTOCK(2,1,1,1,1,1,1,1,1));

end;

删除股票测试

begin

dbms_output.put_line(STOCK_TABLEPACKAGE.DELETESTOCK(1));

end;

显示所有股票信息

var a refcursor;

exec STOCK_TABLEPACKAGE.SHOWSTOCK(:A);

更新某股票信息

begin

dbms_output.put_line(STOCK_TABLEPACKAGE.UPDATESTOCK('1','1',1,1,'1'));

end;

2、交易表增加数据包

声明

CREATE OR REPLACE PACKAGE STOCK_EXCHANGE_PACKAGE IS

FUNCTION ADDSTOCKEXCHANGE(STOCKNO VARCHAR2,SHNO VARCHAR2,BHNO VARCHAR2,PRICE VARCHAR2,STOCKBROKER VARCHAR2,REMARK VARCHAR2)

RETURN NUMBER;

END STOCK_EXCHANGE_PACKAGE;

主体

CREATE OR REPLACE PACKAGE body STOCK_EXCHANGE_PACKAGE IS

FUNCTION ADDSTOCKEXCHANGE(STOCKNO VARCHAR2,SHNO VARCHAR2,BHNO VARCHAR2,PRICE VARCHAR2,STOCKBROKER VARCHAR2,REMARK VARCHAR2)

RETURN NUMBER

AS

BEGIN

INSERT INTO STOCK_EXCHANGE_TABLE(STOCK_EXCHANGE_STOCKNO, STOCK_EXCHANGE_SHNO, STOCK_EXCHANGE_BHNO, STOCK_EXCHANGE_PRICE, STOCK_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;

增加交易数据测试

BEGIN

DBMS_OUTPUT.put_line(STOCK_EXCHANGE_PACKAGE.ADDSTOCKEXCHANGE(2,1,1,1,1,1));

END;

3、经纪人注册表

说明：本包ADDBROKER函数实现经纪人注册，当该编号经纪人存在，并且用户名存在，则注册失败LOGINBROKER函数实现经纪人登录，当用户名或密码不匹配，则登录失败

声明

CREATE OR REPLACE PACKAGE STOCK_BROKERPACKAGE

IS

FUNCTION ADDBROKER(TRUENAME VARCHAR2,NO VARCHAR2, USERNAME VARCHAR2, PASSWORD VARCHAR2, REMARK VARCHAR2) RETURN NUMBER ;

FUNCTION LOGINBROKER(USERNAME VARCHAR2,PASSWORD VARCHAR2) RETURN NUMBER;

END STOCK_BROKERPACKAGE;

主体

CREATE OR REPLACE PACKAGE body STOCK_BROKERPACKAGE

IS

FUNCTION ADDBROKER(TRUENAME VARCHAR2,NO VARCHAR2, USERNAME VARCHAR2, PASSWORD VARCHAR2,REMARK VARCHAR2) RETURN NUMBER as

ICOUNT NUMBER;

BEGIN

SELECT COUNT(*) INTO ICOUNT FROM STOCK_BROKER_TABLE WHERE STOCK_BROKER_NO = NO OR STOCK_BROKER_USERNAME = USERNAME;

IF ICOUNT = 0 THEN

INSERT 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(SYSDATE),REMARK);

RETURN1;

ELSE

RETURN 0;

END IF;

END ADDBROKER;

FUNCTION LOGINBROKER(USERNAME VARCHAR2,PASSWORD VARCHAR2) RETURN NUMBER

AS

ICOUNT NUMBER;

BEGIN

SELECT COUNT(*) INTO ICOUNT FROM STOCK_BROKER_TABLE WHERE STOCK_BROKER_USERNAME = USERNAME

AND STOCK_BROKER_PASSWORD = PASSWORD;

IF ICOUNT > 0 THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

END LOGINBROKER;

END STOCK_BROKERPACKAGE;

4、显示股民和相应的股票信息

说明：本包函数RETURNSTOCK_PRO实现现实某经纪人所有（客户）股民的信息，有引用游标实现。在股民表中的经纪人字段标识该股民所属经纪人。

声明

CREATE OR REPLACE PACKAGE SHOW IS

TYPE RETURNSTOCKHOLDER_CURSOR IS REF CURSOR;

TYPE HOLDERSTOCK_CURSOR IS REF CURSOR;

PROCEDURE RETURNSTOCK_PRO(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 IS

PROCEDURE RETURNSTOCK_PRO(SHOWSTOCKER IN OUT RETURNSTOCKHOLDER_CURSOR,BROKERNO PREFERRED_STOCKHOLDER_TABLE.PREFERRED_STOCKHOLDER_BNO%TYPE) AS

BEGIN

OPEN SHOWSTOCKER FOR SELECT * FROM PREFERRED_STOCKHOLDER_TABLE

WHERE PREFERRED_STOCKHOLDER_BNO = BROKERNO;

END RETURNSTOCK_PRO;

PROCEDURE RETURNSTOCK_CURSOR(SHOWSTOCK IN OUT HOLDERSTOCK_CURSOR,HOLDERNO VARCHAR2) AS

BEGIN

OPEN 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 as

select r.stock_exchange_stockno 股票编号,min(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’)) 最后时间 from stock_exchange_table r where TO_CHAR(sysdate –1,’YYYY-MM-DD’) =TO_CHAR(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’) group by r.stock_exchange_stockno

提取开盘价

create or replace view showrq as select * from stock_exchange_table f,showtq g where TO_DATE(f.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’) = g.最后时间

收盘价视图

create or replace view showtr as

select r.stock_exchange_stockno 股票编号,max(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’)) 最后时间 from stock_exchange_table r where TO_CHAR(sysdate –1,’YYYY-MM-DD’) =TO_CHAR(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’) group by r.stock_exchange_stockno

提取收盘价

create or replace view showr as select * from stock_exchange_table f,showtr g where

TO_DATE(f.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’) = g.最后时间

大盘无开盘价视图

CREATE or replace VIEW SHOW5 AS SELECT st.stock_name,se.stock_exchange_stockno,max(TO_NUMBER(se.STOCK_EXCHANGE_PRICE))

hp,min(TO_NUMBER(se.STOCK_EXCHANGE_PRICE))

lp,sum(to_number(se.STOCK_EXCHANGE_REMARK))

totle,sum(TO_NUMBER(se.STOCK_EXCHANGE_REMARK) *

TO_NUMBER(se.STOCK_EXCHANGE_PRICE))totlem FROM STOCK_TABLE st,STOCK_EXCHANGE_TABLE se WHERE st.stock_no =

se.stock_exchange_stockno and TO_CHAR(TO_DATE(se.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’) = TO_CHAR(sysdate – 1,’YYYY-MM-DD’)

group by st.stock_name,se.stock_exchange_stockno

大盘完整显示视图

create or replace view showend1 as select show5.stock_name stockname,show5.stock_exchange_stockno stocknum,showrq.STOCK_EXCHANGE_PRICE firstprice,showr.STOCK_EXCHANGE_PRICE closeprice,show5.hp hprice,show5.lp lprice,show5.totle tottradcount,show5.totlem tottradmoney from show5,showrq,showr where

show5.stock_exchange_stockno = showrq.STOCK_EXCHANGE_STOCKNO and show5.stock_exchange_stockno = showr.STOCK_EXCHANGE_STOCKNO

开盘价视图

create or replace view showtq1 as

select r.stock_exchange_stockno 股票编号,min(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’)) 开始时间 from stock_exchange_table r where TO_CHAR(sysdate,’YYYY-MM-DD’) =TO_CHAR(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’) group by r.stock_exchange_stockno

提开盘价

create or replace view showrq1 as select f.stock_exchange_stockno fsno, f.stock_exchange_price firstprice from stock_exchange_table f,showtq1 g where TO_DATE(f.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’) = g.开始时间

当前价视图

create or replace view showtq2 as

select r.stock_exchange_stockno 股票编号,max(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’)) 最后时间 from stock_exchange_table r where TO_CHAR(sysdate,’YYYY-MM-DD’) =TO_CHAR(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’) group by r.stock_exchange_stockno

提当前价

create or replace view showrq2 as select f.stock_exchange_stockno lsno, f.stock_exchange_price lastpric from stock_exchange_table f,showtq2 g where TO_DATE(f.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’) = g.最后时间

当前开盘价完整视图

Create or replace view showend as select showend1.*, showrq1.firstprice

first,https://www.360docs.net/doc/3715480514.html,stpric last from showend1,showrq1,showrq2 where showend1.stocknum = showrq1.fsno and showend1.stocknum = showrq2.lsno 今日价格走势图

声明：

CREATE OR REPLACE PACKAGE RUNPRICE IS

TYPE RUNPRICE IS REF CURSOR;

PROCEDURE SHOWPRICE(SHOWP IN OUT RUNPRICE,NO VARCHAR); END RUNPRICE;

主体

CREATE OR REPLACE PACKAGE body RUNPRICE IS

PROCEDURE SHOWPRICE(SHOWP IN OUT RUNPRICE,NO VARCHAR) as

BEGIN

OPEN SHOWP FOR SELECT* FROM STOCK_EXCHANGE_TABLE r WHERE TO_CHAR(sysdate,’YYYY-MM-DD’) =TO_CHAR(TO_DATE(r.stock_exchange_time,’YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD’)AND r.STOCK_EXCHANGE_STOCKNO = NO ORDER BY STOCK_EXCHANGE_TIME;

END SHOWPRICE ;

END RUNPRICE;

显示历史交易记录

声明

CREATE OR REPLACE PACKAGE SHOWHISTORYTRADE IS TYPE SHOWHISTORYCURSOR IS REF CURSOR; PROCEDURE SHOWHISTORY(SHOWH IN OUT SHOWHISTORYCURSOR,NO VARCHAR2); END SHOWHISTORYTRADE;

主体

CREATE OR REPLACE PACKAGE BODY SHOWHISTORYTRADE IS

PROCEDURE SHOWHISTORY(SHOWH IN OUT SHOWHISTORYCURSOR,NO VARCHAR2) as begin

OPEN SHOWH FOR SELECT * FROM STOCK_EXCHANGE_TABLE WHERE STOCK_EXCHANGE_STOCKNO = NO ORDER BY STOCK_EXCHANGE_TIME; end SHOWHISTORY;

END SHOWHISTORYTRADE;

滚动显示股票

声明

CREATE or REPLACE PACKAGE SHOWSTOCKMOV IS

TYPE SHOWMOV IS REF CURSOR;

PROCEDURE SHOWMOVE(SHOW IN OUT SHOWMOV);

END SHOWSTOCKMOV;

主体

CREATE or REPLACE PACKAGE BODY SHOWSTOCKMOV IS PROCEDURE SHOWMOVE(SHOW IN OUT SHOWMOV) AS

BEGIN

OPEN SHOW FOR SELECT * FROM STOCK_TABLE;

END SHOWMOVE;

END SHOWSTOCKMOV;