PL\SQL基础

PL\SQL基础


PL\SQL(Procedural Language/SQL)结合了结构化查询和Oracle自身的过程控制，支持更多数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程


控制语句。


PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如子程序）可以单独编译，永久的存储在数据库中，随时准备执行。PL/SQL的优点还


有：
1、支持SQL - PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL


支持所有的SQL数据类型和NULL值。
2、支持面向对象编程 - PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。
3、更好的性能 - SQL是非过程语言，只能一条一条执行，而PL/SQL把一个PL/SQL块统一进行编译后执行，同时还可以把编译好的PL/SQL块存储


起来，以备重用，减少了应用程序和服务器之间的通信时间，PL/SQL是快速而高效的。
4、可移植性 - 使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，同时还可以编写可移植程序库，在不同环境中


重用。
5、安全性 - 可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤


销其他用户访问的能力。


PL/SQL块的语法
[DECLARE       --declaration statements]  ①
BEGIN       --executable statements  ② 
[EXCEPTION       --exception statements]  ③ 
END;






PL/SQL中的特殊符号说明：




类型  符号 说明  
赋值运算符
  := Java和C#中都是等号，PL/SQL的赋值是：= 
特殊字符
  || 字符串连接操作符。 
--  PL/SQL中的单行注释。 
 /*,*/ PL/SQL中的多行注释，多行注释不能嵌套。 
<<,>> 标签分隔符。只为了标识程序特殊位置。 
.. 范围操作符，比如：1..5 标识从１到５ 


算术运算符  
+，-，*，/  基本算术运算符。  
**  求幂操作，比如：3**2=9  
关系运算符  >，<,>=,<=,=  


基本关系运算符，
=表示相等关系，不是赋值。  
<>,!=  不等关系。 


逻辑运算符  
AND,OR,NOT  逻辑运算符。  


声明变量
SQL> DECLARE    
2       sname VARCHAR2(20) :='jerry';  ①   
3  BEGIN    
4       sname:=sname||' and tom';  ②   
5       dbms_output.put_line(sname);  ③   
6  END;   
7  /jerry


dbms_output.put_line是输出语句，可以把一个变量的值输出，在SQL*Plus中输出 数据时，可能没有结果显示，可以使用命令：set serveroutput on设置输出到SQL*Plus控


制台上。




变量赋值  
SQL> DECLARE   
2       sname VARCHAR2(20) DEFAULT 'jerry';  ①   
3  BEGIN    
4       SELECT ename INTO sname FROM emp WHERE empno=7934;  ②   
5       dbms_output.put_line(sname);   
6  END;   
7  /  
MILLER  
PL/SQL procedure successfully completed  


代码解析： 
 ① 变量初始化时，可以使用DEFAULT关键字对变量进行初始化。 
 ② 使用select…into语句对变量sname赋值，要求查询的结果必须是一行，不能是多 行或者没有记录。


使用CONSTANT关键字声明常量。








在SQL*Plus中还可以声明Session（会话，也就是一个客户端从连接到退出的过程称为当前用户的会话。）全局级变量，该变量在整个会话过程中均起作用，类


似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。     使用var声明宿主变量。


代码演示：
宿主常量  
SQL> var emp_name varchar(30);  ① 
SQL> BEGIN    
2  SELECT ename INTO :emp_name FROM emp WHERE empno=7499;  ②   
3  END; 
4  / 




PL/SQL数据类型


标量数据类型: 标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是Oracle SQL中定义


的数据类型，有的是PL/SQL自身附加的数据类型。


属性数据类型: 当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。
% ROWTYPE - 引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于C#中的一个


对象。可以使用“.”来访问记录中的属性。 


SQL> DECLARE    
2       myemp EMP%ROWTYPE;  ①   
3  BEGIN    
4       SELECT * INTO myemp FROM emp WHERE empno=7934;  ②   
5       dbms_output.put_line(myemp.ename);  ③   
6  END;   
7  /  
MILLER




%TYPE  引用某个变量或者数据库的列的类型作为某变量的数据类型。


代码演示：%TYPE应用 
SQL> DECLARE    
2       sal emp.sal%TYPE;  ①   
3       mysal number(4):=3000;   
4       totalsal mysal%TYPE;  ②   
5  BEGIN    
6       SELECT SAL INTO sal FROM emp WHERE empno=7934;   
7       totalsal:=sal+mysal;    
8       dbms_output.put_line(totalsal);   
9  END;  
10  /  
4300 




PL/SQL流程控制
与C#一样也有三种控制结构 - 顺序结构， 条件控制和循环控制。
PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。


CASE语句还可以作为表达式使用，返回一个值。
代码演示：CASE中存在selector，作为表达式使用  
DECLARE        
v_grade CHAR(1):=UPPER('&grade');       
p_grade VARCHAR(20)  
BEGIN
        p_grade :=  ①
       CASE v_grade
            WHEN 'A' THEN
                'Excellent'
            WHEN 'B' THEN
                'Very Good'
            WHEN 'C' THEN
                'Good'
            ELSE
                 'No such grade'
       END;
    dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); 
 
END;


Oracle提供的循环类型有： 
1. 无条件循环LOOP-END LOOP语句 
2. WHILE循环语句 
3. FOR循环语句


在上面的三类循环中EXIT用来强制结束循环，相当于C#循环中的break。


顺序结构
在程序顺序结构中有两个特殊的语句。GOTO和NULL
NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句，但又不需


要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。 






动态SQL  
EXECUTE IMMEDIATE 动态语句字符串 
[INTO 变量列表] 
[USING 参数列表] 


语法解析：  如果动态语句是SELECT语句，可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数，USING为语句中的参数传值。   
动态SQL中的参数格式是：[:参数名]，参数在运行时需要使用USING传值。


PL/SQL的异常处理
 
发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时


，每一个异常都对应一个异常码和异常信息。


语法格式：异常处理 
BEGIN
  --可执行部分   
EXCEPTION
   -- 异常处理开始
   WHEN 异常名1 THEN
     --对应异常处理
         WHEN 异常名2 THEN
      --对应异常处理
           ……
     WHEN OTHERS THEN
              --其他异常处理   
END;


自定义异常  
SQL> DECLARE
    2      sal emp.sal%TYPE;
   3      myexp EXCEPTION;  ①
   4  BEGIN
    5      SELECT sal INTO sal FROM emp WHERE ename='JAMES';
   6      IF sal<5000 THEN
   7         RAISE myexp;  ②
   8      END IF;
   9  EXCEPTION
   10      WHEN NO_DATA_FOUND THEN
   11           dbms_output.put_line('NO RECORDSET FIND!');
  12      WHEN MYEXP THEN  ③
   13           dbms_output.put_line('SAL IS TO LESS!');
  14  END;
  15  /
   SAL IS TO LESS!  
PL/SQL procedure successfully completed


引发应用程序异常
在Oracle开发中，遇到的系统异常都有对应的异常码，在应用系统开发中，用户自定义的异常也可以指定一个异常码和异常信息，Oracle系统为用户预留了


自定义异常码，其范围介于-20000到-20999之间的负整数。引发应用程序异常的语法是：  
RAISE_APPLICATION_ERROR(异常码，异常信息)


代码演示：引发应用系统异常
  SQL> DECLARE
    2      sal emp.sal%TYPE;
   3      myexp EXCEPTION;
   4  BEGIN
    5      SELECT sal INTO sal FROM emp WHERE ename='JAMES';
   6      IF sal<5000 THEN
   7         RAISE myexp;
   8      END IF;
   9  EXCEPTION
   10      WHEN NO_DATA_FOUND THEN
   11           dbms_output.put_line('NO RECORDSET FIND!');
  12      WHEN MYEXP THEN
   13           RAISE_APPLICATION_ERROR(-20001,'SAL IS TO LESS!');  ①
  14  END;
  15  /
  ORA-20001: SAL IS TO LESS!  ②
 ORA-06512: 在 line 14 


如果要处理未命名的内部异常，必须使用OTHERS异常处理器。也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定。


PRAGMA由编译器控制，PRAGMA在编译时处理，而不是在运行时处理。EXCEPTION_INIT告诉编译器将异常名与ORACLE错误码绑定起来，这样可以通过异常名引用任意的内部异常，并且可以通过异常名为异常编写适当的异常处理器。
PRAGMA EXCEPTION_INIT的语法是：  
PRAGMA EXCEPTION_INIT(异常名,异常码)   
这里的异常码可以是用户自定义的异常码，也可以是Oracle系统的异常码。