Oracle 过程、函数、包、重载、自主事务

子程序包的优点有如下：
模块化：通过子程序，可以将程序分解为可管理的，明确的逻辑模块。
可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用。
可维持性：子程序可以简化维护操作，因为如果一个程序受到影响，

          则只需要修改该子程序的定义。
安全性：用户可以设权限使得访问数据的唯一方式就是通过提供的过程和函数。

 

过程
创建过程
执行过程
过程参数模式

 

函数
定义函数的语法的一些限制。
   函数只能带有IN参数，而不能带有IN OUT 或OUT参数
   参数必须只使用数据库类型，不得使用PL/SQLo类型
   函数的返回类型也必须是数据库类型。

创建函数
函数受权
删除函数

从SQL表达式调用函数的限制
从SELECT语句调用的任何函数均不能修改数据库
当远程执行时，函数不得读取或写入程序包中变量的值。
从SELECT，values或SET子句调用的函数可写入变量，其它的则不能写入变量
要执行UPDATE的存储过程，则该函数不能在SQL语句内使用。

 

自主事务处理
自主事务处理结果的变化不依赖于主事务处理的状态或最终配置
自主事务处提交或回退时，不影响主事务处理的结果
自主事务处理一旦提交，该自主事务处理结果的变化对于其它事务处理是可见的
自主事务处理可以启动其他自主事务处理。

 

程序包的优点
模块化
更轻松的应用程序设计
信息隐藏
新增功能
性能更佳

 

程序包中的游标
使用%rowtype属性根据数据库表定义的记录
根据程序员定义的记录类型的记录

 

------------------------------课堂体验---------------------------

select * from emp

--备份
create table Emptest as select * from emp;

select * from Emptest

--pl/sql 块
declare
   --  empName Emptest.Ename%type;
   --  empSal emptest.sal%type;
     type emprowType is record(empName Emptest.Ename%type,empSal emptest.sal%type);
     emprow emprowType;
     cursor empCursor is select ename,sal from Emptest;
begin
     open empCursor ;
     loop
         fetch empCursor into emprow;
         exit when empCursor%notfound;
         dbms_output.put_line(emprow.empname || '的工资为：'|| emprow.empsal);
     end loop;
     close empCursor;
end;

 

/ *******************

  不能重用，不能在外部调用，每次执行它都得重新编译。
  这是最大的缺陷。
  如果要在外部调用，怎么办？首先有一个名字
  解决方案：过程、函数、包、触发器
  这里主要讲过程、函数、包
*********************/

 

-----------------过程---------------
/ ********************************************************
   过程有些资料书上也叫存储过程,将上面的例子用过程实现如下：
   过程着重强调的是业务过程处理
**********************************************************/

create or replace procedure proEmp
as
    type emprowType is record(empName Emptest.Ename%type,empSal emptest.sal%type);
    emprow emprowType;
    cursor empCursor is select ename,sal from Emptest;
begin
    open empCursor ;
    loop
        fetch empCursor into emprow;
        exit when empCursor%notfound;
        dbms_output.put_line(emprow.empname || '的工资为：'|| emprow.empsal);
    end loop;
    close empCursor;  
end;

--先编译再调用执行
begin
    proemp;
end;

/ *********************************************

  注意：在命令窗口，调用如下：
   --exec proemp;--注意与在pl/sql中调用的区别
***********************************************/

 

---输出如下：
/ ******************************
SMITH的工资为：800
ALLEN的工资为：1600
WARD的工资为：1250
JONES的工资为：2975
MARTIN的工资为：1250
BLAKE的工资为：2850
CLARK的工资为：2450
SCOTT的工资为：3000
KING的工资为：5000
TURNER的工资为：1500
ADAMS的工资为：1100
JAMES的工资为：950
FORD的工资为：3000
MILLER的工资为：1300
***************************************/

 

--假设给每个员工的工资加100元

----带参数的过程--------
--参数有：in out (in out)三种情况

create or replace procedure proEmp(thedept number,empSal emptest.sal%type,flag in out

varchar2)
as
    emprow emptest%rowtype;
    cursor empCursor is select * from Emptest
           where deptno=thedept for update;
begin
    open empCursor;
    loop
        fetch empCursor into emprow;
        exit when empCursor%notfound;
        dbms_output.put_line(emprow.ename || '的工资为：'|| emprow.sal);
        update Emptest set sal = (sal + empsal)
               where current of empCursor;
--      dbms_output.put_line(emprow.ename || '加100元后的工资为：'|| emprow.sal);           

 
--为什么上面输出的结果没有变呢？请思考
    end loop;     
    if empCursor%rowcount = 0 then
        flag := '输入的部门号不存在！'; 
    else
        flag := '影响的行数为：' || empcursor%rowcount;
    end if;
    close empCursor;   
end;

select * from emptest

----调用执行
declare
     flag varchar2(50);
     num number;
     deptno number;
begin
     deptno := '&请输入部门号';
     num := '&请输入上调工资的额度';
     proemp(deptno,num,flag);   
     dbms_output.put_line(flag);
end;

 

/ **************************
注意：
  1、in输入参数： 关键字可以略,默认就是in，调用过程时，必须传值
  2、out输出参数：调用时不用传值，但必须有一个变量参数，以接收输出的结果
                  必须在PL/SQL中执行
  3、in out 输入输出参数: 调用时可以传值，也可以不传值。是in和out参数的结合产物。
************************************/

 
-----------------函数---------------                                                        

                   
/ ***********************************
   函数着重强调的是业务数据处理。
   可以有参数列表，只有输入参数。
   必须要有返回值
***********************************/

 

 

--无参函数
create or replace function funAdd return number
as
  numA number;
  numb number;
  res number;
begin
  numA := 10;
  numB := 20;
  res := numa + numb;
  return res;
end;

----调用------------

/  *****************************************
   1、与调用ORACLE系统函数一样,如果没参数就不用加圆括号。
   2、只能出现在表达式中或select 语句中。
*********************************************/

 

SELECT  funADD FROM DUAL;

--有参函数
create or replace function funAddArgs(numA number,numb number) return number
as
  res number;
begin 
  res := numa + numb;
  return res;
end;

-----调用
declare
   numa number := 10;
   numb number := 20;
   res number;
begin
   res := funAddArgs(numA ,numb);
   dbms_output.put_line('计算结果为：' || res); 
end;

/  ************************************************

  过程和函数的比较:

 

过 程	函  数
实现某一业务处理功能	处理某些数据并返回结果
有输入 输出和输入输出 参数	有输入参数  有返回值
可以有一个或多个输出参数	必须返回且返回单个值
在子程序中调用Execute 过程	在子程序中调用并接受返回值  select 函数 from dual

****************************************************/

 

---------包--------

/ ********************************************
  包是由存储在一起的对象组成的PL/SQL结构,有两部分，独立存在：
  包头：包体的声明部分,声明的是全局变量。
  包体：包的代码段，只能在包头完成编译后才能进行编译。
********************************************/

-------包头-------
create or replace package packTest
is
  procedure proEmp;
  function funAddArgs(numA number,numb number) return number;
end packTest ;
 
------包体-----------
create or replace package body packTest
is
  procedure proEmp
  as
      type emprowType is record(empName Emptest.Ename%type,empSal emptest.sal%type);
      emprow emprowType;
      cursor empCursor is select ename,sal from Emptest;
  begin
      open empCursor ;
      loop
          fetch empCursor into emprow;
          exit when empCursor%notfound;
          dbms_output.put_line(emprow.empname || '的工资为：'|| emprow.empsal);
      end loop;
      close empCursor;  
  end proEmp;
 
  function funAddArgs(numA number,numb number) return number
  as
    res number;
  begin 
    res := numa + numb;
    return res;
  end funAddArgs;
end packTest;

/ ****************************************************
  注意：
  1、包头和包体中的过程和函数的说明必须一致，其中包括子程序
  名和其参数名，以及参数的模式。
  2、在包头中也可以定义变量，但定义的变量是全局的，
  过程和函数都可以共享   
******************************************************/

--------调用-----------
begin
      packTEST.proEmp;
end;

----------包中子过程的重载---------------
---包头
create or replace package pack
is
  function funAdd return number;
  function funAdd(numA number,numb number) return number;
end pack;

---包体
create or replace package body pack
is
  function funAdd return number
  as
    numA number;
    numb number;
    res number;
  begin
    numA := 10;
    numB := 20;
    res := numa + numb;
    return res;
  end funAdd;
 
  function funAdd(numA number,numb number) return number
  as
    res number;
  begin 
    res := numa + numb;
    return res;
  end funAdd;
end pack;

-----调用
declare
   numa number := 10;
   numb number := 20;
   res number;
begin
   res := pack.funAdd(numA ,numb);
   dbms_output.put_line('计算结果为：' || res); 
end;

--------自主事务处理---

 

/ **************************************************************

  自主事务处理（pragma autonomous_transaction包括在过程的声明部分）
  不依赖于主事务的 处理状态或最终配置
  自主事务处理提交或回退时，不影响主事务处理的结果
  自主事务处理一旦提交，该自主事务处理结果的变化对于其他事务处理时可见的。
  自主事务处理可以启动其他自主事务处理。
*****************************************************************/

 

create or replace procedure pro
is
pragma autonomous_transaction; ---实现自主事务处理，
begin
     update emp
     set sal=12000 where empno=7369;
     rollback;--只会回滚本过程，不会影响主调过程
end;

create or replace procedure pro1
is
begin
      update emp
      set ename ='aaaaa' where empno=7499;
      pro;
end;

begin
      pro1;
end;

转载于:https://www.cnblogs.com/suimei/p/6924372.html