程序包：程序包规范、主体两部分组成

程序包规范

CREATE [OR REPLACE] PACKAGE package_name

IS|AS

[Public item declarations]

[Subprogram specification]

END [package_name];

程序包主体

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS

[Private item declarations]

[Subprogram bodies]

[BEGIN

Initialization]

END [package_name];

例1：

--用户注册（用户名不能重复）、登陆、所有用户信息的程序包

--程序包的声明

create or replace package package_user

is

       --声明一个引用游标

       type t_cursor is ref cursor;

       --返回所有用户信息

       procedure proc_UserGet(curUser out t_cursor);

       --注册

       procedure proc_UserAdd(p_name varchar2,p_realyname varchar2, p_password varchar2,p_tel varchar2, p_email varchar2,p_address varchar2, isSuccess out number);

       --登录

       procedure proc_Login(p_name varchar2,p_password varchar2, isSuccess out number);

end package_user;

程序包的主体：

create or replace package body package_user

is

       --返回所有用户信息

       procedure proc_UserGet(curUser out t_cursor)

       is

       begin

              open curUser for select * from userTab;

       end;

--注册用户

       procedure proc_UserAdd(p_name varchar2,p_realyname varchar2, p_password varchar2,p_tel varchar2, p_email varchar2,p_address varchar2, isSuccess out number)

       is    

              count_add number(4);

       begin

              //判断用户是否存在

              select count(*) into count_add from userTab where u_name=p_name;

              if count_add<1 then

                     insert into userTab values(seq_user.nextval,p_name,p_realyname,p_password,p_tel, p_email,p_address);

                     isSuccess := 1;

                     dbms_output.put_line('注册成功');

              else

                     isSuccess := 0;

                     dbms_output.put_line('用户名已存在，请重新注册！');

              end if;

       exception

              when others then

                     dbms_output.put_line('错误!');

       end;

--登录

       procedure proc_Login(p_name varchar2,p_password varchar2, isSuccess out number)                                                                                                   

       is

              count_add number(4);                   

       begin

              select count(*) into count_add from userTab where u_name=p_name and u_password=p_password;

              if count_add<1 then

                     isSuccess := 0;

                     dbms_output.put_line('你的用户或密码不正确,请重新登录！');

              else

                     isSuccess := 1;

                     dbms_output.put_line('登录成功!');

              end if;

       exception

              when others then

                     dbms_output.put_line('错误!');

       end;

end;

程序包的测试：

--测试：注册

declare

       isSuccess number(4);

begin

       package_user.proc_UserAdd('zhongzi2','周姿','zhongzi','2526294','zhong@163.com','湖南省湘潭', isSuccess) ;

       dbms_output.put_line( isSuccess );

end;

--测试：登陆

declare

       isSuccess number(4);

begin

       package_user.PROC_LOGIN('zhongzi2','zhongzi', isSuccess) ;

       dbms_output.put_line( isSuccess );

end;

--测试：得到所有用户信息

declare

       type user_curtype is ref cursor;

       curUser user_curtype;

       rowUser userTab%rowtype;

begin

       package_user.proc_UserGet(curUser) ;

       loop

              fetch curUser into rowUser;

              dbms_output.put_line('编号：' || rowUser.u_id || ' 用户名：' ||rowUser.u_name);

              exit when curUser%notfound;

       end loop;

end;

显示创建的子程序和程序包的信息

Select * from USER_OBJECTS;

显示存储子程序和程序包的源代码

SELECT line, text FROM USER_SOURC WHERE NAME='过程名';