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

数据库  /  管理员 发布于 5年前   391

/* *开发子程序 */--子程序目是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用程序中多次调用--PL/SQL有两种类型的子程序：过程和函数--开发过程--建立过程：不带任何参数CREATE OR REPLACE PROCEDURE out_timeISBEGIN DBMS_OUTPUT.put_line(systimestamp);END;begin out_time();end;--在SQL*PLUS中调用过程--使用execute命令调用过程set serveroutput onexec out_time--使用call命令调用过程set serveroutput oncall 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)isbegin     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 numberexec query_employee(7788,:name,:salary)print name salaryselect * 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 numbervar n2 numberexec :n1:=100exec :n2:=30exec 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_userreturn varchar2is    v_user varchar2(100);begin   select username into v_user from user_users;   return v_user;end;--使用变量接收函数返回值var v1 varchar2(100)exec :v1:=get_userprint v1--在SQL语句中直接调用函数SELECT get_user from dual;--使用包DBMS_OUTPUT调用函数set serveroutput onexec dbms_output.put_line('当前数据库用户：'||get_user)--建立函数：带有IN参数--建立用于返回雇员工资的函数为例，说明建立带有输入参数函数的方法CREATE OR REPLACE FUNCTION get_sal(name in varchar2)return numberas  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 NUMBERexec :sal:=get_sal('scott')print salselect * from emp;--建立函数：带有OUT参数--一般情况下，函数只需要返回单个数据，如果希望使用函数同时返回多个数据，例如同时返回雇员名和工资，那么就需要使用输出参数了--下面以建立用于返回雇员所在部门名和岗位的函数为例，说明建立带有OUT参数函数的方法CREATE OR REPLACE FUNCTION get_info(name varchar2,title out varchar2)return varchar2as    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 NUMBERAS    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 NUMBERvar result2 NUMBERexec :result2:=30exec :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_objectswhere object_type in('PROCEDURE','FUNCTION');--列出子程序源代码--数据字典视图USER_SOURCE用于列出子程序的源代码select text from user_source where name='GET_USER';--列出子程序编译错误--第一种方法是使用SHOW ERRORS命令--第二种方法是使用数据字典视图USER_ERRORSCREATE OR REPLACE PROCEDURE raise_salary(no number,increase number) is begin   update emp set sal=sal+increase where empno=noend;--使用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_salaryCREATE 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\utldtreeexec 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;