Oracle视图、事务

--1、视图  view
--创建视图
create or replace view v_stu
as
select "StudentNo","StudentName","Phone","Address"
from "Student";

select * from v_stu;  --查询视图

drop view v_stu; --删除视图

--创建视图2   视图嵌套
create or replace view v_stu2 as
select "Result"."StudentNo",v_stu."StudentName","Result"."StudentResult"
from v_stu inner join "Result" on v_stu."StudentNo"="Result"."StudentNo";





--事务
--创建一个银行表  bank
create table "bank"(
"userName" VARCHAR2(20),
"money" number(10)
);

--给bank表添加检查约束
alter table "bank"
add constraint ck_money check("money">=1);
--添加数据
insert all 
into "bank" VALUES('张三',1000)
into "bank" VALUES('李四',1)
select 1 from dual;


--模拟转账
BEGIN
update "bank" set "money"="money"+1000 where "userName"='李四';
--  if sql%rowcount =0 then  --如果此条语句运行失败
--    rollback;  --回滚
--  end if;

update "bank" set "money"="money"-1000 where "userName"='张三';
--  if sql%rowcount =0 then  --如果此条语句运行失败
--    rollback;  --回滚
--  end if;

commit; --提交
EXCEPTION
when others then
rollback;--回滚
END;

select * from "bank";



--索引
--创建索引默认是B树索引
create index i_stuNo on "Student"("StudentNo");


--反向键索引
create index i_inCard on "Student"("IdentityCard") reverse;


--删除索引
drop index i_inCard;