这段格式主要描述了数据库存储过程的结构:
CREATE PROCEDURE 用来创建存储过程过程声明部分定义了过程名、参数等信息AS或IS表示执行主体部分开始声明部分可以定义变量、游标等执行部分用BEGIN和END括起来,可以包含多个DML语句异常处理部分用EXCEPTION开头,处理发生的异常具体说明:
CREATE语句表明是创建存储过程过程名定义了名称和模式参数定义了参数名、模式、类型和默认值声明部分可以定义要用到的变量等执行部分使用BEGIN END包裹DML语句异常部分用来捕获可能抛出的异常存储函数
语法如下：
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] <函数声明>
<函数声明> ::= <存储函数名定义> [WITH ENCRYPTION][FOR CALCULATE][(<参数名><参数模式><参数类型> [<默认值表达式>]{,<参数名><参数模式><参数类型> [<默认值表达式>]})]RETURN <返回数据类型> [<调用选项子句>][PIPELINED]
<存储函数名定义> ::=[<模式名>.]<存储函数名>
<调用选项子句> ::= <调用选项> {<调用选项>}
<调用选项> ::= <调用权限子句> | DETERMINISTIC
<模块体> ::= [<声明部分>]
BEGIN
<执行部分>
[<异常处理部分>]
END [存储函数名]
<声明部分> ::=[DECLARE]<声明定义>{<声明定义>}
<声明定义>::=<变量声明>
|<异常变量声明>
|<游标定义>
|<子过程定义>
|<子函数定义>;
<执行部分>::=
<标号说明>::=<<<标号名>>>
<异常处理部分>::=EXCEPTION<异常处理语句>{;<异常处理语句>}
这段格式主要描述了数据库函数的结构:
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;
/
这段存储过程的功能如下：
首先，通过 CREATE OR REPLACE PROCEDURE 创建了一个名为 insert_and_process 的存储过程。
在存储过程的主体部分，首先注释掉了一个创建表的语句 CREATE TABLE test_table(id NUMBER PRIMARY KEY,value1 char(10));。这可能是由于表 test_table 已经存在，因此注释掉了该语句。
使用循环插入了 1 到 1000 之间的偶数数据到表 test_table 中。只有当 i 为偶数时才执行插入操作。
使用 SELECT * FROM test_table 查询表 test_table 的所有数据，并将其输出。
使用游标 FOR r IN (SELECT * FROM test_table WHERE id > 500) 查询表 test_table 中 id 大于 500 的行，并使用 print 函数输出每行的 id 和 value1 值。
使用 UPDATE 语句将表 test_table 中的所有 id 加 1。
再次使用 SELECT * FROM test_table 查询表 test_table 的所有数据，并将其输出。
使用 DELETE FROM test_table WHERE id > 800 删除表 test_table 中 id 大于 800 的行。
使用 SELECT * FROM test_table WHERE id >= 800 查询表 test_table 中 id 大于等于 800 的行，并将其输出。
注释掉了一个删除表的语句 DROP TABLE test_table;，可能是由于不希望在执行存储过程后删除表。
最后，通过 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:参数,输入类型为NUMBERRETURN NUMBER:函数返回值类型为NUMBERIS开始声明部分row_count NUMBER := 0:定义计数器变量BEGIN开始执行部分使用SELECT COUNT(*)查询test_table表中id等于condition_value的记录数将统计结果赋值给row_count变量RETURN row_count:返回统计结果END结束函数/结束定义这个函数具有以下功能:
接收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;
/
执行结果：