oracle小项目,oracle 小项目实战总结

CREATE OR REPLACE PACKAGE BODY FundAccountManager_pack IS

----活期账户开户

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

--创建包说明

--包名：FundAccountManager_pack

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

--原创：钓鱼君

--日期：2016/3/10

--QQ:954739353

--

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

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;