这个是表
create table T_BUSI_ORDER_DTL
(
ORDER_ID NUMBER(6) not null,
ORDER_DATE CHAR(8),
ORDER_TYPE CHAR(1),
ORDERCHANNEL CHAR(1),
BRH_ID CHAR(9),
ASSIGN_FLAG CHAR(1) default 0,
ASSIGN_DATE CHAR(8),
ASSIGN_OPR_ID CHAR(10),
PROCESS_DEADLINE CHAR(8),
PROCESS_REQUEST VARCHAR2(200),
PROCESS_OPR_ID CHAR(10),
PROCESS_FLAG CHAR(1) default 0,
PROCESS_DTL VARCHAR2(200),
CUST_NAME CHAR(10),
PAPER_NO CHAR(18),
SEX CHAR(2),
AGE NUMBER(3),
EDUCATION VARCHAR2(20),
CAREER VARCHAR2(20),
ADDRESS VARCHAR2(20),
PHONE VARCHAR2(20),
EMAIL VARCHAR2(60),
POST_CODE CHAR(6),
JOB_PROPERTY VARCHAR2(60),
RISK_LEVEL CHAR(10),
FAMILY_MONEY VARCHAR2(60),
OPEN_BUSINESS VARCHAR2(60),
INVEST_FUND VARCHAR2(60),
INVEST_CONDUCT VARCHAR2(60),
INVEST_INSU VARCHAR2(60),
INVEST_DEBT VARCHAR2(60),
INVEST_OTHER VARCHAR2(100),
INVEST_PROBLEM VARCHAR2(500),
HOUSE_TYPE VARCHAR2(10),
HOUSE_LEVEL VARCHAR2(10),
HOUSE_AREA VARCHAR2(20),
COM_HOUSE_TYPE VARCHAR2(10),
TEND_TRADE VARCHAR2(20),
TEND_NAME VARCHAR2(60),
TEND_ADDRESS VARCHAR2(60),
TEND_LICENSE VARCHAR2(4),
TEND_YEAR VARCHAR2(10),
YEAR_INCOME VARCHAR2(20),
LOAN_USE VARCHAR2(500),
LOAN_AMT VARCHAR2(30),
USER_TIME VARCHAR2(30),
ASSURE_TYPE VARCHAR2(100),
LAST_PROCESS_DATE CHAR(8)
)
;
create index T_BUSI_ORDER_DTL_BI_AF on T_BUSI_ORDER_DTL (BRH_ID, ASSIGN_FLAG);
create index T_BUSI_ORDER_DTL_OD_BI on T_BUSI_ORDER_DTL (ORDER_DATE, BRH_ID);
create unique index T_BUSI_ORDER_DTL_OI on T_BUSI_ORDER_DTL (ORDER_ID);这个是序列的脚本
create sequence BUSIORDERDTLREC
minvalue 1
maxvalue 999999
start with 1
increment by 1
cache 5
order;
我用的是pl/sql developer用页面做的,分别在表和序列里添加的2个
然后怎么实现表里的 order_id自动增量
另外序列怎么导出脚本呢?
create table T_BUSI_ORDER_DTL
(
ORDER_ID NUMBER(6) not null,
ORDER_DATE CHAR(8),
ORDER_TYPE CHAR(1),
ORDERCHANNEL CHAR(1),
BRH_ID CHAR(9),
ASSIGN_FLAG CHAR(1) default 0,
ASSIGN_DATE CHAR(8),
ASSIGN_OPR_ID CHAR(10),
PROCESS_DEADLINE CHAR(8),
PROCESS_REQUEST VARCHAR2(200),
PROCESS_OPR_ID CHAR(10),
PROCESS_FLAG CHAR(1) default 0,
PROCESS_DTL VARCHAR2(200),
CUST_NAME CHAR(10),
PAPER_NO CHAR(18),
SEX CHAR(2),
AGE NUMBER(3),
EDUCATION VARCHAR2(20),
CAREER VARCHAR2(20),
ADDRESS VARCHAR2(20),
PHONE VARCHAR2(20),
EMAIL VARCHAR2(60),
POST_CODE CHAR(6),
JOB_PROPERTY VARCHAR2(60),
RISK_LEVEL CHAR(10),
FAMILY_MONEY VARCHAR2(60),
OPEN_BUSINESS VARCHAR2(60),
INVEST_FUND VARCHAR2(60),
INVEST_CONDUCT VARCHAR2(60),
INVEST_INSU VARCHAR2(60),
INVEST_DEBT VARCHAR2(60),
INVEST_OTHER VARCHAR2(100),
INVEST_PROBLEM VARCHAR2(500),
HOUSE_TYPE VARCHAR2(10),
HOUSE_LEVEL VARCHAR2(10),
HOUSE_AREA VARCHAR2(20),
COM_HOUSE_TYPE VARCHAR2(10),
TEND_TRADE VARCHAR2(20),
TEND_NAME VARCHAR2(60),
TEND_ADDRESS VARCHAR2(60),
TEND_LICENSE VARCHAR2(4),
TEND_YEAR VARCHAR2(10),
YEAR_INCOME VARCHAR2(20),
LOAN_USE VARCHAR2(500),
LOAN_AMT VARCHAR2(30),
USER_TIME VARCHAR2(30),
ASSURE_TYPE VARCHAR2(100),
LAST_PROCESS_DATE CHAR(8)
)
;
create index T_BUSI_ORDER_DTL_BI_AF on T_BUSI_ORDER_DTL (BRH_ID, ASSIGN_FLAG);
create index T_BUSI_ORDER_DTL_OD_BI on T_BUSI_ORDER_DTL (ORDER_DATE, BRH_ID);
create unique index T_BUSI_ORDER_DTL_OI on T_BUSI_ORDER_DTL (ORDER_ID);这个是序列的脚本
create sequence BUSIORDERDTLREC
minvalue 1
maxvalue 999999
start with 1
increment by 1
cache 5
order;
我用的是pl/sql developer用页面做的,分别在表和序列里添加的2个
然后怎么实现表里的 order_id自动增量
另外序列怎么导出脚本呢?
pl/sql 工具——导出用户对象
导出sequence的脚本:
select dbms_metadata.get_ddl('SEQUENCE','you_sequence') FROM DUAL;
红色部分必须大写。
第二种:序列,触发器结合
CREATE TRIGGER TRG_T_BUSI BEFORE
INSERT ON T_BUSI_ORDER_DTL
FOR EACH ROW begin
SELECT BUSIORDERDTLREC.NEXTVAL
INTO :NEW.ORDER_ID
FROM DUAL;
End TRG_T_BUSI;
/
FROM DUAL;
这两句是不是就是固定格式呢?new是不是意思就是新增?FROM dual完全不懂
另外 我用pl/sql developer 是不是直接在左边那个菜单,直接点触发器新建呢,还是要在所选的表里面建触发器呢?
我刚才发的那个序列是直接在左边菜单,点序列新建的
因为对这个东西 刚刚接触 很多不明白
#2+#3你就明白了
2、另外 我用pl/sql developer 是不是直接在左边那个菜单,直接点触发器新建呢,还是要在所选的表里面建触发器呢?
直接创建
3、这两句是不是就是固定格式呢?new是不是意思就是新增?FROM dual完全不懂
是的 :NEW.ORDER_ID 是存放BUSIORDERDTLREC.NEXTVAL 的值的
from dual,dual 是系统表,可以查很多东西
4、要是不懂的话,最好的学习方法是实践,自己练习琢磨~~~~