10开发子程序:过程和函数

  过程:执行特定操作

  函数:用于返回特定数值

  10.1过程

  语法:

  1. create [or replace] procedure procedure_name(argument1 [model] datatype1,arguement2 [mode2],...) 
  2.  
  3.        is [as] 
  4.  
  5.        pl/sql block; 

  1.建立过程:不带任何参数

  1. create or replace procecdure out_time 
  2.  
  3.  is 
  4.  
  5.  begin 
  6.  
  7.    dbms_output.put_line(systimestemp); 
  8.  
  9.  end; 

  2.调用过程

  1. set serveroutput on 
  2.  
  3.  exec out_time  

 

 

  1. set serveroutput on 
  2.  
  3. call out_time();  

 

  3.建立过程:带有IN参数

  1. create or replace procedure add_employee 
  2.  
  3.  (eno number,name varchar2,sal number,job varchar2 default 'clerk',dno number) 
  4.  
  5.  is 
  6.  
  7.  e_integrity exception; 
  8.  
  9.  pragma exception_init(e_integrity,-2291); 
  10.  
  11.  begin 
  12.  
  13.    insert into imp(empno,ename,sal,job,deptno) values(eno,name,sal,job,dno); 
  14.  
  15.  exception 
  16.  
  17.    when dup_val_on_index then 
  18.  
  19.      raise_application_error(-20000,'雇员号不能重复'); 
  20.  
  21.    when e_integrity then 
  22.  
  23.      raise_application_error(-20001,'部门不存在'); 
  24.  
  25.  end; 
  1. exec add_employee(1111,'clark',2000,'manager',10) ;

  4.建立过程:带有OUT参数

  1. create or replace procedure query_employee 
  2.  
  3.  (eno number,name out varchar2,salary out number) 
  4.  
  5.  is 
  6.  
  7.  begin 
  8.  
  9.    select ename,sal into name,salary from emp where empno=eno; 
  10.  
  11.  exception 
  12.  
  13.    when no_date_found then  ---no_date_found表示如何select into没有数据就放回true 
  14.  
  15.      raise_application_error(-20000,'该雇员不存在'); 
  16.  
  17.  end; 

  当在应用程序中调用该过程时,必须要定义变量接受输出参数的数据

  1. sql>var name varchar2(10) 
  2.  
  3.      var salary number 
  4.  
  5.      exec query_employee(7788,:name,:salary) 
  6.  
  7.      print name salary 

  5.建立过程:带有IN OUT参数(输入输出参数)

 

  1. create or replace procedure compute 
  2.  
  3. (num1 in out number,num2 in out number) 
  4.  
  5. is 
  6.  
  7.   v1 number; 
  8.  
  9.   v2 number; 
  10.  
  11. begin 
  12.  
  13.   v1:num1/num2; 
  14.  
  15.   v2:mod(num1,num2); 
  16.  
  17.   num1:=v1; 
  18.  
  19.   num2:=v2; 
  20.  
  21. end;  
  1. sql>var n1 number 
  2.  
  3.      var n2 number 
  4.  
  5.      exec :n1:=100 
  6.  
  7.      exec :n2:=30 
  8.  
  9.      exec ecmpute(:n1,:n2) 
  10.  
  11.      print n1 n2 

  6.为参数传递变量和数据

    位置传递,名称传递,组合传递三种

    1.位置传递:在调用子程序时按照参数定义的顺序为参数指定相应的变量或数值  

  1. exec add_dept(40,'sales','new york'); 
  2.  
  3.       exec add_dept(10); 

    2.名称传递:在调用子程序时指定参数名,并使用关联符号=>为其提供相应的数值或变量

  1. exec add_dept(dname=>'sales',dno=>50); 
  2.  
  3. exec add_dept(dno=>30); 

    3.组合传递:同时使用位置传递和名称传递

  1. exec add_dept(50,loc=>'new york'); 
  2.  
  3. exec add_dept(60,dname=>'sales',loc=>'new york'); 

  7.查看过程原代码

    oracle会将过程名,源代码以及其执行代码存放到数据字典中.执行时直接按照其执行代码执行

    可查询数据字典(user_source)

  1. select text from user_source where name='add_dept'; 

    删除过程

  1. drop procedure add_dept; 

  10.2函数

  用于返回特定函数

  语法:

  1. create [or replace] function function_name 
  2.  
  3.               (argument1 [mode1] datatype1, 
  4.  
  5.                argument2 [mode2] datatype2, 
  6.  
  7.                .....) 
  8.  
  9.        return datatype       --函数头部必须要带有RETURN子句,至少要包含一条RETURN语句 
  10.  
  11.        is|as  pl/sql block;   

  1.建立函数:比带任何参数

  1. create or replace function get_user 
  2.  
  3.  return varchar2 
  4.  
  5.  is 
  6.  
  7.    v_user varchar2(100); 
  8.  
  9.  begin 
  10.  
  11.    select username into v_user from user_users; 
  12.  
  13.    return v_user; 
  14.  
  15.  end; 

  2.使用变量接受函数返回值

  1. sql>var v1 varchar2(100) 
  2.  
  3.         exec :v1:=get_user 
  4.  
  5.         print v1 
  6.  
  7.     在SQL语句中直接调用函数 
  8.  
  9.     select get_user from dual; 
  10.  
  11.     使用DBMS_OUTPUT调用函数 
  12.  
  13.     set serveroutput on 
  14.  
  15.     exec dbms_output.put_line('当前数据库用户:'||ger_user) 

  3.建立函数:带有IN参数

  1. create or replace function get_sal(name in varchar2) 
  2.  
  3.    return number 
  4.  
  5.    as 
  6.  
  7.      v_sal emp.sal%type; 
  8.  
  9.    begin 
  10.  
  11.      select sal into v_sal from emp where upper(ename)=upper(name); 
  12.  
  13.      return v_sal; 
  14.  
  15.    exception 
  16.  
  17.      when no_data_found then 
  18.  
  19.        raise_application_error(-20000,'该雇员不存在'); 
  20.  
  21.    end; 

  4.建立函数:带有out参数

  1. create or replace function get_info(name varchar2,title out varchar2) 
  2.  
  3.   return varchar2 
  4.  
  5.   as 
  6.  
  7.     deptname dept.dname%type; 
  8.  
  9.   begin 
  10.  
  11.     select a.job,b.dname into title,deptname from emp a,dept b and a.deptno=b.deptno 
  12.  
  13.     and upper(a.ename)=upper(name); 
  14.  
  15.     return deptname 
  16.  
  17.   exception 
  18.  
  19.     when no_data_found then 
  20.  
  21.       raise_application_error(-20000,'该雇员不存在'); 
  22.  
  23.   end; 
  1. sql>var job varchar2(20) 
  2.  
  3.      var dname varchar2(20) 
  4.  
  5.      exec :dname:=get_info('scott',:job) 
  6.  
  7.      print danme job 

  5.建立函数:带有IN OUT参数

  1. create or replace function result(num1 number,num2 in out number) 
  2.  
  3.  return number 
  4.  
  5.  as 
  6.  
  7.    v_result number(6); 
  8.  
  9.    v_remainder number; 
  10.  
  11.  begin 
  12.  
  13.    v_result:=num1/num2; 
  14.  
  15.    v_remainder:=mod(num1,num2); 
  16.  
  17.    num2:=v_remainder; 
  18.  
  19.    return v_result; 
  20.  
  21.  exception 
  22.  
  23.    when zero_divide then 
  24.  
  25.      raise_application_error(-20000,'不能除0'); 
  26.  
  27.  end; 
  1. sql>var result1 number 
  2.  
  3.      var result2 number 
  4.  
  5.      exec :result2:=30 
  6.  
  7.      exec :result1:=result(100,:result2) 
  8.  
  9.      print result result2 

  6.函数调用限制

  SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数

  SQL只能调用带有输入参数,不能带有输出,输入输出函数

  SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)

  SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

  7.查看函数院源代码

  oracle会将函数名及其源代码信息存放到数据字典中user_source

  set pagesize 40

  select text from user_source where name='result';

  8.删除函数

  drop function result; 

 

  10.3管理子程序

  1.列出当前用户的子程序

  数据字典视图USER_OBJECTS用于显示当前用户所包含的所有对象.(表,视图,索引,过程,函数,包

  1. sql>col object_name format a20 
  2.  
  3.      select object_name,created,status from user_objects where object_type in ('procedure','function')   

  2.列出子程序源代码

  1. select text from user_source where name='raise_salsry';  

 

  3.列出子程序编译错误

  使用SHOW ERRORS命令确定错误原因和位置

  1. show errors procedure raise_salary

  使用数据字典视图USER_ERRORS确定错误原因和位置

 

  1. col text format a50 
  2.  
  3. select line||'/'||position as "line/col",text error from user_errors where name='raise_salary';  

 

  4.列出对象依赖关系

  使用数据字典视图USER_DEPENDENCIES确定直接依赖关系

  1. select name,type from user_dependencies where referenced_name='emp'; 

  使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系

  先运行SQL脚本UTLDTREE.SQL来建立这两个视图和过程DEPTREE_FILL,然后调用DEPTREE_FILL填充这两个视图

 

  1. sql>@%oracle_home%\rdbms\admin\utldtree 
  2.  
  3.     exec deptree_fill('TABLE','scott','emp') 

  执行后会将直接或间接依赖于SCOTT.EMP表的所有对象填充到视图DEPTREE和IDEPTREE中. 

  1. select nested_level,name,type from deptree; 
  2.  
  3. select * from ideptree  

 

  5.重新编译子程序

  当修改了被引用对象的结构时,就会将相关依赖对象转变为无效(INVALID)状态。

 

  1. alter table emp add remark varchar2(10); 
  2.  
  3. select object_name,object_type from user_objects where status='invalid'; 

  为了避免子程序的运行错误,应该重新编译这些存储对象

  1. alter procedure add_employee compile; 
  2.  
  3.   alter view dept10 compile; 
  4.  
  5.   alter function get_info compile; 
  6.  
  7.   

 

 

 

感谢April-Myhou！