oracle_7(序列、索引、同义词、 PLSQL) 课堂笔记

序列：

模拟自增，本质就是内存中的数组

创建格式：

	create sequence 序列名;
	increment by 步长;		默认是1
	start with 起始值;		默认是1
	maxvalue	nomaxvalue
	minvalue	nominvalue
	cycle		nocycle
	cache		nocache

两个属性： currval 当前值， nextval 下一个值
序列会接着上一次的值继续使用，不会归零

查看序列

	select *from user_sequences;

示例：

新建一个表
	create table person(pid number primary key,pname varchar2(10));
插入数据
	insert into person values(myseq.nextval,'zs');
	insert into person values(myseq.nextval,'ls');
	insert into person values(myseq.nextval,'ww');
创建自定义序列
	create sequence myseq1
	increment by 2
	start with 1
	maxvalue 9
	minvalue 1
	cycle
	cache 3;
	cache元素的个数 <= 循环元素
	1 3 5 7 1 3 5 7 ....

循环序列不能用于给 主键/唯一约束的键 赋值

裂缝：

	[1,2,3,4,.....20]
	出现断电、异常、回滚、多表使用同一个序列...
	使用过属性nextval，但是没有存入数据，下次再次使用时，中间有裂缝 

修改序列

	alter sequence myseq
	increment by 2;
	只对修改以后的操作有效

删除序列：

	drop sequence myseq;

索引：

类似于书的目录

索引类型： 默认B树索引（默认） 位图索引

创建索引：

	create index 索引名;
	注意： 主键默认就是索引
单层：create index myindex on emp(deptno);
多层：create index myindex1 on emp(deptno,sal);

什么时候适合建立索引？

	数据集中的列，经常在where中使用的列，数据量比较大
	数据集中的列：主键列，不集中，但是因为需要频繁使用， 也适合建索引。

删除索引：

	drop index myindex;

同义词(别名)

数据库对象（表 视图 索引 …） 起别名 (默认是私有/专用的)

查看hr: employees;

查看其他用户的表，报错“表或视图不存在”  也可能是权限不足导致的
去sys账户授权：  
	grant xxx to 用户名;		grant select on he.employees to scott;
	revoke xxx from 用户名;

查询：

	select count(*) from hr.employees;		名字太长

起别名：

		create synonym hremp for hr.employees;

再查询时可以

	select count(*) from hremp;

创建共有同义词：

	create public synonym hremp2 for hr.employees;

删除同义词： drop sysnonym 同义词名;
删除共有同义词： drop public sysnonym 同义词名;
共有同义词的创建和删除，一般建议由管理员来操作。

PLSQL: 对SQL进行编程

开发工具

plsql developer
oracle sql developer

plsql:

declare
	变量、常量、光标（游标）、例外（自定义异常）
begin
	写代码
	exception
		捕获异常
end;

引用型变量：

	pname emp.ename%type;		能保存一个值

记录型变量：（相当于java中的对象，同时保存多个变量值）

	emp_info emp%rowtype;			能保存一行值
	SELECT * into emp_info FROM emp where empno = 7788;

打印：

	dbms_output.put_line(emp_info.empno ||'--'|| emp_info.ename ||'--'|| emp_info.job); 

条件语句if:

1.
	if 条件 then...
	end if;
2.
	if 条件 then...
	else ....
	end if;
3.
	if 条件 then...
	elsif 条件 then...
	else ...
	end if;

循环结构 while do…while for

1.
	while 条件 
	loop
	...
	end loop;
2.
	loop
	...
	exit when 退出条件;
	end loop;
3.
	for i in 1..10
	loop
	...
	end loop;

计算 1-5之和

set SERVEROUTPUT ON;
declare
		pnum number:=1;
		psum number :=0;
begin
	loop
		exit when pnum>5;
		psum := psum+pnum;
		pnum := pnum +1;
	end loop; 
	dbms_output.put_line(psum);
end;

游标（光标） cursor:集合

定义语法:

		cursor 光标名（参数列表）
		is
		select ...

光标的属性

		%isopen				判断光标是否打开
		%rowcount			读取的个数
		%found				判断下一行是否存在数据
		%notfound

查询并打印全部员工的姓名、薪水

setSERVEROUTPUT ON;
declare
		cursor cemp is select ename,sal from emp;
		pename emp.ename%type;
		psal emp.sal%type;
begin
	open cemp;  						--1.打开光标
	loop        						--2.定义循环，准备获取每一行数据
		fetch cemp into pename,psal; 	--3.一行一行的获取光标的值
		exit when cemp%notfound;		--4.使用数据，写跳出循环条件
		dbms_output.put_line(pename||'的工资是'||psal);--
	end loop;
	close cemp; 						--5.关闭光标
end;

涨工资： president 1000，manger 800 其他 400

set SERVEROUTPUT ON;
declare
		cursor cemp is select empno,job from emp;
		pempno emp.empno%type;
		pjob emp.job%type;
begin
	open cemp;  
	loop        
		fetch cemp into pempno,pjob; 
		exit when cemp%notfound;
		if pjob = 'PRESIDENT' then 
			update emp set sal = sal+1000 where empno = pempno;
		elsif pjob = 'MANAGER' then 
			update emp set sal = sal+800 where empno = pempno; 
		else update emp set sal = sal+400 where empno = pempno;
		end if;
	end loop;
	dbms_output.put_line('成功');
	close cemp; --5.关闭光标
	commit; --ACID  oracle read commit,  一边不提交，另一边访问不到
end;

查询某个部门的员工姓名，用带参数的光标来做

	set serveroutput on;
	declare
		cursor cename(dno number) is select ename from emp where deptno = dno;
		pename emp.ename%type;
	begin
		open cename(30);
		loop
			fetch cename into pename;
			exit when cename%notfound;
			dbms_output.put_line(pename);
		end loop;
		close cename;
	end;

例外（异常）：

系统例外；

no_data_found			没有数据错误
Too_many_rows			行数过多
Value_error	：			算术或转换错误
TimeOut_on_resource:	资源等待超时

系统例外的使用

setserveroutput on;
declare
	pnum number;
begin
   pnum := 1/0;
   exception
		when zero_divide then dbms_output.put_line('0不能作为除数');
		when Too_many_rows then dbms_output.put_line('行数太多');
		when others then dbms_output.put_line('其他例外。。');
end;

自定义例外：

set serveroutput on;
declare
	myexc exception;
	pnum number:=1;
begin
	if pnum = 1 then raise myexc;
	end if;
	exception
		when myexc then dbms_output.put_line('自定义例外');
end;

是否存在编号50部门，如果不存在 抛出异常，如果存在，将该部门的员工姓名打印

setserveroutput on;
declare
	cursor cemp(dno number) is select ename from emp where deptno = dno;
	pename  emp.ename%type;
	no_emp_found exception;
begin
	open cemp(50);
	fetch cemp into pename;
	if cemp%notfound then raise no_emp_found;
	else
		loop
			exit when cemp%notfound;
			fetch cemp into pename;
		end loop;
	end if;
	exception
		when no_emp_found then dbms_output.put_line('没有此号部门');
	close cemp;
end;