ORACLE PL/SQL

 PL/SQL 概述

PL/SQL是Oracle对关系数据库语言SQL的过程化扩充,它将数据库技术和过程化程序设计语言联系起来,是一种应用开发语言,可使用循环,分支处理数据,将SQL的数据操纵功能与过程化语言数据处理功能结合起来.   PL/SQL的使用,使SQL成为一种高级程序设计语言,支持高级语言的块操作,条件判断,循环语句,嵌套等,与数据库核心的数据类型集成,使SQL 的程序设计效率更高.

PL/SQL是一种块结构语言,构成PL/SQL的基本单位是逻辑块,块中可以嵌套子块,以块为单位向Oracle Server传递数据执行,其块结构如下:

      DECLARE :说明部分:说明变量及常量

      BEGIN:执行部分:使用SQL的DML语言事务处理

      EXCEPTION:例外处理:处理错误及警告信息

      END:逻辑块结束

   PL/SQL语言的特点

1、PL/SQL中可以定义变量,变量有其作用范围
2、PL/SQL是以块的方式设计,块中可以嵌套子块,子块可以位于块中任何部分
3、子块可以是PL/SQL中的一条命令
4、PL/SQL中可以使用条件判断,循环,游标等高级语言程序设计方法
5、模块式的过程化语言,以块为单位执行

6、使用PL/SQL可以优化程序设计,得到更好的性能(例如,执行 10个SQL语句,需要访问10次 Oracle核心,如果10个SQL组成一个

(例如,执行 10个SQL语句,需要访问10次 Oracle核心,如果10个SQL组成一个PL/SQL程序,则只需访问一次Oracle 核心,将结果一次返回给用户,则程序执行效率高,节省时间,降低通信量)7、PL/SQL程序,则只需访问一次Oracle 核心,将结果一次返回给用户,则程序执行效率高,节省时间,降低通信量)

8、减少对于Oracle核心的访问,降低网络负载
9、数据库数据类型集成PL/SQL支持全部的SQL数据类型,这些共享的数据类型与SQL所提供的直接存取相结合,使PL/SQL与Oracle数据字典结合成一体.

10、PL/SQL与Oracle RDBMS集成在一起,可以使用PL/SQL的集成数据类型,使变量的数据类型在数据库的操作中实时确定.

什么是ORACLE RDBMS?它有什么作用 ：

就是Oracle关系型数据库管理系统
管理ORacle数据库的数据管理、访问等等

PL/SQL与SQL语言结合
PL/SQL语句含有SQL的DML语句,检索及事务处理PL/SQL中可以使用的SQL语句如下: INSERT            UPDATE                DELETE      SELECT  INTO COMMIT       ROLLBACK         SAVEPOINT

支持 RDBMS 的所有数据类型

对于SQL的数据类型进行了扩充,增加了新的数据类型,使PL/SQL所支持的数据类型更灵活.

PL/SQL基础

变量的使用    和其他高级语言一样,在PL/SQL中可以使用变量

注释               单行注释:   --                             多行注释:  /*           */

数据类型：

日期型：Date

字符型  CHAR,VARCHAR2

数字型   NUMBER

布尔型   BOOLEAN(TRUE,FALSE,NULL)

数字型
NUMBER:      存储整型及实型数据，可以定义精度和取值范围
BINARY_INTEGER:     可存储带符号整数，为整数计算优化性能
 DEC:             NUMBER的子类型，存储实型数据
 DOUBLE PRECISION:        NUMBER的子类型，高精度实数
 INTEGER:    NUMBER的子类型，整数

 INT:             NUMBER的子类型，整数

SMALLINT:   NUMBER的子类型，取值范围比INTEGER小
NUMERIC:    NUMBER的子类型，与NUMBER等价

REAL :        NUMBER的子类型，存储实型数据

字符型
VARCHAR2  存放可变长字符串，有最大长度限制
 CHAR   字符型，固定长度
 LONG    长字符型

 CLOB     大对象字符(Oracle8、Oracle8i)

关于CLOB 参考 https://mp.youkuaiyun.com/postedit/80458810

 日期型

 Date  存储日期数据

布尔型
 BOOLEAN  结果是TRUE或FALSE
  二进制数据
 RAW  存储多媒体数据，如图象、声音、视频等

 BLOB  大对象如图象、声音、视频等（Oracle8、Oracle8i）

数据定义

       标示符    数据类型 ; 

       标示符    数据类型 := PL/SQL表达式;

        例如, v_salary   number(7,2);   v_ename  varchar2(20) :=‘SMITH’;    

变量赋值

      变量名 := 值;  

                                  v_salary := 890.76;

                                  v_date    :=‘25-MAR-99’;

                                  Available_now :=FALSE;

        %TYPE类型

                Credit   Real(7,2);

                Debit   Credit%TYPE; 

                 My_name   Employee.ename%TYPE;

                 %TYPE 属性   + 不必知道My_name的真正数据类型当数据库中列ename列定义改变时，数据库运行时自动修改．

        %ROWTYPE:行类型　　　　　　        

定义方法：TYPE  r_record is RECORD (

                      v_name   emp.ename%TYPE,

                      v_job       emp.job%TYPE,

                      v_sal       emp.sal%TYPE

                    );

    变量定义   r_emp    r_record;

    变量使用　　　SELECT ename,job,sal INTO r_emp  FROM emp

                             WHERE empno=7934;

                             则，r_emp.v_ename,r_emp.v_job,r_emp.v_sal 已有值；

    给变量赋值：　r_employee   r_record;r_employee.v_ename :=‘JACK’;

                            r_employee.v_job       :=‘CLERK’;　 r_employee.v_sal := 890.98;

  给变量赋值：　r_employee   r_record;

                          r_employee.v_ename :=‘JACK’;

                         r_employee.v_job       :=‘CLERK’;　

                         r_employee.v_sal := 890.98;

TABLE类型数据　

    PL/SQL中的表(table)类型类似于Ｃ语言中的结构类型数组．

    定义方法：TYPE  table_emp   IS  TABLE OF  emp.ename %TYPE

    INDEX BY BINARY_INTEGER;

一个PL/SQL表有两个列，(key,value),key列类型即是 BINARY_INTEGER,而value类型则是所定义的数据类型．

Table类型使用：　定义变量 my_name为 table_emp 类型,则可以使用变量 my_name ,也可以在SQL语句中使用 table类型变量．

            my_name  table_emp;

            my_name(0) :=‘SCOTT’;

            my_name(1)  :=‘SMITH’;

            my_name(2)   :=‘SUSAN’;

            SELECT  ename   INTO  my_name(10)  FROM emp   WHERE  empno = 7934;

DELETE 语句的使用

  Declare  

        v_empno   EMP.empno%TYPE :=1234;

    Begin

        DELETE FROM   emp   WHERE  empno=v_empno;

    End;

事务处理语句的使用

   在PL/SQL中可以使用SQL的 COMMIT,ROLLBACK及 SAVEPOINT语句．

    Declare  

        v_empno   EMP.empno%TYPE :=1234;

    Begin

        DELETE FROM   emp   WHERE  empno=v_empno;

        COMMIT;

     End;

条件控制语句

IF-THEN语句 :  IF 条件成立  THEN  可执行语句;  END  IF;

IF-THEN-ELSE语句:   IF 条件成立  THEN  执行语句1;    ELSE 执行语句2；    END  IF;

IF-THEN-ELSIF语句;   IF       条件1成立  THEN  执行语句1; ELSIF 条件2成立  THEN   执行语句2； ELSIF 条件3成立  THEN   执行语句3;    ...    END  IF;

循环语句

基本循环    LOOP  执行语句；  EXIT WHEN 条件成立；    END LOOP;

WHILE循环   WHILE 条件成立 LOOP  执行语句；  END LOOP;

FOR循环   FOR 计数器 IN 低界..高界 LOOP        执行语句；   END LOOP;

 光标(CURSOR)设计

什么是光标（游标）

游标是ORACLE 系统在内存中开辟的一个工作区，其中存放select语句返回的查询集。

在游标所定义的工作区中存在着一个指针（POINTER） 在初始状态时它指向查询结果的首记录，

当指针放置到某行后，即可对改行数据进行操作。

光标使用

光标说明 ：在Declare 段中命名光标，将光标与一查询语句联系起来. 

 语法：  CURSOR  光标名(参数)   IS SELECT 字句；      

            打开光标  
            获取活动集中的行   FETCH语句检索活动集中的行，每次一行，每执行一次FECTCH，光标前进到活动集中的下一行。
            光标下移

            关闭光标

隐式游标：

begin

  for i in(select empno,ename from emp) loop
    dbms_output.put_line(i.empno);
  end loop;

end;

Begin
  update emp
  set   sal=1200
  where empno=1234;
  if  SQL%NOTFOUND  then insert into EMP(empno,sal) values(1234,1200);                
  end if;

End;

显式游标(分为普通游标和动态游标)：

普通游标

--1 定义获取数据的变量
Declare
 demo emp%ROWTYPE;
enameVar varchar2(20):='SMITH';
sqlVar varchar2(200):='select * from emp';
--2 定义游标
 mycursor sys_refcursor;
begin
if(enameVar  is not null) then
sqlVar:=sqlVar ||'where ename = '' '||enameVar ||''''; 
-- 在pl/sql中'' 表示一个转译'   select * from emp where ename = 'SMITH';
end if;
--3 打开游标
 open mycursor for sqlVar;
--4 抓取数据
     loop
       fetch mycursor into demo;
             dbms_output.put_line(demo.ename);
       exit when mycursor%NOTFOUND;
     end loop;
--5 关闭游标
   close mycursor;
end;

光标属性  每一个光标有四种属性

    %FOUND             查询语句(FETCH语句)返回记录

    %NOTFOUND     查询语句(FETCH语句)无返回记录，用于循环退出条件

    %ROWCOUNT    FETCH已获取的记录数

    %ISOPEN            光标已打开标记

  if   not  C%ISOPEN   then     open C;    end if;

Loop  FETCH C INTO v_ename,v_sal; EXIT WHEN  C%NOTFOUND; END LOOP;

隐式光标处理 

  隐式光标是指在处理SQL时,不需定义光标，所使用的SQL语句包括：INSERT ,UPDATE,DELETE子句。

隐式光标属性

SQL%FOUND

SQL%NOTFOUND     

SQL%ROWCOUNT

例外处理(EXCEPTION)

 在PL/SQL中,警告信息、出错信息、或返回信息统称为例外(Exception)。有两中类型的例外。
Oracle预定义的例外: 是由PL/SQL运行过程中，系统自动产生的信息。

           用户定义例外: 是用户根据需要，自己定义使用的例外，执行时由用户自己引起。

预定义的例外

CURSOR_ALREADY_OPEN                                          VALUE_ERROR

NO_DATA_FOUND                                                      INVALID_NUMBER

TOO_MANY_ROWS                                                     ZERO_DIVIDE

INVALID_CURSOR

用户自定义例外 用户定义的例外必须在DECLARE段中说明，在

Begin段中用RAISE引起，在EXCEPTION段中使用。

用户自定义例外的使用

--异常 
declare demo number;


begin
  select sal into demo from emp where emp.ename='X';
  exception
   when NO_DATA_FOUND then
     dbms_output.put_line('没有找到数据');
   when  others then
       dbms_output.put_line('其他异常');

end;

--自定义异常 
declare mysex exception;
  sex varchar2(3):='&请输入性别！';
begin
  
  if (sex not in ('男','女')) then
    raise mysex;
    end if;
   exception 
     when mysex then
       dbms_output.put_line('无效性别');
     when others then
        dbms_output.put_line('其他错误');
end;