ORACLE数据库-PL/SQL程序--基础定义和游标的使用

---------PL/SQL 程序 -------------------------

------ PL/SQL 的基本结构

【 
 DECLARE 
 --声明一些变量、常量、用户定义的数据类型以及游标等
 --这一部分可选，如不需要可以不写
BEGIN 
 --主程序体，在这里可以加入各种合法语句
EXCEPTION
 --异常处理部分，当程序中出现错误时执行这一部分
END;--这里必须有分号 
 --主程序体结束
 】
     从此可以看出，PL/SQL 块主要包含3个基本部分：声明部分（declarative section）、
 执行部分（executable section）和异常处理部分（exception section）。其中只有执行部分
 是必须的，其他两个部分是可选的。需要强调的是，该结构最后的分号是必须的。

---------------- PL/SQL 里的注释（comment）

单行注释：如果注释超过一行，就必须在每一行的开头使用双连字符（--）。
多行注释：多行注释由/* 开头，由*/ 结尾。

----------------PL/SQL 字符集

1.PL/SQL合法字符集

-大写和小写英文字母
-数字0—9
-非显示的字符、制表符、空格和回车
-数学符号+、-、*、/、<、>、= 等等
-间隔符，包括()、{}、[]、?、!、;、:、''、""、@ 、# 、% 、$ 、^、& 等等
PL/SQL不区分字母的大小写。
标准的PL/SQL语句字符集是ASCII字符集的一部分。
ASCII字符集是一个单字节字符集，这就是说每个字符可以表示为一个字节的数据，该性质将字符总数限制在最多为256个。

2.分界符

分解符（delimiter）是对PL/SQL有特殊意义的符号，他们用来将标识符相互分割开。
+ --加法操作符
- --减法操作符
* --乘法
/ --除法
= --等于
> --大于号
< --小于号
<> --不等于
!= --不等于
~= --不等于
^= --不等于
<= --小于等于
>= --大于等于
:= --赋值操作符
() --起始表达式分界符和终结表达式操作符
; --语句终结符
% --属性指示符
, --项目分隔符，如查表的多个列字段时，不同字段间用它分开
@ --数据库链接指示符
/ --字符串分界符
: --绑定变量指示符
** --指数操作符
=> --链接操作符
.. --范围操作符
|| --串连接操作符
<< --起始标签分界符
>> --终结标签分解符
-- --单行注释指示符
/**/ --多行注释指示符
<space> --空格
<tab> --制表符

3.PL/SQL数据类型

(1)数字类型-存储整数或者实数

NUMBER --可以存储整数或浮点数
       --NUMBER(P,S) 是一种格式化的数字，其中P 是精度，S 是刻度范围。
       --精度是数值中所有有效数字的个数，而刻度范围是小数点右边数字位的个数。
       --精度和刻度范围都是可选的，但如果指定了刻度范围，那也必须指定精度
       --NUMBER(P) 或者NUMBER(P,S)
       --若刻度范围是个负数，那么就由小数点开始向左边计算数字位的个数
BINARY_INTEGER --只存储整数
PLS_INTEGER --只存储整数
"子类型"是类型的一个候选名，它是可选的，可以用它来限制子类型变量的合法取值。
有多种与NUMBER等价的子类型，实际上，它们是重命名的NUMBER数据类型。
有时候可能出于可读性或者与来自于其他数据库的数据类型相兼容时会使用候选名。
这些等价的类型包括 DEC 、DECIMAL、DOUBLE PRECISION、INTEGER、INT、NUMERIC、REAL、SMALLINT、BINARY_INTEGER、PLS_INTEGER。

(2)字符类型

字符类型变量是用来存储字符串或者字符数据。其类型包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2 
——1.VARCHAR2
VARCHAR2类型和数据库类型中的VARCHAR2类似，都是存储变长字符串的。在PL/SQL中最大字节长度为32767字节，数据
库类型的VARCHAR2的最大长度为4000字节。所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库的一个
VARCHAR2类型变量，只能赋值给LONG类型的数据库变量。                                  
注意：数据库变量和PL/SQL语言的变量是两个不同的概念，在创建表时的变量都是数据库变量，如CREATE TABLE A(NAME VARCHAR2(30));在这里的NAME就是数据库
变量，VARCHAR2就是数据库变量类型。
——2.CHAR
CHAR是定长字符串。最大长度为32767，与VARCHAR2不同，它的最大长度可以不指定，默认为1。如果赋给CHAR类型的值不足定义的最大长度，
则在其后面用空格补全。数据库类型中的CHAR只有2000字节。
——3.LONG
LONG是一个可变的字符串，最大长度是32760字节。数据库类型的LONG长度最大可达2GB。
——4.NCHAR和NVARCHAR2类型是PL/SQL8.0以后加入的类型，它的长度指定根据各国字符集的不同而不同。

(3)日期类型

DATE，用来存储日期和时间信息，包括世纪，年，月，天，小时，分钟和秒。DATE变量的存储空间是7个字节，每个部分占用一个字节。

(4)布尔类型

布尔类型中的唯一类型是 BOOLEAN，主要用于控制程序流程，一个布尔类型变量的值可以是 TRUE、FALSE、NULL。

(5)TYPE定义数据类型

TYPE <数据类型名>IS<数据类型>;
在ORACLE中允许用户定义两种数据类型，一个是RECORD（记录类型）和TABLE（表类型）
如下所示，
TYPE TEACHER_RECORD IS RECORD
(TID NUMBER(5) NOT NULL:=0,
 NAME VARCHAR2(50),
 TITLE VARCHAR2(50),
 SEX CHAR(1)
);
定义一个TEACHER_RECORD的记录变量A：A TEACHER_RECORD;

(6)%TYPE 和%ROWTYPE

PL/SQL 还提供了%TYPE 和%ROWTYPE 两种特殊的变量，用于声明与表的列相匹配的变量和用户定义数据类型，

前一个表示单属性的数据类型，后一个表示整个属性列表的结构，即元组的类型。
声明变量的方法如:
V_NAME SCOTT.EMP.EMPNO%TYPE;--EMP 表的EMPNO 列的属性赋给V_NAME 变量；
V_NAME SCOTT.EMP%ROWTYPE;--EMP 表的所有列字段属性赋给V_NAME ；

4.PL/SQL变量和常量

（1）定义常量
<常量名>CONSTANT<数据类型>:=<值>;
常量一旦定义，在以后的使用中其值将不再改变。
（2）定义变量
<变量名><数据类型>[(宽度):=<初始值>];
变量的宽度和初始值可以定义也可以不定义。
（3）变量初始化
一般而言如果变量的取值可以被确定，那么最好为其初始化一个数值；
PL/SQL 定义了一个未初始化变量应该存放的内容，被赋值为NULL。

5.PL/SQL语句控制结构

（1）选择结构

IF ... THEN 
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE

（2）CASE语句

CASE 检测表达式
WHEN 表达式1 THEN 语句序列1
WHEN 表达式2 THEN 语句序列2
... ...
WHEN 表达式n THEN 语句序列n
[ELSE 其他语句序列]
END;
如果检测表达式的值与下面任何一个表达式的值都不匹配时，PL/SQL 会产生
预定义错误CASE_NOT_FOUND 。
注意：CASE语句中的表达式1到表达式n的类型必须同检测表达式的类型相符。一旦
选定的语句序列被执行，控制就会立即转到CASE语句之后的语句 。

（3）循环结构

LOOP ... EXIT ... END LOOP
如:
N:=0;            --初始化N 为0
LOOP             --开始循环
  IF N>5 THEN    --如果N 大于5 则退出循环
    EXIT;        
    END IF;     
    N:=N+1;      --改变N 的值
    END LOOP;
    
LOOP ... EXIT WHEN ... END LOOP
如：
N:=0;            --初始化N 为0
LOOP             --开始循环
  EXIT WHEN N>5  --如果N >5 则退出循环
   N:=N+1;       --改变N 的值
   END LOOP;     --结束循环
   
WHILE ... LOOP ... END LOOP
如：
N:=0;            --初始化N 的值
WHILE N<=5 LOOP  --如果变量小于或者等于5 则循环
  N:=N+1;        --改变变量的值
  END LOOP;      --结束循环
  
FOR ... IN ... LOOP ... END LOOP
如：
FOR i IN 0..5 LOOP --N 从0 到5 之间就进行循环
  NULL;            --循环主体（这里写的是空操作）
  END LOOP;        --结束循环
  
GOTO
如：
...                --程序其他部分
<<goto_mark>>      --定义了一个转向标签goto_mark
...                --程序其他部分
IF N>98050 THEN     
  GOTO goto_mark;  --如果条件成立则转向goto_mark继续执行
...                 --程序其他部分
使用GOTO要小心，不必要的GOTO语句会使得程序代码变得复杂化，容易出错，而且难以维护和理解。

6.PL/SQL表达式

表达式本身毫无意义，它需要存放在一个位置产生价值。通常，表达式作为赋值语句的一部分出现在
赋值运算符的右边，或者作为函数的参数等。
‘操作符’是运算符的参数。根据所拥有的参数个数，PL/SQL 运算符可分为一元运算符（一个参数）和二元
运算符（两个参数）。表达式按照操作对象的不同，也可以分为字符表达式和布尔表达式两种。
（1）字符表达式
唯一的字符运算符就是并运算符‘||’；
（2）布尔表达式
布尔表达式是一个判断为真还是为假的条件，它的值只有 TRUE\FALSE\NULL 三个；
布尔表达式有三个布尔运算符：AND\OR\NOT 
TRUE AND NULL 的值为 NULL 因为不知道 NULL 操作数是否为 TRUE 。
布尔表达式中的算数运算符：
= 等于    != 不等于      < 小于     > 大于   <= 小于等于     >=大于等于
此外 BETWEEN ... AND ... 范围内为真（闭区间），范围外为假。
IN 操作符判断某一个元素是否属于某个集合。

-------------------PL/SQL 的游标

在PL/SQL块中执行 SELECT、INSERT、UPDATE、DELETE 语句时，ORACLE会在内存中为其分配上下文区（context area），寄一个缓冲区。
游标是指向该区的一个指针，或是命名一个工作区（work area），或是一种结构化数据类型。
游标可以针对多行数据查询结果集中的每一行数据分别进行单独处理。
游标分为显式游标和隐式游标两种。

1.显式游标--声明游标、打开游标、提取游标、关闭游标4 个步骤

显式游标属性
——%NOTFOUND属性。当最近一次读记录(FETCH),成功取到值，此数据为FALSE；
——%FOUND属性，与%notFOUND相反；
——% ROWCOUNT属性， 返回由INSERT、UPDATE或DELETE 所影响的行数。对于游标，则% ROWCOUNT表示FETCH成功的数据条数。如果OPEN游标，但没有进行FETCH，则ROWCOUNT值为0。
——%ISOPEN属性，当游标已打开时返回TRUE

LOOP循环和游标的联合使用：

DECLARE 
V_EMPNO NUMBER(30); --定义两个变量来存放游标的内容
V_ENAME VARCHAR(20);
CURSOR C_EMP IS  --声明游标
  SELECT EMPNO,ENAME FROM EMP;
BEGIN
  OPEN C_EMP; --打开游标
  LOOP
    FETCH C_EMP INTO V_EMPNO,V_ENAME;--提取游标的值给两个变量
    EXIT WHEN C_EMP%NOTFOUND; --NOTFOUND 是游标的一个属性，当从C_EMP 中找不到值时退出循环
    DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_ENAME);--打印两个变量
    END LOOP;--结束循环
    CLOSE C_EMP;--关闭游标
    END;

WHILE循环和游标的联合使用：

DECLARE 
V_EMPNO NUMBER(30); --定义两个变量来存放游标的内容
V_ENAME VARCHAR(20);
CURSOR C_EMP IS  --声明游标
  SELECT EMPNO,ENAME FROM EMP;
BEGIN
  OPEN C_EMP; --打开游标
  FETCH C_EMP INTO V_EMPNO,V_ENAME;--提取游标的值给两个变量（为了进入循环做准备）
   WHILE C_EMP%FOUND LOOP--当从C_EMP 中找到值时进入循环
    DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_ENAME);--打印两个变量
    FETCH C_EMP INTO V_EMPNO,V_ENAME;--提取游标的值给两个变量（提取游标里的下一个值）
    END LOOP;--结束循环
    CLOSE C_EMP;--关闭游标
    END;

FOR循环和游标的联合使用：

DECLARE 
N EMP%ROWTYPE;
CURSOR N_EMP IS --声明游标
SELECT EMPNO,ENAME FROM EMP;
BEGIN 
  FOR N IN N_EMP LOOP --变量N 在游标N_EMP 中则进入循环
    DBMS_OUTPUT.PUT_LINE(N.EMPNO||' '||N.ENAME);--打印变量，注意这里要标识上列属性
    END LOOP;--结束循环
    END;

2.隐式游标

如果在PL/SQL中用SELECT语句进行了操作，则隐式地使用了游标，也就是隐式游标。
隐式游标无需定义，也不用打开和关闭。隐性游标的名字为 SQL，隐性游标属性调用为：SQL%游标属性名


隐式游标属性
——%NOTFOUND属性
——%FOUND属性
——%ROWCOUNT属性
——%ISOPEN属性（隐式游标里总是FALSE）


隐式有标的使用示例：
    Declare
    Begin
           UPDATE EMP SET ENAME='New' WHERE EMPNO=7788;--更新数据
           IF SQL%NOTFOUND THEN  --如果没有找到最近一条隐式游标则输出
               dbms_output.put_line('没有找到数据');
           ELSIF SQL%FOUND THEN  --如果隐式游标里有数据则输出
               dbms_output.put_line('更新'||SQL%ROWCOUNT||'条数据');
           END IF;
    End;

3.参数化游标

定义游标时，可以带上参数，使得在使用游标时，根据参数不同所选中的数据行也不同，达到动态使用的目的。
如：
DECLARE 
V EMP%ROWTYPE;
--定义游标时带上参数N
CURSOR C(N NUMBER) IS SELECT ENAME,JOB,SAL FROM EMP
WHERE EMPNO=N;--使用参数
BEGIN
  OPEN C(7788);--带上实参数
  FETCH C INTO V.ENAME,V.JOB,V.SAL;
  DBMS_OUTPUT.PUT_LINE(V.ENAME||'  '||V.JOB||'  '||V.SAL);
  CLOSE C;
  END;   

4.游标变量

游标变量可以在运行时与不同的语句关联，是动态的。游标变量被用于处理多行的查询结果集。在同一个PL/SQL块中，
游标变量不同于特定的查询绑定，而是再打开游标时才确定所对应的查询。因此，游标变量可以依次对应多个查询。
使用游标变量之前必须先声明，然后在运行时必须为其分配存储空间，因为游标变量是REF类型的变量，类似于高级语言中的指针。
（1）定义游标变量
PL/SQL中的引用类型通过下述的语法进行声明：
REF type其中type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标可以使用的类型
就是REF CURSOR.
TYPE <类型名> IS REF CURSOR
RETURN <返回类型>;
其中<类型名>是新的引用类型的名字，而<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。
游标变量的返回类型必须是一个记录类型。它可以被显示声明为一个用户定义的记录，或者隐式使用%ROWTYPE进行声明。
在定义了引用类型以后，就可以声明该变量了。
注:游标总是指向相同的查询工作区,游标变量能够指向不同的工作区,因此游标和游标变量不能互操作 。


受限的游标变量定义(强类型)：
  TYPE 游标变量的类型名 IS REF CURSOR
  RETURN 变量名; 【注意：这里的变量名必须是一个记录型变量或者数据表的一行】
如：
  TYPE T_EMP IS REF CURSOR--定义游标变量
  RETURN T_EMP1;
  TYPE T_EMP1 IS RECORD(       -- 定义一个记录性变量
     EN EMP.ENAME%TYPE,
     EN1 EMP.JOB%TYPE
     );
  V_EMP1 T_EMP; --声明游标变量
或者 
  TYPE T_EMP IS REF CURSOR
  RETURN EMP%ROWTYPE;
  V_EMP1 T_EMP;
  
非受限的游标变量定义（弱类型）：
DECLARE 
 TYPE T_EMP2 IS REF CURSOR;
 V_CURSORVAR T_EMP2;


(2)打开游标变量：
OPEN <游标变量>FOR<SELECT 语句>;


(3)关闭游标变量：CLOSE
关闭已经关闭的游标变量是非法的。


(4)游标变量的使用示例：
强类型：
DECLARE 
 TYPE T_EMP IS REF CURSOR--定义游标变量，名字为T_EMP，他就是一个变量类型
 RETURN EMP%ROWTYPE;--指定T_EMP 的返回类型
 T_EMP1 T_EMP;--声明游标变量T_EMP1
 T1 EMP%ROWTYPE;--定义一个变量T1
 BEGIN 
   OPEN T_EMP1 FOR SELECT * FROM EMP WHERE DEPTNO=20;--第一次打开游标变量
   LOOP
     FETCH T_EMP1 INTO T1;--将游标变量的值赋给变量T1
     EXIT WHEN T_EMP1%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(T1.EMPNO||'  '||T1.ENAME);
   END LOOP;
   OPEN T_EMP1 FOR SELECT * FROM EMP ;--第二次打开游标变量
   LOOP
     FETCH T_EMP1 INTO T1;--再次将游标变量的值赋给变量T1
          EXIT WHEN T_EMP1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(T1.ENAME||'  '||T1.SAL);
      END LOOP;
   END;--游标变量就是可以多次指定值，而不像普通游标那样只能指定一次值
弱类型：
DECLARE 
 TYPE T_EMP IS REF CURSOR;
 T_EMP1 T_EMP;
 T1 EMP%ROWTYPE;
 BEGIN 
   OPEN T_EMP1 FOR SELECT * FROM EMP;
   LOOP
     FETCH T_EMP1 INTO T1;
     EXIT WHEN T_EMP1%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(T1.ENAME||'  '||T1.SAL);
     END LOOP;
  END;

TYPE --是可以定以多个变量类型的一种变量类型
如：
TYPE TT IS RECORD(U_ID NUMBER(20),U_NAME VARCHAR2(20));--则变量类型TT 含有NUMBER 和VARCHAR 两个数据类型
TT1 TT;--则变量TT1 就拥有了NUMBER 和VARCHAR 两个数据类型
TT1.U_ID:=1 --就是将变量TT1 中的U_ID 类型赋值为1