PL/SQL

一、PL/SQL的语法

DECLARE
--声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。
BEGIN
-- 可以写sql和pl/sql
[EXCEPTION]
-- 异常处理,不必须
END;

---

二、数据类型

%TYPE

如果要定义一个类型与某个变量的数据类型或数据库表中某个列的数据类型一致（不知道该变量或列的数据类型）的变量，可以利用%TYPE来实现。

DECLARE
	V_DEPTNO EMP.DEPTNO%TYPE; --用EMP表的DEPTNO的类型作为V_DEPTNO的类型
BEGIN
END

记录类型

记录类型可以包含一个或多个域,每个域相当于记录类型变量的一个属性.在使用记录变量类型时,实际上是对记录类型变量的属性进行操作.每个域都可以是不同的数据类型,存放不同类型的数据

DECLARE
TYPE t_emp IS RECORD( -- 声明t_emp是一个记录类型
	empno NUMBER(4),
	ename CHAR(10),
	sal NUMBER(6,2));
	
v_emp t_emp; -- 使用t_emp记录类型声明变量
BEGIN
V_EMP.empno='2004'; -- 通过 变量名.字段 名去使用
END;

%ROWTYPE

如果要定义一个与数据库中某个表结构一致的记录类型的变量，可以使用 %ROWTYPE 来实现。

DECLARE
	V_EMP DEPT%ROWTYPE; -- 用DEPT表的字段及其类型来声明一种记录类型，用这个记录类型来声明变量V_EMP,所以V_EMP就代表DEPT中的一行，字段数据类型和表的字段数据类型一致
BEGIN
	V_EMP.DEPTNO:=30;
END

---

三、流程控制

IF语句

IF 条件1 THEN 语句1
[ELSIF 条件2 THEN 语句2]
......
[ELSE 语句]
END IF;

DECLARE
  V_JOB EMP.JOB%TYPE;
  v_SAL EMP.SAL%TYPE;
  v_ADDSAL V_SAL%TYPE;
BEGIN
  SELECT JOB,SAL INTO V_JOB,V_SAL FROM EMP WHERE EMPNO=7369;
  
  IF (V_JOB = 'CLERK') THEN
    V_ADDSAL := V_SAL * 2; --满足V_JOB = 'CLERK'则执行
  ELSE IF (V_JOB = 'SMITH') THEN
    V_ADDSAL := V_SAL * 3; --满足V_JOB = 'SMITH'则执行
  ELSE
    V_ADDSAL := V_SAL*4; --都不满足上面条件则执行
  END IF;
  
  UPDATE EMP SET SAL = V_ADDSAL WHERE EMPNO=7369;
  COMMIT;
END;

简单Loop循环

LOOP
语句
EXIT [WHEN 条件];
END LOOP;

DECLARE
  V_NUM NUMBER(3):=1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('V_NUM='||V_NUM);--循环语句
    V_NUM := V_NUM+1;-- 循环迭代语句
    EXIT WHEN V_NUM>10; -- 当V_NUM大于10时退出（EXIT），循环退出条件
  END LOOP;
END;

WHILE循环

WHILE 条件 LOOP
语句;
END LOOP;

DECLARE
  V_NUM NUMBER(3):=15;
BEGIN
  WHILE V_NUM>11 LOOP --V_NUM大于11时循环，小于则不能进行循环，循环进入条件
    DBMS_OUTPUT.PUT_LINE('V_NUM='||V_NUM);
    V_NUM := V_NUM-1;
  END LOOP;
END;

---

四、PL/SQL

SELECT语句

只能返回一条记录，配合 INTO 赋值给变量，返回零条或多条记录会报错,查询列表一定要和变量匹配

DECLARE
  V_EMPNO EMP.EMPNO%TYPE;
BEGIN
  SELECT EMPNO INTO V_EMPNO FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.put_line('v_EMPNO='||V_EMPNO);
END;

DML语句

PL/SQL中执行 DML 语句,事务控制语句和在oracle中执行没有任何区别

---

五、游标

在执行增删改查语句的时候,ORACLE都会开辟一块内存空间,用来暂时存放受到SQL语句影响的数据，这块内存空间就被称为游标区域.我们可以借助游标来分析这些受到影晌的数据

隐式游标

增删改查语句都会有隐式游标，也就是说,我们可以通过隐式游标来分析受到增删改查语句影响的数据。语法固定为SQL%属性

SQL%ROWCOUNT

返回受影响的数据条数

DECLARE
  V_EMP EMP.EMPNO%TYPE;
  V_COUNT NUMBER(3);
BEGIN
  SELECT EMPNO INTO V_EMP FROM EMP WHERE EMPNO = 7369;
  V_COUNT := SQL%ROWCOUNT; -- 返回受影响的数据条数，也就是1
  DBMS_OUTPUT.PUT_LINE('V_COUNT='||V_COUNT);
END;

显示游标

专门用于 PL/SQL 查询多行数据

DECLARE
  -- 声明一个显式游标，就是和一个有效的selcet语句绑定
  CURSOR CUR_EMP IS SELECT * FROM EMP;
  V_EMP EMP%ROWTYPE;
BEGIN
  -- 打开游标其实就是执行游标绑定的sql语句，并把受影响的数据放到游标区域中
  OPEN CUR_EMP;
  
  LOOP
  
       FETCH CUR_EMP INTO V_EMP;-- 取出一条数据放到记录类型的变量中
  
       EXIT WHEN CUR_EMP%NOTFOUND;--当游标中没有数据了退出
       
       DBMS_OUTPUT.put_line(V_EMP.EMPNO||V_EMP.ENAME||V_EMP.JOB);
  
  END LOOP;
  
  CLOSE CUR_EMP;--其实就是清空游标区域中的数据
END;

对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。

• %ISOPEN
  布尔型。如果游标已经打开，返回TRUE,否则为FALSE。
• %FOUND
  布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;
• %NOTFOUND
  布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;
• %ROWCOUNT
  数值型，返回到目前为止从游标缓冲区检索的元组数。
• %BULK_ROWCOUNT（i）
  数值型，用于取得FORALL语句执行批绑定操作时第i个元素所影响的行数。

bug:如果最后一行数据重复执行两遍，就是没在使用变量之前检查游标状态。

FETCH不到数据时，ORACLE不会报错，也不会改变要填充的变量的值，因此在使用变量之前必须检验游标的状态

---

六、异常处理

• 预定义的Oracle异常

当Oracle错误产生时，与错误对应的预定义异常被自动抛出，通过捕获该异常可以对错误进行处理。

• 非预定义异常

有一些Oracle错误没有预定义异常与其关联，也就是有错误编号和错误信息，但没有为这类错误定义好异常名称，需要在语句块的声明部分声明一个异常名称，然后通过编译指示PRAGMA EXCEPTION_INIT将该异常名称与一个Oracle错误相关联。此后，当执行过程出现该错误时将自动抛出该异常。

异常处理语法

DECLARE
	V_EMP_REMAINING EXCEPTION; --自己定义一种异常名称
	PRAGMA EXCEPTION_INIT(V_EMP_REMAINING,-02292); --与错误编号绑定
BEGIN
……
EXCEPTION
WHEN exception1 THEN --捕获异常
sequence_of_statements1;
WHEN exception3 THEN
sequence_of_statements2;
……
WHEN OTHERS THEN --可以捕获上面没有捕获的所有异常
sequence_of_statementsn;
END;