Oracle学习_02

视图

概念:视图就是提供一个查询的窗口,所有数据来自于原表

--查询语句创建表

create view v_emp as select * from scott.emp;

--创建视图[必须要有dba权限]

create view v_emp as select ename,job from emp;

--查询视图

select v_emp ;

--修改视图[不推荐使用]

update v_emp set job='CLERK' where ename='ALLEN';

commit;

--创建只读视图

create view v_emp1 as select ename ,job from emp with read only;

–视图的作用:

–1.视图可以屏蔽掉一些敏感字段

–2.保证总部和分部数据及时统一

索引

–概念:

–索引就是在表的列上构建一个二叉树,达到大幅度提高查询效率的目的,但是索引会影响增删改的效率

–类似于数组,以下标的形式去存储和访问表中的数据,索引可以是不连续的

索引索引索引,待更biubiubiubiubiuMark

单列索引

--创建单列索引

create index idx_ename on emp(ename);

--单列索引触发规则,条件必须是索引列的原始值.

--单行函数,模糊查询,都会影响索引的触发

select * from emp where ename='SCOTT'

--复合索引

--创建复合索引

create index idx_enamejob on emp(ename,job);

复合索引

–复合索引中第一列为优先检索列

–如果想要触发复合索引,必须包含有优先搜索列中的原始值

select * from emp where ename ='SCOTT' and job='XX';  --触发复合索引

select * from emp where ename ='SCOTT' or job='XX';  --不触发索引

select * from emp where ename ='SCOTT'  --触发单列索引

pl/sql编程语言

–是对sql语言的扩展,似的sql语言具有过程化编程的特性.比一般的过程化编程语言更加灵活高效.

–主要用来编写存储过程和存储函数等

基本操作

–声明方法

declare
i number(2)  := 10;

s varchar2(10) := '小明';

ena emp.ename%type;  --引用型变量

emprow emp%rowtype; --记录型变量

begin

dbms_output.put_line(i);

dbms_output.put_line(s);

select ename into ena from emp where empno = 7788;

dbms_output.put_line(ena);

select  * into emprow from emp where empno = 7788;

dbms_output.put_line(emprow.ename|| '的工作为:' ||emprow.job);

end;

–pl/sql中的if判断

–输入小于18的数字,输出未成年

–输入大于18小于40的数字,输出中年人

–输入大于40的数字,输出老年人

DECLARE	
i NUMBER ( 3 ) := &ii;
BEGIN
	IF
		i < 18 THEN
			dbms_output.put_line ( '未成年' );
	elseif i < 40 THEN
		dbms_output.put_line ( '中年人' );
		ELSE dbms_output.put_line ( '未成年' );	
	END IF;
END;

–pl/sql中的loop循环
–用三种方式输出1到10是个数字
–while循环

DECLARE
	i NUMBER ( 2 ) := 1;
BEGIN
	while i < 11 loop
	dbms_output.put_line ( i );
	i := i + 1;	
	END loop;
END;

–exit循环

DECLARE 
	i NUMBER ( 2 ) := 1;
BEGIN 
	loop 
	exit WHEN i > 10;
	dbms_output.put_line ( i );
	i := i + 1;
	END loop;
END;

–for循环

declare
begin
	for i in 1..10 loop
		dbms_output.put_line(i);
		end loop;
end;

–游标:游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果,

–通俗的说:游标是一段内存区域,用于暂时存放sql语句受影响的数据.将这些数据放到一个内存区域的虚表中,而这个虚表就是游标

–作用:

1.数据库是具有回滚功能的，游标在其中有重要的作用。由于对数据库的操作会暂时放在游标中，只要在提交的时候出现异常，我们就可以根据游标就行回滚，在一定意义上有利于数据库的安全。

2.在Oracle中，PL/SQL只能返回单行数据，而游标的出现，可以返回多条数据。

–游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

–输出emp表中所有的员工的姓名

DECLARE 
	cursor c1 IS SELECT * FROM emp;
	emprow emp % rowtype;
BEGIN
	open c1;
	loop
	FETCH c1 INTO emprow;
	exit 
	WHEN c1 % notfound;
	dbms_output.put_line ( emprow.ename );
	
END loop;
close c1;

END;

–给指定部门员工涨工资

declare
	--定义游标
	cursor c2(eno emp.deptno%type)
	is select empno from emp where deptno = eno;
	en emp.empno%type;
begin
	--打开游标
	open c2(10);
		loop
			--提取游标
			fetch c2 into en;
			exit when c2%notfound;
			update emp set sal=sal+100 where empno=en;
			commit;
			end loop;
			--关闭游标
			close c2;
end;
--查询10号部门员工信息
select * from emp where deptno = 10;

–存储过程

–存储过程:存储过程就是提前已经编译好的一段pl/sql语言,放置在数据库端

–可以直接被调用.这一段pl/sql一般都是固定步骤的业务

–给指定员工涨100块钱

create procedure p1(eno emp.empno%type)
is
begin 
	update emp set sal=sal+100 where empno = eno;
	commit;
end;

select * from emp where empno = 7788;
--测试p1
declare

begin
	p1(7788);
end;

–通过存储函数实现员工的年薪

–存储过程和存储函数的参数都不能带长度

–存储函数的返回值类型不能带长度

CREATE 
	OR REPLACE FUNCTION f_yearsal ( eno emp.empno % TYPE ) return NUMBER IS s NUMBER ( 10 );
BEGIN
	SELECT
		sal * 12+nvl ( comm, 0 ) INTO s 
	FROM
		emp 
	WHERE
		empno = eno;
return s;
END;

--测试 f_yearsal
--存储函数在调用的时候,返回值需要接收
declare 
s number(10);
begin 
	s := f_yearsal(7788);
	dbms_output.put_line(s);
	end;

–out类型参数如何使用

–使用存储过程来算年薪

CREATE 
	OR REPLACE PROCEDURE p_yearsal ( eno emp.EMPNO % TYPE, yearsal OUT NUMBER )
IS 
	s NUMBER ( 10 );
	c EMP.COMM % TYPE;
BEGIN
	SELECT
		sal * 12,
		nvl( comm, 0 ) INTO s,
		c 
	FROM
		emp 
	WHERE
		empno = eno;
	yearsal := s + c;
END;

--测试p_yearsal
DECLARE
	yearsal NUMBER ( 10 );
BEGIN
	p_yearsal ( 7788, yearsal );
	dbms_output.put_line ( yearsal );
END;

in和out类型参数的区别是什么?

凡是涉及到into查询语句赋值或者:=赋值操作的参数,都必须使用out来修饰.

存储过程和存储函数的区别

语法上:关键字不一样,存储函数比存储过程多了两个return

本质上:存储函数有返回值,而存储过程没有返回值.

如果存储过程想实现有返回值的业务,这时我们需要使用out类型的参数

即使是存储过程使用了out类型的参数,其本质也不是真的有了返回值

而是在存储过程内部给给out类型参数赋值,在执行完毕后,我们直接拿到输出类型参数的值

–我们可以使用存储函数有返回值的特性,来定义函数

–而存储过程不能来定义函数

–案例需求;查询出员工姓名.员工所在部门名称

–案例准备工作:把SCOTT用户下的dept表复制到当前用户下

create table dept as select * from SCOTT.dept;
--使用传统方式来实现案例需求
select e.name ,d.name 
from emp e,dept d
where e.deptno = d.deptno;
--使用存储函数来实现提供一个部门编号,输出一个部门名称
CREATE 
	OR REPLACE FUNCTION fdna ( dno dept.DEPTNO % TYPE ) return dept.DNAME % TYPE 
	IS 
		dna dept.DNAME % TYPE;
BEGIN
	SELECT
		DNAME INTO dna 
	FROM
		dept 
	WHERE
		deptno = dno;
return dna;
END;
--使用fdna存储函数来实现案例需求,查询出员工姓名,员工所在部门名称.
select e.ename, fdna(e.deptno)
from emp e;

–触发器,就是制定一个规则,在我们做增删改操作的时候,只要满足该规则,自动触发,无需调用,也不能调用

–触发器分语句级触发器和行级触发器

语句级的触发器可以在某些语句执行前或执行后触发.不包含有for each row的触发器

行级触发器则是在定义了触发的表的行数据改变时就会被触发一次,包含有for each row

for each row是为了使用:old或者:new对象或者一行记录.

语句级触发器

--插入一条记录,输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare

begin
  dbms_output.put_line('一个新员工入职');
end;
--触发t1
insert into person values (1, '小红');
commit;
[dbms导出:
一个新员工入职]

行级别触发器

–不能给员工降薪

–raise_application_error(-20001~-20999之间, ‘错误提示信息’);

CREATE or REPLACE TRIGGER t2
before
update
on emp
for each row --行级
DECLARE

BEGIN
	if :old.sal>:new.sal then 
		raise_application_error(-20001,'不能给员工降薪');
	end if;
end;
--触发t2
select * from emp where empno = 7788;
update emp set sal =sal-1 where empno = 7788;
commit;
------------------------------
信息:
update emp set sal =sal-1 where empno = 7788
> ORA-20001: 不能给员工降薪
  ORA-06512: at "SCOTT.T2", line 5
  ORA-04088: error during execution of trigger 'SCOTT.T2'
  
> 时间: 0.002s
------------------------------

–触发器实现主键自增.[行级触发器]

–分析:在用户做插入操作之前,拿到即将插入的数据,给该数据的主键列赋值

create or replace trigger auid
before
insert
on person
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;
--查询person表数据
select * from person;
--使用auid实现主键自增
insert into person (pname) values ('a');
commit;
insert into person values (1, 'b');
commit;

如何在Navicat中查看触发器?

一、选中指定的database，然后再依次选中“其它”、“触发器”

二、查看已写好的触发器语句

选中触发器–>DDL

触发器