oracle增刪改查存儲過程代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 oracle增刪改查存儲過程
作者:Dezai.CN / 发布于2011/7/7/ 739
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">--創建增刪改查的包</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">CREATE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">OR</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">REPLACE</span><span style="color: rgb(0,0,0)"> package TESTPACKAGE </span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)">
TYPE mycursor </span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)"> REF </span><span style="color: rgb(0,0,255)">CURSOR</span><span style="color: rgb(0,0,0)">;
type mycursorid </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> ref </span><span style="color: rgb(0,0,255)">cursor</span><span style="color: rgb(0,0,0)">;
type mycursorLike </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> ref </span><span style="color: rgb(0,0,255)">cursor</span><span style="color: rgb(0,0,0)">; </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">-模糊查詢</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">type mycursor1 </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> ref </span><span style="color: rgb(0,0,255)">cursor</span><span style="color: rgb(0,0,0)">; </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">多條件查詢</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">PROCEDURE</span><span style="color: rgb(0,0,0)"> updateRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type);
</span><span style="color: rgb(0,0,255)">PROCEDURE</span><span style="color: rgb(0,0,0)"> deleteRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type);
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectRecords(ref_cursor out mycursor);
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> insertRecords(v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type);
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectByIdRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,ref_cursorid out mycursorid);
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectAllByNameRecords(ref_likecursor out mycursorLike,v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type);
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectTbTest(ref_cur out mycursor1,i_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type, i_sex </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.sex</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type, i_age </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.age</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type);
</span><span style="color: rgb(0,0,255)">END</span><span style="color: rgb(0,0,0)"> TESTPACKAGE;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">創建增刪改查的包體</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">CREATE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">OR</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">REPLACE</span><span style="color: rgb(0,0,0)"> package body TESTPACKAGE </span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">修改的</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">PROCEDURE</span><span style="color: rgb(0,0,0)"> updateRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">update</span><span style="color: rgb(0,0,0)"> tb1 </span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">v_name </span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> first_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">v_first_name;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> updateRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">查詢的</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">PROCEDURE</span><span style="color: rgb(0,0,0)"> selectRecords(ref_cursor out mycursor)
</span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">BEGIN</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">OPEN</span><span style="color: rgb(0,0,0)"> ref_cursor </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb1;
</span><span style="color: rgb(0,0,255)">END</span><span style="color: rgb(0,0,0)"> selectRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">刪除的</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> deleteRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">delete</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb1 </span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> first_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">v_first_name;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> deleteRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">增加的</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> insertRecords(v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">insert</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> tb1(first_name,name) </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(squ_cg_test.nextval,v_name);
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> insertRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">根據id查找該條信息</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectByIdRecords(v_first_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.first_name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,ref_cursorid out mycursorid)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">open</span><span style="color: rgb(0,0,0)"> ref_cursorid </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb1 </span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> first_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">v_first_name;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> selectByIdRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">模糊查詢</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectAllByNameRecords(ref_likecursor out mycursorLike,v_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb1.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">open</span><span style="color: rgb(0,0,0)"> ref_likecursor </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb1 </span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> name </span><span style="color: rgb(128,128,128)">like</span><span style="color: rgb(0,0,0)"> v_name</span><span style="color: rgb(128,128,128)">||</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">%</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> selectAllByNameRecords;
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">多條件查詢模糊查詢</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> selectTbTest(
ref_cur out mycursor1,
i_name </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.name</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,
i_sex </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.sex</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type,
i_age </span><span style="color: rgb(128,128,128)">in</span><span style="color: rgb(0,0,0)"> tb_test.age</span><span style="color: rgb(128,128,128)">%</span><span style="color: rgb(0,0,0)">type
)
</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">open</span><span style="color: rgb(0,0,0)"> ref_cur </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb_test
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> ( name </span><span style="color: rgb(128,128,128)">like</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">%</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">||</span><span style="color: rgb(0,0,0)">i_name</span><span style="color: rgb(128,128,128)">||</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">%</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">or</span><span style="color: rgb(0,0,0)"> i_name </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> ( sex </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> i_sex </span><span style="color: rgb(128,128,128)">or</span><span style="color: rgb(0,0,0)"> i_sex </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> ( age </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> i_age </span><span style="color: rgb(128,128,128)">or</span><span style="color: rgb(0,0,0)"> i_age </span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">);
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> selectTbTest;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> TESTPACKAGE;
</span></div>
试试其它关键字
　增刪改查　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)