CREATE OR REPLACE PROCEDURE ORACLE_PROCEDURE_CASE(importData in varchar2,returnValue out varchar2) AS
/**
oracle存储过程小结
returnValue返回值
author:iori
*/
--定义变量
v_rowData varchar2(100);
--变量,用于遍历游标
v_i number(1);
--定义游标
cursor c_importData is select * from test_table
BEGIN
--下面是一个游标的嵌套
v_i := 1;
open c_importData;
loop
fetch c_importData into v_rowData;
exit when c_importData%notfound;
--遍历游标,得到相应的uid,项目ID和项目语言
open c_rowData;
loop
fetch c_rowData into v_cellData;
exit when c_rowData%notfound;
if v_i = 1 then
v_uid := v_cellData;
elsif v_i = 2 then
v_prjId := v_cellData;
else
v_prjlag := v_cellData;
v_i := 0;
end if;
v_i := v_i + 1;
end loop;
close c_rowData;
--用begin/end可以起到try/catch的作用,exception相当于catch异常来处理
begin
select u.ID into v_Id from test_table u
where u.ID = Id and u.USER_NAME = v_uid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
returnValue := v_uid || '用户没有';
return;
end;
returnValue := '1';
EXCEPTION
WHEN NO_DATA_FOUND THEN
returnValue := '数据有误';
WHEN OTHERS THEN
returnValue := '数据有误';
END ORACLE_PROCEDURE_CASE;
/
--输出调试信息,相当于System.out.println()
dbms_output.put_line("输出调试信息");