【达梦数据库】PL/SQL 学习记录

米米酱

已于 2024-08-28 16:44:11 修改

阅读量1.1k

点赞数 10

分类专栏：达梦数据库文章标签：数据库 sql 学习

于 2024-08-28 16:14:12 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_50694517/article/details/141603818

版权

达梦数据库专栏收录该内容

13 篇文章

订阅专栏

1 前言

1.1 概念

PL/SQL（Procedural Language/SQL）：过程化SQL语言，把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

PL/SQL 块由四个基本部分组成：块头、声明单元（可选）、执行单元（必需）、异常处理单元。

块是 PL/SQL中最基本的单元。所有的PL/SQL程序都组合成块。这些块也可以互相嵌套。通常情况下，PL/SQL块把代表单个逻辑任务的语句组合在一起。采用这种结构，程序的逻辑就很容易理解和维护。

‌‌自治事务‌是一个独立的事务，可以从另一个称为主事务的事务中调用。它允许在离开调用事务的环境的情况下执行SQL操作、提交或撤销这些操作，然后返回到调用事务的环境继续执行。自治事务的主要特点是它可以在不中断主事务的情况下独立地提交或回滚其操作，这对于需要独立处理某些数据库操作的情况非常有用。

自治事务与常规事务的区别‌
独立性‌：自治事务具有高度的独立性，可以在不依赖于主事务的情况下执行。常规事务则紧密依赖于主事务，其状态和行为受主事务的控制。‌
提交和回滚‌：自治事务的提交或回滚不会影响主事务的状态。常规事务的提交或回滚可能会影响整个事务的完成。‌
应用场景‌：自治事务适用于需要独立处理某些数据库操作的情况，如审计跟踪、数据验证等。常规事务则适用于需要一系列操作共同完成的任务。

1.2 种类

PL/SQL 块种类

命名：创建子程序时，将使用命名的 PL/SQL 块。这些子程序，包括过程、函数和包，都可以存储在数据库中，并随后通过它们的名称来引用。
匿名：类似过程和函数的子程序也可以在匿名 PL/SQL 块内定义。只要该块被执行，这些子程序就存在，但它们不能在块外被引用。匿名 PL/SQL 块，它们没有名字，因此它们不能被存储在数据库中，也不能在以后被调用。

##结构如下
DECLARE
      --声明语句
BEGIN
     --可执行语句
EXCEPTION
     --异常处理语句
END;

1.3 结构

声明部分
声明部分是 PL/SQL 的第一部分。它包含 PL/SQL 标识符，如变量、常量、游标等的定义。

DECLARE
  v_first_name varchar2(32);
  v_last_name  varchar2(32);

解读：开始于 DECLARE 关键字并包含两个变量声明。变量名 v_first_name 和 v_last_name，后面跟着它们的数据类型和大小。请注意，每个声明都以分号结束。

可执行部分
可执行部分是PL/SQL块的下一个部分。它包含可执行语句，可让你操作已在声明部分声明了的变量。

BEGIN
   SELECT first_name,last_name 
     INTO v_first_name,v_last_name
     FROM student
    WHERE student_id = 123;
   
 print('Student name: '||v_first_name||' '||v_last_name);
END;

解读：

从 student 表中选择 first_name 和 last_name 列，当 student_id 等于 123 时，将这些值分别赋值给变量 v_first_name 和 v_last_name；
将已赋值给 v_first_name 和 v_last_name 的值连接起来，使用 || 运算符进行字符串拼接，并打印出格式化的字符串信息。输出格式为：student name: 姓名姓氏。
用于从数据库中获取特定学生的信息，并以特定格式输出。执行此过程后，将得到学生的名字和姓氏，格式为“学生姓名: 姓名姓氏”。

异常处理部分
在执行一个 PL/SQL 块时，可能会出现两种类型的错误：编译错误（或语法错误）和运行时错误。
编译错误是保留字存在拼写错误或在语句结束处缺少分号的时候由 PL/SQL 编译器检测到的，下面这个示例包含一个语法错误：PRINT 语句末要以分号作为结果。

BEGIN
 print('This is a test')
END;

运行时错误在程序运行时发生，并且是不能由 PL/SQL 编译器检测到的。这些类型的错误由 PL/SQL 块的异常处理部分进行检测或处理。它包含块中发生运行错误时被执行的一系列语句,一旦发生运行时错误，控制就被转到块的异常处理部分。然后该错误被计算出来，并且引发或执行一个特定的异常。

##定义变量
DECLARE
  v_first_name varchar2(32);
  v_last_name  varchar2(32);
  v_errmesg    varchar2(256);
  v_student_id number:= 234;
##执行查询
BEGIN
##查询学生姓名
   SELECT first_name,last_name 
     INTO v_first_name,v_last_name
     FROM student_c
    WHERE student_id = v_student_id;
##输出学生姓名
    print ('Student name: '||v_first_name||' '||v_last_name);
##错误处理
EXCEPTION
##无数据情况
  WHEN NO_DATA_FOUND 
  THEN
    print('There is no student with student id '||v_student_id);
##其他错误情况
  WHEN OTHERS
  THEN
    v_errmesg := SUBSTR(SQLERRM, 1, 200);
    print('error is : '||v_errmesg);
END;

解读：

SQL 查询语句的目的是从 student_c 表中查找并获取 student_id 为 234 的学生的名字（first_name）和姓氏（last_name）。
定义变量：
v_first_name：用于存储学生的姓氏；
v_last_name：用于存储学生的名；
v_student_id：用于存储查询中的学生ID，初始值为234。
查询数据：使用 SELECT 语句从 student_c 表中选取 first_name 和 last_name 字段，并将结果赋值给 v_first_name 和 v_last_name 变量。
输出结果：使用 print 语句将学生名字输出，格式为 student name: 名字姓氏。
错误处理：
无数据时：如果表中没有找到与 v_student_id 相匹配的学生，将捕获 no_data_found 异常，并输出错误信息。
其他错误：如果在执行查询过程中遇到任何其他异常，将捕获该异常。异常信息将被存储在 v_errmesg 变量中，然后通过 print 语句输出错误信息。

1.4 自治事务

自治事务可以独立commit，不对外层事务产生影响，同样外层事务的 rollback 也对自治事务没有影响。通常可以考虑将自治事务定义成一个过程，在外层的事务中调用。

create or replace procedure insert_log(msg varchar2(2000))  
##存储过程体
as  
      pragma autonomous_transaction;  
begin  
 insert into debug_log values(msg,now);
       commit;
end;

解读：

这部分定义了存储过程的名称为 insert_log，它接受一个参数 msg（类型为 varchar2，长度为2000），并为消息注入日志。
存储过程的语法与结构：
CREATE OR REPLACE：表示如果已经存在同名的存储过程，将进行替换，否则创建。
PROCEDURE：说明这是一个存储过程。
insert_log：存储过程的名称。
(msg varchar2(2000))：定义了存储过程的输入参数 msg，其类型为 varchar2，最大长度为2000。
pragma autonomous_transaction;：这一行表示这个存储过程将使用自治事务。这意味着在存储过程执行时，其内部的更新操作不会影响到外部的事务，反之亦然，保证了独立的事务操作。
begin 和 end：这两个关键字包裹了存储过程的主体操作。
insert into debug_log values(msg,now);：这行代码表示将输入参数 msg 的值和当前时间（now，表示系统当前时间点）插入到名为 debug_log 的表中。这里的 debug_log 表应当已经在数据库中存在，且结构允许插入 varchar2 类型的值和 DATE 类型的值。
commit;：表示对 debug_log 表的更改进行提交，使更改永久保存在数据库中。
这个存储过程 insert_log 的主要功能是接收一个消息字符串作为输入，将这个消息及其生成时间戳插入到数据库的 debug_log 表中，并通过 pragma autonomous_transaction 确保事务的独立性，使得这个存储过程的操作不会影响到外部的事务。

##自治事务对比
CREATE TABLE t (test_value VARCHAR2(25)); 

CREATE OR REPLACE PROCEDURE child_block 
IS 
BEGIN  
INSERT INTO t  (test_value)  VALUES  ('Child block insert');  
COMMIT;
END child_block;
/ 


CREATE OR REPLACE PROCEDURE parent_block 
IS 
BEGIN 
 INSERT INTO t  (test_value)  
   VALUES  ('Parent block insert');    
   child_block;    
   ROLLBACK;
END parent_block;
/ 
-- run the parent procedure
call parent_block ;
-- check the results
 SELECT * FROM t;

--------------------------

CREATE OR REPLACE PROCEDURE child_block 
IS
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN  

INSERT INTO t  (test_value)  VALUES  ('Child block insert');  
COMMIT;
END child_block;
/ 

CREATE OR REPLACE PROCEDURE parent_block 
IS 
BEGIN  
INSERT INTO t  (test_value)  VALUES  ('Parent block insert');    
 child_block;   
 ROLLBACK;
 END;
 parent_block;
 /
 -- empty the test table
 TRUNCATE TABLE t; 
 
 -- run the parent procedure
 call parent_block; 
 -- check the results
 SELECT * FROM t;

2 语言基础

2.1 PL/SQL变量

描述：变量可用于保存临时值。

语法：<变量名> <数据类型> [可选的默认赋值]

变量也可以视为标识符。变量必须以一个字母开头，DAMENG 中可以 128 个字符长（oracle中最多可以有 30 个字符长)，PL/SQL 变量是不区分大小的。

2.2 挂靠的数据类型

描述：分配给一个变量的数据类型可以基于某个数据库对象，这种分配称为挂靠的（anchor）声明，因为变量的数据类型取决于基础对象。尽量使用挂靠的数据类型是明智的，以便当基对象的数据类型发生更改时，不需要更新PL/SQL代码。

语法：<变量名> <数据属性> %类型

这里的类型是对数据库表中列的直接引用。

DECLARE
v_first_name student.first_name%TYPE= 'John';  --初始化变量
v_last_name  constant student.last_name%TYPE:='Kennedy'; --常量在过程中不能被更改
BEGIN
   print ('Student name: '||v_first_name||' '||v_last_name);
END;

2.3 运算符（分隔符）：表达式中的分隔符

算术运算符（**、*、/、+、-）
比较运算符（=、<>、!=、<、>、<=、>=、LIKE、IN、BETWEEN、IS NULL、IS NOT NULL、NOT IN）
逻辑运算符（AND、OR、NOT）
字符串运算符（||、LIKE）
运算符优先级
**、NOT
+、-（算术正号和负号）、 *、/、+、-、 ||、 =、 <>、!=、 <=、>=、 <、>、 LIKE、 BETWEEN、 IN、 IS NULL
AND 逻辑合取
OR   逻辑包含

2.4 块作用域、标签和嵌套块

在使用PL/SQL中的变量时，必须理解它们的作用域。这将使你能够了解如何以及何时可以使用变量。这还将帮助你调试你编写的程序、PL/SQL块的开始部分包含声明部分——就是声明该块将使用的变量的部分。

变量的作用域
在声明部分中定义的结构的作用域或存在性，对于该块时局部的。块也提供声明和引发异常的作用域。
变量的作用域时程序中可以访问该变量的部分，或可见到该变量的部分。它通常从声明那一刻一直延伸到声明此变量的块的结尾。变量的可见范围时程序中可访问该变量的那一部分。

BEGIN   --外层块
    BEGIN   --内层块
       ...;
    END;    --内层块的结尾
END;     --外层块的结尾

标签和嵌套块
可以在块中添加标签以提高可读性，并且可以用它限定在嵌套中存在同名元素的名称。块的名称必须在第一行可执行代码（要么是 BEGIN 要么是 DECLARE）的前面。

DECLARE
       v_first_name student.first_name%TYPE= 'John';   
       v_last_name  constant student.last_name%TYPE:='Kennedy';      
BEGIN
    <<first_block>>        --标签
   declare
   v_private1_first_name VARCHAR2(12):='Johnny';
   begin
      print ('First Student name: '||v_private1_first_name||' '||v_last_name);
   end first_block;
   
   <<last_block>>        --标签
   declare
   v_private2_first_name VARCHAR2(12):='Evan';
   begin
       print ('Last student name: '||v_private2_first_name||' '||v_last_name);
   end last_block;
   
   print ('Public student name: '||v_first_name||' '||v_last_name);
END;

3 游标

3.1 概念

概念：是SQL的一个内存工作区，由系统或用户以变量的形式定义。
作用：用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
种类：隐式游标和显式游标。
在前面所述的程序中有用到的 SELECT…INTO… 查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和 DML 操作，系统都会使用一个隐式游标。
如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的 SELECT 语句，游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

3.2 隐式游标

DML操作和单行SELECT语句会使用隐式游标，它们是：

插入操作：INSERT
更新操作：UPDATE
删除操作：DELETE
单行查询操作：SELECT … INTO …

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字 SQL 来访问，但要注意，通过 SQL 游标名总是只能访问前一个 DML 操作或单行 SELECT 操作的游标属性。所以通常在刚刚执行完操作之后，立即使用 SQL 游标名来访问属性。游标的属性有四种，如下所示。

/*隐式游标的属性返回值类型意义 */
SQL%ROWCOUNT – 整型代表 DML 语句成功执行的数据行数
SQL%FOUND --布尔型值为 TRUE 代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND --布尔型与 SQL%FOUND 属性返回值相反
SQL%ISOPEN —布尔型 DML 执行过程中为真，结束后为假

BEGIN  
  UPDATE SYSDBA.STUDENT SET phone=phone+100 
   WHERE student_id=1234;   
     IF SQL%FOUND THEN    
         PRINT('成功修改学生手机号码！');   
         COMMIT;     
     END IF;  
     IF SQL%ROWCOUNT = 0 THEN
       PRINT('执行成功数为0！');   
       IF SQL%NOTFOUND THEN
          PRINT('修改学生手机号码失败！');   
       END IF;
     END IF;
END;

解释：

这段代码描述了在 sysdba.student 表中，将 phone 字段的值增加 100 的操作，条件是 student_id 等于 1234。
update sysdba.student set phone=phone+100
where student_id=1234;
sql%found 是一个 SQL 语句执行结果的布尔变量，如果执行了 update 语句，那么 sql%found 为 true，这意味着有数据被更新。如果修改了至少一行数据，sql%found 将为 true，并在满足条件时执行以下的代码块，打印出 ‘成功修改学生手机号码！’，再执行 commit 操作，将更改永久保存到数据库中。
if sql%found then
print(‘成功修改学生手机号码！’);
commit;
end if;
sql%rowcount 是一个 SQL 语句执行结果的整数变量，表示执行了 update 语句后有多少行受影响。如果 sql%rowcount 等于 0，意味着没有任何行被修改，打印出 ‘执行成功数为0！’；如果 sql%found 为 false（即执行没有返回任何结果，可能是 SQL 语句本身没有执行，或者是没有数据匹配条件），则打印 ‘修改学生手机号码失败！’。
if sql%rowcount = 0 then
print(‘执行成功数为0！’);
if sql%notfound then
print(‘修改学生手机号码失败！’);
end if;
end if;
这段代码包含了用于更新数据库中学生电话信息的 update 语句，以及对执行结果的判断和相应操作。通过使用 if 语句，确保了只有在更新成功的情况下才提交更改，并且在没有结果的情况下提供了明确的错误信息。

3.3 显示游标

声明游标
在 DECLEAR 部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型…])] IS SELECT语句;

参数是可选部分，所定义的参数可以出现在 SELECT 语句的 WHERE 子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT 语句是对表或视图的查询语句，甚至也可以是联合查询。可以带 WHERE 条件、ORDER BY 或 GROUP BY 等子句，但不能使用 INTO 子句。在 SELECT 语句中可以使用在定义游标之前定义的变量。

打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名[(实际参数1[，实际参数2…])];

打开游标时，SELECT 语句的查询结果就被传送到了游标工作区。

提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[，变量名2…];
或
FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

关闭游标
CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。以下是使用显式游标的一个简单示例。

DECLARE
v_first_name student.first_name%type;
v_last_name  student.last_name%type;
CURSOR c_student_name IS  SELECT first_name,last_name FROM student WHERE student_id=123;   
BEGIN
    open c_student_name;
    fetch c_student_name into v_first_name,v_last_name;
    print('Student name: '||v_first_name||' '||v_last_name);
    close c_student_name;
END;

解读：

声明变量
v_first_name student.first_name%type;
v_last_name student.last_name%type;
这里声明了两个变量 v_first_name 和 v_last_name，它们的类型分别与表 student 的 first_name 和 last_name 字段的类型相同。使用 %type 后缀的变量类型表示其能够接收与表字段相同类型的数据。
声明游标
cursor c_student_name is select first_name,last_name from student where student_id=123;
在这里声明了一个名为 c_student_name 的游标，用于执行一个查询操作：从 student 表中选择 first_name 和 last_name 字段，条件是 student_id 等于123。游标允许在执行查询后按需获取查询结果中的行。
打开游标
open c_student_name;
打开之前声明的游标 c_student_name，使得可以开始从数据库中获取数据。
提取数据
fetch c_student_name into v_first_name,v_last_name;
从游标 c_student_name 中提取一行数据，并将提取到的第一行数据的 first_name 字段值赋给变量 v_first_name，last_name 字段值赋给变量 v_last_name。fetch语句与游标一起使用，用于从游标中获取数据。
打印结果
print('student name: ‘||v_first_name||’ '||v_last_name);
将学生的名字和姓氏合并，并打印出来。使用 || 运算符进行字符串拼接。
关闭游标
close c_student_name;
游标操作完毕后，通过关闭游标来释放与游标相关的资源，比如数据库连接等。
通过这段代码，程序将执行查询 student 表中 student_id 为123的学生的 first_name 和 last_name 字段，并将结果打印出来。

3.4 动态 SQL 语句

对于查询结果为一行的 SELECT 语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行。
语法：
execute immediate 查询语句字符串 into 变量1[,变量2…];

DECLARE
v_first_name student.first_name%type;
v_last_name  student.last_name%type;
v_sqlVARCHAR2(2000);
BEGIN
  v_sql := 'SELECT first_name,last_name FROM student WHERE student_id=123';
  EXECUTE IMMEDIATE v_sql INTO v_first_name,v_last_name;
  PRINT('Student name: '||v_first_name||' '||v_last_name);
END;

解读：

定义变量：
v_first_name student.first_name%type: 定义了一个变量 v_first_name，其类型与 student.first_name相同，用于存储查询结果中的first_name字段。
v_last_name student.last_name%type: 定义了一个变量 v_last_name，其类型与 student.last_name相同，用于存储查询结果中的 last_name 字段。
v_sqlvarchar2(2000): 定义了一个能存储最多 2000 个字符的变量 v_sql，用于存储 SQL 查询语句。
构建 SQL 查询：创建一条 SQL 查询，目的是从 student 表中选取 first_name 和 last_name，其 student_id 等于 123。
v_sql := ‘select first_name,last_name from student where student_id=123’;
执行 SQL 查询：使用 execute immediate 语句执行构建的 SQL 查询。这里会将查询结果的每一列存储到对应的变量中。
execute immediate v_sql into v_first_name,v_last_name;
打印结果：最后，使用 print 函数输出学生的名字，格式为 student name: first_name last_name。
print('student name: ‘||v_first_name||’ '||v_last_name);
定义变量：v_first_name 和 v_last_name，用于存储查询结果。
执行 SQL：构建并执行查询 v_sql，该查询筛选出 student_id为123的first_name和last_name。
输出结果：将查询结果使用print函数输出。

3.5 动态游标

在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。定义游标类型的语句如下： TYPE 游标类型名 REF CURSOR; 声明游标变量的语句如下：游标变量名游标类型名; 在可执行部分可以如下形式打开一个动态游标： OPEN 游标变量名 FOR 查询语句字符串;

DECLARE
TYPE type_cursor_type IS REF CURSOR;  
c_student_name type_cursor_type;
v_student student%rowtype; 
BEGIN
    open c_student_name for SELECT * FROM student ;  
    loop
        fetch c_student_name into v_student;
        EXIT WHEN c_student_name%NOTFOUND;  
        print('Student name: '||v_student.first_name||' '||v_student.last_name);
    end loop;
    close c_student_name;
    print('----------first loop end----------');
    open c_student_name for   SELECT * FROM student_b  ;
    loop
        fetch c_student_name into v_student;
        EXIT WHEN c_student_name%NOTFOUND;  
        print('Student name: '||v_student.first_name||' '||v_student.last_name);
    end loop;
    close c_student_name;
END;

解释：

declare 子句定义了变量和游标的类型
type_cursor_type 定义了一个引用游标（ref cursor）类型，用来存储 c_student_name 游标的地址；
c_student_name type_cursor_type; 定义了一个游标变量 c_student_name，其类型为已经定义的 type_cursor_type；
v_student student%rowtype; 定义了一个变量 v_student，其类型和表 student 的行类型相同。
开始 c_student_name游标，执行SQL语句 select * from student。这将会从 student 表中获取所有行。
open c_student_name for select * from student;
when c_student_name%notfound; 创建了一个循环，当 c_student_name 游标中已找不到更多行时（即c_student_name%notfound为true），循环结束。
fetch c_student_name into v_student; 在循环体内，fetch 语句从游标 c_student_name 中读取一行数据到 v_student 变量中。
print(‘student name: ‘||v_student.first_name||’ ‘||v_student.last_name); 打印学生的姓名信息，这里使用了字符串连接来显示 v_student 的 first_name 和 last_name 字段。
close c_student_name; 关闭c_student_name游标，释放资源。
print(’----------first loop end----------’); 打印一条分割线，表示第一个循环结束。
open c_student_name for select * from student_b;重新打开 c_student_name 游标，这次执行的 SQL 语句是 select * from student_b，从 student_b 表中获取所有行。
两个 loop 和 exit when 逻辑结构与前面的 3 的代码块类似，循环从 student_b 表中获取所有学生信息并打印他们的名字。
这段代码展示了如何在 PL/SQL 中使用游标来迭代查询结果，特别是从两个不同的表（student 和 student_b）中获取数据，并在每次循环中打印学生的名字。同时，通过 open 和 close 语句管理游标，确保资源的有效释放。

3.6 游标的几种使用方法

3.6.1 使用特殊的 FOR 循环打印游标中的数据

##声明变量
DECLARE
v_first_name VARCHAR2(32);
v_last_name VARCHAR2(32); 
##定义游标
CURSOR curs is SELECT * FROM student;
##声明并执行循环逻辑
BEGIN
   FOR I IN curs LOOP
          print(i.first_name||' '||i.last_name);
   END LOOP;            
END;

解读：

declare 关键字用于声明局部变量，v_first_name 和 v_last_name 分别用于存储第一名称和姓氏，类型为 varchar2，长度为32。
declare
v_first_name varchar2(32);
v_last_name varchar2(32);
cursor 关键字用于定义游标，curs 是游标的名称，is 关键字后跟 SQL 查询语句 select * from student，该查询从 student 表中选取所有列。
cursor curs is select * from student;
begin 和 end 关键字用于定义一个代码块，这里面包含了循环逻辑；
for i in curs loop 是一个循环结构，curs 是定义的游标，每次循环都会从游标中获取一条记录；
print(i.first_name||’ ‘||i.last_name); 在循环体内，使用 print 语句输出当前记录的 first_name 和 last_name 字段，用 ’ ’ 连接起来。
begin
for i in curs loop
print(i.first_name||’ '||i.last_name);
end loop;
end;
声明了两个用于存储查询结果字段的变量 v_first_name 和 v_last_name；
定义了一个游标 curs，该游标将执行 select * from student 查询语句的结果存储在内存中；
通过一个 for 循环，逐条读取游标中的记录，输出每个记录的 first_name 和 last_name 字段的值；

3.6.2 带参数游标使用方法

##声明变量
DECLARE  
v_first_name VARCHAR2(32);
v_last_name VARCHAR2(32); 
##定义游标
CURSOR  c_cursor(p_id NUMBER) IS  
     SELECT  first_name,last_name  FROM student   
        WHERE  student_id = p_id;   
##声明并执行循环逻辑
BEGIN  
    OPEN c_cursor(123);   
    LOOP   
      FETCH c_cursor INTO v_first_name,v_last_name;   
      EXIT WHEN c_cursor%NOTFOUND;   
      PRINT('Student name : '||v_first_name||','||v_last_name);   
    END LOOP; 
    CLOSE  c_cursor ; 
END;

解读：

目的是从名为 student 的表中根据 student_id 找到对应的 first_name 和 last_name 并打印。
v_first_name varchar2(32);宣告一个变量 v_first_name，用于存储 first_name 字段的值。这里使用了 varchar2 类型，长度为 32 字符，用于存储字符串数据。
v_last_name varchar2(32);宣告一个变量 v_last_name，用于存储 last_name 字段的值，同样使用 varchar2 类型，长度为 32 字符。
cursor c_cursor(p_id number) is 定义一个游标 c_cursor，该游标是基于 select first_name,last_name from student where student_id = p_id 这一查询语句。p_id 是游标内参数，允许在执行游标时传入具体的学生 ID。
select first_name,last_name from student where student_id = p_id：查询语句用于从 student 表中根据 student_id 查找对应的 first_name 和 last_name。
游标操作：
open c_cursor(123);打开游标 c_cursor，并传入参数 123，这里假设要查询的学生 ID 是 123；
loop：开始循环，用于遍历游标结果集；
fetch c_cursor into v_first_name,v_last_name;从游标中获取下一行数据，并将数据分别存储到 v_first_name 和 v_last_name 变量中；
exit when c_cursor%notfound;检查是否已到达游标结果集的末尾，若已到达末尾（%notfound 为真），则退出循环；
print(‘student name : ‘||v_first_name||’,’||v_last_name);打印学生的名字，格式为 first_name 后面跟着逗号和空格再跟着 last_name；
end loop;结束循环；
close c_cursor ;关闭游标 c_cursor。
游标从数据库表 student 中根据给定的学生 ID 查询并打印对应的学生的名字（first_name 和 last_name）。它利用了变量来存储查询结果，并通过循环逐行处理查询结果，最后关闭游标以释放资源。

3.6.3 通过变量传递参数给游标

##声明变量
DECLARE  
v_student_id NUMBER(5);   
v_first_name VARCHAR2(32);
v_last_name VARCHAR2(32); 
##定义游标
CURSOR c_cursor IS  
  SELECT first_name,last_name FROM student   
     WHERE student_id = v_student_id ;   
##声明并执行循环逻辑
BEGIN  
   v_student_id:=123;   
   OPEN c_cursor;   
   LOOP   
      FETCH c_cursor INTO v_first_name,v_last_name;   
      EXIT WHEN c_cursor%NOTFOUND;   
      PRINT('Student name : '||v_first_name||','||v_last_name); 
   END LOOP; 
   CLOSE  c_cursor; 
END;

解读：

v_student_id number(5);：声明了一个整型变量 v_student_id，其类型为 number(5)，用于存储学生 ID。number(5) 表示这是一个 5 位数的整型数；
:v_first_name varchar2(32); 和 v_last_name varchar2(32);：声明了两个字符串变量 v_first_name 和 v_last_name，其类型为 varchar2(32)，用于存储学生的名字和姓氏。varchar2(32) 表示这两个变量的长度限制为 32 个字符。
cursor c_cursor is select first_name,last_name from student：定义了一个游标 c_cursor。这个游标用于执行SQL查询：从 student 表中选择 first_name（名字）和 last_name（姓氏）这两列的数据。
begin v_student_id:=123;：初始化 v_student_id 的值为123。这个值将用来在稍后查询中筛选学生记录。
open c_cursor;：打开之前定义的游标 c_cursor，这意味着数据开始从数据库检索。
loop fetch c_cursor into v_first_name,v_last_name;：使用循环遍历游标 c_cursor 的结果集，每次循环都会通过 fetch 进行记录的获取，并将获取结果存储到 v_first_name 和 v_last_name 变量中；
exit when c_cursor%notfound;：在循环中，当不再有更多记录可取时（即 c_cursor 的结果集结束），使用 exit 语句退出循环。
print(‘student name : ‘||v_first_name||’,’||v_last_name);：在每次循环中，输出学生的名字和姓氏；
close c_cursor;：在循环结束后，关闭游标 c_cursor，表示完成从数据库检索数据的操作。
用户可以获取 student_id = 123 学生的名字和姓氏，并打印出来。

4 集合

在 PL/SQL 中有两种类型的 PL/SQL 表，关联数组（也可以称为索引表）和嵌套表。它们具有相同的结构，他们的行的访问方式也相同，也就是通过下标符号访问。这两种类型之间主要区别在于，嵌套表可以存储在数据库的列中，而关联数组不能。

4.1 关联数组

索引表（key-value形式），索引值可以是无序的，可以数字或字符串为下标来查找集合中的元素，元素数量不限。

TYPE type_name IS TABLE OF element_type [not null] INDEX BY  index_type;
v_type_name     type_name ;

type_name：数组的名字
element_type：指定的数组的数据类型
index_type：指定的数组的索引的数据类型，它可以是字符串类型（例如：VARCHAR2）或者 PLS_INTEGER、BINARY_INTEGER。

##声明变量
declare
  TYPE type_student IS TABLE of student%rowtype  index by BINARY_INTEGER;
  v_student type_student;
##定义游标类型
  TYPE type_cursor_type IS REF CURSOR;  
  c_student  type_cursor_type;
  v_sql  VARCHAR2(2000):='select * from student';
##声明并执行循环逻辑
begin
   open c_student for v_sql; 
   loop
        fetch c_student bulk collect into v_student;
        EXIT WHEN v_student.count=0;  
        for i in v_student.first ..v_student.last  loop
            print('Student name: '||v_student(i).first_name||' '||v_student(i).last_name);
        end loop;
   end loop;
end;

解读：

type_student type_student 定义了一个动态数组类型（表类型），它存储的是 student%rowtype 类型的对象。具体来说，student%rowtype 是指向 student 表的行类型，这意味着 type_student 可以存储 student 表中的单行数据；
v_student 是一个 type_student 类型的变量，用于存储查询结果。
type type_cursor_type is ref cursor; 定义了一个游标类型 type_cursor_type，这将用于执行 SQL 查询；
c_student 是一个 type_cursor_type 游标类型的变量；
v_sql varchar2(2000):=‘select * from student’; 定义一个字符串变量 v_sql，存储 SQL 查询语句。这个语句是用于从 student 表中选择所有列。
open c_student for v_sql; 使用定义的游标类型 type_cursor_type 打开一个游标 c_student 并执行 SQL 查询语句；
fetch c_student bulk collect into v_student; 获取游标中的一批数据，并将数据存储到 v_student 变量中。bulk collect 关键字允许一次性获取多行数据；
exit when v_student.count=0; 当查询结果集为空时，退出循环；
for i in v_student.first …v_student.last loop 这个循环从 v_student 的第一个元素到最后一个元素进行遍历；
print('student name: ‘||v_student(i).first_name||’ '||v_student(i).last_name); 打印当前遍历的 student 的 first_name 和 last_name。
通过循环的方式处理了查询结果，直到结果集为空为止。它遍历了所有的查询结果，并打印了每个 student 的名字。

##定义和初始化关联数组
declare
  TYPE JOIN_LIST IS TABLE of number index by varchar2(20);
  v_string JOIN_LIST;
  i        varchar2(30);
begin
  v_string('a') := 10;
  v_string('b') := 20;
  v_string('c') := 30;
##循环遍历和输出关联数组元素
  i := v_string.FIRST;
  WHILE i is not null loop
    print(i || ' number is:' || v_string(i));
    i := v_string.NEXT(i);
  end loop;
  
#3错误处理
EXCEPTION
  when NO_DATA_FOUND then
    print('no data found reading v_number(5)!');
end;

解读：

旨在操作一个关联数组（join_list），存储类型为 table of number index by varchar2(20)。
type join_list is table of number index by varchar2(20);定义了一个名为 join_list 的关联数组类型，索引类型为 varchar2(20)，数组元素类型为 number；
v_string join_list;定义了一个变量 v_string，其类型为上面定义的 join_list，即一个索引类型为 varchar2(20) 的关联数组；
v_string(‘a’) := 10;初始化关联数组 v_string，将键为 ‘a’ 的元素设置为10；
v_string(‘b’) := 20;将键为 ‘b’ 的元素设置为 20；
v_string(‘c’) := 30;将键为 ‘c’ 的元素设置为 30；
i := v_string.first;初始化一个变量 i，用于在循环中作为指针来遍历关联数组；
while i is not null loop：当 i 不等于 NULL 时，这个循环就会执行；
print(i || ’ number is:’ || v_string(i));在循环中，打印 i（作为键）和对应的关联数组元素（即 v_string(i)）；
i := v_string.next(i);使用 next 方法移动指针 i 到关联数组中的下一个元素。
exception：开启异常处理；
when no_data_found then：捕获 no_data_found 异常，即在尝试访问不存在的元素时发生的异常；
print(‘no data found reading v_string(5)!’);如果在循环中尝试访问不存在的元素时抛出异常，就会打印这条信息。
这段代码定义了一个关联数组 v_string，用于存储键值对（如 ‘a’: 10, ‘b’: 20, ‘c’: 30），并使用 for 循环遍历每个键值对，打印键和对应的值。同时，它包含了异常处理部分，当试图访问不存在的键时，会捕获异常并输出错误信息。

4.2 嵌套表

适用于嵌套表的情况：
元素的数量不确定，索引值不连续，需要同时删除和更新部分元素，而不是全部元素，需要创建一个独立的表查找；

TYPE type_name IS TABLE OF element_type [not null]  ;
v_type_name     type_name ;

创建嵌套表的语与关联数组的声明十分相似，只是没有 INDEX BY 子句。

在模式级定义嵌套表类型

create or replace type type_mid_test1 is table of mid_test1%rowtype;
create or replace type type_mid_test2 is table of varchar2(30);

自定义表类型

declare
type t_table_type is record (field_1 varchar2(32),field_2 number );   
type t_mid_table_type is table of  t_table_type; 
v_mid_table_type  t_mid_table_type ;
BEGIN
   ...;
END;

注：嵌套表是在声明时初始化的。因此它是清空的，但不为空。在游标循环中包含带有集合方法之一 EXTEND 的语句。这种方法可以增加集合的大小。需要注意的是，EXTEND 方法不能与关联数组配合使用。

DECLARE
TYPE t_mid_table_type is table of  student.first_name%type; 
v_mid_table_type  t_mid_table_type := t_mid_table_type();
v_index number := 0;
cursor name_cur is select first_name from student ;

BEGIN
    FOR i IN name_cur LOOP
        v_index := v_index +1;
        v_mid_table_type.extend;
        v_mid_table_type(v_index):= i.first_name;
        
        PRINT('first_name ('||v_index||'): '||v_mid_table_type(v_index));
    END LOOP;
END;

解读：

declare type t_mid_table_type is table of student.first_name%type;定义 t_mid_table_type 是一个可以存储 student 表中 first_name 字段的元素的类型；
v_mid_table_type t_mid_table_type := t_mid_table_type();声明了一个变量 v_mid_table_type为 t_mid_table_type 类型，表示 v_mid_table_type 是一个可以存储 student.first_name 类型元素的表；
v_index number := 0;声明了一个变量 v_index 用于存储当前处理的元素的索引，初始化为 0；
cursor name_cur is select first_name from student ;声明了一个游标 name_cur，用于执行 SQL 查询 select first_name from student，该查询将从 student 表中选择所有 first_name 字段。
begin 和 end 之间的语句定义了一个复合语句，用于循环遍历 name_cur 游标中的所有 first_name；
变量 v_index 的值递增，用来更新 v_mid_table_type 表中的当前元素的位置；
使用 extend 方法扩展 v_mid_table_type 表以匹配当前索引值，并使用 v_mid_table_type(v_index) 将当前 first_name 添加到表中；
打印 first_name（当前处理的 first_name）及其索引（v_index），以此来追踪表中元素的添加过程。
定义了一个可以存储 student 表中 first_name 类型元素的表 t_mid_table_type，创建了一个变量 v_mid_table_type 来实例化并操作这个表，使用 name_cur 游标从 student 表中获取每个 first_name，通过循环遍历这些 first_name，将每个 first_name 添加到 v_mid_table_type 表中的相应索引位置，并打印出每个 first_name 以及其对应的索引。这可以用于将 student 表中的 first_name 存储到一个自定义类型的表中，以便进一步处理或操作。

5 函数

5.1 数值型常用函数

函数	返回值	样例	显示
ceil(n)	大于或等于数值n的最小整数	select ceil(10.6) from dual;	11
floor(n)	小于等于数值n的最大整数	select ceil(10.6) from dual;	10
mod(m,n)	m除以n的余数,若n=0,则返回m	select mod(7,5) from dual;	2
power(m,n)	m的n次方	select power(3,2) from dual;	9
round(n,m)	将n四舍五入,保留小数点后m位	select round(1234.56782) from dual;	1234.57
sign(n)	若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1	select sign(12) from dual;	1
sqrt(n)	n的平方根	select sqrt(25) from dual;	5

5.2 常用字符函数

函数	返回值	样例	显示
initcap(char)	把每个字符串的第一个字符换成大写	select initcap(‘mr.ecop’) from dual;	Mr.Ecop
lower(char)	整个字符串换成小写	select lower(‘MR.ecop’) from dual;	mr.ecop
replace(char,str1,str2)	字符串中所有str1换成str2	select replace(‘Scott’,‘s’,‘Boy’) from dual;	Boycott
substr(char,m,n)	取出从m字符开始的n个字符的子串	select substr(‘ABCDEF’,2,2) from dual;	CD
length(char)	求字符串的长度	select length(‘ACD’) from dual;	3
\|\|	并置运算符	select ‘ABCD’\|\|‘EFGH’ from dual;	ABCDEFGH

5.3 日期型函数

函数	返回值	样例
sysdate	当前日期和时间	select sysdate from dual;
last_day	本月最后一天	select last_day(sysdate) from dual;
add_months(d,n)	当前日期d后推n个月	select add_months(sysdate,2) from dual;
months_between(d,n)	日期d和n相差月数	select months_between(sysdate,to_date(‘20020812’,‘YYYYMMDD’)) from dual;
next_day(d,day)	d后第一周指定day的日期 ‘Monday’ 星期一，‘Tuesday’ 星期二，‘wednesday’ 　星期三，‘Thursday’ 星期四 ‘Friday’ 星期五，‘Saturday’ 星期六 ‘Sunday’ 星期日	select next_day(sysdate,‘Monday’) from dual;

5.4 字符函数

函数	返回值	样例	显示
CONCAT(char1, char2)	返回连接“char2”的“char1”	select CONCAT(‘123’,‘456’) from dual;	123456
INITCAP(string)	将“string”的首字符转成大写。	Select INITCAP(‘string’) from dual;	String
UPPER(‘string’)	将“string”的字符转成大写。	select upper(‘string’) from dual;	STRING
LOWER (‘STRING’)	将“string”转成小写。	select lower(‘STRING’) from dual;	string
LPAD(char1,n [,char2])	返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。	select LPAD(‘123’,7,‘0’) from dual;	0000123
REPLACE(string, if, then)	用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。	SELECT REPLACE(‘JACK and JUE’,‘J’,‘BL’) FROM DUAL;	BLACK and BLUE
LENGTH(string)	返回“string”的长度值。	SELECT LENGTH(‘1234567’) FROM dual;	7
INSTR (string, set[, start[, occurrence] ] )	该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。 “start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。示例 SELECT INSTR(‘aptech is aptech’,‘ap’,1,2) FROM DUAL;	SELECT INSTR(‘aptech is aptech’,‘ap’,1,2) FROM DUAL;	11
SUBSTR(string, start [,count])	截取字符集	SELECT SUBSTR(‘ABCDEFGIJKLM’,3,4) FROM DUAL;	CDEF
RTRIM(string,trim_set)	从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。	SELECT RTRIM(‘abcdef’, ‘f’) FROM DUAL;	abcde
RPAD(char1, n [,char2])	右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符	SELECT RPAD(‘123’,7,‘*’) FROM dual;	123****

5.5 自定义函数

5.5.1 创建函数

函数是与过程非常相似的另一种类型的存储代码。两者之间最大区别是，一个函数是返回单个值的 PL/SQL 块。函数可以接受一个、多个参数或零个参数，但在其执行部分必须有 return 子句。返回值的数据类型必须在函数的标头中声明。函数的运行方式与过程不同，它不是一个独立可执行文件，也就是说，函数必须始终在某些上下文下使用。你可以把它看作等同一个语句片段。函数产生的输出需要被赋予一个变量，或者它可以在 SELECT 语句中使用。

5.5.2 创建存储函数

CREATE [OR REPLACE] FUNCTION 函数名
  (参数列表)
  RETURN 数据类型
IS
BEGIN
   <函数体>
   RETURN (返回值)
END;

函数不一定要有参数，但是它必须有在标头中声明 RETURN 值，并且它必须为所有可能的执行流返回值。RETURN 语句不必出现在主要执行部分的最后一行，并有可能存在一个以上的 RETURN 语句（应为每个异常包含一个 RETURN 语句）。函数可以有IN、OUT 或 IN OUT 参数，但是你很少会看到除了 IN 参数外的任何东西，因为使用其他类型的参数是种不好的编程习惯。

CREATE  OR REPLACE  FUNCTION F_STUDENT_NAME (I_ID IN NUMBER)
  RETURN VARCHAR2
IS
v_student_name varchar2(32);
BEGIN
    SELECT firname||' '||t.last_name  INTO v_student_name FROM student t WHERE t.student_id =I_ID;
   RETURN  v_student_name;
END F_STUDENT_NAME ;

解读：

创建一个名为 f_student_name 的函数，该函数接受一个整数参数 i_id，返回对应学生姓名的字符串。
函数定义：使用 create or replace function，如果已有同名函数，则替换现有函数，如果未找到同名函数，则创建这个函数。
函数变量定义：在函数内部定义一个变量 v_student_name，类型为 varchar2，用于存储查询结果。
SQL 查询：使用 select 命令从 student 表中查询学生姓名。这里使用 firname 和 last_name 字段组合成完整的姓名，并将查询结果存储到变量 v_student_name 中。具体查询语句为：
select firname||’ '||t.last_name into v_student_name from student t where t.student_id =i_id;
其中：
firname：查询第一名称。
t.last_name：查询姓氏。
||：字符串连接操作符，将两个字符串组合成完整的名字。
into v_student_name：将查询结果存储到变量 v_student_name 中。
返回值函数的返回值是 v_student_name 变量的值，即查询到的姓名。
函数结束：函数以 end f_student_name;结束。
当调用 f_student_name(i_id) 且 i_id 是一个有效的学生 ID 时，函数将返回该学生对应的完整姓名。

5.6 函数返回类型

5.6.1 函数中返回游标方法及使用

在 DM 数据库中有个系统定义的一个 ref cursor ： SYS_REFCURSOR 。

也可以自己定义一个游标类型存储在数据库中：

create or replace type type_cursor  is ref cursor ;

创建一个返回游标的函数：

CREATE OR REPLACE FUNCTION  F_TEST (I_ID in number )
return SYS_REFCURSOR  
is
cur_cursor  SYS_REFCURSOR;
BEGIN
open cur_cursor for select first_name,last_name from  student where student_id = I_ID;       
return cur_cursor;
end;

调用方法取出返回游标中的数据：

DECLARE
c_cur  type_cursor := F_TEST(234);
v_first_name student.first_name%Type;
v_last_name student.last_name%Type;
BEGIN

LOOP
    FETCH c_cur INTO v_first_name, v_last_name;
EXIT WHEN c_cur%NOTFOUND;
PRINT(v_first_name || ' ' ||v_last_name);
END LOOP;
END;

5.6.2 函数中返回结果集方法及使用

需要自己定义一个嵌套表类型存储在数据库中：

create or replace type type_student is table of student%rowtype ;

创建一个返回结果集的函数：

CREATE OR REPLACE FUNCTION  F_TEST1 (I_ID in number )
return type_student  
is
t_student    type_student;
BEGIN
 select * bulk COLLECT into t_student from  student where student_id = I_ID;       
return t_student;
end;

调用方法取出返回结果集中的数据：

DECLARE
t_student    type_student:=F_TEST1(123);
BEGIN

 for i in t_student.FIRST .. t_student.last loop
    print(t_student(i).first_name||' '||t_student(i).last_name);
 end loop;
END;

DM6 中函数返回游标用法特殊，采用如下方式表示返回游标类型：

##DM6中用法特殊，采用如下方式表示返回游标类型 
CREATE OR REPLACE PROCEDURE test(tab_id IN  INT,
      psedo_param in int default -12345) 
##psedo_param in int default -12345这个就代表输入游标，这个参数不能作任何改动
IS
BEGIN
##OPEN curs FOR SELECT name from systables where id = tab_id;
    SELECT first_name,last_name from student where student_id = tab_id;
    return;
END; 

##调用函数
call test(123);

5.7 参数返回游标类型使用

5.7.1 存储过程参数返回游标以及调用方法

create or REPLACE PROCEDURE P_TEST_OUT_CURSOR(I_ID IN NUMBER,O_CUR OUT SYS_REFCURSOR)
IS 

BEGIN
    open O_CUR for  select * from student t where t.student_id =I_ID;
EXCEPTION
WHEN OTHERS THEN
 NULL;
END;

DECLARE
c_stu_cur sys_refcursor;
v_student_name student%rowtype;
BEGIN
      P_TEST_OUT_CURSOR(123,c_stu_cur);
      loop
         fetch c_stu_cur  into v_student_name;
         EXIT WHEN c_stu_cur%NOTFOUND; 
         print('Student name: '||v_student_name.first_name||' '||v_student_name.last_name);
      end loop;
END;

5.7.2 函数参数返回游标以及调用方法

create or REPLACE FUNCTION F_TEST_OUT_CURSOR(I_ID IN NUMBER,O_CUR OUT SYS_REFCURSOR)
RETURN VARCHAR2
IS 
v_res VARCHAR2(256);
BEGIN
    open O_CUR for  select * from student t where t.student_id =I_ID;
EXCEPTION
WHEN OTHERS THEN
 NULL;  
END;

DECLARE
c_stu_cur sys_refcursor;
v_student_name student%rowtype;
BEGIN
      F_TEST_OUT_CURSOR(123,c_stu_cur) ;
      loop
         fetch c_stu_cur  into v_student_name;
         EXIT WHEN c_stu_cur%NOTFOUND; 
         print('Student name: '||v_student_name.first_name||' '||v_student_name.last_name);
      end loop;
END;

5.8 自定义函数确定性

确定性函数用关键词 DETERMINISTIC 标识，表示函数的返回值完全由输入参数决定。
确定性函数有以下用处：

可以在基于函数的索引中使用该函数；
可以在物化视图中调用；
DM7 开始会对其参数及其返回结果进行缓存处理以提升性能。
是不是一个确定性函数是需要用户来负责的，就是说对函数进行编译的时候不会检查出这个函数是否是确定性的。例如：

create or replace function f_define_nc (inpstr in varchar2)
  return varchar2 DETERMINISTIC 
 is
    Result1 varchar2(10);
  begin
    Result1 := substr(inpstr,1,3);
    return(Result1);
  end f_define_nc;

创建函数索引

create index idx_student_firstname on student(f_define_nc(first_name));

函数确定性对比sql

create or replace function f_t(i_p int) return number is
i_rtn number;
begin
i_rtn := i_p * dbms_random.value(1,10);
return i_rtn;
end;
/
 
select LEVEL,f_t(1) FROM DUAL CONNECT BY LEVEL<=10; 

create or replace function f_t(i_p int) return number DETERMINISTIC is
i_rtn number;
begin
   i_rtn := i_p * dbms_random.value(1,10);
   return i_rtn;
end;
/
 
select LEVEL,f_t(1) FROM DUAL CONNECT BY LEVEL<=10;

6 常用系统包介绍

6.1 DBMS_OUTPUT

DBMS_OUTPUT 系统包是为了在 DM 上兼容 oracle 的 DBMS_OUTPUT 系统包。提供将文本行写入内存、供以后提取和显示的功能。为用户从 oracle 移植应用提供方便，功能上与 oracle 基本一致, 使用 DBMS_OUTPUT 包打印首先需要创建系统包：

SP_CREATE_SYSTEM_PACKAGES(1);

DBMS_OUTPUT.DISABLE：禁用 DBMS_OUTPUT 包
DBMS_OUTPUT.ENABLE：启用DBMS_OUTPUT包
DBMS_OUTPUT 在命令窗口使用：SET SERVEROUTPUT ON;

SET SERVEROUTPUT ON;    --允许显示输出 
SET SERVEROUTPUT OFF;  --关闭显示输出 
DBMS_OUTPUT.DISABLE(); --禁用 DBMS_OUTPUT 包
DBMS_OUTPUT.ENABLE();   --启用 DBMS_OUTPUT 包
begin
   dbms_output.put_line('Hello, World');
end;

DBMS_OUTPUT. GET_LINE：从缓冲区中读取一行信息

DBMS_OUTPUT.ENABLE();
declare
v_buffer varchar2;
v_status number;
begin
   dbms_output.put_line('Hello,World');
   dbms_output.get_line(v_buffer,v_status);
   dbms_output.put_line('缓冲区内容: '||v_buffer||' 状态:'||v_status);
end;

6.2 PRINT

PRINT 语句用于从 PLSQL 程序中向客户端输出一个字符串，语句中的表达式可以是各种数据类型，系统自动将其转换为字符类型。
PRINT 语句便于用户调试 PLSQL 程序代码。当 PLSQL程序的行为与预期不一致时，可以在其中加入 PRINT 语句来观察各个阶段的运行情况，在命令窗口中还是需要打开缓冲区：set serveroutput on;

BEGIN
    PRINT('Hello, World');
END;

6.3 DBMS_STATS 包

优化统计信息描述了数据库中的对象细节。查询优化使用这些信息选择最合适的执行计划。使用 DBMS_STATS 包来收集统计、删除信息，将收集的统计信息记录在数据字典中。

GATHER_SCHEMA_STATS 收集模式下对象的统计信息。

call dbms_stats.gather_schema_stats(ownname => 'SYSDBA',options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all indexed columns',degree => 6 );

GATHER_TABLE_STATS 收集表、表中的列和表上的索引的统计信息。

call  dbms_stats.gather_table_stats(ownname => 'SYSDBA',tabname => 'STUDENT',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

GATHER_INDEX_STATS 收集索引的统计信息。

call  dbms_stats.gather_index_stats(ownname => 'SYSDBA',indname => 'IDX_STUDENT_FIRSTNAME',estimate_percent => '10',degree => '4') ;

DELETE_TABLE_STATS 删除与表相关对象的统计信息。

call  dbms_stats.DELETE_TABLE_STATS(ownname => 'SYSDBA',tabname => 'STUDENT') ;

DELETE_SCHEMA_STATS 删除模式下对象的统计信息。

call  dbms_stats.DELETE_SCHEMA_STATS(ownname => 'SYSDBA') ;

DELETE_INDEX_STATS 删除索引的统计信息。

call  dbms_stats.DELETE_INDEX_STATS(ownname => 'SYSDBA',indname => 'IDX_STUDENT_FIRSTNAME') ;

7 PL/SQL格式化准则

大小写
PL/SQL与SQL一样，是不区分大小写的。有关大小写的一般准则如下：
大写：
关键字（例如：BEGIN、EXCEPTION、END、IF-THEN-ELSE、LOOP、END LOOP）
数据类型（例如：VARCHAR2、NUMBER）
内置函数（例如、SUBSTR）
用户定义的子程序（例如、包、过程、函数）
小写：
变量名
SQL中的列名
SQL中的表名
空白
空白（多余行和空格）在PL/SQL中与在SQL中同样重要。它是改善可读性的一个主要因素。换句话说，你可以通过在代码中适当的缩进来显示程序的逻辑结构。

在等号或比较运算符的两侧都放置空格。
将结构的单词靠左对齐（例如、DECLARE、BEGIN、EXCEPTION和END，IF和END IF、LOOP和END LOOP）。此外，为结构内的结构缩进三格（使用空格键，而不是TAB键）。
在主要部分之间放置空行，以把它们相互分开。
把同一个结构的不同逻辑部分放在单独的行上，即使结构很短也是如此。

命名约定
为了确保不与关键字和列名/表名发生冲突，使用下面的前缀是有帮助的：

v_变量名
con_常量名
i_in参数名、o_out参数名、io_in_out参数名
c_游标名或名称_cur
rc_引用游标名
r_记录名或名称_rec
FOR r_stud IN c_stou_cur LOOP
FOR stud_rec IN stou_cur LOOP
type_名称_type(用于用户定义类型)
t_表或名称_tab(用于PL/SQL表)
rec_记录名或名称_rec（用于记录变量）
e_异常名（用户定义的异常）
P_过程名称（用户存储过程）
F_函数名称（用户定义的函数）

注释
注释在PL/SQL中与SQL中同样重要。它们应该解释程序的主要部分和任何重要的逻辑步骤。
使用单行注释“–”而不是多行“/* */”注释。虽然PL/SQL以同样的方式看待这些注释，但一旦你完成了代码，这么做会更容易调试，因为你不能在多行注释中嵌入多行注释。换句话说，你可以注释掉一部分包含单行注释的代码，但你不能注释掉一部分包含多行注释的代码。
其他建议
这里有一些额外的小建议，以帮助你确保PL/SQL代码整洁且易于理解。

对于嵌入在PL/SQL中的SQL语句，使用相同的格式化准则来确定此语句应如何在一个块中出现。
提供解释块的意图，并列出创建日期和作者姓名的注释标题。并为每次修订标明作者姓名、日期和修改说明。

8 附录

示例表：

-- Create table
create table debug_log(msg varchar2(2000),date datetime);  

-- Create table
create table STUDENT
(
  student_id        NUMBER(8) not null,
  salutation        VARCHAR2(5),
  first_name        VARCHAR2(25),
  last_name         VARCHAR2(25) not null,
  street_address    VARCHAR2(50),
  zip               NUMBER(10) not null,
  phone             VARCHAR2(15),
  employer          VARCHAR2(50),
  registration_date DATE not null,
  created_by        VARCHAR2(30) not null,
  created_date      DATE not null,
  modified_by       VARCHAR2(30),
  modified_date     DATE not null
);

insert into "STUDENT" ("STUDENT_ID","SALUTATION","FIRST_NAME","LAST_NAME","STREET_ADDRESS","ZIP","PHONE","EMPLOYER","REGISTRATION_DATE","CREATED_BY","CREATED_DATE","MODIFIED_BY","MODIFIED_DATE") values (123, 'Mr', 'John', 'Kennedy', 'xx', 12345, '123456', null, '2020-03-20', '123', '2020-03-20', '123', '2020-03-20');
insert into "STUDENT" ("STUDENT_ID","SALUTATION","FIRST_NAME","LAST_NAME","STREET_ADDRESS","ZIP","PHONE","EMPLOYER","REGISTRATION_DATE","CREATED_BY","CREATED_DATE","MODIFIED_BY","MODIFIED_DATE") values (234, 'Miss', 'Abby', 'Kennedy', 'xx', 12345, '1235167', null, '2020-03-20', '123', '2020-03-20', '123', '2020-03-20');

commit;