1.游标是用来擦做查询结果的结果集，相当于JDBC中的ResultSet;语法:cursor 游标名[(参数 参数类型)] is 查询结果集开发步骤：1)声明游标 2)打开游标3)从游标中获取数据 fetch 游标名 into 变量名游标名%found :找到数据游标名%notfound:没有找到数据4)关闭游标系统引用游标1)声明游标:游标名 sys_refcursor2)打开游标:open 游标名 for 结果集3)从游标中获取数据4)关闭游标例:--普通游标 输出员工表中所有员工的姓名和工资(不带参数游标)declare --1.声明游标cursor vrows is select * from emp;--声明一个变量用来记录所有数据vrow emp%rowtype;begin--2.打开游标open vrows;--3.循环取数据，从游标中获取数据loopfetch vrows into vrow;exit when vrows%notfound;dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loop; --4.关闭游标close vrows;end;--系统引用游标 输出员工表中所有员工的姓名和工资declarevrows sys_refcursor;vrow emp%rowtype;beginopen vrows for select * from emp;loopfetch vrows into vrow;exit when vrows%noutfound;dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loopend;--扩展内容:使用for循环遍历游标declarecursor vrows is select * from emp;beginfor vrow in vrows loopdbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loop;end;--按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400declarecursor vrows is select * from emp;vrow emp%rowtypebeginopen vrows;fetch vrows into vrow;exit when vrows%notfound;if vrow.job='president' then update emp set sal=sal+1000 where empno=vrow.empno;elsif vrow.job='manager' thenupdate emp set sal=sal+800 where empno=vrow.empno;elseupdate emp set sal=sal+400 where empno=vrow.empno;end if;close vrows;commit;end;2.异常语法:exceptionwhen 异常1 then ...when 异常2 then ...when 异常3 then ...when orthers the...处理其他异常异常的常用类型zero_divide : 除零异常value_error : 类型转换异常too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量no_data_found : 没有找到数据例:declarevi number;vrow emp%rowtype;begin--vi := 8/0; --vi := 'aaa';--select * into vrow from emp;select * into vrow from emp where empno=1234567;exceptionwhen zero_divide thendbms_output.put_line('发生了除零异常');when value_error thendbms_output.put_line('发生了类型转换异常');when too_many_rows thendbms_output.put_line(' 查询出多行记录,但是赋值给了rowtype记录一行数据变量');when no_data_found thendbms_output.put_line('没有找到数据异常');when others thendbms_output.put_line('发生了其它异常' || sqlerrm);end;自定义异常:异常名 exception;raise 异常名;例:--查询指定编号的员工,如果没有找到,则抛出自定义的异常/*游标来判断%found %notfound声明一个游标声明一个变量,记录数据从游标中取记录如果有,则不管它如果没有就抛出自定义的异常*/declare--声明游标cursor vrows is select * from emp where empno=8888; --声明一个记录型变量vrow emp%rowtype;--声明一个自定义异常 no_emp exception; begin--1.打开游标open vrows;--2.取数据fetch vrows into vrow;--3.判断游标是否有数据if vrows%notfound thenraise no_emp;end if;close vrows;exceptionwhen no_emp thendbms_output.put_line('发生了自定义的异常');end;3.存储过程实际上是封装在服务器上一段PLSQL代码片段，已经编译好的代码，客户端去调用存储过程，执行效率会非常高语法:create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)is|as--声明部分begin --业务逻辑end;例:--给指定员工涨薪,并打印涨薪前和涨薪后的工资/*参数1:in 员工编号参数2: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: 用的最多的方式declarebeginproc_updatesal(7788,-100);end;4.存储函数实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段语法: create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型is | asbeginend;存储过程和函数的区别:1.它们本质上没有区别2.函数存在的意义是给过程调用 存储过程里面调用存储函数3.函数可以在sql语句里面直接调用4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现默认是 in--查询指定员工的年薪/*参数 : 员工的编号返回 : 年薪*/create or replace function func_getsal(vempno number) return numberis--声明变量.保存年薪vtotalsal number;beginselect sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;return vtotalsal;end;--调用存储函数declarevsal number;beginvsal := func_getsal(7788);dbms_output.put_line(vsal);end;--查询员工的姓名,和他的年薪select ename,func_getsal(empno) from emp;--查询员工的姓名和部门的名称--查询指定员工的年薪--存储过程来实现--参数: 员工编号--输出: 年薪create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)isbeginselect sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;end;declarevtotal number;beginproc_gettotalsal(7788,vtotal);dbms_output.put_line('年薪:'||vtotal);end;select * from emp where empno = 8888; 5.触发器触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑作用 : 在动作执行之前或者之后,触发业务处理逻辑插入数据,做一些校验语法:create [or replace] trigger 触发器的名称before | afterinsert | update | delete on 表名[for each row] --有此句代表是行级触发器,可以影响多行,没有此句代表语句级触发器,只能影响一句declarebeginend;触发器的分类:语句级触发器:不管影响多少行, 都只会执行一次行级触发器:影响多少行,就触发多少次:old 代表旧的记录, 更新前的记录:new 代表的是新的记录--新员工入职之后,输出一句话: 欢迎加入黑马程序员create or replace trigger tri_test1afterinserton empdeclarebegindbms_output.put_line('欢迎加入黑马程序员');end;insert into emp(empno,ename) values(9527,'HUAAN');--数据校验, 星期六老板不在, 不能办理新员工入职--在插入数据之前--判断当前日期是否是周六--如果是周六,就不能插入create or replace trigger tri_test2beforeinsert on empdeclare--声明变量vday varchar2(10);begin--查询当前select trim(to_char(sysdate,'day')) into vday from dual;--判断当前日期:if vday = 'saturday' thendbms_output.put_line('老板不在,不能办理入职');--抛出系统异常raise_application_error(-20001,'老板不在,不能办理入职');end if;end;insert into emp(empno,ename) values(9528,'HUAAN2');--更新所有的工资 输出一句话create or replace trigger tri_test3afterupdateon emp for each rowdeclarebegindbms_output.put_line('更新了数据');end;update emp set sal = sal+10;--判断员工涨工资后的工资一定要大于涨工资前的工资/*200 --> 100触发器 : before旧的工资 新的工资如果旧的工资大于新的工资 , 抛出异常,不让它执行成功 触发器中不能提交事务,也不能回滚事务 */create or replace trigger tri_updatesalbeforeupdateon empfor each rowdeclarebeginif :old.sal > :new.sal thenraise_application_error(-20002,'旧的工资不能大于新的工资');end if;end;update emp set sal = sal + 10;select * from emp;update emp set sal = sal - 100;/*模拟mysql中ID的自增属性 auto_increment insert into person(null,'张三'); 触发器:pid=1 insert pid=1序列 : create sequence seq_person_pid; */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_pidbeforeinserton personfor each rowdeclarebegindbms_output.put_line(:new.pname);--给新记录 pid 赋值select seq_person_pid.nextval into :new.pid from dual;end;insert into person values(null,'张三'); select * from person;
