【博客文章2026】MySQL 9.x数据库管理技巧10：存储过程、函数和异常处理---与Oracle数据库作对比
Author: Bo Tang
MySQL数据库的编程语言是SQL/PSM (Persistent Stored Modules)。它轻量级、简洁易用，无包概念，每个过程/函数独立存储。它适用于Web应用、中小型系统、OLTP。函数不支持递归，过程默认禁用递归。 Oracle数据库的编程语言是PL/SQL。它企业级、功能全面，支持包(Package)组织多个子程序复合体。它适用复杂业务逻辑、数据仓库、大型系统。函数和过程都支持递归。
1. MySQL数据库的存储过程与Oracle数据库的存储过程作对比：
1.1 在MySQL 9.6数据库中创建一个存储过程，用于计算world_y数据库中的各个表的行数：
[root@station95 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 9.6.0 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use world_y; mysql> delimiter // mysql> create procedure row_count() -> begin -> select 'How many rows does the table city have : ', count(*) from city; -> select 'How many rows does the table country have : ', count(*) from country; -> select 'How many rows does the table countrylanguage have : ', count(*) from countrylanguage; -> end// Query OK, 0 rows affected (0.003 sec)
mysql> delimiter ; |
使用“show create procedure”命令来查看MySQL数据库里的该存储过程的DDL信息：
mysql> show create procedure row_count\G *************************** 1. row *************************** Procedure: row_count sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `row_count`() begin select 'How many rows does the table city have : ', count(*) from city; select 'How many rows does the table country have : ', count(*) from country; select 'How many rows does the table countrylanguage have : ', count(*) from countrylanguage; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.001 sec) |
使用“show procedure status like”命令来查看MySQL数据库里的该存储过程的状态信息：
mysql> show procedure status like 'row_count%'\G *************************** 1. row *************************** Db: world_y Name: row_count Type: PROCEDURE Language: SQL Definer: root@localhost Modified: 2026-04-06 19:49:35 Created: 2026-04-06 19:49:35 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.004 sec) |
在information_schema方案下，查看MySQL数据库里的该存储过程的数据字典信息：
mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select * from routines where specific_name='row_count'\G *************************** 1. row *************************** SPECIFIC_NAME: row_count ROUTINE_CATALOG: def ROUTINE_SCHEMA: world_y ROUTINE_NAME: row_count ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin select 'How many rows does the table city have : ', count(*) from city; select 'How many rows does the table country have : ', count(*) from country; select 'How many rows does the table countrylanguage have : ', count(*) from countrylanguage; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: SQL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2026-04-06 19:49:35 LAST_ALTERED: 2026-04-06 19:49:35 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.003 sec) |
在MySQL数据库里执行该存储过程：
mysql> use world_y; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> call row_count(); +-------------------------------------------+----------+ | How many rows does the table city have : | count(*) | +-------------------------------------------+----------+ | How many rows does the table city have : | 4074 | +-------------------------------------------+----------+ 1 row in set (0.002 sec)
+----------------------------------------------+----------+ | How many rows does the table country have : | count(*) | +----------------------------------------------+----------+ | How many rows does the table country have : | 239 | +----------------------------------------------+----------+ 1 row in set (0.003 sec)
+------------------------------------------------------+----------+ | How many rows does the table countrylanguage have : | count(*) | +------------------------------------------------------+----------+ | How many rows does the table countrylanguage have : | 984 | +------------------------------------------------------+----------+ 1 row in set (0.004 sec)
Query OK, 0 rows affected (0.004 sec) |
1.2 在Oracle数据库（19.3.0）中创建一个存储过程，用于计算各个表的行数： 由于在上一篇博客中已将数据从MySQL数据库导出并导入Oracle数据库，所以我们可以在Oracle数据库作如下对比实验，创建功能相同的存储过程，用于计算并显示各个表的行数：
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 6 20:05:29 2026 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn hr/oracle_4U Connected. SQL> create or replace procedure row_count 2 is 3 v_city_count number; 4 v_country_count number; 5 v_countrylan_count number; 6 begin 7 select count(*) into v_city_count from city; 8 select count(*) into v_country_count from country; 9 select count(*) into v_countrylan_count from countrylanguage; 10 dbms_output.put_line('How many rows does the table city have: '||v_city_count||'.'); 11 dbms_output.put_line('How many rows does the table country have: '||v_country_count||'.'); 12 dbms_output.put_line('How many rows does the table countrylanguage have: '||v_countrylan_count||'.'); 13 end; 14 /
Procedure created. |
使用“dbms_metadata.get_ddl”函数来查看Oracle数据库里的该存储过程的DDL信息：
SQL> conn / as sysdba Connected. SQL> set long 1000 SQL> set pagesize 1000 SQL> select dbms_metadata.get_ddl(OBJECT_TYPE=>'PROCEDURE',NAME=>'ROW_COUNT',SCHEMA=>'HR') from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'PROCEDURE',NAME=>'ROW_COUNT',SCHEMA=>'HR') --------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."ROW_COUNT" is v_city_count number; v_country_count number; v_countrylan_count number; begin select count(*) into v_city_count from city; select count(*) into v_country_count from country; select count(*) into v_countrylan_count from countrylanguage; dbms_output.put_line('How many rows does the table city have: '||v_city_count|| '.'); dbms_output.put_line('How many rows does the table country have: '||v_country_c ount||'.'); dbms_output.put_line('How many rows does the table countrylanguage have: '||v_c ountrylan_count||'.'); end; |
使用dba_objects来查看Oracle数据库里的该存储过程的状态信息：
SQL> conn / as sysdba Connected. SQL> desc dba_objects; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER SQL> col owner format a5 trunc SQL> col object_name format a20 trunc SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS from dba_objects where owner='HR' and object_name='ROW_COUNT';
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_ STATUS ----- -------------------- ----------------------- --------- --------- ------- HR ROW_COUNT PROCEDURE 07-APR-26 07-APR-26 VALID |
使用dba_source来查看Oracle数据库里的该存储过程的数据字典信息：
SQL> conn / as sysdba Connected. SQL> desc dba_source; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) NAME VARCHAR2(128) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) ORIGIN_CON_ID NUMBER
SQL> col text format a120 trunc SQL> col name format a20 trunc SQL> set linesize 200 SQL> set pagesize 1000 SQL> select * from dba_source where owner='HR' and name='ROW_COUNT';
OWNER NAME TYPE LINE TEXT ORIGIN_CON_ID ----- -------------------- ------------ ---------- ------------------------------------------------------------------------------------------------------------------------ ------------- HR ROW_COUNT PROCEDURE 1 procedure row_count 0 HR ROW_COUNT PROCEDURE 2 is 0 HR ROW_COUNT PROCEDURE 3 v_city_count number; 0 HR ROW_COUNT PROCEDURE 4 v_country_count number; 0 HR ROW_COUNT PROCEDURE 5 v_countrylan_count number; 0 HR ROW_COUNT PROCEDURE 6 begin 0 HR ROW_COUNT PROCEDURE 7 select count(*) into v_city_count from city; 0 HR ROW_COUNT PROCEDURE 8 select count(*) into v_country_count from country; 0 HR ROW_COUNT PROCEDURE 9 select count(*) into v_countrylan_count from countrylanguage; 0 HR ROW_COUNT PROCEDURE 10 dbms_output.put_line('How many rows does the table city have: '||v_city_count||'.'); 0 HR ROW_COUNT PROCEDURE 11 dbms_output.put_line('How many rows does the table country have: '||v_country_count||'.'); 0 HR ROW_COUNT PROCEDURE 12 dbms_output.put_line('How many rows does the table countrylanguage have: '||v_countrylan_count||'.'); 0 HR ROW_COUNT PROCEDURE 13 end; 0
13 rows selected. |
在Oracle数据库里执行该存储过程：
SQL> conn hr/oracle_4U Connected. SQL> set serveroutput on SQL> exec row_count; How many rows does the table city have: 4074. How many rows does the table country have: 239. How many rows does the table countrylanguage have: 984.
PL/SQL procedure successfully completed. |
2. MySQL数据库的函数与Oracle数据库的函数作对比：
2.1 在MySQL 9.6数据库中创建一个函数，根据输入的：理财投资金额（单位为万元）、投资天数和收益金额（单位为元）这3个参数来计算理财投资的年化收益率： 如果投资1万元，假设年化收益率为1%，投资天数为1年，那么收益金额就是100元，则每天的收益就是100/365=0.2739元。根据以上计算，可以得出任意理财投资金额（单位为万元）、任意投资天数的理财投资的年化收益率为： 理财投资的年化收益率=收益金额（单位为元）/(理财投资金额（单位为万元）*投资天数*0.2739) 注意：与Oracle数据库的PL/SQL语法要求不同，向MySQL函数传递的参数必须指定精度。
[root@station95 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 9.6.0 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use world_y; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> delimiter // mysql> create function annualized_rate (p_amount float(10,2),p_days int,p_return float(8,2)) -> returns float(4,2) -> no sql -> begin -> declare v_annualized_rate float(4,2) default 0; -> set v_annualized_rate=p_return/(p_amount*p_days*0.2739); -> return v_annualized_rate; -> end// Query OK, 0 rows affected, 4 warnings (0.003 sec)
mysql> delimiter ; |
使用“show create function”命令来查看MySQL数据库里的该函数的DDL信息：
mysql> show create function annualized_rate\G *************************** 1. row *************************** Function: annualized_rate sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `annualized_rate`(p_amount float(10,2),p_days int,p_return float(8,2)) RETURNS float(4,2) NO SQL begin declare v_annualized_rate float(4,2) default 0; set v_annualized_rate=p_return/(p_amount*p_days*0.2739); return v_annualized_rate; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.001 sec) |
使用“show function status like”命令来查看MySQL数据库里的该函数的状态信息：
mysql> show function status like 'annualized_rate'\G *************************** 1. row *************************** Db: world_y Name: annualized_rate Type: FUNCTION Language: SQL Definer: root@localhost Modified: 2026-04-07 04:21:16 Created: 2026-04-07 04:21:16 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.004 sec) |
在information_schema方案下，查看MySQL数据库里的该函数的数据字典信息：
mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select * from routines where specific_name='annualized_rate'\G *************************** 1. row *************************** SPECIFIC_NAME: annualized_rate ROUTINE_CATALOG: def ROUTINE_SCHEMA: world_y ROUTINE_NAME: annualized_rate ROUTINE_TYPE: FUNCTION DATA_TYPE: float CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 4 NUMERIC_SCALE: 2 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: float(4,2) ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin declare v_annualized_rate float(4,2) default 0; set v_annualized_rate=p_return/(p_amount*p_days*0.2739); return v_annualized_rate; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: SQL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: NO SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2026-04-07 04:21:16 LAST_ALTERED: 2026-04-07 04:21:16 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.004 sec) |
在MySQL数据库里该执行函数： 投资150万5千元，投资天数为180天，收益金额为25703.56元，计算年化收益率：
mysql> use world_y; Database changed mysql> select annualized_rate(150.5,180,25703.56); +-------------------------------------+ | annualized_rate(150.5,180,25703.56) | +-------------------------------------+ | 3.46 | +-------------------------------------+ 1 row in set (0.002 sec) |
在当前的投资市场中，年化收益率为3.46是一个不错的收益率。 2.2 在Oracle数据库（19.3.0）中创建一个函数，根据输入的：理财投资金额（单位为万元）、投资天数和收益金额（单位为元）这3个参数来计算理财投资的年化收益率： 由于在上一篇博客中已将数据从MySQL数据库导出并导入Oracle数据库，所以我们可以在Oracle数据库作如下对比实验，创建功能相同的函数： 注意：Oracle数据库的PL/SQL语法要求：向函数传递的任何参数都不能指定精度。 [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 7 18:46:13 2026 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn hr/oracle_4U Connected. SQL> create or replace function annualized_rate(p_amount float, p_days int, p_return float) 2 return float 3 is 4 v_annualized_rate float default 0; 5 begin 6 v_annualized_rate := round(p_return/(p_amount*p_days*0.2739),2); 7 return v_annualized_rate; 8 end; 9 /
Function created. |
使用“dbms_metadata.get_ddl”函数来查看Oracle数据库里的该函数的DDL信息：
SQL> conn / as sysdba Connected. SQL> set long 1000 SQL> set pagesize 1000 SQL> select dbms_metadata.get_ddl(OBJECT_TYPE=>'FUNCTION',NAME=>'ANNUALIZED_RATE',SCHEMA=>'HR') from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'FUNCTION',NAME=>'ANNUALIZED_RATE',SCHEMA=>'H --------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE FUNCTION "HR"."ANNUALIZED_RATE" (p_amount float, p_days int, p_return float) return float is v_annualized_rate float default 0; begin v_annualized_rate := round(p_return/(p_amount*p_days*0.2739),2); return v_annualized_rate; end; |
使用dba_objects来查看Oracle数据库里的该函数的状态信息：
SQL> conn / as sysdba Connected. SQL> desc dba_objects; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER
SQL> col owner format a5 trunc SQL> col object_name format a20 trunc SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS from dba_objects where owner='HR' and object_name='ANNUALIZED_RATE';
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_ STATUS ----- -------------------- ----------------------- --------- --------- ------- HR ANNUALIZED_RATE FUNCTION 07-APR-26 07-APR-26 VALID |
使用dba_source来查看Oracle数据库里的该函数的数据字典信息：
SQL> conn / as sysdba Connected. SQL> desc dba_source; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) NAME VARCHAR2(128) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) ORIGIN_CON_ID NUMBER
SQL> col text format a120 trunc SQL> col name format a20 trunc SQL> set linesize 200 SQL> set pagesize 1000 SQL> select * from dba_source where owner='HR' and name='ANNUALIZED_RATE'; OWNER NAME TYPE LINE TEXT ORIGIN_CON_ID ----- -------------------- ------------ ---------- ------------------------------------------------------------------------------------------------------------------------ ------------- HR ANNUALIZED_RATE FUNCTION 1 function annualized_rate(p_amount float, p_days int, p_return float) 0 HR ANNUALIZED_RATE FUNCTION 2 return float 0 HR ANNUALIZED_RATE FUNCTION 3 is 0 HR ANNUALIZED_RATE FUNCTION 4 v_annualized_rate float default 0; 0 HR ANNUALIZED_RATE FUNCTION 5 begin 0 HR ANNUALIZED_RATE FUNCTION 6 v_annualized_rate := round(p_return/(p_amount*p_days*0.2739),2); 0 HR ANNUALIZED_RATE FUNCTION 7 return v_annualized_rate; 0 HR ANNUALIZED_RATE FUNCTION 8 end; 0
8 rows selected. |
在Oracle数据库里执行该函数：
SQL> conn hr/oracle_4U Connected. SQL> select annualized_rate(150.5,180,25703.56) from dual;
ANNUALIZED_RATE(150.5,180,25703.56) ----------------------------------- 3.46 |
3. MySQL数据库编程语言的异常处理与Oracle数据库编程语言的异常处理作对比：
MySQL数据库编程语言所预定义异常较少（SQLEXCEPTION, SQLWARNING, NOT FOUND），比如：无NO_DATA_FOUND，需用FOUND_ROWS()或游标的NOT FOUND handler。异常处理器需用DECLARE HANDLER声明。不支持自定义异常。 Oracle数据库编程语言所预定义异常丰富（NO_DATA_FOUND, TOO_MANY_ROWS等）。异常处理器无需声明，直接使用。支持自定义异常，需RAISE抛出。 3.1 MySQL数据库编程语言的异常处理： 3.1.1 SQL退出异常处理：
mysql> delimiter // mysql> create procedure test() -> begin -> DECLARE EXIT HANDLER FOR SQLEXCEPTION -> begin -> rollback; -> SELECT 'SQL Error occurred.' as message; -> end; -> select a from city; -> end// Query OK, 0 rows affected (0.003 sec)
mysql> delimiter ; mysql> call test; +---------------------+ | message | +---------------------+ | SQL Error occurred. | +---------------------+ 1 row in set (0.001 sec)
Query OK, 0 rows affected (0.001 sec) |
3.1.2 游标的NOT FOUND handler异常处理： 如果不进行游标的NOT FOUND handler异常处理，那么游标取数据到最后一行后，由于取不到下一行数据，将会报错：
mysql> delimiter // mysql> CREATE PROCEDURE test2() -> BEGIN -> declare v_id int; -> DECLARE city_cursor CURSOR FOR -> SELECT id FROM city; -> -> OPEN city_cursor; -> SET @done = 0; -> read_loop: LOOP -> -> FETCH city_cursor INTO v_id; -> IF @done = 1 THEN -> LEAVE read_loop; -> END IF; -> -> END LOOP read_loop; -> CLOSE city_cursor; -> end// Query OK, 0 rows affected (0.003 sec)
mysql> delimiter ; mysql> call test2(); ERROR 1329 (02000): No data - zero rows fetched, selected, or processed |
进行游标的NOT FOUND handler异常处理：
mysql> drop procedure test2; Query OK, 0 rows affected (0.003 sec)
mysql> delimiter // mysql> CREATE PROCEDURE test2() -> BEGIN -> declare v_id int; -> DECLARE city_cursor CURSOR FOR -> SELECT id FROM city; -> DECLARE CONTINUE HANDLER FOR NOT FOUND -> SET @done = 1; -> OPEN city_cursor; -> SET @done = 0; -> read_loop: LOOP -> -> FETCH city_cursor INTO v_id; -> IF @done = 1 THEN -> LEAVE read_loop; -> END IF; -> -> END LOOP read_loop; -> CLOSE city_cursor; -> end// Query OK, 0 rows affected (0.002 sec)
mysql> delimiter ; mysql> call test2(); Query OK, 0 rows affected (0.025 sec) |
3.2 Oracle数据库编程语言的异常处理： 3.2.1 Oracle数据库可以自定义异常处理器，还可以把自定义的异常处理器与Oracle的标准错误号相联系：
SQL> create or replace procedure test3 2 is 3 v_id city.id%type; 4 v_a int; 5 cursor city_cursor is SELECT id FROM city; 6 e_custom EXCEPTION; 7 PRAGMA EXCEPTION_INIT( e_custom,-06502); 8 BEGIN 9 select 'a' into v_a from dual; 10 open city_cursor; 11 loop 12 fetch city_cursor into v_id; 13 exit when city_cursor%notfound; 14 end loop; 15 close city_cursor; 16 EXCEPTION 17 when e_custom then 18 dbms_output.put_line('Character to number conversion error.'); 19 END; 20 /
Procedure created.
SQL> set serveroutput on SQL> exec test3; Character to number conversion error.
PL/SQL procedure successfully completed. |
3.2.2 Oracle数据库有很多预定义的异常处理场景：
SQL> create or replace procedure test4 2 is 3 v_a int; 4 begin 5 select id into v_a from city; 6 exception 7 when too_many_rows then 8 dbms_output.put_line('More rows.'); 9 end; 10 /
Procedure created.
SQL> set serveroutput on SQL> exec test4; More rows.
PL/SQL procedure successfully completed. |
3.2.3 Oracle数据库甚至还可以在执行体中直接抛出异常，错误号可以-20000到-20999之间自己定义：
SQL> create or replace procedure test5 2 is 3 begin 4 raise_application_error(-20004,'No error is error.'); 5 end; 6 /
Procedure created.
SQL> exec test5; BEGIN test5; END;
* ERROR at line 1: ORA-20004: No error is error. ORA-06512: at "HR.TEST5", line 4 ORA-06512: at line 1 |
如果要处理这个异常，就使用3.2.1中的方法：
SQL> create or replace procedure test6 2 is 3 e_custom EXCEPTION; 4 PRAGMA EXCEPTION_INIT( e_custom,-20004); 5 begin 6 raise_application_error(-20004,'No error is error.'); 7 exception 8 when e_custom then 9 dbms_output.put_line('Not an error.'); 10 end; 11 /
Procedure created.
SQL> set serveroutput on SQL> exec test6; Not an error.
PL/SQL procedure successfully completed. |
|
