Oracle 表创建实用语句

1、创建

create table T_YH_HUIPANFILEENTRY(
FID INTEGER,
FENTRYID INTEGER,
FFILENAME VARCHAR(50),
FSHOUDANWEINO VARCHAR(50),
FSHOUDANWEINAME VARCHAR(200),
FORDERNO VARCHAR(50),
FZHIFUFS varchar(50),
FJIAOYIDATE DATE,
FJIAOKUANAMOUNT VARCHAR(20),
FTUIKUANAMOUNT VARCHAR(20),
FYU_E VARCHAR(20),
FJIAOYITYPE VARCHAR(2),
FZHIFUDATE VARCHAR(6),
FQUDAONO VARCHAR(200),
FZIJINLIULIANG VARCHAR(20),
FJIAOKUANRNAME VARCHAR(100),
FQINGSUANZT VARCHAR(5),
FSHOUXUFEI VARCHAR(15),
FBEIYONG1 VARCHAR(100),
FBEIYONG2 VARCHAR(100),
FBEIYONG3 VARCHAR(100)
)

2、给表添加注释

COMMENT ON TABLE T_YH_HUIPANFILEENTRY IS '主表主键';

3、字段添加注释

COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FID IS '主表主键';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FENTRYID IS '子表主键';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FFILENAME IS '文件名';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FSHOUDANWEINO IS '执收单位编码';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FSHOUDANWEINAME IS '执收单位名称';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FORDERNO IS '订单号';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FZHIFUFS IS '支付方式';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FJIAOYIDATE IS '交易日期';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FJIAOKUANAMOUNT IS '缴款金额';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FTUIKUANAMOUNT IS '退款金额';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FYU_E IS '余额';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FJIAOYITYPE IS '交易类型';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FZHIFUDATE IS '支付完成时间';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FQUDAONO IS '渠道流水号';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FZIJINLIULIANG IS '资金流向';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FJIAOKUANRNAME IS '缴款人名称';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FQINGSUANZT IS '清算状态';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FSHOUXUFEI IS '手续费';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FBEIYONG1 IS '备用字段1';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FBEIYONG2 IS '备用字段2';
COMMENT ON COLUMN T_YH_HUIPANFILEENTRY.FBEIYONG3 IS '备用字段3';

4、自增列

create sequence T_YH_HUIPANFILE_SEQ  --序列名 （TEST_SEQ 为序列名，随便取名）­ 
increment by 1 --每次增加1 ­ 
start with 1 --从1开始 ­ 
nomaxvalue --没有最大值 ­ 
nocache --没有缓存序列­ 或者 cache 20 缓存 20 个

自增列使用

select T_YH_HUIPANFILE_SEQ.currval from dual; --查询当前的序列值
select T_YH_HUIPANFILE_SEQ.nextval from dual; --查询当前自增后的序列值

5、游标，存储过程返回结果集

CREATE OR REPLACE PACKAGE query_pkg is
  type Query_cur is ref cursor;
end query_pkg ;

6、存储过程返回结果集

CREATE OR REPLACE PROCEDURE CS_SelT_YH_HUIPANFILEENTRYAll( T_YH_HUIPANFILEENTRY_Cursor out query_pkg.Query_cur)
is
begin
  open  T_YH_HUIPANFILEENTRY_Cursor for
    SELECT
    *
    FROM
    T_YH_HUIPANFILEENTRY
    ;
end CS_SelT_YH_HUIPANFILEENTRYAll;
--endregion