Oracle游标/异常/过程/函数
1、基础语法
http://692088846.iteye.com/blog/2017137 (%type、%rowtype、if\if else\if elseif else、while、do..while、游标、异常、函数、过程)
1.1 声明变量赋值并输出
set serveroutput on --设置数据库输出,默认为关闭,每次重新打开窗口需要重新设置。
Declare
result integer; --声明变量【变量名 变量类型】
begin
result:=10+3*4-20+5**2; --给变量赋值【:=】
dbms_output.put_line('运算结果是:'||to_char(result));
end;
--------------------------------------------------------------------------------------------------
dbms_output.put_line函数输出只能是字符串,因此利用to_char函数将数值型结果转换为字符型。
运算的优先次序为NOT、AND和OR。
To_char:将其他类型数据转换为字符型。
To_date:将其他类型数据转换为日期型。
To_number:将其他类型数据转换为数值型。
1.2 %type、%rowtype、select...into
-- %type 变量的类型和数据表中的字段的数据类型一致
-- %rowtype 变量的类型和数据表中的一行记录数据类型一致
-- select ... into 变量 表中查询数据并赋值,可以一次性给多个变量赋值
declare
v_object CUSTOMER%Rowtype;
begin
select * into v_object from customer where rownum=1;
dbms_output.put_line(v_object.Enterprise_Code ||','||v_object.CreateDate);
end;
1.3 只可以增删改
-- insert,update,delete,select都可以,create table,drop table不行。DPL,DML,和流程控制语句可以在pl/sql里用但DDL语句不行,表定义语言不可以在plsql中改变
declare
v_name student.name%type:='wang'; --声明变量并赋值
begin
insert into student(id,name,age) values(2,v_name,26); --插入数据
end;
--------------------------------------------
declare
v_name student.name%type:='hexian'; --更新数据
begin
update student set name=v_name where id=1;
end;
begin
update student set name='qinaide' where id=2;
end;
------------------------------------------------- --------------
2、 PLSQL流程控制
--if判断---
declare
v_b boolean:=true; -- := 是赋值
begin
if v_b then
dbms_output.put_line('ok');
end if;
end;
--if else判断---
declare
v_b boolean:=true;
begin
if v_b then
dbms_output.put_line('ok');
else
dbms_output.put_line('false');
end if;
end;
--if elsif else判断--
declare
v_name varchar2(20):='cheng';
begin
if v_name='0701' then
dbms_output.put_line('0701');
elsif v_name='cheng' then
dbms_output.put_line('cheng');
else
dbms_output.put_line('false');
end if;
end;
--loop循环,注意推出exit是退出循环,而不是推出整个代码块
declare
v_i binary_integer :=0;
begin
loop
exit when v_i>10;
v_i :=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;
-- while循环
declare
v_i binary_integer:=0;
begin
while v_i<10 loop
dbms_output.put_line('hello'||v_i );
v_i:=v_i+1;
end loop;
dbms_output.put_line('over');
end;
-- for循环,注意不需要声明变量
begin
for v_i in 0..10 loop
dbms_output.put_line('hello'||v_i);
end loop;
dbms_output.put_line('over');
end;
3、PLSQL异常处理
1、声明异常
异常名 EXCEPTION;
2、抛出异常
RAISE 异常名
3、处理异常
抛出异常后的逻辑代码不会被继续执行
异常的定义使用
―――――――――――――――――――――――――――――――――――――
begin
dbms_output.put_line(1/0); --1/0出现错误,抛出异常,输出error
exception
when others then
dbms_output.put_line('error');
end;
-----------------------------------------------------------------------------
declare
e_myException exception; --声明异常
begin
dbms_output.put_line('hello');
--raise抛出异常,用此关键字,抛出后转到自定义的e_myException,执行其
--里面的putline函数后,再跳到end处,结束PL/SQL块,raise接下面的2句不会继续执行。
raise e_myException;
dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception
when e_myException then --执行异常
dbms_output.put_line(sqlcode); --当前会话执行状态,错误编码
dbms_output.put_line(sqlerrm); --当前错误信息
dbms_output.put_line('my error');
when others then
dbms_output.put_line('error');
end;
4、 PLSQL游标
4.1 游标定义
备注:把游标看作一个集合
游标,从declare、open、fetch、close是一个完整的生命旅程。cursor 游标(结果集)用于提取多行数据,定义后不会有数据,使用后才有。一旦游标被打开,就无法再次打开(可以先关闭,再打开)。
--游标的定义 declare v_title books.title%type; --声明为和数据库中数据相同的类型 v_retail books.retail%type; cursor book_cursor is --把游标看作一个集合 select title, retail form books natural join orderitems where order=1012; Begin open book_cursor --打开游标 loop fetch books_cursor into v_title, v_ratial; --通过循坏提取数据 exit when book_cursor%notfound --如果没有数据就退出循坏 dbms_output.line('book title:' || v_title || ',price:' || v_retail); end loop; close book_cursor; --关闭游标 end; --第二种游标的定义方式,用变量控制结果集的数量。 declare v_id binary_integer; cursor c_student is select * from book where id > v_id; begin v_id:=10; open c_student; close c_student; end; --第三种游标的定义方式,带参数的游标,用的最多。 declare cursor c_student(v_id binary_integer) is select * from book where id>v_id; begin open c_student(10); close c_student; end;
4.2 游标的使用
游标的使用,一定别忘了关游标。
declare
v_student book%rowtype;
cursor c_student(v_id binary_integer) is select * from book where id>v_id;
begin
open c_student(10);
fetch c_student into v_student;
close c_student;
dbms_output.put_line(v_student.name);
end;
如何遍历游标fetch
游标的属性 %found,%notfound,%isopen,%rowcount。
%found:若前面的fetch语句返回一行数据,则%found返回true,如果对未打开的游标使用则报ORA-1001异常。
%notfound,与%found行为相反。
%isopen,判断游标是否打开。
%rowcount:当前游标的指针位移量,到目前位置游标所检索的数据行的个数,若未打开就引用,返回ORA-1001。
注:
no_data_found和%notfound的用法是有区别的,小结如下
1)SELECT . . . INTO 语句触发 no_data_found;
2)当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound;
3)当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound;
4)在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件,不要用no_data_found。
4.3 游标实例:
1、loop方式遍历游标 declare v_bookname varchar2(100); --带参数的游标,把游标当做一个集合(只有bookname的集合) cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); --打开游标 Loop Fetch c_book into v_bookname; --获取数据 exit when c_student%notfound; update book set price = '33' where bookname = v_bookname; --如果该id对应的bookname没值则更新 End Loop; Close c_book; --关闭游标 end; 或 declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); Fetch c_book into v_bookname; While c_book%Found Loop update book set price = '33' where bookname = v_bookname; Fetch c_book into v_bookname; End Loop; Close c_book; end; 2、while循环遍历游标,注意,第一次游标刚打开就fetch,%found为null,进不去循环 解决方法:while nvl(c_student%found,true) loop declare v_bookname varchar2(100); cursor c_book(i_id number) is select bookname from book where id = i_id; begin Open c_book(i_id); while nvl(c_book%found,true) --或这种写法:while c_book%found is null or c_book%found loop Fetch c_book into v_bookname; update book set price = '33' where bookname = v_bookname; End Loop; Close c_book; end; 3、 for循环遍历 - 最简单,用的最多,不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标,实现遍历游标最高效方式) declare cursor c_book(i_id number) is select bookname from book where id = i_id; begin for cur in c_book(i_id) --直接将入参i_id传入cursor即可 loop update book set price = '53' where bookname = cur.bookname; end loop; end;
5、Oracle存储过程
存储过程---就像数据库中运行方法(函数),和C#方法一样,由存储过程名/存储过程参数组成,可以有返回结果。
优点:
-- 执行速度更快(在数据库中保存的存储过程语句都是编译过的)
-- 允许模块化程序设计(类似方法的复用)
-- 提高系统安全性(防止sql注入)
-- 减少网络流通量(只要传输存储过程的名称)
一般,以sp_、xp_开头的都是系统存储过程,用户自定义存储过程usp_
总结:输入参数 in或缺省、输出参数out,不带数据长度; 还有in out类型;
对于in类型参数:不能赋值,只能用他给变量赋值,是只读的; 如果后面加了default则在调用用可以不用传值;
对于out类型参数:调用时不用赋值,在过程中会被赋值;
对于in out 类型参数:既可以赋值,也可以改变值;
对过程的调用没有返回值,不能用变量去接收,它没有返回值;
过程是一个完整的sql,函数是一个合法的表达式
IN,OUT,IN OUT是形参的模式。若省略,则为IN模式。
IN模式的形参只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变。
OUT模式的形参会忽略调用时的实参值(或说该形参的初始值总是NULL),但在函数内部可以被读或写,函数返回时形参的值会赋予给实参。
IN OUT具有前两种模式的特性,即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。
调用时,对于IN模式的实参可以是常量或变量,但对于OUT和IN OUT模式的实参必须是变量。
5.1 存储过程案例
create table BOOK ( ID VARCHAR2(10) not null, BOOKNAME VARCHAR2(10) not null, PRICE VARCHAR2(10) not null, CID VARCHAR2(10) not null ); -- %rowcount是SQL的属性表示影响了多少条记录 1、insert------------------------------------------------------------------------------------------------- create or replace procedure say_hello( i_name in varchar2, --输入参数,不带类型长度 o_result_msg out varchar2 --输出参数,不带类型长度 ) as --在as和begin之间定义变量,需要声明类型长度 v_price varchar2(100); e_myException exception; begin insert into book(id,bookname,price) values (1,2,3); --这里会报错,下面的不再继续执行 o_result_msg := 'success'; exception --报错误之后,执行错误的地方 when others then rollback; o_result_msg := substr(sqlcode, 1, 200); --返回错误码 end; --调用过程-------------------------------------------------- declare i_name varchar2(100) := 'dd'; o_result_msg varchar2(100); begin say_hello(i_name,o_result_msg); --注意存储过程没有返回值 dbms_output.put_line(o_result_msg); end; 2、update/delete------------------------------------------------------------------------------------ create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin update book set price = '55' where bookname = i_name; delete from book where bookname = i_name; if sql%notfound then raise e_myException; end if; o_result_msg := 'success'; exception when e_myException then rollback; o_result_msg := 'update or delete dail'; end; 3、select------------------------------------------------------------------------------------------------------------------- create or replace procedure say_hello( i_name in varchar2, o_result_msg out varchar2 ) as v_price varchar2(100); e_myException exception; begin select price into v_price from book where bookname = i_name; o_result_msg := 'success'; exception when no_data_found then rollback; o_result_msg := 'select into dail'; end;
存储过程入参,不论类型,缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间,同时,存储过程中可以再调用其它的存储过程,如果要保证存储过程之间的事务处理不受影响,可以定义为自治事务。
create or replace procedure say_hello(
i_name in varchar2, --入参 ,入参和出参数据不能有长度。对于入参为null情况下给予缺省值
i_flag number, -- 缺省时为in
o_ret out number
)
as
v_name varchar2(100); --定义变量在as和begin之间,需要长度
begin
if i_name is null then
v_name := '0';
else
v_name := i_name;
end if;
insert into phone(..,wname..,) values(..,v_name,..);
end;
或直接在insert语句中调用nvl函数赋缺省值
insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值
5.2 带一个参数的存储过程
输入参数in,输入参数不能进行 :=赋值,但可以将它赋给as后面定义的变量【输入参数不能在存储过程中被赋值,它是只读的;但它的值可以赋给别的变量】;
输入参数in,可以作为变量进行条件判断、is null null值判断;
默认不写就是in;
存储过程没有重载,这个有参的say_hello会替代已经存在的无参say_hello。
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a'; --存储过程入参v_name不能做为赋值目标
dbms_output.put_line('hello '||v_name);
end;
存储过程输入参数作为变量进行条件判断
create or replace procedure say_hello(
i_opFlag in number
)
as
v_name varchar2(100);
begin
if i_opFlag = 1 then
v_name :='0';
else
v_name :='haha';
end if;
dbms_output.put_line('hello '||v_name);
end;
利用存储过程中定义的变量对入参的空值处理:
create or replace procedure say_hello(
i_name in varchar2
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name :='0';
else
v_name :=i_name;--将入赋值给定义变量
end if;
dbms_output.put_line('hello '||v_name);
end;
5.3 多个参数的存储过程
out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值
create or replace procedure say_hello(
v_name in varchar2,
v_content out varchar2
)
begin
v_content:='hello'||v_name;
end;
调用:
declare
v_con varchar2(200); --声明变量时带长度
v_in varchar2(20):='wang'; --赋值
begin
say_hello(v_in,v_con); -- 存储过程没有返回值,这里不用接受
dbms_output.put_line(v_con);
end;
5.4 in out参数,既赋值又取值
create or replace procedure say_hello(v_name in out varchar2)
as
begin
v_name:='hi '||v_name;
end;
调用:
declare
v_inout varchar2(20):='wangsu';
begin
say_hello(v_inout);
dbms_output.put_line(v_inout); --直接输入参数
end;
5.5 对存储过程入参赋缺省值
create or replace procedure say_hello(
v_name varchar2 default 'susu',
v_content varchar2 default 'hello'
)
as
begin
dbms_output.put_line(v_name||' '||v_content);
end;
调用:(用指明形参名的方式调用更好)
begin
say_hello();
end;
或
begin
say_hello('cheng');
end;
或
begin
say_hello(v_name=>'cheng');
end;
6、 PLSQL中的function
FUNCTION和PROCEDURE的区别
1、函数有返回值,过程没有,调用过程不能用变量去接受
2、函数调用在一个表达式中,过程则是作为pl/sql程序的一个语句
过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程
有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的
主要区别不在于返回值,而在于他们的调用方式,过程是作为一个独立执行语句调用的,函数以合法的表达式的方式调用
--1、创建函数:获取某部门的工资总和 create or replace function get_salary( dept_no number, emp_count out number) return number is v_sum number; begin select sum(salary), count(*) into v_sum, emp_count from emp wher emp.id=dept_no; return v_sum; exception when no_data_found then dbms_output.put_line('你需要的数据不存在!'); when others then dbms_output.put_line(sqlcode || '----' || sqlerrm); end get_salary; --2、函数的调用 --2.1第一种:位置表示法 declare v_num number; v_sum number; begin v_sum := get_salary(10, v_num); dbms_output.put_line('部门号为:10的工资总和:'|| v_sum || ', 人数为:'||v_num); end; --2.2 名称表示法 declare v_num number; v_sum number; begin v_sum := get_salary(emp_count => v_num, dept_no => 10); dbms_output.put_line('部门号为:10的工资总和:'||v_sum ||',人数为:'||v_num); end;
带out参数的函数:
create or replace function func(
v_name in varchar2,
v_content out varchar2
)
return varchar2
is
begin
v_content:=v_name||' hello';
return v_content;
end;
调用:
declare
v_name varchar2(20);
v_name1 varchar2(20);
begin
v_name1:=func('susu',v_name);--返回v_name值
dbms_output.put_line(v_name1);--打印func结果
dbms_output.put_line(v_name);--打印v_name结果
end;
带in out 参数的函数
create or replace function func(
v_name in out varchar2)
return varchar2
is
begin
v_name:=v_name||' hello';
return 'cheng';
end;
调用:
declare
v_inout varchar2(20):='world';
v_ret varchar2(20);
begin
v_ret:=func(v_inout);--返回调用v_inout值(作为出参)
dbms_output.put_line(v_ret);--打印func结果
dbms_output.put_line(v_inout);--返回v_name结果
end;
7、包
--包头(定义了一个函数和一个过程) Create or replace package pkg_huxiaohong_product IS procedure update_sal(e_name varchar2, newsal number); function ann_income(e_name varchar2) return number; END; --包体(实现上面的函数和过程) create or replace package body pkg_huxiaohong_product IS --过程,没有返回值 proceducre update_sal(e_name varchar2, newsal number) IS Begin update emp1 set sal = newsal where ename = e_name; End; --函数,有返回值 function ann_income(e_name varchar2) return number IS annsal number; Begin select sal * 12 + nvl(common,0) into annsal form emp1 where ename = e_name; return annsal; End; End; --调用包 --共有元素调用:包名,元素名 --过程调用: sql>exec pkg_huxiaohong_product.update_sal('scott', 1200); --函数调用: declare v_annsal number(7,2); Begin v_annsal:=pkg_huxiaohong_product.ann_income('scott'); dbms_output.put_line('年薪为:'|| v_annsal); End; -- 删除包 drop package[body[user.] pkg_huxiaohong_product
7.1 记录
--2、声明记录类型 declare type emp_record is record( --声明一个记录类型 v_name emp.ename%type, --动态获取变量的类型 ); v_emp_record emp_record; --定义一个记录类型的成员变量 begin select ename into v_emp_record from emp ; dbms_output.put_line('姓名是'||v_name); end;