三篇拿下oracle【篇三】

游标

是用来操作查询结果集，相当于是jdbc中的ResultSet；

语法：cursor 游标名 is 查询结果集
		
		开发步骤：
			1.声明游标
			2.打开游标  open name
			3.从游标中取数据 fetch name into 变量
					游标名%found ：找到数据
					游标名%notfound ：没有找到数据
			4.关闭游标  	close 游标名;
			
		系统引用游标：
			1.声明游标：游标名 sys_refcursor
			2.打开游标：open 游标名 for 结果集
			3.取数据
			4.关闭游标
	

例子

			
	---输出员工表所有员工姓名和工资(不带参数的游标)
		
		游标---结果集：所有员工
		声明一个变,用来记录一行数据 %rowtype
		
	declare
		--游标
		cursor vrows is select * from emp;
		--s声明变量，记录一行数据
		vrow emp%rowtype;
	begin
	--1.打开游标
		open vrows;
	--2.取数据
		loop
			fetch vrows into vrow;
			exit when vrows%notfound;
			dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
		end loop;
		close vrows;
	end;
		
	--输出指定部门下的员工姓名和工资(带参数)
	declare
		cursor vrows(dno number) is select * from emp where deptno = dno;
		vrow emp%rowtype;
	begin
	--指定10号部门
		open vrows(10);
		loop
			fetch vrows into vrow;
			exit when vrows%notfound;
			dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
		end loop;
		close vrows;
	end;
	

	--系统引用游标：
		declare
			vrows sys_refcursor;
			vrow emp%rowtype;
		begin
			open vrows for select * from emp;
			loop
				fetch vrows into vrow;
				exit when vrows%notfound;
				dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
			end loop;
			close vrows;
		end;
		
--使用for循环遍历游标(简化流程)
			不需要打开、关闭、声明游标；
	declare 
	--声明游标
		cursor vrows is select * from emp;
	begin
		for vrow in vrows loop
			dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
		end loop;
	end;
	
--按照员工工作给所有员工涨工资，总裁1000，经理800，其他400

	游标：所有员工
	声明一个记录一行数据
	
	declare
		cursor vrows is select * from emp;
		vrow emp%rowtype;
	begin
		open vrows;
		loop
			fetch vrows into vrow;
			exit when vrows%notfound;
			if vrow.job = 'PRESIDENT' then
				update emp set sal = sal+1000 where empno=vrow.empno;
			elseif vrow.job ='MANAGER' then
				update emp set sal = sal+800 where empno=vrow.empno;
			else
				update emp set sal = sal+400 where empno=vrow.empno;
			end if;
		end loop;
		close vrows;
		commit;
	end;

PLSql异常

例外(意外)：程序运行过程中的异常

exception 
		--处理异常
		when 1 then
		when 2 then
		when others then

zero_divide:除0异常
value_error:类型转换异常
too_many_rows:查询多行记录，但是赋值给了rowtype记录一行数据变量
no_data_found:没有找到数据

自定义异常：
	异常名 exception;
	raise 异常名

存储过程

实际上是封装在服务器上的一段plsql代码片段，已经编译好的代码；
客户端调用存储过程，执行高效

语法：
	create [or replace] procedure 名称(参数名 in|out 参数类型，参数名 in|out 参数类型)
		is|as
			--声明部分
		begin
			--业务逻辑
		end；

–给指定员工长薪，并打印涨薪前后的工资
参数:in员工编号
参数：in涨多少
声明一个变量：存储涨工资前的工资
查询当前工资多少
打印涨工资前的工资
更新工资
打印涨薪后的工资

	create or replace procedure proc_updatesal(vempno in number,vnum in number)
	is
		--声明变量：记录当前工资
		vsal number;
		
	begin
		--查询当前工资多少
			select sal into vsal from emp where empno=vempno;
			dbms_output.put_line('涨薪前：'||vsal);
		--更新工资
			update emp set sal = vsal+vnum where empno =vempno;
		--输出涨薪后工资
			dbms_output.put_line('涨薪后：'||(vsal+vnum));
		--提交
		commit;
	end;
	
------**调用**：
	方式1：
		call proc_updatesal(7788,10);
	方式2：（常用）
	declare
	
	begin
		proc_updatesal(7788,-100);
	end；

存储函数

实际上是一段封装在oracle服务器中的一段plsql代码片段，它是已经编译好的代码片段；

语法：
		create [or replace] function 函数名称(参数名 in|out 参数类型) return 参数类型
		is|as
		
		begin
		
		end；

存储过程和函数的区别
1.本质上没有区别
2.函数存在的意义是给过程调用
3.函数可以在sql语句中直接调用
4.存储过程能实现的，存储函数也能实现；
默认是in

--查询指定员工年薪
	
	参数：员工编号
	返回：年薪
	
	create or replace function func_getsal(vempno number) return number
	
	is
		--声明变量，保存年薪
		vtotalsal number;
	begin
		select sal*12+nvl(comm,0) into vtotalsal from emp where empno=vempno;
		return vtotalsal;
	end;
	
	--调用存储函数
	declare
		vsal number;
	begin
		vsal = func_getsal(7788);
		dbms_output.put_line(vsal);
	end;
	
	--sql语句直接调用函数
	select ename,func_getsal(empno) from emp；
	
	--输出参数
	
	create or replace procedure proc_gettotalsal (vempno in number,vtotalsal out number)
	is
	
	begin
		select sal*12+nvl(comm,0) into vtotalsal from emp where empno = vempno;
	end;

触发器

当用户执行了 insert update delete 这些操作后，可以触发一系列动作

分类：
语句级触发器：不管影响多少行，都只会执行一次
行级触发器：影响多少行，就触发多少次 [for each row]
:old 代表旧的记录，更新前的记录
:new 代表更新后的记录

作用：
数据确认：插入数据，做一些校验
复杂性安全性
审计

语法：	
	create [or replace] trigger 触发器名称 
		before|after
			insert|update|delete  
				on 表名 [for each row]
	declare
				
    begin	
    	
	end；

--插入员工之后，输出一句话
	create or replace trigger tri_test1
		after
			insert
			on emp
		declare
		begin
			dbms_output.put_line('welcome');
		end；
		
--数据校验，星期六老板不在，不能办理新员工入职
	在插入数据之前，判断当前日期是否是周六，如果周六，就不能插入
	create or replace trigger tri_test2
	before
		insert
		on emp
	declare
		--声明变量：
			vday varchar2(10);
	begin
		--查询当前日期
		select to_char(sysdate,'day') into vday from dual;
		--判断当前日期：
		if vday = 'saturday' then
			dbms_output.put_line('老板不在，不能办理');
			--抛出系统异常
			raise_application_error(-20001,'老板不在，不能办理');
		end if;
	end;
	
--更新所有员工的工资
	create or replace trigger tri_test3
	after 
		update
		on emp
		for each row
	declare
		
	begin
		dbms_output.put_line('工资更新');  //输出多次
	end;
	
--判断员工涨工资后的工资大于涨工资前的工资(两个隐对象)
	触发器：before
		旧的工资
		新的工资
		如果旧工资大于新的工资，抛出异常，不让它执行
		
	create or replace trigger tri_updatesal
	before
		update
		on emp
		for each row
	declare
	begin
		if :old.sal > :new.sal then
			raise_application_error(-20002,'不执行');
		end if;
	end;

--模拟mysql中id自增长属性 auto_increment

	触发器：
	create table person(
		pid number primary key,
		pname varchar2(20)
	)
	insert into person values(null,'张三');//没有触发器，空值不能被插入
	
	--序列用途：写触发器
	create sequence seq_person_pid;
	
	--触发器
	create or replace trigger tri_add_person_pid
	before
	insert
		on person
		for each row
	declare
	begin
		dbms_output.put_line(:new.pname);
		--给新记录pid赋值
		select seq_person_pid.nextval into :new.pid from dual;
	end；