Oracle存储过程例子游标异常

来源：互联网发布：摄影测量数据处理软件编辑：程序博客网时间：2024/04/30 17:08

四种情况：有无参数、有无输出
SQL中调用存储过程语句：call procedure_name();
注：调用时”()”是不可少的，无论是有参数还是无参数.
            
定义对数据库存储过程的调用时
1、无参数存储过程:{call procedure_name}
2、仅有输入参数的存储过程：{call procedure_name(?,?...)}。
这里?表示输入参数，创建存储过程时用in表示输入参数
3、仅有输出参数的存储过程:{call procedure_name(?,?...)}。
这里的?表示输出参数，创建存储过程时用out表示输出参数
4、既有输入参数又有输出参数的存储过程{call procedure_name(?,?...)}。
这里的？有表示输出参数的，也有表示输入参数的
           
下面将会对这4种情况分别举出实例！！！
1、无参数存储过程
CREATE OR REPLACE PROCEDURE stu_proc AS
    --声明语句段
    v_name VARCHAR2(20);
BEGIN
    --执行语句段
    SELECT o.sname INTO v_name FROM student o WHERE o.id=4;
    dbms_output.put_line(v_name);
EXCEPTION
    --异常处理语句段
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
END;

2、仅带入参的存储过程
CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type) AS
    --声明语句段
    v_name varchar2(20);
BEGIN
    --执行语句段
    SELECT o.sname INTO v_name FROM student o where o.id=v_id;
    dbms_output.put_line(v_name);
EXCEPTION
    --异常处理语句段
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
END;

3、仅带出参的存储过程 
--此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明
CREATE OR REPLACE PROCEDURE stu_proc(v_name OUT student.sname%type) AS
    --声明语句段
BEGIN
    --执行语句段
    SELECT o.sname INTO v_name FROM student o where o.id=1;
    dbms_output.put_line(v_name);
EXCEPTION
    --异常处理语句段
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
END;

4、带入参和出参的存储过程
--此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明
CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type, v_name OUT student.sname%type) AS
    --声明语句段
BEGIN
    --执行语句段
    SELECT o.sname INTO v_name FROM student o where o.id=v_id;
    dbms_output.put_line(v_name);
EXCEPTION
    --异常处理语句段
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
END;

----------------------------------------------------------分割线-------------------------------------------------------------------
Java代码  

Qracle PL/SQL
--存储过程
1.PL/SQL
SQL是数据库普通话，每个数据库在实现SQL国际标准之外，也有自己特有的语句。（Hibernate dialect）
通讯的标准：ODBC(ado , ado.net)，JDBC(jdo , Hibernate)
ODBC、JDBC是最有效率的，但是开发繁琐，才有后来括号中的高度函数化的拓展
PL/SQL:Procudural Language Extension to SQL.
在SQL语句基础上，加上了结构化流程控制，可用多条SQL语句完成一个功能。
PL/SQL功能强大，经常用来书写复杂的业务逻辑。
语法：
set serveroutput on; //打开控制台输出
declare [变量名] [变量数据类型]; //定义变量，写在begin之前
begin
//代码块
end; //代码编辑区
:= //赋值符号
dbms_output.put_line('内容'||变量); //控制台输出，和Java不同的是连接使用的是||符号而不是加
号，注意字符串必须用单引号来修饰
范例：
declare result int;
begin
select x into result from ttt where rownum=1;
result:=result+1;
dbms_output.put_line('result='||result);
end;
学生表的创建
create table my_student
(
stuno int primary key,
stuname varchar2(20) not null,
stuage int not null
)
范例2
declare
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=2;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end;
范例3：
new -> program window ->procedure
--根据学号显示对应学生信息
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end uuu_show_student;
运行存储过程
执行execute uuu_show_student;
范例4.两数求和
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
else
x_big_number := x_num2;
x_num_index := 2;
end if;
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end show_uuu_number;
范例5.循环分支
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
elsif x_num1 > x_num2 then
x_big_number := x_num2;
x_num_index := 2;
else
dbms_output.put_line('equal');
end if;
if x_num1 <> x_num2 then
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end if;
end show_uuu_number;
》case
when....
when....
else
.....
and case;
范例6
有返回值的用function
计算三角形面积
create or replace function uuu_area(width in number,height in number) return number is
Result number;
begin
Result := 0.5*width*height;
return(Result);
end uuu_area;
运行函数
select uuu_area(3,5) from dual;
uuu_AREA(3,5)
-------------
15
范例7
》case
when....
when....
else
.....
and case;
应用
create or replace function show_uuu_day(datestr in varchar2)
return varchar2 is
Result varchar2(200);
checkdate date;
v_day varchar2(1);
begin
checkdate := to_date(trim(datestr), 'YYYY-MM-DD');
v_day := to_char(checkdate, 'D');
case v_day
when '1' then
Result := datestr || '是星期天';
when '2' then
Result := datestr || '是星期一';
when '3' then
Result := datestr || '是星期二';
when '4' then
Result := datestr || '是星期三';
when '5' then
Result := datestr || '是星期四';
when '6' then
Result := datestr || '是星期五';
when '7' then
Result := datestr || '是星期六';
else
dbms_output.put_line(datestr || '是星期六');
end case;
return(Result);
end show_uuu_day;
执行函数
SQL> select show_uuu_day('2010-11-4') from dual;
SHOW_uuu_DAY('2010-11-4')
--------------------------------------------------------------------------------
2010-11-4是星期四
范例8
循环结构
create or replace function uuu_get_sum(num1 in number, num2 in number) return number is
Result number := 0;
temp number;
begin
temp := num1;
loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop;
return(Result);
end uuu_get_sum;
执行函数
SQL> select uuu_get_sum(1,100) from dual;
uuu_GET_SUM(1,100)
------------------
5050
while loop
例：
create or replace function uuu_get_sum(num1 in number, num2 in number) return
number is
Result number := 0;
temp number;begin
temp := num1;
while num2 <= 200 loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop; return(Result);
end uuu_get_sum;
执行函数
SQL> select uuu_get_sum(1,1000) from dual;
uuu_GET_SUM(1,1000)
-------------------
0
范例8
数字for loop循环
create or replace procedure sum(begin1 number,end2 number)
as
tosum number;
begin
tosum:=0;
for i in begin1..end2
loop
tosum:=tosum+i;
end loop;
dbms_output.put_line(tosum);
end sum;
001.判断一个数是否为质数
create or replace function x_isprime(num in int) return int is
Result int;
begin
for i in 2..num-1
loop
if num mod i = 0 then
Result := 0;
return(Result);
end if;
end loop;
if num >= 2 then
Result := 1;
end if;
return(Result);
end x_isprime;
002判断质数
create or replace procedure x_prime(begini in int, endi in int)
is
count2 int := 0;
begin
for i in begini..endi
loop
if x_isprime(i) = 1 then
dbms_output.put(i||' ');
count2:=count2+1;
if count2 mod 8 = 0 then
dbms_output.put_line('');
end if;
end if;
end loop;
end x_prime;
======
debug 权限设置
grant debug connect session to test1;
003判断质数
=======
CREATE OR REPLACE PROCEDURE show_prime(bval IN INT, eval IN INT) IS
flag INT;
icount int:=0;
BEGIN
-- 取数循环
FOR i IN bval .. eval LOOP
-- 质数判断循环
flag := 1;
FOR j IN 2 .. i - 1 LOOP
IF i MOD j = 0 THEN
flag := 0;
EXIT;
END IF;
END LOOP;
IF flag = 1 THEN
dbms_output.put(i||' ');
icount:=icount+1;
if icount mod 8 =0 then
dbms_output.put_line('');
end if;
END IF;
END LOOP;
END show_prime;
======================================================================
--游标
pl/sql cursor 操作
1. 什么是游标？
oracle在执行一条SQL语句的时候，它将创建一个内存区域 (context area),该内存区域包含执行这条语句所需要的所有信息。
信息如下：
1. 该语句执行之后返回的记录集
2. 一个指针，指向了该语句在内存中的被解析后的结果。
cursor(游标) 是一个handle (pointer), 指向了这个上下文区域。
通过cursor, PL/SQL程序能够控制context area, 掌握在语句运行的时，将如何对该区域产生影响。
2. 游标的类型
1) implicit cursor
每条SQL语句执行的时候，将自动产生一个implicit游标。该游标，用户不可控制。
一个cursor将自动和每条DML语句关联（update,delete,insert), 我们可以通过cursor了解上头语句产生的结果。
所有update和delete语句相关联cursor，包含了该操作影响的行的集合。
最后打开的cursor，名字叫SQL cursor.
--------------------------------------------------------------------------------------------
update my_student set stuname='mary' where stuno=60;
dbms_output.put_line(SQL%ROWCOUNT);
2) explicit cursor
用户自己定义的游标，针对的是返回超过一条记录的查询。用户可以通过该cursor控制记录返回过程。
Record Type
记录是复杂的数据结构。记录往往表现成为表的一行。
create or replace procedure show_student2 is
vr_student my_student%ROWTYPE;
begin
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end show_student2;
a. 声明游标（declare a cursor)
该操作初始化这个游标，为其创建内存空间。
CURSOR c_cursor_name is select statement
(该游标和select语句相关联)
------------------
declare
v_name varchar2(20);
CURSOR c_mycursor is
select * from student where name like '%h%';
游标打开后，不能继续再二次打开。
b. 打开游标 (open cursor)
创建context area, 执行语句，获得rows.
open c_mycursor
c. 获取cursor中的行。
fetch cursorname into pl/sql variables
fetch cursorname into pl/sql record
d. 关闭cursor
一旦所有的行被处理结束，cursor应该被关闭。
关闭的操作通知pl/sql engine，程序对该cursor的需求已经结束，可以释放context are所占用的内存资源。
cursor一旦关闭，则不可以继续fetch . 也不能重复关闭cursor.
> 常用的cursor属性
cursorname%NOTFOUND
cursorname%FOUND
cursorname%ROWCOUNT
cursorname%ISOPEN
--------------------
create or replace procedure show_student2 is
CURSOR c_student is
select * from my_student order by stuno desc;
vr_student my_student%ROWTYPE;
TYPE simple_stu is record
(
stuname my_student.stuname%TYPE,
stuage my_student.stuage%TYPE,
stuage2 int
);
vr_simple_student simple_stu;
cursor c_simple_student is
select stuname,stuage,stuage+2 from my_student order by stuno desc;
begin
/*
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
*/
open c_student;
loop
fetch c_student into vr_student;
exit when c_student%NOTFOUND;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
if c_student %ISOPEN then
close c_student;
end if;
open c_simple_student;
loop
fetch c_simple_student into vr_simple_student;
exit when c_simple_student%NOTFOUND;
dbms_output.put_line(vr_simple_student.stuname||','||vr_simple_student.stuage
||','||vr_simple_student.stuage2);
end loop;
close c_simple_student;
end show_student2;
--------------------------------------------
cursor for loop
nested cursor
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id, first_name, last_name
FROM student
WHERE student_id < 110;
CURSOR c_course IS
SELECT c.course_no, c.description
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = v_sid;
BEGIN
/*
使用for loop cursor, 可以便捷的遍历游标，省去了open,fetch,close的书写。
连保存变量的定义也可以省略，可在for后直接书写变量名。
*/
FOR r_student IN c_student LOOP
v_sid := r_student.student_id;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(' The Student ' || r_student.student_id || ' ' ||
r_student.first_name || ' ' ||
r_student.last_name);
DBMS_OUTPUT.PUT_LINE(' is enrolled in the ' || 'following courses: ');
-- nested cursor
FOR r_course IN c_course LOOP
DBMS_OUTPUT.PUT_LINE(r_course.course_no || ' ' ||
r_course.description);
END LOOP;
END LOOP;
END;
---------------------------------------------------------------
带参数的游标
CURSOR c_student(p_stuage in my_student.stuage%type) is
select * from my_student where stuage=p_stuage order by stuno desc;
　
for vr_student in c_student(20)
loop
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
　　CURSOR c_student２(p_stuage in my_student.stuage%type，p_stuname in my_student.stuname%TYPE) is
select * from my_student where stuage=p_stuage order by stuno desc;
cursor具备的参数：
　1) cursor得到了复用。
　2) 提高了性能，压缩了返回的行的数量。
====================================================================
--异常
1.PL/SQL Exception
常用的预定义异常
a）NO_DATA_FOUND
根据查询条件，没有查询记录被找到
b）TOO_MANY_ROWS
select into 结构只能返回一条记录，赋予存储过程变量。如果select ..into..结构返回记录为多条，将产生这个异常
c）ZERO_DIVIDE
除数是0(把ORA-01476 error映射成ZERO_DIVIDE错误）
例： SQL> select 6/0 from dual;
select 6/0 from dual
ORA-01476: 除数为 0
d)VALUE_ERROR
从运算或者数据库中取得值赋予变量的时候类型不匹配或者长度不足，导致的异常
e)DUP_VAL_ON_INDEX
主键不可重复，违反主键唯一约束
f）OTHERS
(类似java Exception异常）
数据库表
create table my_student(
stuno int primary key,
stuname varchar2(20),
stuage int
);
insert into my_student values(1,'dadiv',20);
insert into my_student values(2,'mary',20);
insert into my_student values(3,'henry',20);
异常举例例子：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替换v_stuname varchar2(2)/int值不匹配或者长度不足异常
v_stuage int;
begin
insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX异常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'产生值记录太多异常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('记录太多');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主键不可重复，插入失败');
when OTHERS then
dbms_output.put_line('其它异常捕获');--一些预定义异常的父类
end uuu_show_student;
异常的作用域
DECLARE
v_student_id NUMBER := &sv_student_id;
v_name VARCHAR2(30);
v_total NUMBER(1);
-- outer block
BEGIN
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
-- inner block
BEGIN
SELECT COUNT(*)
INTO v_total
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student is registered for '||
v_total||' course(s)');
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;
自定义异常：
例：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替换v_stuname varchar2(2)/int值不匹配或者长度不足异常
v_stuage int;
e_invalid_stuno EXCEPTION;--自定义异常
begin
if x_stuno < 0 then
raise e_invalid_stuno;
else
--insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX异常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'产生值记录太多异常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('记录太多');
when e_invalid_stuno then --控制台输入execute uuu_show_student(-2);
dbms_output.put_line('学生编号不合法');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主键不可重复，插入失败');
when OTHERS then
dbms_output.put_line('其它异常捕获');--一些预定义异常的父类
end uuu_show_student;
==================================================================
--触发器的一个例子
create or replace trigger student_aud
before insert on my_student
for each row
declare
v_highage int;
begin
select stu_highage
into v_highage
from stu_stat;
if :NEW.stuage >25 then
v_highage:=v_highage+1;
end if ;
update stu_stat set stu_count=stu_count+1,stu_highage=v_highage;
end student_aud;

0 0