当前位置:首页 > 开发 > 数据库 > 正文

精通Oracle10编程SQL(11)开发子程序

发表于: 2015-06-29   作者:bijian1013   来源:转载   浏览:
摘要: /* *开发子程序 */ --子程序目是指被命名的PL/SQL块,这种块可以带有参数,可以在不同应用程序中多次调用 --PL/SQL有两种类型的子程序:过程和函数 --开发过程 --建立过程:不带任何参数 CREATE OR REPLACE PROCEDURE out_time IS BEGIN DBMS_OUTPUT.put_line(systimestamp); E
/*
 *开发子程序
 */
--子程序目是指被命名的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;

 

精通Oracle10编程SQL(11)开发子程序

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
<style type="text/css"> /* banner */ #banner { position:static; top: 0px; left: 0px; wid
<style type="text/css"> /* banner */ #banner { position:static; top: 0px; left: 0px; wid
第八章 PL/SQL子程序 一、什么是子程序 子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。
精通Oracle 10g Pl/SQL编程 PL/SQL是Oracle特有的编程语言,它不仅具有过程编程语言的基本特征(循
JavaScript 和交互性 在Visual Studio 11中,JavaScript成为构造Windows和Web应用程序的第一类语言
Visual Studio 2010 可以采取的第一个操作是打开分析菜单,启动性能向导,然后选择"形象的多线程应
JDBC, 全称为Java DataBase Connectivity standard, 它是一个面向对象的应用程序接口(API), 通过
我们编写的Servlet一般继承HttpServlet 要查看HttpServlet的源码需到tomcat网站上下载 http://tomca
我用实例项目写了个子父页面传值,算是比较灵活的写法,可以把js提取出来写成包,然后调用,我先一
《精通Qt4编程》第2版,china-pub计算机新书推荐 推荐语: 畅销书升级版 诸多大型项目工程开发经验
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号