自己做的常用的oracle增刪改查存儲過程 。。。。。。。。。。。。。

 

----創建增刪改查的包
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;