insert into jr_xmzyzdsyj
(xmid, yjlx, fxmc, fxyj, time, yjid)
select
'xm0205254', 01, 01, '555454', sysdate,( DEPT_DEPTID_ZYZDSYJ.nextval)
from dual
UNION ALL
select
'xm0205254', 01, 02, '', sysdate, DEPT_DEPTID_ZYZDSYJ.nextval
from dual如题,DEPT_DEPTID_ZYZDSYJ.nextval是序号,在这不能用,求相同功能的语句
(xmid, yjlx, fxmc, fxyj, time, yjid)
select
'xm0205254', 01, 01, '555454', sysdate,( DEPT_DEPTID_ZYZDSYJ.nextval)
from dual
UNION ALL
select
'xm0205254', 01, 02, '', sysdate, DEPT_DEPTID_ZYZDSYJ.nextval
from dual如题,DEPT_DEPTID_ZYZDSYJ.nextval是序号,在这不能用,求相同功能的语句
我试着好用呢。
CREATE SEQUENCE NTEMSS.DEPT_DEPTID_ZYZDSYJ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOORDER
CACHE 20
/
INSERT INTO jr_xmzyzdsyj (xmid, yjlx, fxmc, fxyj, time, yjid)
SELECT t.*,DEPT_DEPTID_ZYZDSYJ.NEXTVAL FROM (
SELECT 'xm0205254' xmid, 01 yjlx, 01 fxmc, '555454' fxyj, SYSDATE time FROM DUAL UNION ALL
SELECT 'xm0205254', 01, 02, '', SYSDATE FROM DUAL
) t
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator --问题所在■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constrain
sysdate,
(DEPT_DEPTID_ZYZDSYJ.nextval)
from TEST aa
1.直接插入报:无效字符 ;
2.用union 或union all报:此处不能使用序号
用UNION, INTERSECT, MINUS合并的select语句中是无法使用序列的!!!
直接插入数据的时候,检查字段数量和字段类型是否匹配
1楼那个和这个
yjlx,fxmc这两个是什么类型的
create table JR_XMZYZDSYJ
(
XMID VARCHAR2(10) not null,
YJLX VARCHAR2(10),
FXMC VARCHAR2(10),
FXYJ VARCHAR2(1000),
TIME DATE,
YJID NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
);
你把两个语句写在一行了,分号之后换行,像下面
insert into jr_xmzyzdsyj
(xmid, yjlx, fxmc, fxyj, time, yjid)
values
('xm0205254', '01', '01', '555454', sysdate, DEPT_DEPTID_ZYZDSYJ.nextval);
insert into jr_xmzyzdsyj
(xmid, yjlx, fxmc, fxyj, time, yjid)
values
('xm0205254', 01, 02, '', sysdate, DEPT_DEPTID_ZYZDSYJ.nextval);
localPs = localConn.prepareStatement(sql.toString());
localRs = localPs.executeQuery();