PL/SQL概述(一)

       PL/SQL是Procedure Language/Structuer Query Language 的英文缩写，是ORACLE对标准的SQL的扩展，全面支持SQL的数据库操作、事务控制等。PL/SQL完全支持SQL数据类型，减少了在应用程序和数据库之间的转换数据的操作。在PL/SQL程序中，PL/SQL程序块是基本程序单元，编写PL/SQL程序实际上就是编写PL/SQL块。要完成相对简单的功能，可能只需要写编写一个PL/SQL块；如果要实现复杂的应用功能，那么可能需要在一个PL/SQL块中嵌套其他PL/SQL块。

      为了编写正确、高效的PL/SQL块，PL/SQL的编写应该遵从特定的编写规则，否则会导致编译错误或运行错误。应该遵循的规则（参考其他资料）。

      PL/SQL块结构

      PL/SQL块由三个部分组成：定义部分、执行部分和例外处理部分。其中，定义部分用于定义常量、变量、游标、例外、复杂数据类型等；执行部分用户实现应用模块功能，该部分包含要执行的PL/SQL语句和SQL语句；例外处理部分用于处理执行部分可能出现的运行错误。PL/SQL基本结构：

 

DECLARE
/*
* 定义部分－－定义常量、变量等
*/
BEGIN
/*
*执行部分－－PL/SQL语句和SQL语句
*/
EXCEPTION
/*
*异常处理部分－－处理运行错误
*/
END;(END后一定要有分号哦！！)

 

    其中，DECLARE部分是可选的；BEGIN是必须的；异常处理部分也是课选的；END部分是块的结束标记。

    示例一：只含后执行部分。

SET SERVEROUTPUT ON/*如果想在SQL*plus中输出，必须设置这个参数*/
BEGIN
     dbms_output.put_line('Hello,everyone');
END;
/

 当执行PL/SQL块时，会输出消息。其中dbms_output时oracle提供的系统包，put_line时该包所包含的过程，用于输出字符串信息。当要在sqlplus窗口中执行时，在最后需要“/”才能执行。

示例二：包含定义部分和执行部分

DECLARE
    v_ename VARCHAR2(5);
BEGIN
    SELECT ename INTO v_ename FROM emp WHERE empno=3733;
    dbms_output.put_line('雇员姓名'||v_ename);
END;
/

 

   PL/SQL块分类

   主要包括匿名块、命名块和子程序，其中子程序又包括过程、函数、包和触发器。

1. 匿名块：就是没有名字的PL/SQL块，就是上面定义的块。
2. 命名块：是指具有特定名字的PL/SQL块。命名块和匿名块非常相似，只不过是在块前加了一个<<>> 符号。当使用嵌套块是，为了区分可以使用命名块。例如：

   <<outer>>
   DECLEAR
        v_deptno NUMBER(2);
   BEGIN
       <<inner>>
        BEGIN
           SELECT deptno INTO v_deptno FROM emp WHERE ename='abc';
        END;
        SELECT dname INTO v_dname FROM dept where deptno='123';
   END;

    
3. 子程序：子程序包括过程、函数、包和触发器

            (1)过程：也叫存储过程，是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。存储 过程经过编译后存储在数据库中，所以执行存储过程比执行SQL语句更有效率。创建存储过程使用CREATE PROCEDURE语句，语法格式：CREATE[OR REPLACE] PROCEDURE procedure_name

                        [(parameter)[IN]|OUT|IN OUT]data_type,[……]]

                        IS|AS

                            [declareation_section;]

                       BEGIN

                            procedure_body;

                       END [procedure_name];

             执行存储过程方法：

                  1.使用CALL－－CALL procedure_name([parameter[, ...]]);

                  2.使用EXEC[UTE]－－EXEC[UTE] procedure_name[(parameter[, ...])];

          示例一：没有输出输出参数

/*创建*/
CRATE OR REPLACE PROCEDURE update_emp AS 
BEGIN
     UPDATE emp SET ename='abcd' WHERE empno=7933;
END update_emp;  
/*调用*/
1.CALL update_emp();
2.EXEC update_emp;

        示例二：只有IN参数

/*创建*/
CREATE PROCEDURE update_emp2
          (emp_num IN NUMBER,emp_name IN VARCHAR2) AS
BEGIN
     UPDATE emp SET ename=emp_name 
     WHERE empno=emp_num;
END update_emp2;
 /*不指定调用*/
EXEC update_emp2(7933,'abcd');
或者
CALL update_emp2(7933,'abcd');
/*指定参数名调用*/
EXEC update_emp2(emp_num=>7933,emp_name=>'abcd');
或者
CALL update_emp2(emp_num=>7933,emp_name=>'abcd');

       示例三：带有OUT参数

/*创建*/
CREATE PROCEDURE select_emp
(emp_num IN NUMBER,emp_name OUT VARCHAR2) AS 
BEGIN
     SELECT ename INTO emp_name 
     FROM emp WHERE empno=emp_num;
END select_emp;
/*执行*/
VARIABLE employee_name VARCHAR2(10);
EXEC select_emp(7933,:employee_name);
/*查看结果*/
PRINT employee_name;

    说明：OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种参数，必须在参数后面添加OUT关键字。调用存储过程时，如果要显示该过程中OUT参数返回值，还需要事先使用VARIABLE语句声明对应的变量棘手返回值，并且在调用过程时绑定该变量形式如下：

     VARIABLE variable_name data_type;

     EXEC procedure_name(:variable_name);注意：在绑定时需要在变量名称前加“：”；

    示例四：带有IN OUT 参数…………

     说明：IN OUT 参数同时拥有IN和OUT参数特性，它既可以棘手用户的传值，也允许在过程体中修改，并可以将值返回。使用这种模式的参数需要在参数后面加IN OUT关键字。不过，IN OUT 参数不接收常量值，只能使用变量为其赋值。

使用EXEC命令可以为变量赋值，并且变量名前也需要加“：”。

       (2)函数：函数用于返回特定数据。如果在应用程序中经常需要通过执行SQL语句返回特定数据，那么可以基于这些操作建立特定函数。函数和过程的结构很相似，他们都可以接收输入值并向应用程序返回值，区别在于，过程用来完成一项任务，可能不翻悔值，也可能返回多个值，过程的调用时一条PL/SQL语句；函数包含RETURN子句，用来进行数据操作，并返回一个单独的函数值，函数的调用只能在一个表达式中。创建语法：

   CREATE [OR REPLACE] FUNCTION function_name

    [(paramater[IN|OUT|IN OUT] date_type)]

    RETURN data_type

   IS|AS

    [declaration_section;]

   BEGIN

      function_body;

   END [function_name];

   (3)程序包：包主要是为了实现程序的模块化，程序包可以将相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合到一起，通过这种方式可以构建程序人员重用的代码库。

    (4)触发器：是一种特殊的存储过程，它在发生某种数据库事件时由ORCALE系统自动出发。触发器通常用户加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比check约束更为复杂的约束。