过程:执行特定操作函数:用于返回特定数据 语法:create [orreplace] procedure procedure_name(argument1 [model]datatype1,argment2 [mode2],...)is [as]pl/sql block;1.建立过程:不带任何参数create or replaceprocecdure out_timeisbegindbms_output.put_line(systimestamp);end;2.调用过程set serveroutput onexec out_timeset serveroutput oncall out_time();3.建立过程:带有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 intoimp (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 add_employee;4.建立过程:带有OUT参数create or replaceprocedure qry_employee(eno number,name outvarchar2,salary out number)isbeginselectename,sal into name,salary from emp where empno=eno;exceptionwhenno_date_found thenraise_application_error(-20000,"该雇员不存在");end;当在应用程序中调用该过程时,必须要定义变量接受输出参数的数据sql>var name varchar2(10)var salary numberexec qry_employee(7788,:name,:salary)print name salary5.建立过程:带有INOUT参数(输入输出参数)create or replaceprocedure compute(num1 in outnumber,num2 in out number)isv1number;v2number;beginv1:num1/num2;v2:mod(num1,num2);num1:=v1;num2:=v2;end;sql>var n1 numbervar n2 numberexec :n1:=100exec :n2:=30exec ecmpute(:n1,:n2)print n1 n26.为参数传递变量和数据位置传递,名称传递,组合传递三种1.位置传递:在调用子程序时按照参数定义的顺序为参数指定相应的变量或数值exec add_dept(40,"sales","new york");exec add_dept(10);2.名称传递:在调用子程序时指定参数名,并使用关联符号=>为其提供相应的数值或变量execadd_dept(dname=>"sales",dno=>50);exec add_dept(dno=>30);3.组合传递:同时使用位置传递和名称传递exec add_dept(50,loc=>"new york");execadd_dept(60,dname=>"sales",loc=>"newyork");7.查看过程原代码oracle会将过程名,源代码以及其执行代码存放到数据字典中.执行时直接按照其执行代码执行可查询数据字典(user_source)select textfrom user_source where name="add_dept";删除过程dropprocedure add_dept; 用于返回特定函数语法:create [orreplace] function function_name(argument1 [mode1] datatype1,argument2 [mode2] datatype2,.....)returndatatype --函数头部必须要带有RETURN子句,至少要包含一条RETURN语句is|as pl/sql block;1.建立函数:不带任何参数create or replacefunction get_userreturn varchar2isv_uservarchar2(100);beginselectusername into v_user from user_users;returnv_user;end;2.使用变量接受函数返回值sql>var v1 varchar2(100)exec :v1:=get_userprint v1在SQL语句中直接调用函数selectget_user from d l;使用DBMS_OUTPUT调用函数setserveroutput onexecdbms_output.put_line("当前数据库用户:"||ger_user)3.建立函数:带有IN参数create orreplace function get_sal(name in varchar2)returnnumberasv_sal emp.sal%type;beginselect sal into v_sal from emp where upper(ename)=upper(name);return v_sal;exceptionwhen no_data_found thenraise_application_error(-20000,"该雇员不存在");end;4.建立函数:带有out参数create or replacefunction get_info(name varchar2,title out varchar2)return varchar2asdeptnamedept.dname%type;beginselecta.job,b.dname into title,deptname from emp a,dept b anda.deptno=b.deptnoandupper(a.ename)=upper(name);returndeptnameexceptionwhenno_data_found thenraise_application_error(-20000,"该雇员不存在");end;sql>var job varchar2(20)var dname varchar2(20)exec :dname:=get_info("scott",:job)print danme job5.建立函数:带有INOUT参数create or replacefunction result(num1 number,num2 in out number)return numberasv_resultnumber(6);v_remaindernumber;beginv_result:=num1/num2;v_remainder:=mod(num1,num2);num2:=v_remainder;returnv_result;exceptionwhenzero_divide thenraise_application_error(-20000,"不能除0");end;sql>var result1 numbervar result2 numberexec :result2:=30exec :result1:=result(100,:result2)print result result26.函数调用限制SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数SQL只能调用带有输入参数,不能带有输出,输入输出函数SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句7.查看函数源代码oracle会将函数名及其源代码信息存放到数据字典中user_sourceset pagesize 40select text fromuser_source where name="result";8.删除函数drop functionresult; 1.列出当前用户的子程序数据字典视图USER_OBJECTS用于显示当前用户所包含的所有对象.(表,视图,索引,过程,函数,包)sql>col object_name format a20select object_name,created,status from user_objects whereobject_type in ("procedure","function")2.列出子程序源代码select text fromuser_source where name="raise_salsry";3.列出子程序编译错误使用SHOWERRORS命令确定错误原因和位置show errorsprocedure raise_salary使用数据字典视图USER_ERRORS确定错误原因和位置col text formata50selectline||"/"||position as line/col,text error from user_errors wherename="raise_salary";4.列出对象依赖关系使用数据字典视图USER_DEPENDENCIES确定直接依赖关系select name,typefrom user_dependencies where referenced_name="emp";使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系先运行SQL脚本UTLDTREE.SQL来建立这两个视图和过程DEPTREE_FILL,然后调用DEPTREE_FILL填充这两个视图sql>@%oracle_home%
dbmsadminutldtreeexec deptree_fill("TABLE","scott","emp")执行后会将直接或间接依赖于SCOTT.EMP表的所有对象填充到视图DEPTREE和IDEPTREE中.select nested_level,name,type from deptree;select * from ideptree5.重新编译子程序当修改了被引用对象的结构时,就会将相关依赖对象转变为无效(INVALID)状态。alter table emp addremark varchar2(10);selectobject_name,object_type from user_objects wherestatus="invalid";为了避免子程序的运行错误,应该重新编译这些存储对象alter procedureadd_employee compile;alter view dept10compile;alter functionget_info compile; 包用于逻辑组合相关的PL/SQL类型,项和子程序,由包规范和包体组成1.建立包规范:包规范是包与应用程序之间的接口,用于定义包的公用组件,包括常量,变量,游标,过程,函数等create [or replace]package package_nameis|asp lic type and item declarationss program specificationsend package_name;create or replacepackage emp_package isg_deptnonumber(3):=30;procedureadd_employee(eno number,name varchar2,salary number,dno numberdefault g_deptno);procedurefire_employee(eno number);functionget_sal(eno number) return number;end emp_package;2.建立包体:用于实现包规范所定义的过程和函数create [or replace]package body package_nameis|asprivate type and item declarationss program bodiesendpackage_name;create or repalce package body emp_package isfunctionvalidate_deptno(v_deptno number)return booleanisv_temp int;beginselect 1 into v_temp from dept where deptno=v_deptno;return tr;exceptionwhen no_date_found thenreturn false;end;procedure add_employee(eno number,name varchar2,salary number,dnonumber default g_deptno)isbeginif validate_deptno(dno) theninsert into emp(empno,ename,sal,deptno)vals(eno,name,salsry,dno);elseraise_application_error(-20010,"不存在该部门");end if;exceptionwhen dup_val_on_index thenraise_application_error(-20012,"该雇员已存在");end;procedure fire_employee(eno number) isbegindelete from emp where empno=eno;if sql%notfound thenraise_application_error(-20012,"该雇员不存在");end if;end;functionget_sal(eno number) return numberisv_sal emp.sal%type;beginselect sal into v_sal from emp where empno=eno;return v_sal;exceptionwhenno_data_found thenraise_application_error(-20012,"该雇员不存在");end;end emp_package;3.调用包组件3.1在同一个包内调用包组件create or replacepackage body emp_package isprocedure add_employee(eno number,name va har2,salary number,dnonumber default g_deptno)isbeginifvalidate_deptno(dno) theninsert into emp(empno,ename,sal,deptno)vals(eno,name,salary,dno);elseraise_application_error(-20010,"该部门不存在")end if;exceptionwhen dup_val_on_index thenraise_application_error(-20011,"该雇员已存在")end;.........3.2调用包公用变量execemp_package.g_deptno:=203.3调用包公用过程execemp_package.add_employee(1111,"mary",2000)3.4调用包公用函数var salarynumberexec:salary:=emp_package.get_sal(7788)print salary3.5以其他用户身份调用包公用组件connsystem/managerexecscott.emp_package.add_employee(1115,"scott",1200)execscott.emp_package.fire_employee(1115)3.6调用远程数据库包的公用组件execemp_package.add_employee@orasrv(1116,"scott",1200)4.查看源代码:存放在数据字典USER_SCOURCE中select text fromuser_source where name="emp-package" and type="package";5.删除包drop packageemp_package;6.使用包重载重载(overload)是指多个具有相同名称的子程序1.建立包规范同名的过程和函数必须具有不同的输入参数,同名函数返回值的数据类型必须完全相同create or replacepackage overload isfunctionget_sal(eno number) return number;functionget_sal(name varchar2) return number;procedurefile_employee(eno number);procedurefile_employee(name varchar2);end;2.建立包体必须要给不同的重载过程和重载函数提供不同的实现代码create or replacepackage body overload isfunction get_sal(eno number) return numberisv_sal emp.sal%type;beginselect sal into v_sal from emp where empno=eno;return v_sal;exceptionwhen no_data_found thenraise_application_error(-20020,"该雇员不存在");end;function get_sal(name varchar2) return numberisv_sal emp.sal%type;beginselect sal into v_sal from emp where upper(ename)=upper(name);return v_sal;exceptionwhen no_data_found thenraise_application_error(-20020,"该雇员不存在");end;procedure fire_employee(eno number) isbegindelete from emp where empno=no;if sql%notfound thenraise_application_error(-20020,"该雇员不存在");end if;end;procedurefire_employee(name varchar2) isbegindelete from emp where upper(ename)=upper(name);if sql%notfound thenraise_application_error(-20020,"该雇员不存在");end if;end;end;3.调用重载过程和重载函数var sal1 numbervar sal2 numberexec:sal1:=overload.get_sal("scott")exec:sal2:=overload.get_sal(7685)execoverload.fire_employee(7369)execoverload.fire_employee("scott")7.使用包构造过程类似于高级语言中的构造函数和构造方法1.建立包规范包的构造过程用于初始化包的全局变量.create or replacepackage emp_package isminsalnumber(6,2);maxsalnumber(6,2);procedureadd_employee(eno number,name varchar2,salary number,dnonumber);procedureupd_sal(eno number,salary number);procedureupd_sal(name varchar2,salary number);end;2.建立包体包的构造过程没有任何名称,它是实现了包的其他过程后,以BEGIN开始,END结束的部分create or replacepackage body emp_package isprocedureadd_employee(eno number,name varchar2,salary number,dno number)isbeginif salarybetween minsal and maxsal theninsert into emp (empno,ename,sal,deptno)vals(eno,name,salary,dno);elseraise_application_error(-20001,"工资不在范围内");end if;exceptionwhen dup_val_on_index thenraise_application_error(-20002,"该雇员已经存在");end;procedureupd_sal(eno number,salary number) isbeginif salary between minsal and maxsal thenupdate emp set sal=salary where empno =eno;if sql%notfound thenraise_application_error(-20003,"不存在雇员号");end if;elseraise_application_errpr(-20001,"工资不在范围内");end if;end;procedure upd_sal(name varchar2,salary number) isbeginif salary between minsal and maxsal thenupdate emp set sal=salary where upper(ename)=upper(name);if sql%notfound thenraise_application_error(-20004,"不存在该雇员名");end if;elseraise_application_error(-20001,"工资不在范围内");end if;end;beginselectmi(sal),max(sal) into minsal,maxsal from emp ;end;调用包公用组件:构造过程只调用一次execemp_package.add_employee(1111,"mary",3000,20)execemp_package.upd_sal("mary",2000)8.使用纯度级别在SQL中引用包的公用函数，该公用函数不能包含DML语句(insert,update,delete),也不能读写远程包的变量为了对包的公用函数加以限制,在定义包规范时,可以使用纯度级别(purity level)限制公用函数语法:pragmarestrict_references (function_name,wnds[,wnps][,rnds][,rnps]);wnds:用于限制函数不能修改数据库数据(禁止DML)wnps:用于限制函数不能修改包变量(不能给包变量赋值)rnds:用于限制函数不能读取数据库数据(禁止SELECT操作)rnps:用于限制函数不能读取包变量(不能将包变量赋值给其他变量)1.建立包规范create or replacepackage purity isminsalnumber(6,2);maxsalnumber(6,2);functionmax_sal return number;functionmin_sal return number;pragmarestrict_references(max_sal,wnps);--不能修改pragmarestrict_references(min_sal,wnps);end;2.建立包体create or replacepackage body purity isfunction max_sal return numberisbeginreturn maxsal;end;functionmin_sal return numberisbeginreturn minsal;end;beginselect min(sal),max(sal) into minsal,maxsal from emp;end;3.调用包的公用函数var minsal numbervar maxsal numberexec :minsal:=purity.minsal()exec :maxsal:=purity.maxsal()print minsal maxsal PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。当发生错误时，程序无条件转到异常处理部分，这就要求代码要非常干净并把错误处理部分和程序的其它部分分开。oracle允许声明其他异常条件类型以扩展错误/异常处理。这种扩展使PL/SQL的异常处理非常灵活。当一个运行时错误发生时，称为一个异常被抛出。PL/SQL程序编译时的错误不是能被处理得异常，只有在运行时的异常能被处理。在PL/SQL程序设计中异常的抛出和处理是非常重要的内容。
2023-07-11 10:06:221
南yi
西柚不是西游