--

--創建增刪改查的包

CREATE

 

OR

 

REPLACE

 package  TESTPACKAGE 

IS

TYPE mycursor 

IS

 REF 

CURSOR

;

type mycursorid 

is

 ref 

cursor

;

type mycursorLike 

is

 ref 

cursor

; 

--

-模糊查詢

type mycursor1 

is

 ref 

cursor

; 

--

多條件查詢

PROCEDURE

 updateRecords(v_first_name 

in

 tb1.first_name

%

type,v_name 

in

 tb1.name

%

type);

PROCEDURE

 deleteRecords(v_first_name 

in

 tb1.first_name

%

type);

procedure

 selectRecords(ref_cursor out mycursor);

procedure

 insertRecords(v_name 

in

 tb1.name

%

type);

procedure

 selectByIdRecords(v_first_name 

in

 tb1.first_name

%

type,ref_cursorid out mycursorid);

procedure

 selectAllByNameRecords(ref_likecursor out mycursorLike,v_name 

in

 tb1.name

%

type);

procedure

 selectTbTest(ref_cur out mycursor1,i_name 

in

  tb_test.name

%

type, i_sex  

in

  tb_test.sex

%

type, i_age  

in

  tb_test.age

%

type);

END

 TESTPACKAGE;

--

創建增刪改查的包體

CREATE

 

OR

 

REPLACE

 package body  TESTPACKAGE 

IS

--

修改的

PROCEDURE

 updateRecords(v_first_name 

in

 tb1.first_name

%

type,v_name 

in

 tb1.name

%

type)

is

begin

update

 tb1 

set

 name

=

v_name 

where

 first_name

=

v_first_name;

end

 updateRecords;

--

查詢的

PROCEDURE

 selectRecords(ref_cursor out mycursor)

IS

BEGIN

 

OPEN

 ref_cursor 

for

 

select

 

*

 

from

 tb1;

END

 selectRecords;

--

刪除的

procedure

 deleteRecords(v_first_name 

in

 tb1.first_name

%

type)

is

begin

delete

 

from

 tb1 

where

 first_name

=

v_first_name;

end

 deleteRecords;

--

增加的

procedure

 insertRecords(v_name 

in

 tb1.name

%

type)

is

begin

 

insert

 

into

 tb1(first_name,name) 

values

(squ_cg_test.nextval,v_name);

end

 insertRecords;

--

根據id查找該條信息

procedure

 selectByIdRecords(v_first_name 

in

 tb1.first_name

%

type,ref_cursorid out mycursorid)

is

begin

open

 ref_cursorid 

for

 

select

 

*

 

from

 tb1 

where

 first_name

=

v_first_name;

end

 selectByIdRecords;

--

模糊查詢

procedure

 selectAllByNameRecords(ref_likecursor out mycursorLike,v_name 

in

 tb1.name

%

type)

is

begin

open

 ref_likecursor 

for

 

select

 

*

 

from

 tb1 

where

 name 

like

 v_name

||

'

%

'

;

end

 selectAllByNameRecords;

--

多條件查詢模糊查詢

procedure

 selectTbTest(

 ref_cur out mycursor1,

 i_name 

in

  tb_test.name

%

type,

  i_sex  

in

  tb_test.sex

%

type,

  i_age  

in

  tb_test.age

%

type

)

is

begin

open

 ref_cur 

for

 

select

 

*

 

from

 tb_test

where

 ( name 

like

 

'

%

'

||

i_name

||

'

%

'

 

or

 i_name 

is

 

null

)

and

 ( sex  

=

 i_sex  

or

 i_sex 

is

 

null

)

and

 ( age  

=

 i_age  

or

 i_age 

is

 

null

);

end

 selectTbTest;

end

 TESTPACKAGE;