补充-ORACLE_存储过程

Oracle子程序 存储过程和函数 2020 4 6 2 问题 怎样才能简单 高效地在不同应用程序之间共享PL SQL代码 特性 具有唯一的名称 可存储在数据库中 可作为数据字典中的元数据使用 可将参数传递给它 也可返回值 一个或多个 可在顶层执行或使用它的名称在其它程序中调用使用 主要功能 模块化 封装性 代码集中 可重用性 执行性能好 使用PL SQL提供的存储子程序机制 过程 函数和包 2020 4 6 3 示例 create procedure sql use procedure sql 过程和函数可以在各种环境中创建 过程和函数也称为子程序 1 存储子程序与应用子程序的区别 子程序由下列几部分组成 块的名字和类型块头必需的建立局部标识符的说明部分 DECLARE 可选的执行动作的执行部分 EXCUTE 必需的进行处理错误的错误处理部分 EXCEPTION 可选的 2 开发一个存储过程和函数的步骤 用系统编辑器或字处理软件写一个含有CREATEPROCEDURE或CREATEFUNCTION语句的脚本文件 在写CREATEPROCEDURE或CREATEFUNCTION语句时 应该充分考虑运行时出错的处理 即应该考虑例外处理EXCEPTION 在SQL PLUS中运行脚本文件 将过程或函数的源代码编译成编译代码P code 并把两种代码都存储到数据库在中 调试编译错误 在Oracle环境下调用开发的函数或存储过程 在运行期间调试过程 函数的逻辑错误 注意 不论编译是否成功 创建过程 函数命令CREATEPROCEDURE或CREATEFUNCTION都将自动把其源代码存入数据库中 而编译代码只有在编译成功后才能存入数据库中 只有编译代码被存入到数据库的存储过程和函数才能被调用 2 1 创建过程与使用 创建过程语句的语法如下所示 CREATE ORREPLACE PROCEDURE schema procedure name argument IN OUT INOUT datatype argument IN OUT INOUT datatype IS AS descriptionpart说明部分 BEGINSQLSTATEMENT语句序列 EXCEPTION例外处理 END procedureName过程名 创建了过程就可以从PL SQL块中调用它 示例 BEGINshow line 50 END 过程参数说明 ORREPLACE是一个可选的关键字 建议用户使用此关键字 如果过程已经存在 该关键字将重新创建过程 这样就不必删除和重新创建过程 关键字IS和AS均可 它们本身没有区别 IS后面是一个完整的PL SQL块 可以定义局部变量 但不能以DECLARE开始 局部变量在过程内部存放值 形式参数可以有三种模式 IN OUT INOUT 如果没有为形式参数指定模式 那么默认的模式是IN IN OUT INOUT三种模式的描述 举例说明三种模式参数的区别以及在过程体内可以出现的位置 CREATEORREPLACEPROCEDUREmodetest P inparameterinnumber P outparameteroutnumber P inoutparameterinoutnumber ISV localvarriablenumber BeginV localvarriable P inparameter P outparameter 7 V localvarriable P inoutparameter P inoutparameter 7 End 举例 为新雇员存储其所有信息 几种去掉不必要的输入参数的方法 利用数据库序列 SEQUENCE 生成器为主关键字创建序号 例如 雇员编号 部门编号 定单号 从USER函数中获取用户名 例如 销售人员 顾客 审计表使用的用户名 从SYSDATE函数中获取当前日期 例如雇佣日期 定货日期 定货日期 开始日期 在适当时候存储默认值 例如 某定单数量一定是从1开始 在实际中按照一些常识性知识获得变量的值 例如 新雇员若不是做销售工作 则其奖金为NULL 等 举例 没有指定参数的模式 利用过程进行查询处理 获取一个雇员的信息 这些变量起返回作用 注意 改变INOUT参数的值有两种方法 一是应用赋值语句 一是通过SELECT INTO语句 如果INOUT参数的值在过程中没有被更改 则它返回调用环境时值不变 如果参数是字符型 不能指定长度 长度由调用环境决定 对于过程中出现的错误应该如何处理 例外处理EXCEPTION 在SQL PLUS中调用过程 交互显示信息 创建带有游标的过程 1 显式定义游标的格式SQL CREATEORREPLACEPROCEDUREISCURSORISBEGINFORINLOOPENDLOOP END 显式定义游标 SQL CREATEORREPLACEPROCEDUREemp cur proISCURSORemp curISselectphone numberFROMemployees BEGINFORemp recINemp curLOOPdbms output put line emp rec phone number ENDLOOP ENDemp cur pro 隐式定义游标 其中emp rec为循环变量 SQL createorreplaceprocedureimp curproisbeginforemp recin selectphone numberfromemployees loopnull 循环体中可根据实际编写任何执行语句endloop endimp curpro 通过查询数据字典视图USER SOURCE来获得 如 selecttextfromUSER SOURCEwherename FIRE EMP andtype PROCEDURE 查询过程 函数的源代码 过程 函数的编译出错信息可通过USER ERRORS获得 查询过程 函数的编译出错信息 selectline positionpos textfromUSER ERRORSwherename HIRE EMP andtype PROCEDURE ORDERBYline 参数传递 调用含有形式参数的过程 函数时 必须有对应的实际参数 有三种方法实现由实参向形参传递值的过程 在过程的调用中 实参的次序与定义过程时形参的次序 类型 个数保持一一对应 如 SQL EXECUTEhire emp SCOTT ANALYST 7566 3000 指定法即调用过程时 通过 符号给每个形式参数指定值 其次序是任意的 如 SQL EXECUTEhire emp v emp sal 3000 v mgr no 7566 v emp name SCOTT v emp job ANALYST 注意 的左边是形式参数的名字 右边是参数的实际值 位置对应 混合法 调用过程时 首先按次序给出一些参数的实际值 然后用符号 给顶另外一些参数的实际值 SQL EXECUTIVEhire emp SCOTT ANALYST v emp sal 3000 v mgr no 7566 注意 如果形式参数是IN模式的参数 实际参数可以是一个具体的值或一个有值的变量 如果形式参数是OUT模式的参数 实际参数必须是一个变量 当调用过程后 此变量就被赋值了 可以输出此变量的值来测试过程执行的结果 如果形式参数是INOUT模式的参数 则实际参数必须是一个预先已经赋值的变量 执行完过程后 该变量被重新赋值 可以输出此变量的值来测试过程执行结果 过程的调用 过程和函数一旦被存储到数据库中 就可从各种环境中调用 既可通过命令行的方式调用 也可从一个具体应用程序中调用 但不同的调用环境 其调用过程函数的语法有所区别 过程与函数的调用方法的区别 各种环境调用的语法 1 在PL SQL中调用存储过程Declearv empnonumber 7564 Beginfire emp v empno Dbms output put line 已经开除7564员工 END 在PL SQL块中如何调用存储过程和函数 此参数为IN模式 Declarev empnoemp empno type 7564 v empnameemp empname type v salemp sal type v m type Beginquery emp v empno v empname v sal v comm Dbms output put line v ename v sal v comm END 参数v empno为IN模式 其他三个为OUT模式 1 过程的调用调用只有IN模式参数的过程方法 SQL PLUS中用EXECUTE命令调用过程 例如 从SQL PLUS中调用fire emp 过程fire emp只有IN模式的参数 用具体值替代 SQL ACCEPTp empnoPROMPT pleaseentertheemployeenumber 接受员工号 SQL EXECUTEfire emp 引用员工号 在SQL PLUS中调用过程和函数的方法 ACCEPTVariable namePROMPT prompttext SETVERIFYOFFSETECHOOFFACCEPT PROMPT SETVERIFYONSETECHOON 在一个用户中调用另一个用户的过程方法 在过程名之前加其所属的用户名来加以限制SQL EXECUTEscott fire emp 7654 调用异地数据库的过程方法 在过程名后面加链路名加以限制例如 从SQL PLUS调用一个数据库链路被命名为newsoft的异地数据库的过程 SQL EXECUTEscott fire emp newsoft 7654 调用带OUT参数的过程从SQL PLUS中调用过程QUERY EMP 该过程有一个IN参数 3个OUT参数 必须在SQL PLUS中用VARIABLE定义定义3个变量 执行过程时往这3个变量中赋值 可以用下列SQL PLUS的脚本实现 SETserveroutputonACCEPTp emp noPROMPT Pleaseentertheemployeenumber VARIABLEv emp nameVARCHAR2 14 VARIABLEv emp salnumber VARIABLEv emp commNUMBER EXECUTEquery emp 注意 在SQL PLUS中 用VARIABLE定义的变量在引用时 必须前边加 号 用ACCEPT接受的变量在引用时 前面必须加 符号 1 提高数据