（数据库）16_PL_SQL

一、PL/SQL

SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。
PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。

1. 什么是PL/SQL?

PL/SQL是 Procedure Language & Structured Query Language 的缩写。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。

2. PL/SQL的优点或特征

1. 有利于客户/服务器环境应用的运行
2. 适合于客户环境
3. 过程化
4. 模块化
5. 运行错误的可处理性
6. 提供大量内置程序包

3. 运行PL/SQL程序

PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。

二、PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下：

DECLARE  
/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
    /*  执行部分:  过程及SQL 语句  , 即程序的主要部分  */
EXCEPTION
   /* 执行异常部分: 错误处理  */
END;
其中 执行部分是必须的。

PL/SQL块可以分为三类：

1. 无名块：动态构造，只能执行一次。
2. 子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
3. 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

declare
  str varchar2(30);
  a number;
 begin
   str := 'hello world';
   dbms_output.put_line(str);
 end;

declare
  str varchar2(30);
  a number;
 begin
   str := 'hello world';
   a := 'aaa';
   dbms_output.put_line(str);
   dbms_output.put_line(a);
   exception
     when others  then
       dbms_output.put_line('输入有误');
 end;

1.变量的声明

1.1.标识符

PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：

标识符名不能超过30字符；
第一个字符必须为字母；
不分大小写；
不能用’-‘(减号);
不能是SQL保留字。

1.2.变量命名

2.变量类型

2.1.基本类型

2.2.RECORD 类型

记录类型
记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD 数据类型的成员，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。
定义记录类型语法如下:

TYPE record_type IS RECORD(
   Field1 type1  [NOT NULL]  [:= exp1 ],
   Field2 type2  [NOT NULL]  [:= exp2 ],
   . . .   . . .
   Fieldn typen  [NOT NULL]  [:= expn ] ) ;

 -- Type recode 
 declare
    type person is record(
         pname varchar2(30),
         page number,
         psex varchar2(10)
    ) ;--自定义类型
    V_per  person;--声明了一个自定义类型的变量
 begin
   V_per.pname := '张三';
   V_per.page :=22;
   V_per.psex :='男';
   dbms_output.put_line(V_per.pname || '---'||V_per.page||'--'||V_per.psex);
 end;

2.3.使用%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
使用%TYPE特性的优点在于：

所引用的数据库列的数据类型可以不必知道；
所引用的数据库列的数据类型可以实时改变。

 --%Type
 declare
    V_ename emp.first_name%Type;
    V_sal  emp.salary %Type;
    V_date emp.hire_date%Type;
 
 begin
   select first_name,salary,hire_date into V_ename,V_sal,V_date
   from emp where employee_id =100; 
 
   dbms_output.put_line(V_ename||'--'||V_sal||'--'||V_date);
 
 end;

2.4.%ROWTYPE

-- %ROWTYPE
declare 
   V_emp emp%Rowtype;--V_emp的类型为emp表的一行

begin
  select first_name,salary ,hire_date into V_emp.first_name,V_emp.salary,V_emp.hire_date
  from emp where employee_id=100;
  dbms_output.put_line(V_emp.first_name||'--'||V_emp.salary);
end;
 

3.运算符

3.1.关系运算符

3.2.一般运算符

3.3.逻辑运算符

4.变量赋值

在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：

 variable  := expression ;

variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.

4.1.字符及数字运算特点

1. 空值加数字仍是空值：NULL + < 数字> = NULL
2. 空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>

4.2.BOOLEAN 赋值

**布尔值只有TRUE, FALSE及 NULL 三个值。**如：

DECLARE
done BOOLEAN;
BEGIN
done := &done;
 dbms_output.put_line(
     CASE 
        WHEN done IS NULL THEN 'unknow'
        WHEN done THEN 'yes'
         WHEN  NOT done THEN 'no'
     END
 );
END;

4.3. 数据库赋值

数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：

DECLARE
emp_id    emp.empno%TYPE :=7788;
emp_name emp.ename%TYPE;
wages     emp.sal%TYPE;
BEGIN
SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages 
FROM emp WHERE empno = emp_id;
Dbms_output.put_line(emp_name||'----'||to_char(wages));
END;

提示：不能将SELECT语句中的列赋值给布尔变量。

4.4. 可转换的类型赋值

1. CHAR 转换为 NUMBER：使用 TO_NUMBER 函数来完成字符到数字的转换，如：

v_total  := TO_NUMBER(‘100.0’) + sal;

2. NUMBER 转换为CHAR：使用 TO_CHAR函数可以实现数字到字符的转换，如：

v_comm := TO_CHAR(‘123.45’) || ’元’ ;

3. 字符转换为日期：使用 TO_DATE函数可以实现 字符到日期的转换，如：

v_date := TO_DATE('2001.07.03','yyyy.mm.dd');

4. 日期转换为字符使用 TO_CHAR函数可以实现日期到字符的转换，如：

v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

5.PL/SQL编程练习

5.1.简单数据插入例子

--给Student表插入数据
declare
  V_id  student.stu_id%type ;
  V_name student.stu_name%type;
  V_age student.stu_age%type ;
  V_sex student.stu_sex%type ;
  
begin
  insert into student(stu_id,stu_name,stu_age,stu_sex)
  values(&V_id,'&V_name',&V_age,'&V_sex');  
end;

5.2. 简单数据删除例子

--简单数据删除例子
declare 
  V_id student.stu_id%type;

begin
  delete from student where stu_id=&V_id