--第五节课内容
--可以用预编译命令给我们的自定义异常绑定编号
--自定义异常的编号范围-20000~-20999
Pragma Exception_Init(异常名，异常编号)
------------------------------------------------------------------------------------------------
Declare
Too_Many_People Exception;
Pragma Exception_Init(Too_Many_People,-20001);
No_Such_Dept Exception;
Pragma Exception_Init(No_Such_Dept,-20002);
Begin
Update Emp Set Sal = Sal + 100 Where Deptno = &p_Deptno;
If Sql%Rowcount > 5 Then
Raise Too_Many_People;
Elsif Sql%Notfound Then
Raise No_Such_Dept;
End If;
--commit;
Dbms_Output.Put_Line('操作成功');
Exception
When Too_Many_People Then
Dbms_Output.Put_Line('部门人数过多');
Dbms_Output.Put_Line(Sqlcode);
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
When No_Such_Dept Then
Dbms_Output.Put_Line('部门编号不合法或部门无员工');
Dbms_Output.Put_Line(Sqlcode);
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End;
/
-------------------------------------------------------------------------------------------
--绑定描述
--将异常编号和异常描述绑定抛出
--异常描述最长可以支持2048个字符
--异常编号不需要绑定名字
raise_application_error(异常编号，异常描述);
-------------------------------------------------------------------------------------------
Begin
Update Emp Set Sal = Sal + 100 Where Deptno = &p_Deptno;
If Sql%Rowcount > 5 Then
Raise_Application_Error(-20001, '部门人数过多'); --绑定描述
Elsif Sql%Notfound Then
Raise_Application_Error(-20002, '部门编号不合法或部门无员工');
End If;
--commit;
Dbms_Output.Put_Line('操作成功');
Exception
When Others Then
Dbms_Output.Put_Line(Sqlcode);
Dbms_Output.Put_Line(Sqlerrm);
End;
/
------------------------------------------------------------------------------------------
第六章 子程序
子程序：有名字的PL/SQL块。可以被当做对象存储在数据库当中，通常可以接收参数，被别人调用执行。
符合PL/SQL块的标准结构。
一、存储过程
通常用来完成某项复杂的操作
1.创建语法
Create [Or Replace] Procedure 存储过程名[(参数列表)]
Is|As
--声明部分，不用写declare关键字
Begin
Exception
End [存储过程名];--建议加上存储过程名，增加程序的可读性
--参数列表
参数名1 [参数模式] 数据类型1,参数名2 [参数模式] 数据类型2,...参数名n [参数模式] 数据类型n
a.参数的数据类型只写类型名，不能加长度；
b.参数模式：in模式，out模式，in out模式。默认为in模式；
2.如何编辑和编译存储过程
a.可以在SQL-window中编辑，全选，按F8键编译
b.可以在command-window中编辑,用/来编译，用show errors命令查看编译错误
c.可以使用向导。
练习：写一个存储过程，输出Hello World!
Create Or Replace Procedure print_procedure
As
Begin
dbms_output.put_line('Hello World');
End print_procedure;
3.如何调用存储过程
a.在另一块中调用 print_procedure;
b.在SQL/PLUS环境中，使用exec环命令调用
Exec 存储过程名字(参数列表)
------------------------------------------------------------------------------------------------
C:\Documents and Settings\ttc>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 2月 28 15:07:31 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set serveroutput on
SQL> exec print_procedure;
Hello World
PL/SQL 过程已成功完成。
-----------------------------------------------------------------------------------------------
练习：写一个存储过程，根据参数传入的员工编号，输出员工的姓名和工资
Create Or Replace Procedure put_ename(p_empno emp.empno%Type)
Is
v_ename emp.ename%Type;
v_sal emp.sal%Type;
Begin
Select ename,sal
Into v_ename,v_sal
From emp
Where empno=p_empno;
--p_empno:7788;错误
dbms_output.put_line(v_ename || ' ' ||v_sal);
Exception
When no_data_found Then
dbms_output.put_line('没有数据');
End put_ename;
------------------------------------------------------
Begin
put_ename(&p_empno);
End;
/
--------------------------------------------------------
4.参数模式
a.In 模式：调用环境向存储过程内部传递参数，in模式的参数在存储过程内部被当做一个常量来使用。
In 模式的参数可以设定默认值。
Create Or Replace Procedure put_ename(p_empno emp.empno%Type Default 7788)
Is
v_ename emp.ename%Type;
v_sal emp.sal%Type;
Begin
Select ename,sal
Into v_ename,v_sal
From emp
Where empno=p_empno;
--p_empno:7788;错误
dbms_output.put_line(v_ename || ' ' ||v_sal);
Exception
When no_data_found Then
dbms_output.put_line('没有数据');
End put_ename;
------------------------------------------------------
--调用
Begin
put_ename();--这是不传参数，会自动查处数据，利用默认值
End;
/
--------------------------------------------------------
b.Out 模式：由存储过程内部向调用环境传值。out模式的参数在存储过程内部当做一个变量来使用
out模式的实参一定是一个变量
--存储过程没有返回值，在存储过程内部，一定不能够出现return语句；
--存储过程可以使用out模式的参数向调用环境传值
--给out模式参数赋予什么值，调用环境就会接收一个什么值
练习：写一个存储过程，根据参数传入的员工编号，返回员工的姓名和工资
Create Or Replace Procedure Get_Ename(p_Empno Emp.Empno%Type, p_Ename Out Emp.Ename%Type, p_Sal Out Emp.Sal%Type) Is
Begin
Select Ename, Sal Into p_Ename, p_Sal From Emp Where Empno = p_Empno;
Exception
When No_Data_Found Then
p_Ename := 'not found';
p_Sal := 0;
End Get_Ename;
-----------------------------------------------------------------
--调用
Declare
v_Ename Emp.Ename%Type; --声明变量接收
v_Sal Emp.Sal%Type;
Begin
Get_Ename(&p_Empno, v_Ename, v_Sal);
Dbms_Output.Put_Line(v_Ename || ' ' || v_Sal);
End;
/
-----------------------------------------------------------------
c.In Out 模式：既能传入又能传出的参数。 in Out 模式参数的实参一定是一个有值的变量。
练习：写一个存储过程，根据员工编号和工资的涨幅，更新emp表，要求返回员工修改后的工资
Create Or Replace Procedure Emp_Procedure(p_Empno Emp.Empno%Type, p_Sal In Out Emp.Sal%Type) Is
Begin
Update Emp Set Sal = Sal + p_Sal Where Empno = p_Empno;
Select Sal Into p_Sal From Emp Where Empno = p_Empno;
Exception
When No_Data_Found Then
Dbms_Output.Put_Line('没有数据');
Rollback;
End Emp_Procedure;
---------------------------------------------------------------
--调用
Declare
v_sal emp.sal%Type;
Begin
v_sal:=200;
Emp_Procedure(&p_empno,v_sal);
dbms_output.put_line(v_sal);
End;
/
---------------------------------------------------------------
5.参数传递方式
a.按照位置传参
Get_Ename(&p_Empno, v_Ename, v_Sal);
b.按照名称传参
Emp_Procedure(p_Sal=>v_Sal,p_Empno=>7788);--形参名字=>实参名字
c.按照混合方式传参
前面使用位置传参，后面用名字传参
Get_Ename(&p_Empno, p_Sal=>v_Sal,p_Ename=>v_Ename);
6.本地子程序(见课件)--本地子程序一定是声明部分的最后一个元素
7.删除存储过程
Drop Procedure 存储过程名;
Drop Procedure print_procedure;
二、函数
函数有返回值，通常用来做某些计算。
函数的内部至少要含有一个return语句。
函数可以作为表达式的一部分调用。
Select Sysdate +7 From dual;--当前日期加七天
1.函数的创建语法
Create [Or Replace] Function 函数名[(参数列表)]
Return 数据类型--定义了函数的返回值类型，函数的声明必须要有return子句
Is|As
--声明部分，不用写declare
Begin
Exception
End [函数名];
--参数列表的定义和存储过程一样
2.如何编辑和编译
--参照存储过程
练习：写一个函数，根据员工的编号，返回年薪（考虑comm）
Create Or Replace Function Get_Sal(p_Empno Emp.Empno%Type) Return Number As
v_Annsal Number;
Begin
Select (Sal + Nvl(Comm, 0)) * 12 Into v_Annsal From Emp Where Empno = p_Empno;
Return v_Annsal;
Exception
When No_Data_Found Then
Return 0;
End Get_Sal;
3.调用环境
a.在另一个块中调用
变量:=函数名(参数列表);
------------------------------------------------------------------------------
Declare
v_sal emp.sal%Type;
Begin
v_sal:=Get_Sal(&p_empno);
dbms_output.put_line(v_sal);
End;
/
--------------------------------------------------------------------------------
b.在SQLplus环境中调用
Exec :变量:=函数名(参数列表);--外部变量
-------------------------------------------------------------------------------
Sql> Set serveroutput On;
SQL> variable g_annsal number;
SQL> exec:g_annsal:=get_sal(1000);
PL/SQL 过程已成功完成。
SQL> print g_annsal;
G_ANNSAL
----------
10560
-------------------------------------------------------------------------------
c.在SQL语句中调用
Select,Where,Order By,Group By
insert语句的values子句中调用
update语句的set子句中调用
Select ename,sal,get_sal(empno)
From emp
Where get_sal>30000
Order By get_sal(empno);
4.函数的使用限制
a.在sql语句中调用的函数，只能含有in 模式的参数，不能含有out或者in Out 模式的函数
b.在sql语句中调用的函数，只能使用Oracle Sql 的标准的数据类型，不能使用PL/SQL特有的数据类型；
(记录、表、布尔)
c.在sql语句中调用的函数，不能包含 insert\Update\Delete 语句(Dml)
d.在update或者delete某个表的操作中，调用的函数内部，不能够查询同一张表
5.删除函数
Drop Function 函数名;
扫一扫
122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
