SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。
游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法
游标分为显式游标和隐含游标两种
隐含游标用于处理SELECT INTO和DML语句
显式游标则用于处理SELECT语句返回的多行数据
显示游标属性用于返回显式游标的执行信息
游标属性使用格式为：游标名 + 属性名
%ISOPEN
用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE
%FOUND
检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE
%NOTFOUND
与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE
%ROWCOUNT
返回到当前行为止已经提取到的实际行数
declare
v_dept dept%rowtype;
cursor dept_cursor is select * from dept where deptno>10; --定义游标
begin
open dept_cursor; --打开游标
loop
fetch dept_cursor into v_dept; --提取数据
exit when dept_cursor%notfound; --判断循环退出条件
dbms_output.put_line('编号：'||v_dept.deptno
||' 名称：'||v_dept.dname||' 地址：'||v_dept.loc);
end loop;
close dept_cursor; --关闭游标
end;
用table结构提取游标数据
declare
type v_dept is table of dept%rowtype index by binary_integer;
cursor dept_cursor is select * from dept;
v_dept_table v_dept;
begin
open dept_cursor;
fetch dept_cursor bulk collect into v_dept_table;
close dept_cursor;
for i in v_dept_table.first..v_dept_table.last loop
dbms_output.put_line(v_dept_table(i).deptno||'
'||v_dept_table(i).dname||' '||v_dept_table(i).loc);
end loop;
end;
带有参数的游标
declare
cursor emp_cursor(param_dept number) is
select empno,ename from emp where deptno=param_dept;
emp_record emp_cursor%rowtype;
begin
open emp_cursor(20);
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.empno||' '||emp_record.ename);
end loop;
close emp_cursor;
end;
游标for循环简化游标处理
declare
cursor dept_cursor is select * from dept;
begin
for dept_row in dept_cursor loop
dbms_output.put_line('第'||dept_cursor%rowcount||
'个部门：'||dept_row.dname);
end loop;
end;
begin
for dept_row in(select deptno,dname from dept) loop
dbms_output.put_line('第'||dept_row.deptno||
'个部门：'||dept_row.dname);
end loop;
end;
游标变量示例
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
v_emp_row emp%rowtype;
begin
open emp_cursor for select * from emp where deptno=10;
loop
fetch emp_cursor into v_emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%rowcount||
'个员工：'||v_emp_row.ename);
end loop;
close emp_cursor;
end;
存储过程：存储过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。
创建存储过程
CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
参数类型：
in为输入类型的参数 out为输出类型的参数。
过程的调用：1 用plsql块 begin procedurename(); end;
2 exec procedurename(); //sqlplus中
3 cell proecdurename();
--声明接收参数的只声明类型，不声明大小
当为过程定义参数时，如果不指定参数模式，则默认为输入参数
create or replace procedure pro2(p_id in varchar2,p_name in varchar2)
as
begin
insert into person values(p_id,p_name);
end;
--调用
set serveroutput on;
exec pro2('P100','Marray');
接收输出类型的参数
--插入一条记录，统计行数，并返回
create or replace procedure pro4(pid in varchar,pnm in varchar,rcount out number)
as
rc number:=0;
begin
--先写入数据
insert into person values(pid,pnm);
commit;
select count(1) into rc from person;
rcount:=rc;
end;
--测试调用
set serveroutput on;
declare
rc number:=0;
begin
--第三个参数为返回值的参数
pro4('P210','Jack',rc);
dbms_output.put_line(rc);
end;
带有IN OUT参数
create or replace procedure pro_testinout
(param_num in out number)
as
begin
select sal into param_num from emp
where empno=param_num;
end;
declare
inout_num number;
begin
inout_num:=&no;
pro_testinout(inout_num);
dbms_output.put_line('工资是：'||inout_num);
end;
函数：函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数
函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。
CREATE [OR REPLACE] FUNCTION function_name
(argument1 [model] datatype1,
argument2 [mode2] datatype2,
...)
RETURN datatype
IS|AS
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
函数的调用： select function() from dual; 或者 用plsql块 begin function(); end;
根据员工编号，返回员工姓名
create or replace function f2(v_empno in number)
return varchar2
as
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=v_empno;
return v_ename;
end;
--调用
select f1(7369) from dual;
过程与函数有许多相同的功能及特性
都使用IN模式的参数传入数据、OUT模式的参数返回数据
输入参数都可以接收默认值，都可以传值
调用时的实参都可以使用位置表示法或名称表示法
都有声明部分、执行部分和异常处理部分
一般而言，如果需要返回多个值或不返回值，就使用过程
如果只需要返回一个值，就使用函数
虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格
过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值
触发器：触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码
触发器类型
DML触发器
在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
替代触发器
oracle8专门为进行视图操作的一种触发器
系统触发器
对数据库系统事件进行触发，如启动、关闭等
触发器组成
触发事件
DML或DDL语句。
触发时间
是在触发事件发生之前(before)还是之后(after)触发
触发操作
使用PL/SQL块进行相应的数据库操作
触发对象
表、视图、模式、数据库
触发频率
触发器内定义的动作被执行的次数。
编写触发器执行代码时，需要注意以下限制
触发器不接受参数
一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
一个表上的触发器越多，该表上的DML操作的性能影响就越大
触发器代码的大小不能超过32K。如需要大量的代码创建触发器，则首先创建过程，然后在触发器中使用CALL语句调用过程
触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，
不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）
触发语句与伪记录变量的值
eg:如果指定了BEFORE关键字，则表示执行DML操作之前触发触发器
create or replace trigger tri_no_sun
before
insert or update or delete
on emp
begin
if to_char(sysdate,'day')in('星期四','星期六') then
raise_application_error(-20000,'不能在周四或周六修改员工信息');
end if;
end;
使用条件谓词
当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词
INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE
UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE
DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE
create or replace trigger tri_option_dept
before
insert or update or delete
on dept
begin
if to_char(sysdate,'day') in ('星期四','星期六') then
case
when inserting then
raise_application_error(-20000,'周六或周四不能进行录入操作');
when updating then
raise_application_error(-20001,'周六或周四不能进行修改操作');
when deleting then
raise_application_error(-20002,'周六或周四不能进行删除操作');
end case;
end if;
end;
行级触发器
for each row
:old 修改前的该行记录
:new 修改后的该行记录
如果修改的是部门编号为30的员工工资，则工资不能降低
create or replace trigger trig_update_sal
before update of sal,comm
or delete on emp
for each row
when(old.deptno=30)
begin
case
when updating('sal') then
if :new.sal<:old.sal then
raise_application_error(-20002,'部门30的人员工资不能降');
end if;
when updating('comm') then
if :new.comm<:old.comm then
raise_application_error(-20001,'部门30的奖金不能降低！');
end if;
when deleting then
raise_application_error(-20003,'不能删除部门30的员工');
end case;
end;
创建AFTER语句触发器
如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器
利用行触发器实现级联更新。在修改了主表dept中的deptno之后（AFTER），级联的、自动的更新子表emp中原来在该部门的deptno。
create or replace trigger tri_casupdate
after update of deptno on dept
for each row
begin
dbms_output.PUT_LINE('旧的deptno值是：'||:old.deptno);
dbms_output.PUT_LINE('新的deptno值是：'||:new.deptno);
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器
创建INSTEAD OF触发器时需要注意以下几点
INSTEAD OF选项只适用于视图
当基于视图创建触发器时，不能指定BEFORE和AFTER选项
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]} [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定 [WHEN condition]
PL/SQL_block | CALL procedure_name;
创建查询视图
create or replace view emp_view as
select deptno,count(*) total_employeer,sum(sal) total_sal
from emp group by deptno;
创建instead_of触发器
create or replace trigger emp_view_del
instead of delete on emp_view for each row
begin
delete from emp where deptno=:old.deptno;
end;
创建系统事件触发器
创建登录和退出触发器
create table log_event(
username varchar2(20),
ipAddress varchar2(20),
logonTime timestamp,
logoffTime timestamp
);
create or replace trigger logon_trigger
after logon on database
begin
insert into log_event(username,ipaddress,logonTime)
values(ora_login_user,ora_client_ip_address,sysdate);
end;
create or replace trigger logoff_trigger
before logoff on database
begin
insert into log_event(username,ipaddress,logoffTime)
values(ora_login_user,ora_client_ip_address,sysdate);
end;
扫一扫
5530
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
