public int getCountitems(String workid) throws Exception { Connection conn = null; Statement st = null; ResultSet rs = null; String sql = "select max(items) from ArchivesManage where workid ='"+workid+"'"; try { int intItems = 0; conn = ds.getConnection(); st = conn.createStatement(); rs = st.executeQuery(sql); while (rs.next() ){ intItems = rs.getInt(1); } rs.close(); st.close(); close(conn); return intItems; } catch (Exception e) { System.out.println("getMYCountItems = " + e.toString()); rs.close(); st.close(); close(conn); throw e; } }
这个问题很多人问过了,我试过一种比较简单的解决方法:先建立SEQUENCE create sequence name increment by n start with i maxvalue j;eg:CREATE SEQUENCE "BBB"."ID" INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER插入sequence value insert into table_name (column_name,new_column_name) values (value1,sequence_name.nextval);eg: "insert into bbb.Users(id,UserID,Name,Email,Address) values(bbb.id.nextval,""+userid+"",""+name+"",""+email+"",""+address+"")");
我试过下面这种方法: create or replace trigger T_VehicleState before insert on VehicleState n1 number; begin select max(Id) into n1 from VehicleState; new.id=n1+1; end; 但是在SQL*Plus里面给出的错误是: n1 number; * ERROR 位于第 3 行: ORA-04079: 无效的触发器说明怎么回事呢?还有,这种方法好不好? select max(Id) into n1 from VehicleState; 里面的ID是什么东西?
我不太喜欢用这种方法。ID是VehicleState表中自增的字段。 我推荐下面的方法:1。先建SEQUENCE。2。建TRIGGER。语句如下: CREATE OR REPLACE TRIGGER T_A_MemberShipID BEFORE INSERT ON AnnounceMembership REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF :new.MemberShipID IS NULL THEN SELECT S_A_MemberShipID.NEXTVAL INTO :new.MemberShipID FROM dual; END IF; END; / 其中AnnounceMembership是表名,MemberShipID是AnnounceMembership表中的自增字段。
搞定了,谢谢各位。我用以下的办法 CREATE OR REPLACE TRIGGER T_A_MemberShipID BEFORE INSERT ON AnnounceMembership REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN SELECT S_A_MemberShipID.NEXTVAL INTO :new.MemberShipID FROM dual;
(2)nowID = id +1
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String sql = "select max(items) from ArchivesManage where workid ='"+workid+"'";
try {
int intItems = 0;
conn = ds.getConnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
while (rs.next() ){
intItems = rs.getInt(1);
}
rs.close();
st.close();
close(conn);
return intItems;
} catch (Exception e) {
System.out.println("getMYCountItems = " + e.toString());
rs.close();
st.close();
close(conn);
throw e;
}
}
create sequence name
increment by n
start with i
maxvalue j;eg:CREATE SEQUENCE "BBB"."ID" INCREMENT BY 1 START WITH 1 MAXVALUE
9999 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER插入sequence value
insert into table_name (column_name,new_column_name)
values (value1,sequence_name.nextval);eg:
"insert into bbb.Users(id,UserID,Name,Email,Address) values(bbb.id.nextval,""+userid+"",""+name+"",""+email+"",""+address+"")");
create or replace trigger T_VehicleState
before insert on VehicleState
n1 number;
begin
select max(Id) into n1 from VehicleState;
new.id=n1+1;
end;
但是在SQL*Plus里面给出的错误是:
n1 number;
*
ERROR 位于第 3 行:
ORA-04079: 无效的触发器说明怎么回事呢?还有,这种方法好不好?
select max(Id) into n1 from VehicleState;
里面的ID是什么东西?
我推荐下面的方法:1。先建SEQUENCE。2。建TRIGGER。语句如下:
CREATE OR REPLACE TRIGGER T_A_MemberShipID
BEFORE INSERT ON AnnounceMembership
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
IF :new.MemberShipID IS NULL THEN
SELECT S_A_MemberShipID.NEXTVAL
INTO :new.MemberShipID
FROM dual;
END IF;
END;
/
其中AnnounceMembership是表名,MemberShipID是AnnounceMembership表中的自增字段。
CREATE OR REPLACE TRIGGER T_A_MemberShipID
BEFORE INSERT ON AnnounceMembership
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
SELECT S_A_MemberShipID.NEXTVAL
INTO :new.MemberShipID
FROM dual;
END T_A_MemberShipID;