PL/SQL 笔记

PL/SQL是一种编程语言，称为过程化SQL语言（Proceduaral Language/SQL）,PL/SQL 是Oracle数据库对SQL语言的扩展。MySql目前不支持PL/SQL。

程序结构：

PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分为三部分：声明部分（用declare开头）、执行部分（以begin开头）和异常处理部分（以Exception开头）。其中执行部分是必须的，其他两个部分可选。

DECLARE    --标记声明部分 
……          --此处用来定义常量、变量、类型和游标等
BEGIN      --标记程序体部分开始 
……           --此处用来编写各种PL/SQL语句、函数和存储过程
EXCEPTION --标记异常处理部分开始
……           --此处用来编写异常处理代码
END;        --标记程序体部分结束

示例：编写计算圆面积的PL/SQL块。

SET SERVEROUTPUT ON
DECLARE
    PI CONSTANT NUMBER(6,5) := 3.14159;
--声明两个变量并赋初值
    r  FLOAT := 2;
   area FLOAT;
BEGIN
    area := PI * r * r;
    DBMS_OUTPUT.PUT_LINE('Area =' || area);
END;

（一）存储过程

在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（ProceDure）。使用存储过程具有如下优点：

（1）存储过程在服务器运行，执行速度快。

（2）存储过程执行一次后驻留在oracle数据库服务器的高速Cache中，以后再次执行存储过程时，只需要从高速Cache中调用已经编译好的代码即可，从而提高了系统的性能。

（3）存储过程确保了数据库的安全。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。

（4）自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行一些手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。

创建存储过程：

CREATE [ OR REPLACE ] PROCEDURE 过程名 
( 参数名  in | out | in out  数据类型 [:=初始值],
  参数名  in | out | in out  数据类型 [:=初始值],…
  参数名  in | out | in out  数据类型 [:=初始值]
)  
IS | AS
[ <局部变量声明> ]
BEGIN
  <过程体>
END [ <过程名> ];

存储过程的调用：

在sqlplus中调用：
    execute 过程名(参数1,参数2,…)

在程序中调用：
     过程名(参数1,参数2,…);

带输入参数的存储过程：

Create or replace procedure p_insert
( v_xh in xs_kc.xh%type,
  v_kch in xs_kc.kch%type,
  v_cj  in xs_kc.cj%type
)
As
begin
  insert into xs_kc values(v_xh,v_kch,v_cj);
  exception
    when dup_val_on_index then
       dbms_output.put_line(‘该学生这门课程成绩已存在！’);
End;

带输出参数的存储过程：

Create or replace procedure p_num
( v_xh in xs_kc.xh%type,
  v_num out number
)
As
begin
  select count(*)   into v_num
  from xs_kc
  where xh=v_xh and cj<60
End;

注意：

输入参数（in类型）的值在调用时传入过程中，在过程中不能再赋值。

输出参数（out类型）在过程中一定要有对其进行赋值的语句，过程结束后其值传递到外界。

调用时，一定要用变量接收输入参数传出的值。

（二）函数

创建函数：

CREATE [ OR REPLACE ] FUNCTION 函数名 
( 参数名  in | out | in out  数据类型 [:=初始值],
  参数名  in | out | in out  数据类型 [:=初始值],…
  参数名  in | out | in out  数据类型 [:=初始值]
)  
RETURN 返回值数据类型
IS | AS
[ <局部变量声明> ]
BEGIN
  <函数体>
  RETURN  返回值
END [ <函数名> ];

带输出参数的函数：

Create or replace function f_num
( v_xh in xs_kc.xh%type,v_xm out xs.xm%type)
Return number
As
   v_num number;
Begin
  select xm into v_xm from xs where xh=v_xh;
  select count(*)   into v_num
  from xs_kc
  where xh=v_xh and cj<60
  return v_num;
End;

函数的调用：

函数的调用必须作为表达式的一部分出现，不能作为单独的语句出现。

过程的调用必须作为独立的语句出现。

（三）存储过程和函数的区别

相同点：1、创建语法结构相似，都可以有多个传入参数和传出参数。

               2、都是一次编译，多次执行。

不同点：1、存储过程定义关键字用procedure，函数定义用function。

               2、存储过程中不能用return返回值，函数中可以而且函数中必须有return字句。

               3、 执行方式不同，存储过程的执行方式有两种，函数可以当做表达式使用，例如放在select中（select 函数名（） from 表名）。

总之，在使用的时候，如果只有一个返回值用函数，否则使用存储过程。