使用Oracle时遇到的一些问题

1. 必要时候重启sql plus
   在eclipse中查询数据库中的信息 和 直接在sql plus中查找出来的信息不一致
   百思不得其解后,只好死马当活马医,先重启了一下eclipse,发现没卵用,然后重启了一下
   sql plus , 再用eclipse查询时,两者的结果就一致了,具体原因到现在还没搞明白,反正重启这个操作我会一直记着的…
2. 使用触发器时候的问题
   想在删除操作的时候使用触发器,得用old.userid
   想更新正在插入的数据时,得用before语句,用after不可以更新。

附上项目中对数据库操作的代码：
–创建表空间
create tablespace tbs_yxlm
datafile ‘F:\yxlm\yxlm.dbf’
size 100m
autoextend on next 20m
maxsize 200m;

–创建临时表空间
create temporary tablespace temp_yxlm
tempfile ‘F:\yxlm\yxlm.tpf’ size 100m;

–创建用户并分配表空间
create user lly identified by 123
default tablespace tbs_yxlm
temporary tablespace temp_yxlm
quota unlimited on tbs_yxlm;

–授予新建用户权限
grant connect,resource to lly;

–连接新建用户
conn lly/123;

–创建表
create table hero(
heroid number(11) primary key,
name varchar2(64) not null,
position varchar2(64) not null,
skill_q varchar2(64) not null,
skill_w varchar2(64) not null,
skill_e varchar2(64) not null,
skill_r varchar2(64) not null,
cet number(3)
);
create table hero_eq(
position varchar2(64) not null,
eq varchar2(255) not null,
primary key (position,eq)
);

–创建序列实现英雄id自增
CREATE SEQUENCE seq_heroid
MAXVALUE 999999
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER cet_tri
BEFORE INSERT
ON hero
FOR EACH ROW
BEGIN
select seq_heroid.nextval into :new.heroid
from dual;
END;
/

–创建触发器实现 hero_eq装备增加hero推荐装备+1
CREATE OR REPLACE TRIGGER eq_tri AFTER
INSERT ON hero_eq FOR EACH ROW
BEGIN
UPDATE hero SET cet=cet+1
WHERE position=:NEW.position;
END;
/

–创建触发器实现 hero_eq装备增加hero推荐装备-1
CREATE OR REPLACE TRIGGER eq_delete_tri BEFORE
DELETE ON hero_eq FOR EACH ROW
BEGIN
UPDATE hero SET cet=cet-1
WHERE position=:OLD.position;
END;
/
–正确
CREATE OR REPLACE TRIGGER addhero_tri BEFORE
INSERT ON hero FOR EACH ROW
BEGIN
select count(*) into :new.cet from hero_eq where hero_eq.position=:new.position;
END;
/

UPDATE hero SET cet=(select count(*) from hero_eq where hero_eq.position=‘射手’)
WHERE 1=1;

–插入数据
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘寒冰’, ‘射手’, ‘射手的专注’,‘万箭齐发’,‘鹰击长空’,‘魔法水晶箭’,0);
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘sad’, ‘射手’, ‘射手的专注’,‘万箭齐发’,‘鹰击长空’,‘魔法水晶箭’,0);
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘盖伦’, ‘坦克’, ‘致命打击’,‘勇气’,‘审判’,‘德玛西亚正义’,0);
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘亚索’, ‘刺客’, ‘斩钢闪’,‘风之障壁’,‘踏前斩’,‘狂风绝息斩’,0);
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘诡术妖姬’, ‘法师’, ‘恶意魔印’,‘魔影迷踪’,‘幻影锁链’,‘故技重施’,0);
INSERT INTO hero(name,position,skill_q,skill_w,skill_e,skill_r,cet) VALUES ( ‘ooo’, ‘法师’, ‘恶意魔印’,‘魔影迷踪’,‘幻影锁链’,‘故技重施’,0);

INSERT INTO hero_eq values(‘射手’,‘攻速鞋,无尽,破败,飓风,绿叉,兰盾’);
INSERT INTO hero_eq values(‘坦克’,‘布甲鞋,日炎,反甲,振奋,狂徒,兰盾’);
INSERT INTO hero_eq values(‘法师’,‘法穿鞋,卢登,鬼书,折磨,帽子,金身’);
INSERT INTO hero_eq values(‘刺客’,‘抵抗鞋,幽梦,岚切,幕刃,黑切,锋刃’);
INSERT INTO hero_eq values(‘射手’,‘抵抗鞋,幽梦,岚切,幕刃,黑切,锋刃’);
INSERT INTO hero_eq values(‘战士’,‘抵抗鞋,黑切,破军,反甲,魔女,复活甲’);

set pagesize 120;
set lin 120;
col heroid format 99999;
col name format a15;
col position format a15;
col skill_q format a15;
col skill_w format a15;
col skill_e format a15;
col skill_r format a15;
col cet format 99999;
col eq format a50;

select * from hero where heroid not in (select heroid from hero where rownum<=2) and rownum<=5 order by heroid;