/*
*开发子程序
*/
--子程序目是指被命名的PL/SQL块,这种块可以带有参数,可以在不同应用程序中多次调用
--PL/SQL有两种类型的子程序:过程和函数
--开发过程
--建立过程:不带任何参数
CREATE OR REPLACE PROCEDURE out_time
IS
BEGIN
DBMS_OUTPUT.put_line(systimestamp);
END;
begin
out_time();
end;
--在SQL*PLUS中调用过程
--使用execute命令调用过程
set serveroutput on
exec out_time
--使用call命令调用过程
set serveroutput on
call out_time();
--给表emp加主键
alter table emp add primary key(empno);
--建立过程:带有IN参数
CREATE OR REPLACE PROCEDURE add_employee
(eno NUMBER,name varchar2,sal number,job varchar2 default 'CLERK',dno NUMBER)
IS
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
insert into emp(empno,ename,sal,job,deptno) values(eno,name,sal,job,dno);
EXCEPTION
when DUP_VAL_ON_INDEX then
raise_application_error(-20000,'雇员号不能重复');
when e_integrity then
raise_application_error(-20001,'部门号不存在');
END;
begin
add_employee(1111,'MARY',2000,'MANAGER',10);
end;
select * from emp;
--建立过程:带有OUT参数
CREATE OR REPLACE PROCEDURE query_employee
(eno NUMBER,name out VARCHAR2,salary out number)
is
begin
select ename,sal into name,salary from emp where empno=eno;
exception
when NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
end;
--在SQL*Plus中调用该过程的示例
var name varchar2(10)
var salary number
exec query_employee(7788,:name,:salary)
print name salary
select * from emp;
--建立过程:带有IN OUT参数
CREATE OR REPLACE PROCEDURE compute
(num1 in out number,num2 in out number)
is
v1 number;
v2 number;
begin
v1:=num1/num2;
v2:=mod(num1,num2);
num1:=v1;
num2:=v2;
end;
--在SQL*Plus中调用该过程的示例
var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec compute(:n1,:n2)
print n1 n2
--为参数传递变量和数据
--给表dept加主键
alter table dept add primary key(deptno);
--为参数传递变量和数据可以采用位置传递国、名称传递和组合传递等三种方法
CREATE OR REPLACE PROCEDURE add_dept
(dno number,dname varchar2 default null,loc varchar2 default null)
is
begin
insert into dept values(dno,dname,loc);
exception
when DUP_VAL_ON_INDEX then
raise_application_error(-20000,'部门号不能重复');
end;
--位置传递
exec add_dept(50,'SALES','NEW YORK');
exec add_dept(60);
exec add_dept(70,'ADMIN');
--名称传递
exec add_dept(dname=>'SALES',dno=>51);
exec add_dept(dno=>52);
--组合传递:在调用子程序时同时使用位置传递和名称传递
exec add_dept(53,loc=>'NEW YORK');
select * from dept;
--查看过程源代码
--当建立了过程之后,ORACLE会将过程名、源代码及其执行代码存放到数据字典中。
--当调用过程时,应用程序会按照其执行代码直接执行,而不需要重新解析过程代码,所以使用子程序的性能要优于直接执行SQL语句
--通过查询数据字典USER_SOURCE,可以显示当前用户的所有子程序及其源代码
select text from user_source where name='ADD_DEPT';
--删除过程
drop procedure add_dept;
--开发函数
--函数用于返回特定数据。
--建立函数:不带任何参数
--建立用于显示当前数据库用户名的函数
CREATE OR REPLACE FUNCTION get_user
return varchar2
is
v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
--使用变量接收函数返回值
var v1 varchar2(100)
exec :v1:=get_user
print v1
--在SQL语句中直接调用函数
SELECT get_user from dual;
--使用包DBMS_OUTPUT调用函数
set serveroutput on
exec dbms_output.put_line('当前数据库用户:'||get_user)
--建立函数:带有IN参数
--建立用于返回雇员工资的函数为例,说明建立带有输入参数函数的方法
CREATE OR REPLACE FUNCTION get_sal(name in varchar2)
return number
as
v_sal emp.sal%TYPE;
BEGIN
SELECT sal into v_sal from emp
where upper(ename)=upper(name);
return v_sal;
EXCEPTION
when NO_DATA_FOUND THEN
raise_application_error(-20000,'该雇员不存在');
END;
--在SQL*Plus中调用该函数的示例
var sal NUMBER
exec :sal:=get_sal('scott')
print sal
select * from emp;
--建立函数:带有OUT参数
--一般情况下,函数只需要返回单个数据,如果希望使用函数同时返回多个数据,例如同时返回雇员名和工资,那么就需要使用输出参数了
--下面以建立用于返回雇员所在部门名和岗位的函数为例,说明建立带有OUT参数函数的方法
CREATE OR REPLACE FUNCTION get_info
(name varchar2,title out varchar2)
return varchar2
as
deptname dept.dname%TYPE;
BEGIN
select a.job,b.dname into title,deptname
from emp a,dept b
where a.deptno = b.deptno
and upper(a.ename)=upper(name);
return deptname;
EXCEPTION
when NO_DATA_FOUND THEN
raise_application_error(-20000,'该雇员不存在');
END;
--在建立了函数get_info之后,就可以在应用程序中调用该函数了。
--注意,因为该函数带有OUT参数,所以不能在SQL语句中调用该函数,而必须要定义变量接收OUT参数和函数的返回值
--在SQL*Plus中调用函数get_info的示例如下:
var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)
print dname job
--建立函数:带有IN OUT参数
--使用这种参数时,在调用函数之前需要通过变量给该种参数传递数据,在调用结束之后ORACLE会将函数的部分结果通过该变量传递给应用程序
--下面以计算两个数值相除的结果的函数result为例,说明在函数中使用IN OUT参数的方法
CREATE OR REPLACE FUNCTION result
(num1 NUMBER,num2 IN OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER(6);
v_remainder NUMBER;
BEGIN
v_result:=num1/num2;
v_remainder:=MOD(num1,num2);
num2:=v_remainder;
return v_result;
EXCEPTION
when ZERO_DIVIDE THEN
raise_application_error(-20000,'不能除0');
END;
--因为该函数带有IN OUT参数,所以不能在SQL语句中调用该函数,而必须使用变量为IN OUT参数传递数值并接收数据,另外还需要定义变量接收函数返回值。
--在SQL*Plus中调用该函数的示例
var result1 NUMBER
var result2 NUMBER
exec :result2:=30
exec :result1:=result(100,:result2)
print result1 result2
--查看函数源代码
--当建立了函数之后,ORACLE会将函数名及其源代码信息存放到数据字典中。
--通过查询数据字典USER_SOURCE,可以显示当前用户的所有子程序及其源代码
select text from user_source where name='RESULT';
--删除函数
DROP FUNCTION result;
--管理子程序
--列出当前用户的子程序
--object_name用于标识对象名称,object_type用于标识对象类型,created用于标识对象建立时间
--status用于标识对象当前状态,VALID表示对象有效,而INVALID则表示对象无效(不能引用)
select object_name,created,status from user_objects
where object_type in('PROCEDURE','FUNCTION');
--列出子程序源代码
--数据字典视图USER_SOURCE用于列出子程序的源代码
select text from user_source where name='GET_USER';
--列出子程序编译错误
--第一种方法是使用SHOW ERRORS命令
--第二种方法是使用数据字典视图USER_ERRORS
CREATE OR REPLACE PROCEDURE raise_salary
(no number,increase number) is
begin
update emp set sal=sal+increase where empno=no
end;
--使用SHOW ERRORS命令确定错误原因和位置
--在SQL*Plus中调用
show errors procedure raise_salary
--使用数据字典视图USER_ERRORS确定错误原因和位置
select line||'/'||position as "LINE/COL",text error from user_errors where name='RAISE_SALARY';
--重新建立对象raise_salary
CREATE OR REPLACE PROCEDURE raise_salary
(no number,increase number) is
begin
update emp set sal=sal+increase where empno=no;
end;
--列出对象依赖关系
--无论是直接依赖,还是间接依赖,当修改了被引用对象的结构时,都会使得相关依赖对象转变为INVALID状态。
--确认对象依赖关系有两种方法,第一种方法是使用数据字典视图USER_DEPENDENCIES确定直接依赖关系,另一种方法是使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系
--使用USER_DEPENDENCIES确定直接依赖关系
--下面显示直接依赖于表EMP的所有对象以及对象类型为例,说明使用数据字典视图USER_DEPENDENCIES的方法
select name,type from user_dependencies where referenced_name='EMP';
--使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系
--当使用工具视图DEPTREE和IDEPTREE确定依赖关系时,必须首先运行SQL脚本utldtree.sql来建立这两个视图和过程DEPTREE_FILL,然后调用过程DEPTREE_FILL填充这两个视图
@%oracle_home%\rdbms\admin\utldtree
exec deptree_fill('TABLE','HAIYA1','EMP');
--在执行了过程deptree_fill之后,会将直接或间接依赖于SCOTT.EMP表的所有对象填充到视图DEPTREE和IDEPTREE中
--通过查询它们可以非常直观地显示直接或间接依赖于HAIYA1.EMP表的对象
select nested_level,name,type from deptree;
select * from ideptree;
--重新编译子程序
--当修改了被引用对象的结构时,就会将相关依赖对象转变为无效(INVALID)状态。
--例如,当为EMP表增加了列之后,会使所有依赖对象转变为INVALID状态
alter table emp add remark varchar2(100);
select object_name,object_type from user_objects where status='INVALID';
select * from emp;
--当对象状态为INVALID时,为了避免子程序的运行错误,应该重新编译这些存储对象,示例如下:
alter procedure add_employee compile;
alter function get_info compile;