2、游标 cursor
2.1、作用
用来处理多行的查询结果集
2.2、概念
游标可以看成是一个位置实体或者位置指针
游标是映射在结果集中的一行数据上的位置实体。
有了游标,用户就可以访问结果集中的任何一行数据。
将游标放到某行数据后就可以操作该行数据,比如提取出数据。
2.3、游标的使用步骤
1) 声明游标
在声明区
声明游标名和对应的select语句(这个时候select还没有执行)
语法:
cursor 游标名 is select 语句;
游标声明后,可以使用游标名%rowtype声明变量
执行区
2) 打开游标
执行select语句,把结果集存储在游标的工作区,并且指针指向结果集的第一行数据。
语法:
open 游标名;
3) 提取数据
从游标对应的工作区中,把当前行的数据检索到变量中。
游标指针自动向下移动一行。
语法:
fetch 游标名 into 变量;
4) 关闭游标
当提取和处理完结果集中的数据后,应该及时关闭游标,释放其占有的资源,游标对应的工作区变为不可用
语法:
close 游标名;
2.4、使用游标查询s_emp中的数据
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
close empcursor;
end;
/
2.5、游标的属性
游标名%属性
found 在提取数据时,如果提取到了新数据,则返回真,否则返回假
如果游标没有打开,产生非法游标异常。
如果没有提取,则返回NULL
notfound 在提取数据时,如果没有提取到新数据,则返回真,否则返回假
如果游标没有打开,产生非法游标异常。
如果没有提取,则返回NULL
ifopen 游标是否为打开状态,如果游标打开,则返回真,游标关闭则返回假
已经打开的游标不能再次打开(异常,游标已打开)
已经关闭的游标不能再次关闭(异常,非法游标)
rowcount 游标指针的偏移量
如果没有打开,则为非法游标
2.6、使用简单循环
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
end loop;
close empcursor;
end;
2.7、使用while循环
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
while empcursor%found loop
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
fetch empcursor into var_emp;
end loop;
close empcursor;
end;
2.8、使用for循环
智能循环(自动打开,提取和关闭)
declare
cursor empcursor is select * from s_emp;
begin
for var_emp in empcursor loop
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
end loop;
end;
2.9、带参游标
参数的数据类型不能有长度或精度的修饰。可以使用%type.
声明游标时:
cursor 游标名(形参列表) is select 字段列表 from 表名;
where 条件;
打开游标时传参:
open 游标名(实参列表)
特殊情况,for循环
declare
cursor empcursor(var_id number) is select * from s_emp where id>var_id;
begin
for var_emp in empcursor(10) loop
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
end loop;
end;
2.10、参考游标 ref cursor
游标+动态sql
2.10.1、使用步骤
sqlstr:='select * from s_emp';
1) 定义参考游标类型
type 参考游标类型名 is ref cursor;
2) 声明参考游标类型变量(相当于游标使用的声明游标)
参考游标变量名 参考游标类型名;
3) 把参考游标变量和动态sql语句结合(相当于游标使用的打开游标)
open 参考游标变量名 for sqlstr;
4) 提取数据
5) 关闭游标
2.10.2、使用参考游标处理多行结果集
declare
sqlstr varchar2(100);
type emprcursor is ref cursor;
var_cursor emprcursor;
var_emp s_emp%rowtype;
begin
sqlstr:='select * from s_emp where id>:d0';
open var_cursor for sqlstr using 10;
loop
fetch var_cursor into var_emp;
exit when var_cursor%notfound;
dbms_output.put_line(var_emp.id||','||var_emp.first_name);
end loop;
close var_cursor;
end;
/
3、QLSQL中的异常
3.1、系统预定义异常
3.1.1、概念
Oracle系统为用户提供的,可以在plsql中去使用,用来检查用户代码失败的一般原因。
定义在Oracle的plsql的核心库,使用时用异常的名字进行标志,对异常进行捕获和处理。
3.1.2、语法:
exception
when 异常名 then
异常处理
3.1.3、示例:处理一个异常
declare
var_id number:=&id;
var_name varchar2(25);
begin
select first_name into var_name from s_emp where id=var_id;
dbms_output.put_line(var_name);
exception
when no_data_found then dbms_output.put_line('no emp');
end;
/
3.1.4、处理其他处理
declare
var_id number:=&id;
var_name varchar2(25);
begin
select first_name into var_name from s_emp where id>var_id;
dbms_output.put_line(var_name);
exception
when no_data_found then dbms_output.put_line('no emp');
when others then
dbms_output.put_line('others:'||sqlcode||'#####'||sqlerrm);
end;
3.1.5、常用的系统预定义异常
cursor_already_open 游标已打开
invalid_cursor 非法游标
invalid_number 内嵌的sql语句无法将字符串隐式转换成数字
no_data_found select...into语句没有返回行
too_many_rows select...语句返回超过1行
dup_val_on_index 唯一索引对应的字段上有重复值
zero_divide 除数为0
declare
var_id number:=&id;
var_name varchar2(25);
begin
select first_name into var_name from s_emp where id>var_id;
dbms_output.put_line(var_name);
exception
when no_data_found then dbms_output.put_line('no emp');
when too_many_rows then dbms_output.put_line('too many rows');
when others then
dbms_output.put_line('others:'||sqlcode||'#####'||sqlerrm);
end;
3.2、自定义异常
3.2.1、使用步骤
1) 定义异常
在声明区
异常命 exception
2) 根据条件引发异常
在执行区
if 引发异常的条件 then
raise 异常名;
end if;
3) 捕获和处理异常
在异常处理区
when 异常名 then 异常处理
3.2.2、根据id修改员工的信息,如果员工不存在,引发异常
declare
var_id number:=&a;
no_emp exception;
begin
update emp_lx_42 set salary=salary+500 where id=var_id;
if sql%notfound then
raise no_emp;
end if;
exception
when no_emp then dbms_output.put_line('no this emp');
end;
/
隐式游标:
执行任何一个sql的语句的时候oracle都会自动的创建一个隐式游标。
这个游标是内存中为该sql语句分配的工作区。
隐式游标也有属性,并且使用属性时需要提供游标名,oracle给隐式游标命名为sql.
隐式游标通常用来处理DML(insert update delete)语句
4、存储过程 procedure
4.1、匿名块和有名块
匿名块:
匿名块是不保存在数据库中的
每次使用时都需要进行编译
匿名块不能被其他块调用
有名块:
可以存储在数据库中
可以在任何需要的地方调用
有名块包括: 存储过程 函数 包 触发器
procedure function package trigger
4.2、创建存储过程的语法
create[ or replace] procedure 过程名[(参数列表)] {is|as}
--临时变量
begin
exception
end;
4.3、无参的存储过程
4.3.1、创建:根据编号查名字
create or replace procedure serch_name_proc_lx_42 is
var_i number:=1;
var_name varchar2(25);
begin
select first_name into var_name from s_emp where id=var_i;
dbms_output.put_line(var_name);
end;
show error//列出报错
4.3.2、调用
存储过程在plsql中调用方式 是直接调用。
begin
serch_name_proc_lx_42;
end;
4.4、带参的存储过程
4.4.1、参数列表的语法
参数名{[in]|out|in out} 数据类型[{:=|default}值]
1) 参数的数据类型(不能包含长度或者精度的修饰)
2) 参数的模式
in 输入参数 负责传入数据 缺省方式
out 输出参数 负责传出数据 必须是变量,不需要初始化
in out 输入输出参数 及负责传入又负责输出 实参是初始化的变量
3) 参数的默认值
in模式的参数,才可以有默认值
4.4.2、创建带参的存储过程,实现根据id查询first_name
create or replace procedure serch_name_proc_lx_42(var_id number:=1,var_name out varchar2) is
begin
select first_name into var_name from s_emp where id=var_id;
end;
4.4.3、调用带参数的存储过程
1) 按照位置赋值
declare
var_name varchar2(25);
begin
serch_name_proc_lx_42(2,var_name);
dbms_output.put_line(var_name);
end;
2) 按照名字赋值 性参名=>值
declare
var_name varchar2(25);
begin
serch_name_proc_lx_42(var_name=>var_name);
dbms_output.put_line(var_name);
end;
desc 存储过程名,可以看到存储过程的参数列表
4.5、创建一个存储过程,输入一个大于1的整数,返回1..n的累加和
create or replace procedure serch_name_proc_lx_42(var_max number,var_sum out number) is
begin
var_sum:=0;
for i in 1..var_max loop
var_sum:=var_sum+i;
end loop;
end;
declare
var_sum number(7);
begin
serch_name_proc_lx_42(10,var_sum);
dbms_output.put_line(var_sum);
end;
5、函数 function
5.1、函数和存储过程的区别
1) 关键字不同 存储过程是 procedure 函数是 function
2) 函数有返回类型和返回值,存储过程没有
3) 在plsql中调用的时候,过程是直接调用,而函数的调用必须组成表达式
使用变量接受返回值
把函数的调用作为其他函数或者存储过程的参数
5.2、创建函数的语法
create[ or replace] function 函数名[(参数列表)] return 返回值的数据类型 {is|as}
--临时变量
begin
--必须有return语句
return 值;
exception
end;
5.3、把上一个练习改写成函数实现
--创建
create or replace function serch_name_func_lx_42(var_max number) return number is
var_sum number:=0;
begin
for i in 1..var_max loop
var_sum:=var_sum+i;
end loop;
return var_sum;
end;
--调用
begin
dbms_output.put_line(serch_name_func_lx_42(10));
end;
6、包 package
6.1、概念
把逻辑上相关的一组变量、数据类型、过程、函数等组织到一起的一种逻辑结构。
6.2、系统提供的包
dbms_output
dbms_random
dbms_job
查看包中的数据: desc 包名
使用包中的子程序: 包名.函数 或者 包名.过程
6.3、自定义包
1) 定义包的头部 类似于C中的.h文件
--语法
create[ or replace] package 包名
is
--过程、函数的声明,类型的定义,变量的声明
end[ 包名];
2) 包的实现 类似与C中的.c文件
create[or replace] package body 包的名字
is
--过程、函数的实现
end[ 包名];
举例:创建包,包含一个求最大值的函数,一个求最小值的过程
--创建包的头部
create or replace package mypackage_lx_42
is
procedure getmin(a number,b number);
function getmax(a number,b number) return number;
end mypackage_lx_42;
--创建包的正文
create or replace package body mypackage_lx_42
is
procedure getmin(a number,b number) is
begin
if a < b then
dbms_output.put_line(a);
else
dbms_output.put_line(b);
end if;
end;
function getmax(a number,b number) return number is
begin
if a > b then
return a;
else
return b;
end if;
end;
end mypackage_lx_42;
--测试
begin
dbms_output.put_line(mypackage_lx_42.getmax(2,3));
end;
7、触发器 trigger
7.1、概念和作用
触发器可以看做一种特殊的存储过程,定义了一些和数据库相关的事件(insert,update,delete,...)发生时应该执行的功能代码块。
通常用来管理复杂的完整性约束、监控对表的修改、通知其他程序等。
7.2、创建触发器的语法
DML触发器
create [or replace] trigger 触发器名 {before|after} {insert|update|delete} on 表名 [for each row]
declare
begin
end;
7.3、语句级触发器
create or replace trigger emp_update_tri_lx_42 after update on emp_lx_42
declare
begin
dbms_output.put_line('someone update table -> emp_lx_42');
end;
语句级触发器和影响的行数无关
7.4、行级触发器
create or replace trigger emp_update_tri_lx_42 after update on emp_lx_42 for each row
declare
begin
dbms_output.put_line('someone update table -> emp_lx_42');
dbms_output.put_line('old salary:'||:old.salary);
dbms_output.put_line('new salary:'||:new.salary);
end;
update emp_lx_42 set salary=salary+100 where id=1;
update emp_lx_42 set salary=salary+100 where id<1;
update emp_lx_42 set salary=salary+100 where id>1;
列标识符: %rowtype类型
:new 新值标识符
:old 原值标识符
7.5、使用触发器自动为主键字段填充值
--创建测试表
create table testtrigger_lx_42(id number primary key,name varchar2(25));
--创建一个序列
create sequence testtrigger_id_lx_42;
--创建触发器
create or replace trigger testtrigger_insert_lx_42 before insert on testtrigger_lx_42 for each row
begin
select testtrigger_id_lx_42.nextval into :new.id from dual;
dbms_output.put_line('new id:'||:new.id);
end;
--测试
insert into testtrigger_lx_42(name) values('test');
学习笔记day67-----oracle-存储过程、函数、触发器、包
最新推荐文章于 2022-10-03 09:52:20 发布