Oracle，day8，开发程序和包

前言：

今天早上参加了中移在线的一个线上评测，对于测试题目的反应和思路感觉都不太清晰，哎，思维灵敏度还需要锻炼啊。

子程序

子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中被多次调用，其实也就是可以保存。

通过将商业逻辑和企业规则集成到PL/SQL子程序中，可以简化客户端应用的开发和维护，提高应用的性能。

PL/SQL有两种类型的子程序：过程和函数。过程就包含了函数。
过程用于执行特定的操作，而函数用于返回特定数据。

还有一种叫程序包。

这三样东西，其实只是有名字的块。。

---

过程

过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。

结构：

Create [or replace] procedure procedure_name
(argument1 [mode1] datetype1,argument2 [mode2] datatype2,…)
is [as]
    声明部分
Begin
    执行部分
Exception
    异常处理部分
End;

解释：
create就是创建，replace就是替换。存在就替换，不存在就创建。procedure意思是过程，括号内为他的参数，argument就是参数名，mode就是模式，分为输入和输出，datatype就是参数数据类型，注意：不能指定其长度。

---

创建无参过程

实例：

删除表中重复数据

首先创建表：

create table tb_test(A varchar2(10),B varchar2(10));

insert into tb_test values('aa','bb');
insert into tb_test values('aa','cc');
insert into tb_test values('bb','cc');
insert into tb_test values('aa','bb');
insert into tb_test values('aa','cc');
insert into tb_test values('bb','cc');

创建无参过程，删除重复数据

create or replace procedure proc_del_dup_rec
is
begin
  delete tb_test a where a.rowid=
  (select max(rowid) from tb_test b where a.a=b.a and a.b=b.b);
end;

这样只是创建了过程，但并没有执行。

TIPS：一般sqlserver等多用as，为了区分oracle多用is。

若是要执行过程，可以有三种执行方法，如下：

1:

call proc_del_dup_rec();

2:
exec proc_del_dup_rec;

3:
begin
    proc_del_dup_rec;
end;

Tips：上述我们犯了一个小错误，在oracle中，用户自定义的过程我们一般起名都以u开头，例如：u_ proc _del _duprec;

---

创建带有IN参数的过程

创建过程时，可以使用输入参数将应用程序的数据传递到过程中。当为过程定义参数时，如果不指定参数模式，则默认为输入参数。另外，可以使用IN关键字显式地定义输入参数。那么很明显……OUT代表了输出参数。

示例：

传入参数员工编号，输出员工的工资

create or replace procedure u_proc_selsal(v_empno in emp.empno%type)
is
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=v_empno;
  dbms_output.put_line('该员工的工资为：'||v_sal);
exception
  when no_data_found then
  dbms_output.put_line('没有查找到该员工');
end;

写程序块存储过程的好处：

• 节省流量
• 已经经过了语法的编译和调试，执行的效率高，速度快
• 能被多次调用

通常来讲，写成过程都是需要多条记录的修改或者是整个表里的数据才写成过程。比如说，张三给李四转账，张三要扣钱，李四要加钱，这两条可以写成一个过程，也就是一个事务，要么都成功，要么都失败。

相比较在sqldeveloper里写过程一类的程序和在sqlplus里写，sqldeveloper比较慢，占内存多，效率低。所以管理员一般喜欢在sqlplus里面做

---

创建带有IN参数和OUT参数的过程

create or replace procedure u_proc_getSalByEmpno
(v_empno in emp.empno%type,v_sal out emp.sal%type)
is
begin
  select sal into v_sal from emp where empno=v_empno;
exception
  when no_data_found then
  dbms_output.put('没有找到该员工信息');
end;

执行方法：

declare
  v_empno emp.empno%type;
  v_sal emp.sal%type;
begin
  v_empno:=&no;
  u_proc_getSalByEmpno(v_empno,v_sal);
  dbms_output.put_line('该员工的工资为：'||v_sal);
end;  

---

使用过程时多参传值

在调用带有参数的子程序时，传递给形式参数（形参）的参数被称为实际参数（实参）。在过程内部，通过形参引用这些实参的值，为形参传递变量和数据可以采用位置传递、名称传递和组合传递3种方法。如果在定义参数时带有默认值，则在调用子程序时可以不为该形参提供数值。

编译一段过程，计算三数相加的和；

create or replace procedure u_sum
(num1 number,num2 number,num3 number)
is
v_sum number(5);
begin
  v_sum:=num1+num2+num3;
  dbms_output.put_line(num1||'+'||num2||'+'||num3||'='||v_sum);
end;

注意：在上面这段代码中，我们没有给num1.2.3这三个参数设定模式，那么他们的默认模式就是in——输入模式。

---

1.按位置传值

按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将开参与实参关联起来进行传递。在这种方法中，形参与实参的名称是相互独立的、没有关系的，次序才重要。如果更新了一个过程的形参的次序，则对应该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序的难度。

call u_sum(1,2,3);

2.按名称传值

按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参进行传递。在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要。它比按位置传递方法在书写上要复杂，如果只更新了一个过程的形参的次序，则不需要对该过程的任何调用进行任何更新。但如果更新了一个过程的形参的名称，则对该过程的所有调用都必须进行相应的更新，会增加维护应用程序的难度。名称传递在调用子程序时指定参数，并使用关联符号“=>”为其提供相应的数值或变量。

u_sum NUM1=>1,NUM3=>2,NUM2=>3);

3.组合传递

根据应用的需要，可以将按位置传递、名称传递两种方式在同一调用中混合使用。但前面的实参必须使用按位置传递方法，而后面其余的实参则可以按名称传递方法。

其实就是。。记得位置的，把他写在那个位置，不记得在那个位置的用名称传。

call u_sum(1,num3=>3,num2=>2);

---

函数

函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，可以基于这些操作创建特定的函数。使用函数不仅可以简化客户端应用程序的开发和维护，还可以提高应用程序的执行性能。

语法：

Create [or replace] function function_name
(argument1 [mode1] datatype1,
Argument2 [mode2] datatype2,
…)
Return datatype
Is|as
    声明部分
Begin
    执行部分
Exception
    异常处理部分
End;

在上述语法中， function name用于指定函数名称， argument2等用于指定函数的参数。当指定参数数据类型时，不能指定其长度。RETURN子句用于指定函数返回值的数据类型，is或as用于开始一个PL/SQL块。当创建函数时，函数头部必须带有RETURN子句，在函数体内至少需要包含一条RETURN语句。另外，当创建函数时，既可以指定输入参数(IN)，又可以指定输出参数(OUT)及输入和输出参数(IN OUT)。

---

创建无参函数

示例：

创建一个用于产生随机数的函数：

create or replace function fun_getRandom
return number
is
v_num number(5);
begin
  v_num:=floor(dbms_random.value(1,10));
  return v_num;
end;

上述代码也只是创建了函数产生1-10之间的随机数，并没有执行，具体执行方法如下：

declare
  num number(5);
begin
  num:=fun_getRandom();
  dbms_output.put_line(num);
end;

另外，函数还可以在sql语句中被调用，如下：

select fun_getRandom from dual; 

函数像过程一样可以有参数，创建函数时也可以指定in、out、in out参数。我们在使用函数的时候传入参数即可。

---

函数与过程的比较

• 都使用In模式的参数传入数据、OUT模式的参数返回数据。
• 输入参数都可以接收默认值，都可以传值。
• 调用时的实参都可以使用位置表示法或名称表示法。
• 都有声明部分、执行部分和异常处理部分。

使用过程和函数的时机通常取决于需要从子程序中返回多少个值以及需要如何使用这些值。一般而言，返回多个值或不返回值时使用过程，只需要返回一个值时使用函数。虽然带OUT模式的参数的函数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格。过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。可以在SQL语句内部调用函数来完成复杂计算问题，因为函数一定会有一个值通过其名称返回给调用环境；但不能调用过程，因为过程的返回值与过程的名称无关。

---

包

包（package）用于组合逻辑相关的PL/SQL类型、PL/SQL项和PL/SQL子程序。

通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏，子程序重载等功能。

包由包规范和包体两部分组成。

当创建包时，首先需要创建包规范，然后再创建包体。

包规范相当于接口，包体相当于接口的实现。

创建包规范

包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数。

在包规范中定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用。

创建包规范时需要注意的是：为了实现信息隐藏，不应该将所有组件放在包规范处定义，而应该只定义公用组件。

实际上，包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数。在包规范中所定义的公用组件不仅可以在包内引用，而且可以由其他的子程序引用。创建包规范时需要注意，为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，应该只定义公用组件。在SQL/Plus中创建包规范时，需要使用CREATE PACKAGE命令来完成。

语法：

create or replace package package_name
is
public type and item declarations
subprogram specifications
end package_name;

上述语法中，package_name用于指定包名，而以IS或AS开始的部分用于定义公用组件。以下通过创建用于计算圆面积的包dbutil_package为例，说明创建包规范的方法。当定义该包规范时，定义常量pi、公用过程print_area以及公用函数getarea。

示例：

create or replace package dbutil_package
is
  pi constant number(10,7):=3.1415926;
  function getarea(radius number) return
  number;
  procedure print_area;
end dbutil_package;

这里只是创建了一个规范，里面的function和procedure还需要我们在创建包体的时候去填充。

---

创建包体

为了实现包规范中所定义的公用过程和函数，必须创建包体。

包体用于实现包规范所定义的过程和函数。

在创建包时，为了实现信息隐藏，应该在包体内定义私有组件。

语法：

create or replace package body dbutil_package
is
  area number(10);
  function getarea(radius number) return number
  is
  begin
    area:=pi*radius*radius;
    return area;
  end;

  procedure print_area is
  begin
    dbms_output.put_line('圆面积为：'||area);
  end;
end dbutil_package;

注意：
在oracle sqldeveloper中运行编译后，会出现错误，导致不能正常运行。是因为其bug，将最后的分号丢掉了，再编辑一下，加上即可。

---

开发包

调用包的组件：

• 对于包的私有组件，只能在包内调用，并且可以直接调用。
• 对于包的公用组件，既可以在包内调用，又可以在其他应中调用。
• 在调用包内其他组件，可直接调用，不需要加包名作为前缀。

调用包的公用变量，过程，函数：

当在其他应用中调用包的公用变量时，必须在公用变量、过程、函数名前添加包名作为前缀。

调用包的语法：

declare
  area number(10,7);
begin
  area:=dbutil_package.getarea(3);
  dbms_output.put_line('由function返回的面积为：'||area);
  dbutil_package.print_area;
end;

返回值为：

匿名块已完成
由function返回的面积为：28
圆面积为：28

---

子程序和包的管理

过程与函数被存储在数据库中，可以随时查看源代码。如果需要，可以在创建过程与函数时随时查看更加详细的编译错误信息，不需要的过程与函数可以随时删除。

1.查看存储的子程序或包

select name from user_source;

返回一个表的数据，里面是各种包名，过程名，函数名等。

2.查看子程序或包的源码

select text from user_source where type='procedure' and name='u_proc_getsalbyempno';

代码是这样的。。但是我特喵的查出来什么都没有，气炸！！

3.删除子程序

drop procedure u_sum;

上面查不到源码，但是能删除，服~