本文共 7285 字，大约阅读时间需要 24 分钟。
游标，用于操作结果集，类似jdbc的ResultSet
CURSOR 游标名 [(参数名，数据类型),(参数名，数据类型),…] is select 语句；
-- 输出所有员工的姓名declare cursor vrows is select * from emp; vrow emp%rowtype;begin open vrows; loop fetch vrows into vrow; exit when vrows%notfound; dbms_output.put_line(vrow.ename); end loop; close vrows;end;
-- 输出指定部门的员工姓名declare -- 声明游标 cursor vrows(vdeptno number) is select * from emp where deptno = vdeptno; -- 记录型变量 vrow emp%rowtype;begin -- 1.打开游标 open vrows(20); -- 提取数据 loop fetch vrows into vrow; exit when vrows%notfound; dbms_output.put_line(vrow.ename); 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; 变量名 sys_refcursor;
open 变量名 for select 语句；-- 输出所有员工的姓名和工资declare -- 声明一个游标 vrows sys_refcursor; -- 声明一个变量 vrow emp%rowtype;begin -- 1.打开游标 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; -- 语法: declare -- 声明部分 begin -- 编写业务 exception --处理异常 when 异常1 then --处理异常 when 异常2 then --处理异常 when others then 捕获所有异常 --处理异常 end;
declare i number; vrow emp%rowtype;begin -- i := 5/0; -- i := 'adsfadfwerwer'; -- select * into vrow from emp where empno=1234567; select * into vrow from emp;exception when too_many_rows then dbms_output.put_line('发生了找到了多行记录,但是赋值给了一行的例外'); when no_data_found then dbms_output.put_line('发生了没有找到的例外'); when value_error then dbms_output.put_line('发生了类型转换例外'); when zero_divide then dbms_output.put_line('发生了除零例外'); when others then dbms_output.put_line('发生了未知的例外'); end; 声明: 例外名称 exception; 抛出例外: raise 例外名称
-- 自定义例外declare -- 声明例外 no_emp_found exception;begin -- 抛出例外 raise no_emp_found;exception when no_emp_found then dbms_output.put_line('发生了no_emp_found的例外'); when others then dbms_output.put_line('发生了未知的例外');end;-- 查询指定编号的员工信息,如果没有找到则抛出自定义的例外-- 如果没有找到员工,抛出的是自定义例外-- 错误的演示declare -- 声明例外 no_emp_found exception; -- 声明记录型变量 vrow emp%rowtype;begin select * into vrow from emp where empno=9999; -- 已经抛出了例外 no_data_found if vrow.ename is null then raise no_emp_found; end if;exception when no_emp_found then dbms_output.put_line('没有找到员工,抛出了自定义的例外'); when others then dbms_output.put_line('发生了未知的');end;-- 如果没有找到员工,抛出的是自定义例外-- 游标 : 如果没有找到数据 %notfounddeclare -- 声明一个游标 cursor vrows is select * from emp where empno=1234567; -- 自定义例外 no_emp_found exception; -- 声明一个记录型变量 vrow emp%rowtype;begin -- 1,打开游标 open vrows; -- 将游标向下移动 fetch vrows into vrow; if vrows%notfound then raise no_emp_found; end if; -- 关闭游标 close vrows;exception when no_emp_found then dbms_output.put_line('没有找到员工'); when others then dbms_output.put_line('未知的例外');end; 存储过程 : 实际上是将一段已经编译好了的PLSQL代码片断,封装在数据库中,方便调用。
作用: 1. 方便代码重用 2. 提高SQL的执行的效率语法: create [or replace] procedure 存储过程名称(参数1 in|out 参数类型,参数2 in|out 参数类型) is|as -- 声明部分 begin -- 业务逻辑部分 end; in : 输入参数 out : 输出参数
-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资-- 员工编号 , 输入参数-- 涨多少create or replace procedure proc_updatesal(vempno in number,vcount 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+vcount where empno=vempno; -- 打印涨薪后 dbms_output.put_line('涨薪后:'||(vsal+vcount)); -- 提交事务 commit;end;-- 调用方式1call proc_updatesal(7369,10);-- 调用方式2declarebegin proc_updatesal(7369,-100);end; --存储过程: 获取指定员工的年薪-- 参数 : 员工编号 in number-- 参数: 年薪 out numbercreate or replace procedure proc_getyearsal(vempno in number,vyearsal out number)is begin select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;end;-- 调用declare yearsal number;begin proc_getyearsal(7369,yearsal); dbms_output.put_line('年薪:'||yearsal);end; 存储函数:
实际上是将一段PLSQL代码片断,封装在数据库,方便别人去调用 作用: 1.提高代码复用性 2.提高执行效率语法: create [or replace] function 函数名称(参数1 in|out 参数类型) return 返回类型 is|as begin end; 区别: 1.函数有返回值,过程没有 2.过程能实现的,函数能实现 3.函数能实现的,过程也能实现 4.函数可以直接在SQL语句中使用 5.函数和过程本质上没有区别
-- 获取指定员工的年薪create or replace function func_getyearsal(vempno number) return numberis --声明一个变量 vyearsal number; begin select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno; return vyearsal;end; -- 调用函数declare yearsal number;begin yearsal := func_getyearsal(7369); dbms_output.put_line('年薪:'||yearsal);end;-- 在SQL语句中直接调用select emp.*,func_getyearsal(emp.empno) "年薪" from emp;select * from emp; 触发器:
当我们在SQL中执行了insert | update | delete这些操作的时候,可以去触发一段PLSQL代码片断执行语法: create [or replace] trigger 触发器名称 before | after insert | update | delete on 表名 [for each row] //行级触发器,写这个语句 declare -- 声明 begin -- 业务 end; 作用: 表的内容发生变化 1. 当数据发生变化的时候, 需要触发一段业务逻辑的执行 2. 数据校验 触发器的分类: 语句级触发器: 一条语句,不管影响多少行记录,都只触发一次 行级触发器: 一条语句,影响了多少行记录,就触发多少次 :new 新的记录 :old 旧的记录
-- 当向emp表插入数据之前,就输出一句hello triggercreate or replace trigger tri_test1beforeinsert on empdeclarebegin dbms_output.put_line('hello trigger');end;insert into emp(empno,ename) values(9527,'huaan');update emp set sal=10 where empno=9527; -- 校验指定周六不能插入员工--before insert create or replace trigger tri_checkbeforeinsert on empdeclare vday varchar2(20);begin -- 查询今天周几 select trim(to_char(sysdate,'day')) into vday from dual; -- 条件判断 if vday='saturday' then raise_application_error(-20000,'老板周六不在,不能插入数据!'); end if;end;
create or replace trigger tri_test2beforeupdate on empdeclarebegin dbms_output.put_line('语句级触发器');end;create or replace trigger tri_test3beforeupdate on empfor each rowdeclarebegin -- :new sal+10 :old sal dbms_output.put_line('行级触发器:'||:new.sal||' 旧的记录:'||:old.sal);end;update emp set sal=sal-10; /* 模拟mysql中的ID自动增长 auto_increment insert into 表名 values(null,zhangsan); 触发器 : before insert 序列 : 1,2,3,4,5,6,7,8...*/create table ppp( pid number primary key, pname varchar2(20));insert into ppp values(null,'zs');-- 创建一个序列create sequence seq_ppp_index;-- 触发器create or replace trigger tri_pppbeforeinsert on pppfor each rowdeclarebegin if :new.pid is null then select seq_ppp_index.nextval into :new.pid from dual; end if;end;insert into ppp values(null,'zs');
转载地址：http://pphwz.baihongyu.com/