建表语句和存储过程模板

建表语句和存储过程模板

CREATE TABLE YHDWB.INFO_REPO_TRADE_PRICE
(
  BATCH_TIME           CHAR(18) NULL ,
	TRADEDATE            CHAR(18) NOT NULL ,
	EXCH                 CHAR(18) NULL ,
	REPO_TYPE            CHAR(18) NULL ,
	TERM_VARIETY         CHAR(18) NULL ,
	LCLOSE               CHAR(18) NULL ,
	OPENPRICE            CHAR(18) NULL ,
	HIGH_PRICE           CHAR(18) NULL ,
	LOW_PRICE            CHAR(18) NULL ,
	CLOSE_PRICE          CHAR(18) NULL ,
	WT_PRICE             CHAR(18) NULL ,
	INTEREST_RATE_BOND   CHAR(18) NULL ,
	TRADEVOL             CHAR(18) NULL ,
	DEAL_COUNT           CHAR(18) NULL ,
	DEAL_AMT             CHAR(18) NULL ,
	RUNBATCH_BEGIN_TIME  CHAR(18) NULL ,
	CREATED_BY           CHAR(18) NULL ,
	NDC_CREATED_TIME     CHAR(18) NULL ,
	NDC_UPDATED_TIME     CHAR(18) NULL ,
	ORISYS               CHAR(18) NULL ,
	SEC_PFCODE           VARCHAR2(20) NOT NULL ,
	STDATE               VARCHAR2(20) NOT NULL ,
	ENDDATE              VARCHAR2(20) NOT NULL 
);
-- 添加表注释
COMMENT ON TABLE  YHDWB.INFO_REPO_TRADE_PRICE IS '回购交易行情';
--添加字段注释
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.TRADEDATE   IS		'交易日期';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.SEC_PFCODE   IS		'证券内码';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.STDATE   IS		'开始日期';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.ENDDATE   IS		'结束日期';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.EXCH   IS		'交易市场';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.REPO_TYPE   IS		'回购类型';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.TERM_VARIETY   IS		'期限品种';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.LCLOSE   IS		'昨日收盘';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.OPENPRICE	IS		'开盘价';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.HIGH_PRICE   IS		'最高价';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.LOW_PRICE   IS		'最低价';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.CLOSE_PRICE   IS		'收盘价';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.WT_PRICE   IS		'加权价';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.INTEREST RATE BOND   IS		'利率债';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.TRADEVOL   IS		'成交量';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.DEAL_COUNT   IS		'成交笔数';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.DEAL_AMT   IS		'成交金额';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.CREATED_BY   IS		'创建人';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.BATCH_TIME   IS		'跑批起始时间';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.NDC_CREATED_TIME   IS		'数据创建时间';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.NDC_UPDATED_TIME   IS		'数据更新时间';
COMMENT ON COLUMN YHDWB.INFO_REPO_TRADE_PRICE.ORISYS   IS		'源系统';

CREATE UNIQUE INDEX XPK_INFO_REPO_TRADE_PRICE ON YHDWB.INFO_REPO_TRADE_PRICE
(TRADEDATE   ASC,SEC_PFCODE   ASC,STDATE   ASC,ENDDATE   ASC);

ALTER TABLE YHDWB.INFO_REPO_TRADE_PRICE
	ADD CONSTRAINT  PK_INFO_REPO_TRADE_PRICE PRIMARY KEY (TRADEDATE,SEC_PFCODE,STDATE,ENDDATE);
-- 存储过程语句
PROCEDURE P_JY_REPO_TRADE_PRICE(P_DATE       DATE,
                                 P_BATCH_TIME DATE,
                                 P_CREATED_BY VARCHAR2,
                                 V_RETCODE    OUT VARCHAR2,
                                 V_RETINFO    OUT VARCHAR2) AS
    V_CHG_OBJ   VARCHAR2(2000);
    V_CHG_SHEMA VARCHAR2(2000);
    V_DATE      DATE;
  BEGIN
    V_CHG_OBJ   := 'REPO_TRADE_PRICE';
    V_CHG_SHEMA := 'YHDWB';
    -- 执行成功后的返回信息
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    --把数据插入目标表
    DELETE FROM YHDWB.INFO_REPO_TRADE_PRICE;
    INSERT INTO YHDWB.INFO_REPO_TRADE_PRICE
        (
		    TRADEDATE,
        SEC_PFCODE,
        STDATE,
        ENDDATE,
        EXCH,
        REPO_TYPE,
        TERM_VARIETY,
        LCLOSE,
        OPENPRICE,
        HIGH_PRICE,
        LOW_PRICE,
        CLOSE_PRICE,
        WT_PRICE,
        INTEREST RATE BOND,
        TRADEVOL,
        DEAL_COUNT,
        DEAL_AMT,
        CREATED_BY,
        BATCH_TIME,
        NDC_CREATED_TIME,
        NDC_UPDATED_TIME,
        ORISYS
		)
      SELECT 
            T2.TRADINGDAY  AS TRADEDATE,
            T1.SEC_CODE  AS SEC_PFCODE,
            T1.BEGIN_DATE  AS STDATE,
            T1.END_DATE  AS ENDDATE,
            T1.EXCHANGE  AS EXCH,
            T2.REPURCHASENATURE  AS REPO_TYPE,
            T2.MATURITYTYPE  AS TERM_VARIETY,
            T2.PRECLOSEPRICE  AS LCLOSE,
            T2.OPENPRICE  AS OPENPRICE,
            T2.HIGHPRICE  AS HIGH_PRICE,
            T2.LOWPRICE  AS LOW_PRICE,
            T2.CLOSEPRICE  AS CLOSE_PRICE,
            T2.WEIGHTAVERAGEPRICE  AS WT_PRICE,
            T2.WEIGHTAVERAGEPRICER  AS INTEREST RATE BOND,
            T2.TURNOVERVOL  AS TRADEVOL,
            T2.TURNOVERDEALS  AS DEAL_COUNT,
            T2.TURNOVERVALUE  AS DEAL_AMT,
            P_CREATED_BY  AS CREATED_BY,
            P_BATCH_TIME  AS BATCH_TIME,
            CURRENT_TIMESTAMP  AS NDC_CREATED_TIME,
            CURRENT_TIMESTAMP  AS NDC_UPDATED_TIME,
            'JY' AS ORISYS
        FROM YHPUB.INFO_SEC_INFO T1
             JOIN YHODS.T_JY_BOND_REPUEXIBQUOTE T2
             ON T1.SEC_CODE = T2.INNERCODE;
    COMMIT;
    --异常处理
  EXCEPTION
    WHEN OTHERS THEN
      V_RETCODE := SQLCODE;
      V_RETCODE := SUBSTR(SQLERRM, 1, 200);
  END;