SQL codecreate or replace package pack_sunyard_test is
type refcur is ref cursor;
/******************************************************************************
--函数名称: func_sunyard_test
--作者： sunyard_zhengxl
--时间： 2007年04月01日
--使用源表名称: sunyard.tset_a(测试表A)
sunyard.tset_b(测试表B)
--目标表明称：
--参数说明：
-- i_id (ID值)
-- o_name (返回NAME值)
--功能： oracle plsql开发示例-函数
--解释：
******************************************************************************/
function func_sunyard_test(
i_id in number --ID值
)
return varchar2;
/******************************************************************************
--存储过程名称: proc_sunyard_test1
--作者： sunyard_zhengxl
--时间： 2007年04月01日
--使用源表名称: sunyard.tset_a(测试表A)
sunyard.tset_b(测试表B)
--目标表明称：
--参数说明：
-- i_id (ID值)
-- o_name (返回NAME值)
--功能： oracle plsql开发示例-数据类型、控制结构、调用函数
--解释：
******************************************************************************/
procedure proc_sunyard_test1(
i_id in number, --ID值
o_name out varchar2 --姓名
);
/******************************************************************************
--存储过程名称: proc_sunyard_test2
--作者： sunyard_zhengxl
--时间： 2007年04月01日
--使用源表名称: sunyard.tset_a(测试表A)
sunyard.tset_b(测试表B)
--目标表明称：
--参数说明：
-- i_id (ID值)
-- o_name (返回NAME值)
--功能： oracle plsql开发示例-调用过程、游标、游标变量、动态sql
--解释：
******************************************************************************/
procedure proc_sunyard_test2(
i_id in number, --ID值
o_name out varchar2, --姓名
o_cur out refcur --游标变量
);
end pack_sunyard_test;
/
create or replace package body pack_sunyard_test is
/******************************************************************************
--函数名称: func_sunyard_test
--作者： sunyard_zhengxl
--时间： 2007年04月01日
--使用源表名称: sunyard.tset_a(测试表A)
sunyard.tset_b(测试表B)
--目标表明称：
--参数说明：
-- i_id (ID值)
-- o_name (返回NAME值)
--功能： oracle plsql开发示例-函数
--解释：
******************************************************************************/
function func_sunyard_test(
i_id in number --ID值
)
return varchar2
is
v_name varchar2(10); --姓名
begin
--根据输入的ID查询相应的NAME值
select a.name
into v_name
from test_a a
where a.id = i_id;
return v_name;
exception
when others then
raise;--抛出异常到调用模块
end func_sunyard_test;
procedure proc_sunyard_test1(
i_id in number, --ID值
o_name out varchar2 --姓名
)
is
v_code1 number;
v_code2 number(17,2);
v_name varchar2(100);
begin
--number显式或隐式转换成varchar2时会将前面的0转换掉
v_name := '001';
v_code1 := v_name;
--控制结构
if v_name = '001' then
v_code1 := 0;
elsif v_name = '002' then
v_code1 := 1;
else
v_code1 := -1;
end if;
case v_name
when '001' then v_code1 := 0;
when '002' then v_code1 := 1;
else v_code1 := -1;
end case;
loop
v_code1 := v_code1 + 1;
exit when v_code1 > 4;
end loop;
while v_code1 < 10 loop
v_code1 := v_code1 + 1;
end loop;
for v_code2 in 1..10 loop
v_code1 := v_code1 + v_code2;
end loop;
--调用函数
o_name := func_sunyard_test(i_id);
exception
when others then
o_name := sqlcode||'-'||sqlerrm;
return;
end proc_sunyard_test1;
procedure proc_sunyard_test2(
i_id in number, --ID值
o_name out varchar2, --姓名
o_cur out refcur --游标变量
)
is
v_id number;
v_name varchar2(100);
v_sql varchar2(2000);
cursor cur_select is
select a.id,a.name
from test_a a
where a.id >= 3;
cursor cursor_select(id number) is
select a.id,a.name
from test_a a
where a.id >= id;
begin
--调用过程
sunyard.pack_sunyard_test.proc_sunyard_test1(i_id,v_name);
--隐式游标
select a.name
into v_name
from test_a a
where a.id = i_id;
--小范围异常控制
begin
select a.name
into v_name
from test_a a
where a.id = i_id + 1;
exception
when others then
v_name := '未匹配';
return;
end;
--小技巧
select nvl(max(a.name),'未匹配')
into v_name
from test_a a
where a.id = i_id + 2;
o_name := v_name;
--显式游标
open cur_select;
loop
fetch cur_select into v_id,v_name;
exit when cur_select%notfound;--此行若放到输出语句之后，则会出现重复行
o_name := to_char(v_id)||'-'||v_name;
dbms_output.put_line(o_name);
end loop;
close cur_select;
--游标for循环
for c_cur in cursor_select(3) loop
v_id := c_cur.id;
v_name := c_cur.name;
o_name := to_char(v_id)||'-'||v_name;
dbms_output.put_line(o_name);
end loop;
--游标变量
v_name := '10000001';
v_sql := ' select a.code,a.name '
||' from test_a a,test_b b '
||' where a.id = b.id '
||' and a.id < '||i_id
||' and a.code != '''||v_name||'''';
open o_cur for v_sql;
--动态sql
v_sql := ' select a.id,a.name '
||' from test_a a '
||' where a.id = '||i_id;
execute immediate v_sql into v_id,v_name;
v_sql := ' select a.id,a.name '
||' from test_a a '
||' where a.id = :i_id';
execute immediate v_sql into v_id,v_name using i_id;
--DDL语句
delete from test_a a where a.id = 10;
update test_a a set a.name = '' where a.id = 11;
insert into test_a(id,code,name,age,address) values(10,'10000010','十',10,'石');
commit;
exception
when others then
o_name := sqlcode||'-'||sqlerrm;
return;
end proc_sunyard_test2;
end pack_sunyard_test;
/
