1、创建表空间

CREATE TABLESPACE fund DATAFILE 'e:\ORADATA\fundd_file.dbf' SIZE 40M;

2.创建用户及密码

CREATE USER test_user

IDENTIFIED BY test123456 DEFAULT TABLESPACE fund;

3、给用户赋权限

GRANT CONNECT,RESOURCE TO test_user;

4、创建表

CREATE TABLE Fund(

FundNo VARCHAR2(20),

CompanyId VARCHAR2(20),

FundName VARCHAR2(20),

Price NUMBER(10,2),

FundType NUMBER(1,0),

Invest NUMBER(1,0),

BuyLimit NUMBER(5,0),

IsChange NUMBER(1,0),

YearRate NUMBER(6,5),

ApplyDate DATE,

State NUMBER(1,0)

);

5、给表添加主键及外键约束

--删除已有的主键

--ALTER TABLE Fund DROP CONSTRAINT PK_Fund;

--添加主键约束

ALTER TABLE Fund ADD CONSTRAINT PK_Fund_FundNo PRIMARY KEY(FundNo);

--添加外键约束

ALTER TABLE Fund ADD CONSTRAINT FK_Fund_CompanyId FOREIGN KEY(CompanyId) REFERENCES FundCompany(CompanyId);

6、给表及字段添加注释

COMMENT ON TABLE Fund IS '基金表';

--给表字段添加注释

COMMENT ON COLUMN Fund.FundNo IS '基金代码ID，主键';

COMMENT ON COLUMN Fund.CompanyId IS '基金公司ID，外键引用FundCompany表的CompanyId';

COMMENT ON COLUMN Fund.FundName IS '基金名称';

COMMENT ON COLUMN Fund.Price IS '基金净值';

COMMENT ON COLUMN Fund.FundType IS '基金类型，1表示开放式，2表示封闭式';

COMMENT ON COLUMN Fund.Invest IS '投资方向，1股票，2债券，3货币，4混合';

COMMENT ON COLUMN Fund.BuyLimit IS '购买下限';

COMMENT ON COLUMN Fund.IsChange IS '是否可转换，0表示不可转换，1表示可转换';

COMMENT ON COLUMN Fund.YearRate IS '年利率，必须是0-1之间的数字';

COMMENT ON COLUMN Fund.ApplyDate IS '申请日期';

COMMENT ON COLUMN Fund.State IS '基金状态，0表示正常，1表示冻结';

7、创建函数

CREATE OR REPLACE FUNCTION FUNC_NEXTID(I_SQ IN VARCHAR2,

I_TITLE IN VARCHAR2,

I_LEN IN NUMBER) RETURN VARCHAR2 AS

V_SQ VARCHAR2(100);

V_KEY VARCHAR2(100);

BEGIN

SELECT I_SQ || '.NEXTVAL' INTO V_SQ FROM DUAL;

SELECT I_TITLE || LPAD(V_SQ, I_LEN, 0) INTO V_KEY FROM DUAL;

RETURN V_KEY;

END;

8.创建序列

--为基金公司表（FundCompany）主键创建序列

CREATE SEQUENCE SQ_COMPANYID

INCREMENT BY 1 --每次加1

START WITH 1 --从1开始

NOMAXVALUE --没有最大值

NOCYCLE --一直累加不循环

CACHE 10;

9.创建触发器

CREATE OR REPLACE TRIGGER TR_Fund

BEFORE INSERT ON Fund

FOR EACH ROW

BEGIN

:NEW.FUNDNO := FUNC_NEXTID('V', 'SQ_FundNo.NEXTVAL', 6);

END;

10、创建程序包（包体中包括了函数及过程）包：

/*------------------------------------------------

--创建包说明

--包名：FundAccountManager_pack

--功能描述：创建过程或函数分别实现，基金账户开户、基金账户信息查询。

------------------------------------------------*/

CREATE OR REPLACE PACKAGE FundAccountManager_pack IS

--活期账户开户

FUNCTION FUNC_ADD_CURRENTACCOUNT(I_CURRENTPASSWORD VARCHAR2,

I_DEPOSITSUM NUMBER,

I_CARDTYPE NUMBER,

I_CARDNO VARCHAR2,

I_NAME VARCHAR2,

I_ADDRESS VARCHAR2,

I_PHONE VARCHAR2,

I_SEX NUMBER,

I_OPENACCDATE DATE,

I_STATE NUMBER) RETURN NUMBER;

--理财账户开户

FUNCTION FUNC_ADD_FINANCINGACCOUNT(I_FINANCEPASSWORD VARCHAR2,

I_MONEYTYPE NUMBER,

I_ACCOUNTBALANCE NUMBER,

I_ENABLEBALANCE NUMBER,

I_CONGEALFUND NUMBER,

I_STATE NUMBER,

I_CURRENTACCOUNT VARCHAR2)

RETURN NUMBER;

--基金账户开户

FUNCTION FUNC_ADD_FUNDACCOUNT(I_FINANCINGACCOUNT VARCHAR2,

I_COMPANYID VARCHAR2,

I_CARDTYPE NUMBER,

I_CARDNO VARCHAR2,

I_NAME VARCHAR2,

I_SEX NUMBER,

I_ADDRESS VARCHAR2,

I_PHONE VARCHAR2,

I_POSTNUM VARCHAR2,

I_EMAIL VARCHAR2,

I_CREATEDATE DATE,

I_CONGEALSTATE NUMBER) RETURN NUMBER;

--基金账户信息查询

PROCEDURE PRO_QUERY_FUNDACCOUNT(O_RESULT OUT SYS_REFCURSOR,

I_FINANCINGACCOUNT IN VARCHAR2);

END FundAccountManager_pack;

包体：

CREATE OR REPLACE PACKAGE BODY FundAccountManager_pack IS

----活期账户开户

/*------------------------------------------------

--创建包说明

--包名：FundAccountManager_pack

--功能描述：创建过程或函数分别实现，基金账户开户、基金账户信息查询。

------------------------------------------------*/

FUNCTION FUNC_ADD_CURRENTACCOUNT(I_CURRENTPASSWORD VARCHAR2,

I_DEPOSITSUM NUMBER,

I_CARDTYPE NUMBER,

I_CARDNO VARCHAR2,

I_NAME VARCHAR2,

I_ADDRESS VARCHAR2,

I_PHONE VARCHAR2,

I_SEX NUMBER,

I_OPENACCDATE DATE,

I_STATE NUMBER) RETURN NUMBER IS

ERR_CURRENTACCOUNT EXCEPTION;

PRAGMA EXCEPTION_INIT(ERR_CURRENTACCOUNT, -1);

/*违反唯一主键约束为-1*/

BEGIN

INSERT INTO CURRENTACCOUNT

(CURRENTPASSWORD,

DEPOSITSUM,

CARDTYPE,

CARDNO,

NAME,

ADDRESS,

PHONE,

SEX,

OPENACCDATE,

STATE)

VALUES

(I_CURRENTPASSWORD,

I_DEPOSITSUM,

I_CARDTYPE,

I_CARDNO,

I_NAME,

I_ADDRESS,

I_PHONE,

I_SEX,

I_OPENACCDATE,

I_STATE);

IF SQL%FOUND THEN

RETURN 1;

END IF;

EXCEPTION

WHEN ERR_CURRENTACCOUNT THEN

RETURN 0;

WHEN OTHERS THEN

RETURN - 1;

END FUNC_ADD_CURRENTACCOUNT;

--理财账户开户

FUNCTION FUNC_ADD_FINANCINGACCOUNT(I_FINANCEPASSWORD VARCHAR2,

I_MONEYTYPE NUMBER,

I_ACCOUNTBALANCE NUMBER,

I_ENABLEBALANCE NUMBER,

I_CONGEALFUND NUMBER,

I_STATE NUMBER,

I_CURRENTACCOUNT VARCHAR2)

RETURN NUMBER IS

ERR_FINANCINGACCOUNT EXCEPTION;

PRAGMA EXCEPTION_INIT(ERR_FINANCINGACCOUNT, -1);

BEGIN

INSERT INTO FINANCINGACCOUNT

(FINANCEPASSWORD,

MONEYTYPE,

ACCOUNTBALANCE,

ENABLEBALANCE,

CONGEALFUND,

STATE,

CURRENTACCOUNT)

VALUES

(I_FINANCEPASSWORD,

I_MONEYTYPE,

I_ACCOUNTBALANCE,

I_ENABLEBALANCE,

I_CONGEALFUND,

I_STATE,

I_CURRENTACCOUNT);

IF SQL%FOUND THEN

RETURN 1;

END IF;

EXCEPTION

WHEN ERR_FINANCINGACCOUNT THEN

RETURN 0;

WHEN OTHERS THEN

RETURN - 1;

END FUNC_ADD_FINANCINGACCOUNT;

--基金账户开户

FUNCTION FUNC_ADD_FUNDACCOUNT(I_FINANCINGACCOUNT VARCHAR2,

I_COMPANYID VARCHAR2,

I_CARDTYPE NUMBER,

I_CARDNO VARCHAR2,

I_NAME VARCHAR2,

I_SEX NUMBER,

I_ADDRESS VARCHAR2,

I_PHONE VARCHAR2,

I_POSTNUM VARCHAR2,

I_EMAIL VARCHAR2,

I_CREATEDATE DATE,

I_CONGEALSTATE NUMBER) RETURN NUMBER IS

ERR_FUNDACCOUNT EXCEPTION;

PRAGMA EXCEPTION_INIT(ERR_FUNDACCOUNT, -1);

V_COUNT NUMBER;

BEGIN

--约束条件检查

SELECT COUNT(FINANCINGACCOUNT)

INTO V_COUNT

FROM FUNDACCOUNT

WHERE FINANCINGACCOUNT = I_FINANCINGACCOUNT

AND COMPANYID = I_COMPANYID;

IF V_COUNT = 0 THEN

INSERT INTO FUNDACCOUNT

(FINANCINGACCOUNT,

COMPANYID,

CARDTYPE,

CARDNO,

NAME,

SEX,

ADDRESS,

PHONE,

POSTNUM,

EMAIL,

CREATEDATE,

CONGEALSTATE)

VALUES

(I_FINANCINGACCOUNT,

I_COMPANYID,

I_CARDTYPE,

I_CARDNO,

I_NAME,

I_SEX,

I_ADDRESS,

I_PHONE,

I_POSTNUM,

I_EMAIL,

I_CREATEDATE,

I_CONGEALSTATE);

IF SQL%FOUND THEN

RETURN 1;

END IF;

ELSE

RETURN - 1;

END IF;

EXCEPTION

WHEN ERR_FUNDACCOUNT THEN

RETURN 0;

WHEN OTHERS THEN

RETURN - 1;

END FUNC_ADD_FUNDACCOUNT;

--基金账户的查询

PROCEDURE PRO_QUERY_FUNDACCOUNT(O_RESULT OUT SYS_REFCURSOR,

I_FINANCINGACCOUNT IN VARCHAR2) AS

BEGIN

OPEN O_RESULT FOR

SELECT *

FROM FUNDACCOUNT T

WHERE T.FINANCINGACCOUNT = I_FINANCINGACCOUNT;

END PRO_QUERY_FUNDACCOUNT;

END FundAccountManager_pack;