Oracle10G学习笔记之五Pl/sql高级

游标是一种pl/sql控制结构，可以对sql语句的处理进行显式控制，便于对表的行数据逐条进行处理。
游标的属性：
%found
%isopen
%notfound
%rowcount

eg1:
/*游标使用*/
declare
cursor mycur is
select * from emp;
myrecord emp%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.empno||'   '||myrecord.ename||'   '||myrecord.job);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/

eg2:
/*带参数的游标*/
declare
cursor mycur(no number) is
select * from emp where empno=no;
myrecord emp%rowtype;
begin
open mycur(7900);
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.empno||'   '||myrecord.ename||'   '||myrecord.job);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/

eg3:
/*带参数的游标2*/
declare
cursor mycur(no number) is
select * from emp where empno=no;
begin
for myrecord in mycur(7900) loop
dbms_output.put_line(myrecord.empno||'   '||myrecord.ename||'   '||myrecord.job);
end loop;
end;
/

eg4:
/*带参数的游标使用3*/
declare
name emp.ename%type;
cursor mycur(no number) is
select ename from emp where empno=no;
begin
if mycur%isopen then
dbms_output.put_line('游标已经被打开！');
else
open mycur(7900);
end if;
fetch mycur into name;
close mycur;
dbms_output.put_line(name);
end;
/

eg4:
/*带参数的游标使用4*/
declare
cursor mycur is
select * from emp;
myrecord emp%rowtype;
begin
open mycur;
loop
fetch mycur into myrecord;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line('记录数为：'||mycur%rowcount);
end loop;
close mycur;
end;
/

eg5:
/*利用游标修改数据*/
declare
cursor mycur is
select sal from emp for update;
sal emp.sal%type;
begin
open mycur;
fetch mycur into sal;
while mycur%found loop
update emp set sal=sal+100 where current of mycur;
fetch mycur into sal;
end loop;
close mycur;
end;
/

eg6:
/*隐式游标*/
begin
for cur in(select ename from emp) loop
dbms_output.put_line(cur.ename);
end loop;
end;
/

eg7:
/*仅带输入参数的存储过程*/
/*创建存储过程*/
create or replace procedure myproc(no number)
is
name VARCHAR2(10);
begin
select ename into name from emp where empno=no;
dbms_output.put_line(name);
end myproc;
/
/*show errors procedure myproc*/
/*执行存储过程1*/
begin
myproc(7900);
end;
/*执行存储过程2*/
execute myproc(7900);

eg8:
/*带输出参数的存储过程*/
create or replace procedure myproc2(no number,name out varchar2)
is
begin
select ename into name from emp where empno=no;
end myproc2;
/
/*执行存储过程*/
delcare
no number(4);
name varchar2(10);;
begin
no:=7900;
myproc2(no,name);
dbms_output.put_line(name);
end;
/

eg9:
/*创建包声明*/
create or replace package comm_package
is
    g_comm number:=100;
    procedure reset_comm(p_comm in number);
end comm_package;
/

eg10:
/*创建包体*/
create or replace package body comm_package
is
    function valid_comm(p_comm in number)
    return boolean
    is
        v_comm number;
    begin
        select max(comm) into v_comm from emp;
        if p_comm>v_comm then return(false);
        else return(true);
        end if;
    end valid_comm;
    procedure reset_comm(p_comm in number)
    is
    begin
        if valid_comm(p_comm) then
            g_comm:=p_comm;
        else
            dbms_output.put_line('非法数据');
        end if;
    end reset_comm;
end comm_package;

eg11:
/*调用包结构*/
/*从SQL*Plus 中调用包中的过程*/
EXECUTE comm_package.reset_comm(2000);
/*从不同的方案中调用包中的过程*/
EXECUTE scott.comm_package.reset_comm(2000);
/*通过匿名块调用包中的过程*/
BEGIN
comm_package.reset_comm(2000);
END;
/*通过过程调用包中的过程*/
CREATE OR REPLACE PROCEDURE test
IS
BEGIN
comm_package.reset_comm(2000);
END;
/
exec test;

eg12:
/*声明无体包*/
CREATE OR REPLACE PACKAGE global_consts
IS
    mile_2_kilo CONSTANT NUMBER := 1.6093;
    kilo_2_mile CONSTANT NUMBER := 0.6214;
    yard_2_meter CONSTANT NUMBER := 0.9144;
    meter_2_yard CONSTANT NUMBER := 1.0936;
END global_consts;
/
/*调用无体包*/
EXECUTE DBMS_OUTPUT.PUT_LINE('20 miles = '||20*global_consts.mile_2_kilo||'km');

/*调用无体包*/
CREATE OR REPLACE PROCEDURE meter_to_yard
(p_meter IN NUMBER, p_yard OUT NUMBER)
IS
BEGIN
p_yard := p_meter * global_consts.meter_2_yard;
END meter_to_yard;
/
VARIABLE yard NUMBER
EXECUTE meter_to_yard (1, :yard)
PRINT yard

eg13:
/*删除包声明和包体*/
DROP PACKAGE package_name;
/*只删除包体*/
DROP PACKAGE BODY package_name;

Ps:
开发包的原则
•先定义包声明，再定义包体
•包声明中应该只包含公共结构
•改变包的声明需要重新编译每个涉及到的子程序
•包体的变化不要求重新编译包的声明
•包声明应该尽可能包含少的内容

包的好处
•模块性：封装相关的结构
•使应用程序设计容易：可以分别编码和编译声明和体
•隐藏信息：–只有在包声明中的定义是可见和可以被应用程序访问–在包体中的私有结构是隐藏的和不可访问的–所有在包体
中的代码是隐藏的
•更好的性能：–在包第一次被引用时，整个包被装载到内存中–对所有用户来说只有一个拷贝在内存中