创建过程函数和包.docx
《创建过程函数和包.docx》由会员分享,可在线阅读,更多相关《创建过程函数和包.docx(10页珍藏版)》请在冰豆网上搜索。
![创建过程函数和包.docx](https://file1.bdocx.com/fileroot1/2023-1/4/ead76345-bbb9-4027-b526-8c6461f28723/ead76345-bbb9-4027-b526-8c6461f287231.gif)
创建过程函数和包
创建过程、函数和包
目标:
创建和使用子程序
创建和使用程序包
过程:
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