创建过程函数和包.docx
《创建过程函数和包.docx》由会员分享，可在线阅读，更多相关《创建过程函数和包.docx（10页珍藏版）》请在冰豆网上搜索。
创建过程函数和包
创建过程、函数和包
目标：
创建和使用子程序
创建和使用程序包
过程：
procedure
过程的参数类型有in、out、inout类型的！
！
函数：
function
函数的参数类型只有in类型，不能有out、inout类型的！
！
包：
package
setserveroutputon
Rem===================================================================
Rem1、创建简单的存储过程，如何执行存储过程
Rem===================================================================
CREATEorreplaceproceduremy_procAS（用is也行）
BEGIN
DBMS_OUTPUT.PUT_LINE（'这是一个简单的存储过程的例子！
'）;
endmy_proc;等价于end；
/
--下面是在PL/SQL中执行存储过程
begin
my_proc;
end;
/
--下面是在SQLPLUS中执行存储过程
executemy_proc;等价于execmy_proc;
Rem===================================================================
Rem2、创建带参数的存储过程
Rem===================================================================
createtablet（
n_varnumber
）;
CREATEORREPLACEPROCEDUREinsert_into_t（p_parminnumber）AS
BEGIN
DBMS_OUTPUT.PUT_LINE（'---------start----------------------'）;
insertintotvalues（p_parm）;
endinsert_into_t;
/
--下面是在SQLPLUS中执行存储过程
executeinsert_into_t（p_parm=>100）;
executeinsert_into_t（200）;
Rem===================================================================
Rem3、创建带参数带默认值的存储过程
Rem===================================================================
RemOUT和INOUT参数不能有默认值
CREATEORREPLACEPROCEDURE
default_values（p_parm1varchar2,
p_parm2varchar2default'Lemon',
p_parm3varchar2default'Oracle'）AS
BEGIN
DBMS_OUTPUT.PUT_LINE（p_parm1）;
DBMS_OUTPUT.PUT_LINE（p_parm2）;
DBMS_OUTPUT.PUT_LINE（p_parm3）;
enddefault_values;
/
--下面是在SQLPLUS中执行存储过程,=>表示命名参数，可以采用无顺序传递
execdefault_values（'.net',p_parm3=>'Java'）;
Rem===================================================================
Rem4、创建带OUT参数的存储过程
Rem===================================================================
RemOUT和INOUT参数不能有默认值
CREATEORREPLACEPROCEDURE
emp_lookup（p_empnoinnumber,
o_enameoutemp.ename%type,
o_saloutemp.sal%type）AS
BEGIN
selectename,salintoo_ename,o_salfromempwhereempno=p_empno;
exception
whenNO_DATA_FOUNDthen
o_ename:
='NULL';
o_sal:
=-1;
endemp_lookup;
/
--定义两个变量
variablev_nonumber;//等价于varv_nonumber;
variablenamevarchar2（10）;
variablesalnumber;
--下面是在SQLPLUS中执行存储过程
execemp_lookup（'7160',:
name,:
sal）;
printname;
printsal;
--下面是在SQLPLUS中执行存储过程
execemp_lookup（'7160',:
name,:
sal）;
--下面是PL/SQL程序块中调用
declare
l_enameemp.ename%type;
l_salemp.sal%type;
begin
emp_lookup（7160,l_ename,l_sal）;
dbms_output.put_line（'员工姓名：
'||l_ename）;
dbms_output.put_line（'员工薪水'||l_sal）;
end;
/
Rem===================================================================
Rem5、创建带OUT参数的存储过程
Rem===================================================================
RemOUT和INOUT参数不能有默认值
CREATEORREPLACEPROCEDURE
newemp_lookup（p_empnonumber,
o_enameoutemp.ename%type,
o_saloutemp.sal%type）AS
BEGIN
selectename,salintoo_ename,o_salfromempwhereempno=p_empno;
exception
whenNO_DATA_FOUNDthen
o_ename:
='NULL';
o_sal:
=-1;
endnewemp_lookup;
/
CREATEORREPLACEPROCEDURE
test_proc（para1innumber,
para2outvarchar2,
para3inoutvarchar2）
AS
l_vartext1varchar2（60）;
l_vartext2varchar2（70）;
begin
l_vartext1:
='这是一个关于INOUT参数的例子！
';
l_vartext2:
='这是一个关于OUT参数的例子！
';
IFPARA1=1THEN
PARA2:
=l_vartext2;
DBMS_OUTPUT.PUT_LINE（PARA2）;
ELSE
PARA3:
=l_vartext1;
DBMS_OUTPUT.PUT_LINE（PARA3）;
ENDIF;
END;
declare
p1varchar2（60）;
p2varchar2（70）;
begin
test_proc（2,p1,p2）;
end;
Rem===================================================================
Rem6、创建程序包:
先建包规范，再建包主体。
Rem===================================================================
REM=====================1、创建程序规范===============================
createorreplacepackagedept_emp_pakis
typerec_empisrecord（
deptnoemp.deptno%type,
enameemp.ename%type,
salemp.sal%type）;
cursorcur_newemp（dept_nonumber）
returnrec_emp;
procedurepro_newemp（dept_nonumber）;
enddept_emp_pak;
REM====================2、创建程序主体================================
createorreplacepackagebodydept_emp_pakis
cursorcur_newemp（dept_nonumber）
returnrec_empis
selectdeptno,ename,salfromemp
wheredeptno=dept_no;
procedurepro_newemp（dept_nonumber）is
l_recemprec_emp;--定义一个记录
begin
opencur_newemp（dept_no）;
loop
fetchcur_newempintol_recemp;
exitwhencur_newemp%notfound;
DBMS_OUTPUT.PUT_LINE（'部门编号：
'||l_recemp.deptno
||'员工姓名：
'||l_recemp.ename
||'薪水：
'||l_recemp.sal）;
endloop;
closecur_newemp;
endpro_newemp;
enddept_emp_pak;
如：
SQL>createorreplacepackagepack_emp
2is
3procedurefind_empbyempno（v_empnot_emp.empno%type）;
4endpack_emp;
5/
程序包已创建。
SQL>createorreplacepackagebodypack_emp
2is
3procedurefind_empbyempno（v_empnot_emp.empno%type）
4is
5l_empt_emp%rowtype;
6begin
7select*intol_empfromt_empwhereempno=v_empno;
8dbms_output.put_line（l_emp.ename||''||l_emp.sal）;
9exceptionwhenno_data_foundthen
10dbms_output.put_line（'nodatafound!
!
'）;
11raise;
12endfind_empbyempno;
13endpack_emp;
14/
程序包体已创建。
SQL>execpack_emp.find_empbyempno（7788）;
SCOTT3000
PL/SQL过程已成功完成。
综合案例：
--利用程序包，实现：
根据部门编号，查询所有的员工。
并统计该部门的总人数？
SQL>createorreplacepackagepack_test
2is
3cursorcur_emp（l_deptnot_emp.deptno%type）returnt_emp%rowtype;
4procedurefind_empbydeptno（v_deptnot_emp.deptno%type）;
5procedurestatic_numbydeptno（v_deptnot_emp.deptno%type）;
6functiongetEmpByEmpno（v_empnot_emp.empno%type）returnvarchar2;
7endpack_test;
8/
程序包已创建。
1createorreplacepackagebodypack_test
2is
3cursorcur_emp（l_deptnot_emp.deptno%type）returnt_emp%rowtype
4is
5select*fromt_empwheredeptno=l_deptno;
6procedurefind_empbydeptno（v_deptnot_emp.deptno%type）
7is
8l_empt_emp%rowtype;
9begin
10opencur_emp（v_deptno）;
11loop
12fetchcur_empintol_emp;
13exitwhencur_emp%notfound;
14dbms_output.put_line（l_emp.ename||''||l_emp.sal）;
15endloop;
16closecur_emp;
17endfind_empbydeptno;
18procedurestatic_numbydeptno（v_deptnot_emp.deptno%type）
19is
20l_countnumber（4）:
=0;
21begin
22selectcount（*）intol_countfromt_empwheredeptno=v_deptno;
23dbms_output.put_line（v_deptno||'部门有'||l_count||'名员工'）;
24endstatic_numbydeptno;
25functiongetEmpByEmpno（v_empnot_emp.empno%type）returnvarchar2
26is
27l_msgvarchar2（200）;
28begin
29selectename||'isa'||jobintol_msg
30fromt_emp
31whereempno=v_empno;
32returnl_msg;
33exceptionwhenno_data_foundthen
34dbms_output.put_line（'nodatafound!
!
'）;
35raise;
36endgetEmpByEmpno;
37*endpack_test;
SQL>/
程序包体已创建。
SQL>execpack_test.find_empbydeptno（20）;
SMITH800
JONES2975
SCOTT3000
ADAMS1100
FORD3000
PL/SQL过程已成功完成。
SQL>execpack_test.static_numbydeptno（20）;
20部门有5名员工
PL/SQL过程已成功完成。
SQL>selectpack_test.getEmpByEmpno（7788）fromdual;
PACK_TEST.GETEMPBYEMPNO（7788）
-------------------------------------------------------------------------------
SCOTTisaANALYST
升级会员 