pl/sql编程基础

　　pl/sql是一种块结构的语言，pl/sql块由三个部分组成：定义部分、执行部分和异常处理部分。定义部分用于定义常量、变量、游标、用户自定义异常等；执行部分用于实现应用模块化功能；异常处理部分用于实现应用可能出现的运行错误。pl/sql基础结构块如下：

declare
/*
 * 定义部分 -- 定义常量、变量、游标、用户自定义异常
 */
begin
/*
 * 执行部分 -- pl/sql 和SQL语句
 */
exception
/*
 * -- 异常处理部分 -- 处理运行异常
 */
end; /* 块结束标记 */

pl/sql块的类型 -- 子程序

　　子程序包括过程、函数和包；使用子程序的有点在于可以简化客户端的开发和维护，并且提高了应用程序性能。

　　1.过程：是用于执行特定的操作，在pl/sql中可以使用create procedure 命令建立过程。实例：

/* 创建过程 */
CREATE OR REPLACE PROCEDURE procedure_number() 
IS 
BEGIN 
       SELECT SUM(GuestNum) INTO gNum FROM RoomType JOIN          room ON roomTypeID = TYPEID WHERE TypeName = tName;
END;/*这是一个简单查询*/

　　2.函数：函数用于返回特定数据，用create function 命令建立函数。（*在函数中必须有return返回值）实例：

CREATE OR REPLACE FUNCTION function_delect(tName VARCHAR2) 
RETURN NUMBER
IS       
       count_Guest NUMBER(2);-- 用来接收删除的记录条数
       rownumber NUMBER(2) := 0;
BEGIN
       -- 删除房间信息记录
      DELETE FROM Room WHERE RoomID IN  (SELECT RoomID FROM RoomType JOIN room ON 
      TYPEID = RoomTypeid WHERE TypeName = tName);
      rownumber := rownumber + sql%rowcount; -- 返回删除的记录数
       -- 提交数据 
      RETURN rownumber;
      COMMIT;  
      /* 异常处理 */
      EXCEPTION 
             WHEN OTHERS THEN
                  ROLLBACK;-- 回滚  
                  DBMS_OUTPUT.PUT_LINE(SQLERRM);
                  RETURN -1;
END;

用函数在上面的代码中，是你简单删除操作，声明了一些变量，提交和异常处理。

3.包：包用于逻辑组合相关的过程和函数，它由包规范和包体组成的。包规范就像java中的接口一样，定义过程和函数，在包体中必须实现包规范中的所有过程和函数。用create package 命令。

/* 定义包规范 */
CREATE OR REPLACE PACKAGE pkg_gues IS
       FUNCTION fun_room(tName VARCHAR2) RETURN NUMBER;--  a. 统计某类型客房的入住客人人数的函数
       PROCEDURE proc_RoomType;-- c. 统计各类型客房数量的过程
END pkg_gues;

在包规范中，值包含了过程和函数的说明。具体实现是包体中体现的。包体使用CREATE PACKAGE BODY命令。实例

/* 包体：实现 */
CREATE OR REPLACE PACKAGE BODY pkg_gues IS
/*函数*/
       FUNCTION fun_room(tName VARCHAR2) RETURN NUMBER IS
                       amount NUMBER(3);
              BEGIN 
                       SELECT SUM(GuestNum) INTO amount FROM Room WHERE DESCRIPTION = tName;
                       RETURN amount;
              END;
/*过程*/
       PROCEDURE proc_RoomType IS     
                       /*保存各房间类型有多少房间*/
                       TYPE count_type_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  
                       count_type count_type_table;
                       /*保存已有人入住的房间类型*/
                        TYPE count_table_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; 
                        count_table count_table_type;
              BEGIN
                       SELECT DESCRIPTION,COUNT(*) BULK COLLECT INTO count_table,count_type FROM room 
                       GROUP BY DESCRIPTION;
                       FOR i IN 1 .. count_type.count LOOP
                              dbms_output.put_line('房间类型：'|| count_table(i) ||'房间数：' || count_type(i));
                       END LOOP;       
              END;
       
END pkg_gues;

在包体和包规范中用到的OR REPLACE 作用是在每次运行的时候将以前运行后生成的替换。