010-PLSQL

匿名块

DECLARE
  l_Dummy_Nm  NUMBER;
  l_Dummy_Str VARCHAR2(360);
  l_Exp_Err EXCEPTION;
  l_Rt_Status  VARCHAR2(100);
  l_Rt_Message VARCHAR2(4000);
BEGIN
  --1# 判断
  IF 1 = 1 THEN
    NULL;
  ELSIF 1 < 2 THEN
    RETURN; --结束程序
  ELSE
    l_Rt_Status  := 'E';
    l_Rt_Message := '错误';
    RAISE l_Exp_Err; --抛出自定义异常
  END IF;

  --2# FOR循环
  FOR Rec IN 1 .. 3 LOOP
    CONTINUE;
    EXIT;
  END LOOP;

  --3# WHILE循环
  WHILE 1 < 2 LOOP
    EXIT WHEN 1 = 1;
  END LOOP;

  --4# GOTO跳转
  <<go_Flag>>
  GOTO Go_Flag;

  --#6 保存点
  SAVEPOINT a;
  ROLLBACK TO a; --回滚保存点A之后产生的所有DML操作，原有参数和变量值不会产生影响

  --7# 提交
  COMMIT;
EXCEPTION
  WHEN l_Exp_Err THEN
    ROLLBACK;
    Raise_Application_Error(-20002, '自定义异常:');
    Dbms_Output.Put_Line('自定义异常:' || l_Rt_Status || l_Rt_Message);
  WHEN OTHERS THEN
    ROLLBACK;
    Dbms_Output.Put_Line(Substrb(REPLACE(Dbms_Utility.Format_Error_Backtrace ||
                                         Dbms_Utility.Format_Error_Stack
                                        ,Chr(10)
                                        ,';')
                                ,1
                                ,4000));
END;

包

NOCOPY应用于函数或存储过程在传递数据量很大的参数时，可起到优化性能的作用。当参数是OUT或者IN OUT类型时：没有NOCOPY＝按值传递；加上NOCOPY＝按引用传递。
默认情况下，OUT模式的参数是以按值传递进行调用的，NOCOPY模式用于限定OUT模式在调用时按引用传递（它只是一个编译器暗示,不一定总是起作用）。传引用的方式会在赋值的时候立即生效 ，如果发生异常，也会返回你函数或存储过程中赋进入的值；如果是传值的方式，如果发生异常，等于函数或存储过程中赋进入的值都无效，返回的值还是原来参数传进去的值。

CREATE OR REPLACE PACKAGE Test_Pkg IS
  --==================================================
  -- Test_Fun
  -- DESC:方法声明
  --==================================================
  FUNCTION Test_Fun RETURN NUMBER;
  --==================================================
  -- Test_Proc
  -- DESC:存储过程声明
  --==================================================
  PROCEDURE Test_Proc(x_Rt_Status  OUT NOCOPY VARCHAR2
                     ,x_Rt_Message OUT NOCOPY VARCHAR2);
END Test_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Test_Pkg IS
  g_Pkg_Name CONSTANT VARCHAR2(100) := 'TEST_PKG';
  --==================================================
  -- Test_Fun
  -- DESC:方法
  --==================================================
  FUNCTION Test_Fun RETURN NUMBER IS
    l_Rt_Data NUMBER;
  BEGIN
    RETURN l_Rt_Data;
  END Test_Fun;
  --==================================================
  -- Test_Proc
  -- DESC:存储过程
  --==================================================
  PROCEDURE Test_Proc(x_Rt_Status  OUT NOCOPY VARCHAR2
                     ,x_Rt_Message OUT NOCOPY VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION; /*自治事务*/
    l_Rt_Status  VARCHAR2(10);
    l_Rt_Message VARCHAR2(4000);
  BEGIN
    l_Rt_Status  := Apps.Fnd_Api.g_Ret_Sts_Success;
    l_Rt_Message := NULL;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      x_Rt_Status  := l_Rt_Status;
      x_Rt_Message := x_Rt_Message || l_Rt_Message;
  END Test_Proc;
END Test_Pkg;

记录类型

TIPS：索引从1开始

• 记录表提供的方法几种：
• COUNT：返回记录表中的数量
• FIRST：返回记录表中最小索引值
• LAST：返回记录表中最大的索引值
• NEXT：返回记录表变量中的下一个索引
• DELETE：删除记录表中的记录

DECLARE
  l_Empno  Emp.Empno%TYPE; --表字段类型
  l_Emp_Tb Emp%ROWTYPE; --单行记录表类型
  TYPE Emp_Rec IS RECORD( --记录类型
     Empno    Emp.Empno%TYPE
    ,Ename    Emp.Ename%TYPE
    ,Job      Emp.Job%TYPE
    ,Mgr      Emp.Mgr%TYPE
    ,Hiredate Emp.Hiredate%TYPE
    ,Sal      Emp.Sal%TYPE
    ,Comm     Emp.Comm%TYPE
    ,Deptno   Emp.Deptno%TYPE);
  TYPE Emp_Rec_Tb IS TABLE OF Emp_Rec INDEX BY BINARY_INTEGER; --多行记录表类型
  l_Emp_Rec_Tb Emp_Rec_Tb; --初始化
  /*INDEX BY BINARY_INTEGER：下标数据类型，使得下标自增长，使用时不再需要初始化，也不需要每次extend一个空间
  手动扩展空间eg:
  DECLARE
   TYPE Emp_Rec_Tb2 IS TABLE OF Emp_Rec;
   l_Emp_Rec_Tb2 Emp_Rec_Tb2 := Emp_Rec_Tb2();
  BEGIN
   l_Emp_Rec_Tb2.Extend;
   l_Emp_Rec_Tb2(1).Empno := 2;
   l_Emp_Rec_Tb2.Extend;
   l_Emp_Rec_Tb2(2).Empno := 3;
   FOR i IN 1 .. l_Emp_Rec_Tb2.Count LOOP
     Dbms_Output.Put_Line(l_Emp_Rec_Tb2(i).Empno);
   END LOOP;
  END;*/
BEGIN
  --#1.1
  SELECT t.* INTO l_Emp_Tb FROM Emp t WHERE t.Empno = 7839;
  --#1.2
  INSERT INTO Emp VALUES l_Emp_Tb;
  --#1.3
  UPDATE Emp t SET t.Ename = l_Emp_Tb.Ename WHERE t.Empno = l_Emp_Tb.Empno;
  --#1.4
  UPDATE Emp t SET t.Ename = l_Emp_Tb.Ename WHERE Row = l_Emp_Tb;

  --#2
  SELECT t.* BULK COLLECT INTO l_Emp_Rec_Tb FROM Emp t; --BULK COLLECT可以将查询结果一次性地加载到collections
  FOR i IN 1 .. l_Emp_Rec_Tb.Count LOOP
    Dbms_Output.Put_Line('Empno=' || l_Emp_Rec_Tb(i).Empno || ',Ename=' || l_Emp_Rec_Tb(i)
                         .Ename);
  END LOOP;

  Dbms_Output.Put_Line('Count1:' || l_Emp_Rec_Tb.Count);
  Dbms_Output.Put_Line('First:' || l_Emp_Rec_Tb.First);
  Dbms_Output.Put_Line('Last:' || l_Emp_Rec_Tb.Last);
  Dbms_Output.Put_Line('Next:' || l_Emp_Rec_Tb.Next(1));
  l_Emp_Rec_Tb.Delete(l_Emp_Rec_Tb.Count); --只能从最后一个开始删除，不然会报错
  l_Emp_Rec_Tb.Delete; --删除全部记录
END;

游标

• Emp_Cursor%FOUND 布尔型：当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE
• Emp_Cursor%NOTFOUND 布尔型：与%FOUND相反
• Emp_Cursor%ISOPEN 布尔型：当游标已打开时返回 TRUE
• Emp_Cursor%ROWCOUNT 数字型：返回已从游标中读取的记录数

DECLARE
  CURSOR Emp_Cursor(p_Job VARCHAR2) IS
    SELECT t.Empno
          ,t.Ename
      FROM Emp t
     WHERE t.Job = p_Job FOR UPDATE NOWAIT;  --对查询出来的数据添加行级锁，在打开游标时生效
  l_Emp_Cursor Emp_Cursor%ROWTYPE;  --同上相同类型的记录变量
  l_Empno Emp.Empno%TYPE;
  l_Ename Emp.Ename%TYPE;
  TYPE Cursor_Type IS REF CURSOR;  --游标变量
  Temp_Cur Cursor_Type;
  l_Sql    VARCHAR2(4000);
BEGIN
  --1.1 显式打开关闭游标
  OPEN Emp_Cursor('SALESMAN'); --打开游标
  FETCH Emp_Cursor --提取游标
    INTO l_Emp_Cursor;
        
  WHILE Emp_Cursor%FOUND LOOP --是否成功提取游标
    Dbms_Output.Put_Line('1.1#' || l_Empno || ':' || l_Ename);
    FETCH Emp_Cursor
      INTO l_Empno
          ,l_Ename;
  END LOOP;
  CLOSE Emp_Cursor; --关闭游标

  --2.1 隐式打开关闭游标
  FOR Emp_Rec IN Emp_Cursor('SALESMAN') LOOP
    Dbms_Output.Put_Line('1.2#' || Emp_Rec.Empno || ':' || Emp_Rec.Ename);
  END LOOP;

  --2.2 隐式游标
  FOR Emp_Rec IN (SELECT t.Empno
                        ,t.Ename
                    FROM Emp t
                   WHERE t.Job = 'SALESMAN') LOOP
    Dbms_Output.Put_Line('2#' || Emp_Rec.Empno || ':' || Emp_Rec.Ename);
  END LOOP;

  --3 动态游标
  l_Sql := 'SELECT t.Ename FROM Emp t WHERE t.Job = ' || p_Job;
  OPEN Temp_Cur FOR l_Sql;
  LOOP
    FETCH Temp_Cur
      INTO l_Ename;
    Dbms_Output.Put_Line(l_Ename);
    EXIT WHEN Temp_Cur%NOTFOUND;
  END LOOP;
  CLOSE Temp_Cur;
  
  --使用游标的属性判断DML是否执行了操作
  UPDATE Emp SET sal=sal+100 WHERE empno=1234;
  IF SQL%FOUND THEN    
    DBMS_OUTPUT.PUT_LINE('更新成功！');   
  END IF；
END;

动态SQL

绑定变量

DECLARE
  l_Sql VARCHAR2(240);
BEGIN
  l_Sql := 'CREATE TABLE TABLENAME(ID NUMBER,NAME VARCHAR2(100))';
  EXECUTE IMMEDIATE l_Sql;
  l_Sql := 'INSERT INTO TABLENAME VALUES (:1,:2)';
  EXECUTE IMMEDIATE l_Sql
    USING 101, 'name'; --使用using绑定输入变量 
  COMMIT;
END;

查询返回单行值

DECLARE
  l_Sql  VARCHAR2(240);
  l_Id   NUMBER;
  l_Name VARCHAR2(100);
BEGIN
  l_Sql := 'SELECT ID,NAME FROM TABLENAME WHERE ID=:1';
  EXECUTE IMMEDIATE l_Sql
    INTO l_Id, l_Name
    USING '101';
  Dbms_Output.Put_Line('ID=' || l_Id || ',NAME=' || l_Name);
EXCEPTION
  WHEN OTHERS THEN
    Dbms_Output.Put_Line('Not data found!');
END;

查询返回多行值

DECLARE
  l_Sql VARCHAR2(240);
  TYPE Tablename_Rec IS RECORD(
     Id   Tablename.Id%TYPE
    ,NAME Tablename.Name%TYPE);
  TYPE Tablename_Rec_Tb IS TABLE OF Tablename_Rec INDEX BY BINARY_INTEGER;
  l_Tablename_Rec_Tb Tablename_Rec_Tb;
BEGIN
  l_Sql := 'SELECT ID,NAME FROM TABLENAME';
  EXECUTE IMMEDIATE l_Sql BULK COLLECT
    INTO l_Tablename_Rec_Tb;
  FOR i IN 1 .. l_Tablename_Rec_Tb.Count LOOP
    Dbms_Output.Put_Line('ID=' || l_Tablename_Rec_Tb(i).Id || ',NAME=' ||l_Tablename_Rec_Tb(i).Name);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    Dbms_Output.Put_Line('Not data found!' || SQLERRM);
END;

动态调用存储过程

DECLARE
  l_Sql  VARCHAR2(240);
  l_Name VARCHAR2(20);
BEGIN
  l_Sql := 'BEGIN TABLENAME_PKG(:1,:2); END;';
  EXECUTE IMMEDIATE l_Sql
    USING '101', OUT l_Name; --输出变量需显式说明 
  Dbms_Output.Put_Line('NAME=' || l_Name);
END;