• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

PL(Procedural Language)/SQL程序设计语言

互联网 diligentman 7天前 9次浏览

PL(Procedural Language)/SQL程序设计语言

  • PL/SQL基础
    • PL/SQL程序块
    • PL/SQL第一个程序
    • 变量的声明
    • 字符串分隔符
    • 数据类型
    • %TYPE属性
    • 常用定界符
    • 序列
    • MERGE替代UPDATE
    • IF语句
    • CASE表达式与CASE语句
    • LOOP循环
    • WHILE循环
    • FOR循环
    • CONTINUE
  • 常用的组合数据类型
    • PL/SQL记录
    • %ROWTYPE
    • INDEX BY 表 (PL/SQL表)
    • INDEX BY 记录表
  • SQL游标(CURSOR)
    • 常用的4个CURSOR属性
    • CURSOR的FOR循环
    • 参数的CURSOR
    • FOR UPDATE
    • WHERE CURRENT OF
    • 动态CURSOR
  • 异常处理
    • 预定义的ORACLE服务器错误
    • 非预定义的ORACLE服务器错误
    • SQLCODE和SQLERRM
    • 用户定义的异常
    • RAISE_APPLICATION_ERROR过程
  • 模块化程序设计
    • 存储过程

PL/SQL基础

SQL是第四代(非结构化的)程序设计语言,PL/SQL是适用于ORACLE数据库编程的结构化的程序设计语言。

PL/SQL程序块

DECLARE            --可选
--variables,cursors,user-defined exceptions
BEGIN              --强制的(必须的)
  --一个或多个SQL语句
  --一个或多个PL/SQL语句
  EXCEPTION      --可选
    --当错误发生是要进行的处理
END;             --强制的(必须的)
/	
  1. 匿名程序块,不能存储在数据库中,执行时被编译并执行;
  2. 每一个SQL语句或PL/SQL语句都是以 ; 结束;
  3. 使用 / 结束PL/SQL程序块;
  4. 过程和函数是被命名的PL/SQL程序块,预编译在数据库中。

PL/SQL第一个程序

DECLARE            --可选
  --定义变量
  var VARCHAR2(100) := 'hello world!';
BEGIN              --强制的(必须的)
  --输出定义好的字符串变量
  dbms_output.put_line(var);
END;             --强制的(必须的)
/	

PL(Procedural Language)/SQL程序设计语言

变量的声明

标识符 [CONSTANT] 数据类型 [NOT NULL] [:= | DEFAULT 表达式]
  1. 标识符:所声明的变量名;
  2. CONSTANT:限制所声明变量的值不能更改(声明常量,必须初始化);
  3. 数据类型:可以是一个标量类型、组合类型或LOB类型;
  4. NOT NULL:限制变量不能为空,必须初始化;
  5. 初始化变量:既可以使用PL/SQL的赋值操作符 :=,也可以使用关键字 DEFAULT 来初始化变量;
  6. 表达式:PL/SQL表达式,可以是文字表达式、另外的变量或带有操作符和函数的表达式。
  7. 注意:PL/SQL中赋值操作符是 :=,而不是 =

字符串分隔符

  1. 直接使用单引号 ‘’ ,字符串中若有单引号 ,每个 需要重复一次进行转义;程序的可读性比较差,不建议使用这种方式;
SELECT 'What''s your name?' FROM dual;

PL(Procedural Language)/SQL程序设计语言

  1. 使用 q’ 操作符定义定界符;
--通过q'定义||为定界符
SELECT q'|What's your name?|' FROM dual;
--通过q'定义//为定界符
SELECT q'/What's your name?/' FROM dual;
--通过q'定义{}为定界符
SELECT q'{What's your name?}' FROM dual;
--通过q'定义[]为定界符
SELECT q'[What's your name?]' FROM dual;

PL(Procedural Language)/SQL程序设计语言

数据类型

数据类型 说明
VARCHAR2(size) size最大值为32767,VARCHAR2会吧空字符串当做空值处理,在表中,size的最大值为4000
CHAR(size) size默认为值1,最大值为32767
NUMBER(p,s) p为数的精度(位数),s为规模(指数值),p最小为1,最大为38,s最小值为-84,最大值为124
BINARY_INTEGER 基本整型,范围:-2147483647~2147483647
PLS_INTEGER 基本带符号整型,范围:-2147483647~2147483647,相比NUMBER存储更少,效率高,在PLS_INTEGER的范围尽可能使用PLS_INTEGER
BINARY_FLOAT 浮点数,5个字节
BINARY_DOUBLE 浮点数,9个字节
BOOLEAN 逻辑类型,有3个可能值,TRUE、FALSE和NULL
DATE 日期和时间型,年月日时分秒
TIMESATMP 日期和时间型,最多可精确到小数点后9位的秒数,默认精确到小数点后6位
TIMESATMP WITH TIME ZONE 扩展的TIMESATMP,包括了时区
CLOB 存储单字节的大数据对象
BLOB 存储大的二进制对象
BFILE 外部数据类型,存储在数据库之外的,可能是操作系统文件
NCLOB 存储NCHAR类型的单字节或定长多字节的Unicode大数据对象

%TYPE属性

  1. 使用 %type 属性按照之前已经声明过的变量或者数据库中表的列来声明一个变量;
  2. 当存储在一个变量中的值来自于数据库中的表时,最好使用 %type 声明这个变量;
  3. 使用 %type 后,当数据库中表的数据类型改变时,无需修改程序。
--建表
CREATE TABLE example (
  example_id NUMBER,
  example_name VARCHAR2(200)
);

--插入数据
INSERT INTO example VALUES (1,'样例1');
INSERT INTO example VALUES (2,'样例2');
COMMIT;

--测试%TYPE
DECLARE
  --用%TYPE属性声明p_example_id 是与example表中example_id相同的数据类型
  p_example_id example.example_id%TYPE;
  --用%TYPE属性声明p_example_name 是与example表中example_name相同的数据类型
  p_example_name example.example_name%TYPE;
BEGIN
  --将从example表中读取的一行数据的两列分别写进p_example_id和p_example_name变量
  SELECT * 
  INTO p_example_id,p_example_name
  FROM example
  WHERE ROWNUM = 1;
  --在oracle输出区输出p_example_id的值
  dbms_output.put_line(p_example_id);
  --在oracle输出区输出p_example_name的值
  dbms_output.put_line(p_example_name);
END;
/

PL(Procedural Language)/SQL程序设计语言

常用定界符

操作符 含义
+ 加法运算符
减法运算符/否定操作符
* 乘法运算符
** (指数)取幂运算符,只能在PL/SQL中使用,在SQL语句中使拥会报错
/ 除法运算符
= 相等操作符
; 语句结束符
@ 远程访问符
:= 赋值操作符
!= 不等运算符
<> 不等运算符
/* 开始注释定界符
*/ 结束注释定界符
单行注释符

序列

--创建序列
CREATE SEQUENCE exp_seq  
  START WITH 100         --从100开始,初始值为100
  INCREMENT BY 1         --每次递增1
  MAXVALUE 380380        --可以递增到的最大值设置为380380
  NOCACHE                --不使用缓存
  NOCYCLE;               --到达最大值后不再从头开始

--使用序列
--nextval 每次递增
--currval 指向序列的当前值
SELECT exp_seq.nextval,exp_seq.currval FROM dual;

PL(Procedural Language)/SQL程序设计语言
PL(Procedural Language)/SQL程序设计语言

MERGE替代UPDATE

MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
  UPDATE SET
    col1 = col1_val,
    col2 = col2_val
WHEN NOT MATCHED THEN
  INSERT (column_list)
  VALUES (column_values);
  1. 在涉及到关联子查询更新数据的时候,尽量使用MERGE来替代UPDATE,MERGE的效率比UPDATE更高;
  2. 当表中存在该行数据的时候,进行更新操作,不存在这行数据的时候,则插入数据,这样的操作直接MERGE就能实现,就不需要PL/SQL复杂的逻辑判断;
  3. INTO 子句——说明正在修改或插入的目标表;
  4. USING 子句——标识要修改或插入的数据源,既可以是表,也可以是视图,甚至可以是子查询;
  5. ON 子句——定义MERGE 语句是进行修改操作还是插入操作的条件;
  6. WHEN MATCHED THEN——定义当条件满足时所做的操作;
  7. WHEN NOT MATCHED THEN——定义当条件不满足时所做的操作;
  8. ON 条件中的列是不能更新的;
  9. MERGE语句与其他DML语句一样,不会自动结束事务,需要手动COMMIT或者ROLLBACK;
  10. MERGE和USIGN后的表名必须给别名;
  11. UPDATE后是可以加WHERE条件的。
    例:
CREATE TABLE example (
  example_id NUMBER,
  example_name VARCHAR2(200)
);

INSERT INTO example VALUES (1,'样例1');
INSERT INTO example VALUES (2,'样例2');
COMMIT;

PL(Procedural Language)/SQL程序设计语言

MERGE INTO example a
USING (
  SELECT 1 example_id, 'MERGE后的样例1' example_name FROM dual
  UNION 
  SELECT 2 example_id, 'MERGE后的样例2' example_name FROM dual
  UNION 
  SELECT 3 example_id, 'MERGE后的样例3' example_name FROM dual
) b
ON (a.example_id = b.example_id)
WHEN MATCHED THEN
  UPDATE SET
    a.example_name = b.example_name
  --限制当 example_id = 1 的时候才做更新
  WHERE 
    a.example_id = 1
WHEN NOT MATCHED THEN
  INSERT (a.example_id,a.example_name)
  VALUES (b.example_id,b.example_name);
COMMIT;

PL(Procedural Language)/SQL程序设计语言

IF语句

IF 条件1 THEN
  --如果满足条件1,则执行该语句块中的语句
  statements;
[
--ELSIF 可以有0个或多个
ELSIF 条件2 THEN
  --如果不满足条件1但满足条件2,则执行该语句块中的语句
  statements;]
[
--ELSE可以有0个或1个
ELSE 
  --如果条件1和条件2都不满足,则执行该语句块中的语句
  statements;]
END IF;

DECLARE
  score NUMBER;
BEGIN

  score := 100;
  
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good!');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good!');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible!');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much!');
  END IF;
  
  score := 80;
  
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good!');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good!');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible!');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much!');
  END IF;
  
  score := 65;
  
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good!');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good!');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible!');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much!');
  END IF;
  
  score := 50;
  
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good!');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good!');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible!');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much!');
  END IF;
END;
/

PL(Procedural Language)/SQL程序设计语言

CASE表达式与CASE语句

--CASE表达式既可以用在SQL语句中,也可以用在PL/SQL中
--第一种CASE表达式
CASE selector
  WHEN 表达式1 THEN 结果1
  WHEN 表达式2 THEN 结果2
  ...
  WHEN 表达式n THEN 结果n
  [ELSE 结果n+1]
END;    --必须以END结尾
--当1=2时输出1,否则输出0
--显然结果是输出0
SELECT CASE 1 WHEN 2 THEN 1 ELSE 0 END FROM dual;

--第二种CASE表达式
CASE 
  WHEN 搜索条件1 THEN 结果1
  WHEN 搜索条件2 THEN 结果2
  ...
  WHEN 搜索条件n THEN 结果n
  [ELSE 结果n+1]
END;    --必须以END结尾
--当1=2时输出1,否则输出0
--显然结果是输出0
SELECT CASE WHEN 1 = 2 THEN 1 ELSE 0 END FROM dual;

--CASE语句只能用在PL/SQL中
--第一种CASE语句
CASE selector
  WHEN 表达式1 THEN 
    statements1;
  WHEN 表达式2 THEN 
    statements2;
  ...
  WHEN 表达式n THEN 
    statementsn;
  [ELSE 
    statementsn+1]
END CASE;    --必须以END CASE;结尾

--第二种CASE语句
CASE 
  WHEN 搜索条件1 THEN 
    statements1;
  WHEN 搜索条件2 THEN 
    statements2;
  ...
  WHEN 搜索条件n THEN 
    statementsn;
  [ELSE 
    statementsn+1]
END CASE;    --必须以END CASE;结尾

BEGIN
  --第一种
  CASE 1 
    WHEN 2 THEN
      dbms_output.put_line('1 = 2');
    ELSE
      dbms_output.put_line('1 <> 2');
  END CASE;
  
  --第二种
  CASE  
    WHEN 1 = 2 THEN
      dbms_output.put_line('1 = 2');
    ELSE
      dbms_output.put_line('1 <> 2');
  END CASE;
    
END;
/

LOOP循环

/*
  循环输出1到10
*/
DECLARE
  --定义一个数,初始值为1
  num NUMBER := 1;
BEGIN
  --循环开始
  LOOP
    --输出num
    dbms_output.put_line(num);
    --num+1
    num := num + 1;
    --循环退出条件,当num第一次出现比10大的时候退出
    EXIT WHEN num > 10;
  END LOOP;

END;
/

PL(Procedural Language)/SQL程序设计语言

WHILE循环

/*
  循环输出1到10
*/
DECLARE
  --定义一个数,初始值为1
  num NUMBER := 1;
BEGIN
   
  --先判断退出循环条件,退出循环条件为 num <= 10
  WHILE num <= 10 
  LOOP
  
    --输出num
    dbms_output.put_line(num);  
    --num++
    num := num + 1;
  
  END LOOP;

END;
/

PL(Procedural Language)/SQL程序设计语言

FOR循环

/*
  循环输出1到10
*/
BEGIN
 
  FOR i IN 1..10 LOOP
    dbms_output.put_line(i);  
  END LOOP;

END;
/

PL(Procedural Language)/SQL程序设计语言

CONTINUE

--CONTINUE退出当前循环进入下一次循环
/*
  循环输出1到4,6到10,不输出5
*/
--可以这么写
BEGIN
 
  FOR i IN 1..10 LOOP
    CONTINUE WHEN i = 5;
    dbms_output.put_line(i);  
  END LOOP;

END;
/
--也可以这么写
BEGIN
 
  FOR i IN 1..10 LOOP
    IF i = 5 THEN
      CONTINUE;
    END IF;
    dbms_output.put_line(i);  
  END LOOP;

END;
/

PL(Procedural Language)/SQL程序设计语言

常用的组合数据类型

PL/SQL记录

  1. 创建一个记录类型;
TYPE 数据类型名 IS RECORD (
  字段声明[,字段声明]...
);
  1. 声明一个PL/SQL记录类型的变量;
标识符 数据类型名;
  1. 例:
DECLARE
  --创建一个记录类型
  TYPE exp_record_type IS RECORD (
    example_id example.example_id%TYPE,
    example_name example.example_name%TYPE
  );
  --声明记录类型的变量
  exp_record exp_record_type;
BEGIN

  --将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中
  SELECT 
    example_id,
    example_name
  INTO exp_record
  FROM example
  WHERE ROWNUM = 1;
  
  --输出
  dbms_output.put_line(exp_record.example_id);
  dbms_output.put_line(exp_record.example_name);

END;
/

%ROWTYPE

利用%ROWTYPE属性声明一个能够存储一个表或视图中一整行数据的记录(变量)。

DECLARE
  --声明记录类型的变量
  exp_record example%ROWTYPE;
BEGIN

  --将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中
  SELECT 
    example_id,
    example_name
  INTO exp_record
  FROM example
  WHERE ROWNUM = 1;
  
  --输出
  dbms_output.put_line(exp_record.example_id);
  dbms_output.put_line(exp_record.example_name);

END;
/

INDEX BY 表 (PL/SQL表)

  1. INDEX BY 表 又称 PL/SQL表,是一组关联的键值对,类似于其他程序设计语言的数组;
  2. 声明一个INDEX BY 表的数据类型;
TYPE 数据类型名 IS TABLE OF
  {列数据类型 | 变量%TYPE
  | 表名.列名%TYPE} [NOT NULL]
  | 表名%ROWTYPE
  [INDEX BY PLS_INTEGER | BINARY_INTEGER | VARCHAR2(<size>)];
  1. 声明一个INDEX BY 表的数据类型的变量;
标识符 数据类型名;
  1. 例:
DECLARE 
  --声明记录类型的变量
  exp_record example%ROWTYPE;
  --声明数组类型
  TYPE exp_table_type IS TABLE OF example.example_name%TYPE
    INDEX BY PLS_INTEGER;
  --声明数组类型的变量
  exp_table exp_table_type;
BEGIN
  
  --将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中
  SELECT 
    example_id,
    example_name
  INTO exp_record
  FROM example
  WHERE ROWNUM = 1;
  
  --对数组进行赋值
  exp_table(exp_record.example_id) := exp_record.example_name;
  
  --输出刚赋值的数组
  dbms_output.put_line('exp_table(' || exp_record.example_id || ') = ''' || exp_table(exp_record.example_id) || '''');

END;
/
  1. INDEX BY 表的内置函数
方法 描述
EXISTS(n) 如果第n个元素在PL/SQL表(数组)中存在,返回TRUE
COUNT 返回一个PL/SQL表当前所包含元素的个数
FIRST 返回PL/SQL表中第一个(最小的)下标数字;如果PL/SQL表是空的,返回NULL
LAST 返回PL/SQL表中最后一个(最大的)下标数字;如果PL/SQL表是空的,返回NULL
PRIOR(n) 返回PL/SQL表中当前元素的前n个元素的表值
NEXT(n) 返回PL/SQL表中当前元素的前n个元素的表值
DELETE 删除PL/SQL表的全部元素
DELETE(n) 删除PL/SQL表的第n个元素
DELETE(m,n) 删除PL/SQL表的m到n范围内的全部元素

INDEX BY 记录表

  1. 如果用想用数组来操作一个表中的多列,那么需要基于这个表的每一列都定义一个INDEX BY 表,这样非常不方便;
  2. 声明一个INDEX BY 记录表就是定义一个存放一个表中整行数据(每一列)的INDEX BY表变量;
  3. 例:
DECLARE 
  v_count NUMBER := 3;
  --声明数组类型
  TYPE exp_table_type IS TABLE OF example%ROWTYPE
    INDEX BY PLS_INTEGER;
  --声明数组类型的变量
  exp_table exp_table_type;
BEGIN

  --循环对数组进行赋值
  FOR i IN 1 .. v_count 
  LOOP
    SELECT * 
    INTO exp_table(i)
    FROM example
    WHERE example_id = i;
  END LOOP;
  
  --遍历数组
  FOR i IN 1 .. exp_table.count 
  LOOP
    dbms_output.put_line(exp_table(i).example_id || '|' || exp_table(i).example_name);
  END LOOP;

END;
/

SQL游标(CURSOR)

  1. 声明游标;
--cursor_name :自定义的游标名称
--select_statement :为一个没有INTO子句的	SELECT 语句
CURSOR cursor_name IS
  select_statement;
  1. 打开游标;
OPEN cursor_name;
  1. 循环获取游标的每一行数据;
LOOP
  FETCH cursor_name into var1,var2;
  EXIT WHEN cursor_name%NOTFOUND OR cursor_name%NOTFOUND IS NULL;
  ...
END LOOP;
  1. 关闭游标;
CLOSE cursor_name;
  1. 优化打开游标,先判断游标是否已经被打开,如果没有被打开则打开游标;
IF NOT cursor_name%ISOPEN THEN
  OPEN cursor_name;
END IF;
  1. 如果一个表中的列很多,需要定义与列数相同的变量,实际上可以定义基于游标的记录类型;
DECLARE
  --声明游标
  CURSOR exp_cursor IS 
    SELECT * FROM example;
  --声明基于游标的记录变量
  exp_cursor_record exp_cursor%ROWTYPE;
BEGIN
  
  --如果游标不是打开状态,则打开游标
  IF NOT exp_cursor%ISOPEN THEN
    OPEN exp_cursor;
  END IF;
  
  --循环获取游标的数据到基于游标的记录变量
  LOOP
    FETCH exp_cursor INTO exp_cursor_record;
    EXIT WHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND IS NULL;
    
    dbms_output.put_line(exp_cursor_record.example_id || '|' || exp_cursor_record.example_name);
      
  END LOOP;

  CLOSE exp_cursor;
  
END;
/

常用的4个CURSOR属性

属性 描述
%ISOPEN 如果游标是打开的,返回TRUE
%FOUND 如果最近(刚刚执行过)的SQL语句返回至少一行数据,返回TRUE
%NOTFOUND 如果最近(刚刚执行过)的SQL语句没有返回任何数据,返回TRUE
%ROWCOUNT 返回最近(刚刚执行过)的SQL语句所影响的数据行数(为一个整数)

CURSOR的FOR循环

--record_name :隐含声明的记录名
--cursor_name :前面声明的游标
--select_statement :前面未声明游标时,使用查询语句隐式的声明游标
FOR record_name IN cursor_name | (select_statement) 
LOOP         --隐含打开游标并提取数据行
  语句1;
  语句2;
  ...
END LOOP;    --隐含关闭游标
BEGIN
  
  FOR i IN (
    SELECT * FROM example
  )
  LOOP
    dbms_output.put_line(i.example_id || '|' || i.example_name);
  END LOOP;
  
END;
/

带参数的CURSOR

  1. 参数在DECLARE中CURSOR声明之前声明,直接在CURSOR使用参数参数名不能与列名相同);
DECLARE
  p_example_id NUMBER := 1;
  --声明游标
  CURSOR exp_cursor IS 
    SELECT * 
    FROM example
    WHERE example_id = p_example_id;
  --声明基于游标的记录变量
  exp_cursor_record exp_cursor%ROWTYPE;
BEGIN
  
  --如果游标不是打开状态,则打开游标
  IF NOT exp_cursor%ISOPEN THEN
    OPEN exp_cursor;
  END IF;
  
  --循环获取游标的数据到基于游标的记录变量
  LOOP
    FETCH exp_cursor INTO exp_cursor_record;
    EXIT WHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND IS NULL;
    
    dbms_output.put_line(exp_cursor_record.example_id || '|' || exp_cursor_record.example_name);
      
  END LOOP;
 
  CLOSE exp_cursor;
  
END;
/
DECLARE
  p_example_id NUMBER := 1;
BEGIN
  
  FOR i IN (
    SELECT * FROM example
    WHERE example_id = p_example_id
  )
  LOOP
    dbms_output.put_line(i.example_id || '|' || i.example_name);
  END LOOP;
  
END;
/
  1. 参数在CURSOR的声明中声明;
DECLARE
  
  --声明游标
  CURSOR exp_cursor (
    p_example_id NUMBER,
    p_example_name VARCHAR2
  ) 
  IS 
    SELECT * 
    FROM example
    WHERE example_id = p_example_id;
  --声明基于游标的记录变量
  exp_cursor_record exp_cursor%ROWTYPE;
BEGIN
  
  --如果游标不是打开状态,则打开游标
  IF NOT exp_cursor%ISOPEN THEN
    OPEN exp_cursor (1,'MERGE后的样例1');
  END IF;
  
  --循环获取游标的数据到基于游标的记录变量
  LOOP
    FETCH exp_cursor INTO exp_cursor_record;
    EXIT WHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND IS NULL;
    
    dbms_output.put_line(exp_cursor_record.example_id || '|' || exp_cursor_record.example_name);
      
  END LOOP;

  CLOSE exp_cursor;
  
END;
/

FOR UPDATE

当游标在处理数据的时候,FOR UPDATE 会给查询表加行级锁(没有其他事物占用时才能加上锁,否则会等待其他事物释放锁),加锁后,其他事物不能对查询表的该行进行更改,直到该事物COMMIT或者ROLLBACK后才会释放锁。

SELECT ...
FROM   ...
FOR UPDATE [OF column_reference] [NOWAIT | WAIT n];

测试:

  1. 先开启一个会话,执行下面的语句,此时,该会话没有提交;
UPDATE example SET example_name = '111' WHERE example_id = 1;
--COMMIT;
  1. 另外开启一个会话,执行下面的语句;
DECLARE
  p_example_id NUMBER := 1;
BEGIN
  
  FOR i IN (
    SELECT * FROM example
    WHERE example_id = p_example_id
    FOR UPDATE WAIT 10
  )
  LOOP
    dbms_output.put_line(i.example_id || '|' || i.example_name);
  END LOOP;
  
END;
/

PL(Procedural Language)/SQL程序设计语言

  1. 此时COMMIT或者ROLLBACK第一个UPDATE的操作,再执行第二个PL/SQL块时,便可以执行了,因为COMMIT和ROLLBACK都会把锁释放。

WHERE CURRENT OF

  1. WHERE CURRENT OF 需要与 FOR UPDATE 配合使用;
  2. 在UPDATE或DELETE中使用 WHERE CURRENT OF,在CURSOR声明中说明 FOR UPDATE ;
  3. WHERE CURRENT OF 引用显示CURSOR的当前行;
DECLARE
  --声明游标,并对example的example_id=1的数据行加锁
  CURSOR exp_cursor IS 
    SELECT * FROM example
    WHERE example_id = 1
    FOR UPDATE OF example_name;
BEGIN
  
  --循环更新example表被锁定的行数据
  FOR i IN exp_cursor
  LOOP
    UPDATE example 
    SET
      example_name = '对游标中的表数据进行更新'
    WHERE CURRENT OF exp_cursor;
  END LOOP;
  
  --若正常执行到这里,则提交
  COMMIT;
  
  --若出现异常,则回滚
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
  
END;
/
SELECT * FROM example;

PL(Procedural Language)/SQL程序设计语言

动态CURSOR

  1. 声明的时候不关联SQL查询,在打开游标的时候动态关联SQL查询;
  2. 声明游标类型;
--cursor_type 为自定义的类型名
TYPE cursor_type IS REF CURSOR;
  1. 声明游标类型的变量;
--v_cursor 为自定义的变量名
--cursor_type 为上面声明的引用游标类型
v_cursor cursor_type;
  1. 自定义拼接动态SQL;
--拼接SQL赋值给v_sql变量
v_sql VARCHAR2(32767);
v_sql := 'select_statement';
  1. 打开游标,并关联动态SQL;
OPEN v_cursor FOR v_sql;
  1. 循环获取数据行;
  2. 关闭游标;
CLOSE v_cursor;

例:

CREATE TABLE example_202011 (
  example_id NUMBER,
  example_name VARCHAR2(200)
);

CREATE TABLE example_202010 (
  example_id NUMBER,
  example_name VARCHAR2(200)
);

INSERT INTO example_202011 VALUES (20201101,'样例20201101');
INSERT INTO example_202011 VALUES (20201102,'样例20201102');
INSERT INTO example_202010 VALUES (20201001,'样例20201001');
INSERT INTO example_202010 VALUES (20201002,'样例20201002');
COMMIT;

DECLARE
  --定义动态游标类型
  TYPE cursor_type IS REF CURSOR;
  --声明动态游标类型的变量
  v_cursor cursor_type;
  --当月月份变量   &thismonth表示替代变量
  thismonth VARCHAR2(10) := '&thismonth';
  --动态sql
  v_sql VARCHAR2(32767);
  --example_id变量用来接收游标中的example_id
  example_id NUMBER;
  --example_name变量用来接收游标中的example_name
  example_name VARCHAR2(200);
BEGIN
  
  --动态从指定月份的备份表中查询数据
  v_sql := q'[SELECT * FROM example_]' || thismonth;
  
  --打开动态游标并关联动态sql
  OPEN v_cursor FOR v_sql;
  
  --循环输出游标中的所有数据
  LOOP
    FETCH v_cursor INTO example_id,example_name;
    EXIT WHEN v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS NULL;
    
    dbms_output.put_line(example_id || '|' || example_name);
  
  END LOOP;
  
  --关闭游标
  CLOSE v_cursor;

END;
/

当输入202010时,结果为:
PL(Procedural Language)/SQL程序设计语言
当输入202011时,结果为:
PL(Procedural Language)/SQL程序设计语言

异常处理

EXCEPTION
  WHEN 异常1 [或 异常2 ...] THEN
    语句1;
    语句2;
    ...
  [WHEN 异常3 [或 异常4 ...] THEN
    语句1;
    语句2;
    ...]
  [WHEN OTHERS THEN
    语句1;
    语句2;
    ...]

预定义的ORACLE服务器错误

预定义异常名 ORACLE服务器错误代码 描述
ACCESS_INTO_NULL ORA_06530 试图为一个未初始化的对象的属性赋值
CASE_NOT_FOUND ORA_06592 在选择的CASE语句的WHEN子句中没有选择条件,并且没有ELSE语句
COLLECTION_IS_NULL ORA_06531 试图对一个未初始化嵌套表或VARRAY使用除了EXISTS以外的集合方法
CURSOR_ALREADY_OPEN ORA_06511 试图打开一个已打开的CURSOR
DUP_VAL_ON_INDEX ORA_00001 试图插入一个重复值
INVALID_CURSOR ORA_01001 发生了非法的CURSOR操作
INVALID_NUMBER ORA_01722 将字符串转换成数字失败
LOGIN_DENIED ORA_01017 以一个无效的用户名或密码登录ORACLE服务器
NO_DATA_FOUND ORA_01403 单行查询没有返回任何数据
NOT_LOGIN_ON ORA_01012 在没有连接ORACLE服务器的情况下,PL/SQL程序发出了一个数据库调用
PROGRAM_ERROR ORA_06501 PL/SQL有一个内部问题
ROWTYPE_MISMATCH ORA_06504 在一个赋值语句中涉及的宿主变量与PL/SQL CURSOR的数据类型不匹配
STORAGE_ERROR ORA_06500 PL/SQL耗光了内存或内存崩溃
SUBSCRIPT_BEYOND_COUNT ORA_06533 通过下标引用一个嵌套表或VARRAR元素时,下标数值大于集合中元素的数目
SUBSCRIPT_OUTSIDE_LIMIT ORA_06532 通过下标引用一个嵌套表或VARRAR元素时,下标数值超出合法范围(比如-1)
SYS_INVALID_ROWID ORA_01410 将一个字符串转换成通用(universal)ROWID失败,因为该字符串不能表示为一个有效的ROWID
TIMEOUT_ON_RESOURCE ORA_00051 当ORACLE服务器等待资源期间发生超时
TOO_MANY_ROWS ORA_01422 单行查询返回多行数据
VALUE_ERROR ORA_06502 发生算数、转换、截断或大小限制的错误
ZERO_DIVIDE ORA_01476 试图除以0
/*
  如果不进行异常处理,会直接报错,极其不友好
  进行异常处理后,发生异常时执行异常段中的代码,不会报错
*/
DECLARE
  exp_record example%ROWTYPE;
BEGIN

  --这里有异常, example表中不止一行数据
  SELECT * 
  INTO exp_record
  FROM example;
  
  --异常处理
  EXCEPTION 
    --当碰到单行查询返回多行数据的异常时,执行以下输出
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('单行查询返回多行数据');

END;
/

非预定义的ORACLE服务器错误

  1. 声明异常;
异常名 EXCEPTION;
  1. 将声明的异常与ORACLE服务器的错误号码关联起来;
PRAGMA EXCEPTION_INIT(已经声明的异常名,标准ORACLE错误号码);
/*
  父表与子表通过外键关联,如果直接删除父表中在子表中存在的记录,则会报错,
  因为必须先删除子表的记录才能删除附表的记录
*/
CREATE TABLE example_parent(
  exp_id NUMBER PRIMARY KEY,
  exp_name VARCHAR2(200)
);

CREATE TABLE example_sub(
  exp_id NUMBER REFERENCES example_parent(exp_id),
  exp_name VARCHAR2(200)
);

INSERT INTO example_parent VALUES (1,'样例1');
INSERT INTO example_parent VALUES (2,'样例2');
INSERT INTO example_sub VALUES (1,'样例1');
INSERT INTO example_sub VALUES (2,'样例2');
COMMIT;

--会报错
DELETE FROM example_parent WHERE exp_id = 1;

-- -02292是上述说明的错误的标准ORACLE错误号码
--不会报错,删除也不会执行,会输出错误信息
DECLARE
  e_exp_deleting EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_exp_deleting,-02292);
BEGIN

  DELETE FROM example_parent WHERE exp_id = 1;
  
  COMMIT;
  
  EXCEPTION 
    WHEN e_exp_deleting THEN
      ROLLBACK;
      dbms_output.put_line('删除失败,请先删除子表的记录');

END;
/

SQLCODE和SQLERRM

  1. SQLCODE:为错误代码返回一个数值;
  2. SQLERRM:返回字符串数据,它包含了与错误号相关的错误信息;
  3. SQLCODE的取值和每种值的具体含义:
SQLCODE的值 描述
0 没有遇到异常
1 用户定义的异常
+100 NO_DATA_FOUND异常
负数 其他的ORACLE服务器错误号码
DECLARE
  e_exp_deleting EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_exp_deleting,-02292);
BEGIN

  DELETE FROM example_parent WHERE exp_id = 1;
  
  COMMIT;
  
  EXCEPTION 
    WHEN e_exp_deleting THEN
      ROLLBACK;
      dbms_output.put_line('删除失败,请先删除子表的记录');
      dbms_output.put_line(SQLCODE);
      dbms_output.put_line(SQLERRM);

END;
/

PL(Procedural Language)/SQL程序设计语言

用户定义的异常

  1. 在一个PL/SQL程序块的声明段中声明一个用户定义的异常;
  2. 使用RAISE语句显式地跑出这个异常;
  3. 在EXCEPTION段处理这个异常;
/*
  example_parent 表中不存在 exp_id = 3 的数据行,更新时肯定会报错
  我们自定义一个这样的异常,然后捕获
*/
DECLARE
  --声明异常
  e_exp_updating EXCEPTION;
BEGIN

  UPDATE example_parent 
  SET 
    exp_name = '样例3'
  WHERE exp_id = 3;
  
  --抛出异常
  IF SQL%NOTFOUND THEN
    RAISE e_exp_updating;
  END IF;
  
  COMMIT;
  
  --捕获异常
  EXCEPTION 
    WHEN e_exp_updating THEN
      ROLLBACK;
      dbms_output.put_line('该样例不存在!无法更新');
      dbms_output.put_line(SQLCODE);
      dbms_output.put_line(SQLERRM);

END;
/
  1. 异常处理一定是在BEGIN … END;块中的。

RAISE_APPLICATION_ERROR过程

raise_application_error (error_number,message[,TRUE | FALSE]); 
  1. 使用PL/SQL提供的RAISE_APPLICATION_ERROR过程与一种与预定义异常的显示格式一样的方式返回一个非标准的错误代码和错误信息(用户定义的错误代码和错误信息);
  2. error_number:是一个用户说明的异常号码,范围只能是-20000~-20999;
  3. message:用户定义的异常信息,是一个字符串,最大长度是2048个字节;
  4. TRUE | FALSE:是一个可选的布尔参数,如果是TRUE,这个错误被放在之前错误层之上,如果是FALSE,默认为FALSE,这个错误取代之前所有错误;
BEGIN

  UPDATE example_parent 
  SET 
    exp_name = '样例3'
  WHERE exp_id = 3;
  
  --也可以自定义异常,在捕获异常的时候调用 raise_application_error 过程
  --抛出异常
  IF SQL%NOTFOUND THEN
    raise_application_error(-20200,'该样例不存在!无法更新');
  END IF;
  
  COMMIT;

END;
/

PL(Procedural Language)/SQL程序设计语言

模块化程序设计

存储过程

CREATE [OR REPLACE] PROCEDURE 过程名 [(
  参数1 [方式] 数据类型1 [DEFAULT 默认值 | := 默认值],
  参数2 [方式] 数据类型2 [DEFAULT 默认值 | := 默认值],
  ...
)]
IS | AS
  [本地声明的变量;...]
BEGIN
  --执行的操作;
END [过程名];
  1. OR REPLACE 只如果过程已经存在,它将被删除,并被由语句创建的新版本所替代。但是REPLACE并不取消任何与该过程相关的权限;
  2. 参数1或参数2表示参数的名字;
  3. 方式包括 IN(默认)、OUT 或 IN OUT;
  4. 数据类型1或数据类型2表示参数的数据类型,但是 没有精度
IN OUT IN OUT
默认方式(mode) 必须说明 必须说明
将值传递给子程序 返回给调用程序 将值传递给子程序;返回给调用程序
形式参数如同一个常量一样 初始化的变量 初始化的变量
实参可以是一个文字、表达式、常量或初始化的变量 必须是一个变量 必须是一个变量
可以赋予一个默认值 不能赋予默认值 不能赋予默认值
  1. 删除存储过程;
DROP PROCEDURE 过程名;
  1. 执行存储过程;
--CALL是PL/SQL的关键字
CALL 过程名([实参1,实参2,...]);

--EXEC和EXECUTE是SQL*PLUS的执行命令,不是PL/SQL的关键字
EXEC 过程名([实参1,实参2,...])
EXECUTE 过程名([实参1,实参2,...]);
  1. 在过程中调用过程,直接使用过程名加参数,不需要EXECUTE或者CALL;
  2. 最好能在存储过程中进行异常处理;
--不带参数的存储过程
--创建一个存储过程删除example表中的所有记录
CREATE OR REPLACE PROCEDURE delete_example
AS
BEGIN
  DELETE FROM example;
  COMMIT;
  
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
END;
/
--执行delete_example
CALL delete_example();
--表被清空
SELECT * FROM example;

--带输入参数的存储过程
--创建一个存储过程更新example表中的指定列
CREATE OR REPLACE PROCEDURE update_example(
  example_id example.example_id%TYPE,
  example_name example.example_name%TYPE
)
AS
  --声明变量,因为参数与列名相同,避免冲突
  p_example_id example.example_id%TYPE := example_id;
  p_example_name example.example_name%TYPE := example_name;
BEGIN
  
  --做更新操作
  UPDATE example 
  SET
    example_name = p_example_name
  WHERE example_id = p_example_id;
  --正常执行到这里,提交
  COMMIT;
  
  --更新中出现异常,则回滚
  EXCEPTION WHEN OTHERS THEN
    
    ROLLBACK;

END;
/
--执行update_example
CALL update_example(1,'更改后的样例1');
--example表中example_id为1的行的example_name 被更改为了 '更改后的样例1'
SELECT * FROM example;

--带输出参数的存储过程
--创建一个存储过程更新example表中的指定列,并告诉调用环境是否更新成功
CREATE OR REPLACE PROCEDURE update_example(
  example_id example.example_id%TYPE,
  example_name example.example_name%TYPE,
  is_success OUT NUMBER
)
AS
  p_example_id example.example_id%TYPE := example_id;
  p_example_name example.example_name%TYPE := example_name;
BEGIN
  
  UPDATE example 
  SET
    example_name = p_example_name
  WHERE example_id = p_example_id;
  COMMIT;
  is_success := 1;
  
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    is_success := 0;

END;
/
--调试存储过程
DECLARE
  is_success NUMBER;
BEGIN
  update_example(1,'更改后的样例1',is_success);
  dbms_output.put_line(is_success);
END;
/
--最后做了更改,并且在输出区输出了 1
SELECT * FROM example;


喜欢 (0)