--创建表

create table TESTTABLE

(

  id1  VARCHAR2(12),

  name VARCHAR2(32)

)

select t.id1,t.name from TESTTABLE t

insert into TESTTABLE (ID1, NAME)

values ('1', 'zhangsan');

insert into TESTTABLE (ID1, NAME)

values ('2', 'lisi');

insert into TESTTABLE (ID1, NAME)

values ('3', 'wangwu');

insert into TESTTABLE (ID1, NAME)

values ('4', 'xiaoliu');

insert into TESTTABLE (ID1, NAME)

values ('5', 'laowu');

//-------------------------　　声明部分　　---------------------------

create or replace package PRO_TEST_TESTTABLE is

PROCEDURE Test_Count;

PROCEDURE Test_Pra(Name IN varchar2);

PROCEDURE Test_Cursor;

PROCEDURE Test_Case;

PROCEDURE Test_Loop;

PROCEDURE Test_Cursor_Loop;

PROCEDURE Test_Error(isOk in varchar2);

PROCEDURE Test_Exception(isOk in varchar2);

end PRO_TEST_TESTTABLE;

//-------------------------　　body部分　　---------------------------

create or replace package body PRO_TEST_TESTTABLE IS

PROCEDURE Test_Count IS

v_total number(1);

begin

select count(*) into v_total from TESTTABLE;

DBMS_OUTPUT.put_line('总人数：'||v_total);

end test_count;

PROCEDURE Test_Pra(Name IN varchar2)IS

begin

null;

end Test_Pra;

-- 关于 游标 if,for 的例子

PROCEDURE Test_Cursor IS

cursor var_c is select * from TESTTABLE;

BEGIN

for temp in var_c loop

if temp.name = 'lisi' then

dbms_output.put_line('name----- '||temp.name);

elsif temp.name = 'zhangsan' then

dbms_output.put_line('name----- '||temp.name);

else

dbms_output.put_line('------name------ ');

end if;

end loop;

NULL;

END Test_Cursor;

-- 关于 Case的例子

PROCEDURE Test_Case IS

cursor var_c is select * from TESTTABLE;

BEGIN

for temp in var_c loop

case

when 'zhangsan'= temp.name then

dbms_output.put_line(temp.name);

when 'zhangsan'= temp.name then

dbms_output.put_line(temp.name);

else

dbms_output.put_line('--------------'||temp.name);

end case ;

end loop;

END Test_Case;

-- 关于 Loop的例子

PROCEDURE Test_Loop IS

sum1 number := 0;

temp number :=0 ;

begin

loop

exit when temp >= 2 ;

sum1 := sum1+temp;

temp := temp +1;

end loop;

dbms_output.put_line(sum1 );

END Test_Loop;

PROCEDURE Test_Cursor_Loop IS

cursor var_cur is select * from TESTTABLE;

stu_name varchar2(100);

T TESTTABLE%rowtype;

begin

open var_cur;

loop

fetch var_cur into T;

exit when var_cur%notfound;

dbms_output.put_line(T.name);

end loop;

close var_cur;

END Test_Cursor_Loop;

---关于异常处理的例子2

PROCEDURE Test_Error(isOk in varchar2) IS

error EXCEPTION;

BEGIN

if isOk = 'OK' then

insert into TESTTABLE (id1,name) values ('1',isOk);

elsif isOk = 'NO' then

insert into TESTTABLE (id1,name) values ('2',isOk);

raise error;

else

Dbms_Output.put_line('isOk' || isOk);

end if;

commit;

exception

when error then

rollback;

Dbms_Output.put_line('ERRO');

END Test_Error;

---关于异常处理的例子1

PROCEDURE Test_Exception(isOk in varchar2) IS

Temp varchar2(12);

BEGIN

select name into Temp from TESTTABLE where name = isOk;

dbms_output.put_line(Temp);

exception

when no_data_found

then

dbms_output.put_line('try');

when TOO_MANY_ROWS

then

dbms_output.put_line('more');

END Test_Exception;

end PRO_TEST_TESTTABLE;