存储过程

语法如下：

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] < 过程声明 > <AS_OR_IS> < 模块体 >

< 过程声明 > ::= < 存储过程名定义 > [WITH ENCRYPTION][(< 参数名 >< 参数模式 >< 参数类型 > [< 默认值 表达式>] {,<参数名 >< 参数模式 >< 参数类型 > [< 默认值表达式 >] }

)][< 调用权限子句 >] <存储过程名定义 > ::=[< 模式名 >.]< 存储过程名 > <AS_OR_IS>::= AS | IS <模块体 > ::= [< 声明部分 >]

BEGIN

< 执行部分 >

[< 异常处理部分 >]

END [ 存储过程名 ]

< 声明部分 > ::=[DECLARE]< 声明定义 >{< 声明定义 >}

< 声明定义 >::=< 变量声明 >

|< 异常变量声明 >

|< 游标定义 >

|< 子过程定义 >

|< 子函数定义 >;

< 执行部分 >::=<DMSQL 程序语句序列 >{;<DMSQL 程序语句序列 >}

<DMSQL 程序语句序列 > ::= [< 标号说明 >]<DMSQL 程序语句 >;

< 标号说明 >::=<<< 标号名 >>>

<DMSQL 程序语句 >::=<SQL 语句 >|< 控制语句 >

< 异常处理部分 >::=EXCEPTION< 异常处理语句 >{;< 异常处理语句 >}

这段格式主要描述了数据库存储过程的结构:

• CREATE PROCEDURE 用来创建存储过程
• 过程声明部分定义了过程名、参数等信息
• AS或IS表示执行主体部分开始
• 声明部分可以定义变量、游标等
• 执行部分用BEGIN和END括起来,可以包含多个DML语句
• 异常处理部分用EXCEPTION开头,处理发生的异常

具体说明:

• CREATE语句表明是创建存储过程
• 过程名定义了名称和模式
• 参数定义了参数名、模式、类型和默认值
• 声明部分可以定义要用到的变量等
• 执行部分使用BEGIN END包裹DML语句
• 异常部分用来捕获可能抛出的异常

这段格式主要描述了数据库函数的结构:

• CREATE FUNCTION用来创建函数
• 函数声明定义了函数名、参数和返回类型等信息
• AS或IS表示执行主体部分开始
• 声明部分可以定义变量、游标等
• 执行部分用BEGIN和END括起来,必须返回值
• 异常处理部分用EXCEPTION开头,处理异常

具体说明:

• 定义了函数名、模式、参数、返回类型
• 参数定义包含名、模式、类型和默认值
• 声明部分可以定义变量
• 执行部分必须包含返回语句
• 异常部分用来捕获异常
• 必须返回一个值

和存储过程相比,函数的主要特点是:

• 必须包含返回值定义
• 执行部分内只能包含一个返回语句
• 可以在SQL语句中直接调用

三、存储过程、函数实战

存储过程

--定义存储过程
CREATE OR REPLACE PROCEDURE insert_and_process
IS
BEGIN
  --创建表
//CREATE TABLE test_table(id NUMBER PRIMARY KEY,value1 char(10));

  --插入1-1000的偶数数据
  FOR i IN 1..1000 LOOP
    IF MOD(i,2) = 0 THEN
      INSERT INTO test_table VALUES(i,'test');
    END IF;
  END LOOP;
  select * from test_table;
  --查询id>500的行,输出
  FOR r IN (SELECT * FROM test_table WHERE id > 500) LOOP
    print(r.id||'-'||r.value1);
  END LOOP;

  --修改id加1
  UPDATE test_table SET id = id + 1;
  select * from test_table;
  --删除id>800的行
  DELETE FROM test_table WHERE id > 800;
  select * from test_table WHERE id >= 800;
  //DROP TABLE test_table;

END;
/
EXECUTE insert_and_process;

EXECUTE insert_and_process;
--执行存储过程

或者

BEGIN
  insert_and_process;
END;
/

这段存储过程的功能如下：

1. 首先，通过 CREATE OR REPLACE PROCEDURE 创建了一个名为 insert_and_process 的存储过程。

2. 在存储过程的主体部分，首先注释掉了一个创建表的语句 CREATE TABLE test_table(id NUMBER PRIMARY KEY,value1 char(10));。这可能是由于表 test_table 已经存在，因此注释掉了该语句。

3. 使用循环插入了 1 到 1000 之间的偶数数据到表 test_table 中。只有当 i 为偶数时才执行插入操作。

4. 使用 SELECT * FROM test_table 查询表 test_table 的所有数据，并将其输出。

5. 使用游标 FOR r IN (SELECT * FROM test_table WHERE id > 500) 查询表 test_table 中 id 大于 500 的行，并使用 print 函数输出每行的 id 和 value1 值。

6. 使用 UPDATE 语句将表 test_table 中的所有 id 加 1。

7. 再次使用 SELECT * FROM test_table 查询表 test_table 的所有数据，并将其输出。

8. 使用 DELETE FROM test_table WHERE id > 800 删除表 test_table 中 id 大于 800 的行。

9. 使用 SELECT * FROM test_table WHERE id >= 800 查询表 test_table 中 id 大于等于 800 的行，并将其输出。

10. 注释掉了一个删除表的语句 DROP TABLE test_table;，可能是由于不希望在执行存储过程后删除表。

11. 最后，通过 EXECUTE insert_and_process; 执行了名为 insert_and_process 的存储过程。

这个存储过程的功能是创建一个表 test_table，插入偶数数据，查询并输出满足条件的行，修改 id 列的值，删除满足条件的行，并输出相应的结果。

实现结果如图：

可以看到，一个存储过程可以实现很多条sql群，这让数据库sql语言的程序设计变得可扩展性非常高，所以学习存储过程是非常有必要的

函数：

CREATE OR REPLACE FUNCTION CountRowsWithCondition(condition_value IN NUMBER) RETURN NUMBER
IS
  row_count NUMBER := 0;
BEGIN
  SELECT COUNT(*) INTO row_count
  FROM test_table
  WHERE id = condition_value;

  RETURN row_count;
END;
/

这段函数的sql定义了一个CountRowsWithCondition的函数,用于统计符合条件的行数:

• CREATE OR REPLACE FUNCTION:定义或替换函数
• CountRowsWithCondition:函数名
• condition_value IN NUMBER:参数,输入类型为NUMBER
• RETURN NUMBER:函数返回值类型为NUMBER
• IS开始声明部分
• row_count NUMBER := 0:定义计数器变量
• BEGIN开始执行部分
• 使用SELECT COUNT(*)查询test_table表中id等于condition_value的记录数
• 将统计结果赋值给row_count变量
• RETURN row_count:返回统计结果
• END结束函数
• /结束定义

这个函数具有以下功能:

1. 接收NUMBER类型的参数condition_value
   2.查询test_table表中的id等于condition_value的记录数
   3.将查询结果保存到row_count变量中
   4.返回row_count变量,也就是符合条件的记录数

通过这个函数,我们可以方便地统计指定条件下的行数,提供了简单的接口。

执行调用函数：

查询test_table表中id为101的行有多少条

DECLARE
  condition_value NUMBER := 101;
  row_count NUMBER;
BEGIN
  row_count := CountRowsWithCondition(condition_value);
  print(condition_value || '值有: ' || row_count||'条');
  
END;
/

执行结果：