oracle中 包,Oracle 中包的应用

(

1

)创建包头

create

or

replace

package pck_infos

as

--

总数量

v_infos_count

number

;

--

初始化操作

procedure

p_init(p_max

number

, p_min

number

);

--

显示学生列表数据

procedure

p_list_infos;

--

增加一条学生记录

procedure

p_add_infos(

p_stuid infos.stuid

%

type,

p_stuname infos.stuname

%

type,

p_gender infos.gender

%

type,

p_age infos.age

%

type,

p_seat infos.seat

%

type,

p_enrolldate infos.enrolldate

%

type,

p_stuaddress infos.stuaddress

%

type,

p_classno infos.classno

%

type);

--

删除一条学生记录

procedure

p_delete_infos(p_stuid infos.stuid

%

type);

--

根据stuid修改学生的姓名

procedure

p_edit_infos_name(

p_stuid infos.stuid

%

type,

p_stuname infos.stuname

%

type);

end

;

(

2

)创建包体

create

or

replace

package body pck_infos

as

v_msg

varchar2

(

100

);

--

show message

v_max_age

number

;

--

max age

v_min_age

number

;

--

min age

--

判断学生是否存在

function

f_exist_infos(p_stuid infos.stuid

%

type)

return

boolean;

--

show msg

procedure

p_show_msg;

--

初始化操作

procedure

p_init(p_max

number

, p_min

number

)

as

begin

select

count

(stuid)

into

v_infos_count

from

infos;

v_max_age:

=

p_max;

v_min_age:

=

p_min;

v_msg:

=

'

init finished!

'

;

p_show_msg;

end

p_init;

--

显示信息

procedure

p_show_msg

as

begin

dbms_output.put_line(v_msg);

end

p_show_msg;

--

判断学生是否存在

function

f_exist_infos(p_stuid infos.stuid

%

type)

return

boolean

as

v_num

number

;

begin

select

count

(stuid)

into

v_num

from

infos

where

stuid

=

p_stuid;

if

v_num

=

1

then

return

true;

else

return

false;

end

if

;

end

f_exist_infos;

--

显示学生列表数据

procedure

p_list_infos

as

v_infos_record infos

%

rowtype;

cursor

cur_infos

is

select

*

from

infos;

begin

open

cur_infos;

loop

fetch

cur_infos

into

v_infos_record;

exit

when

cur_infos

%

notfound;

dbms_output.put_line(

'

stuid:

'

||

v_infos_record.stuid);

end

loop;

close

cur_infos;

end

p_list_infos;

--

增加一条学生记录

procedure

p_add_infos(

p_stuid infos.stuid

%

type,

p_stuname infos.stuname

%

type,

p_gender infos.gender

%

type,

p_age infos.age

%

type,

p_seat infos.seat

%

type,

p_enrolldate infos.enrolldate

%

type,

p_stuaddress infos.stuaddress

%

type,

p_classno infos.classno

%

type)

as

begin

if

not

f_exist_infos(p_stuid)

then

insert

into

infos(stuid,stuname,gender,age,seat,enrolldate,stuaddress,classno)

values

(p_stuid,p_stuname,p_gender,p_age,p_seat,p_enrolldate,p_stuaddress,p_classno);

commit

;

v_infos_count:

=

v_infos_count

+

1

;

else

v_msg:

=

'

already exist!

'

;

end

if

;

end

p_add_infos;

--

删除一条学生记录

procedure

p_delete_infos(p_stuid infos.stuid

%

type)

as

begin

if

f_exist_infos(p_stuid)

then

delete

from

infos

where

stuid

=

p_stuid;

commit

;

v_infos_count:

=

v_infos_count

-

1

;

else

v_msg:

=

'

not exist infos!

'

;

end

if

;

end

p_delete_infos;

--

根据stuid修改学生的姓名

procedure

p_edit_infos_name(

p_stuid infos.stuid

%

type,

p_stuname infos.stuname

%

type)

as

begin

if

f_exist_infos(p_stuid)

then

update

infos

set

stuname

=

p_stuname

where

stuid

=

p_stuid;

commit

;

else

v_msg:

=

'

not exists infos

'

;

end

if

;

end

p_edit_infos_name;

end

;