SQL的游标cursor、自定义函数function、存储过程procedure速成

楼观剑切小麻薯

已于 2023-03-10 13:56:38 修改

阅读量792

点赞数

于 2023-03-09 14:22:37 首次发布

本文链接：https://blog.csdn.net/m0_60209100/article/details/129421946

版权

mysql 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

oracle

2 篇文章 0 订阅

订阅专栏

本文介绍了Oracle数据库中如何声明和使用SQL的游标、自定义函数以及存储过程。通过示例展示了变量、游标、异常处理、循环结构的用法，并探讨了游标在数据操作中的应用，特别是`FORUPDATE`子句在锁定数据方面的不同层次。同时，文章还讲解了自定义函数和存储过程的创建、调用及参数模式（IN,OUT,INOUT）。

摘要由CSDN通过智能技术生成

SQL的游标cursor、自定义函数function、存储过程procedure

本文使用Oracle数据库进行测试

1. declare

declare -- 声明
	-- 声明变量、游标
	-- 例如：
	ver_str in out varchar2(255); -- （1）定义一个varchar2类型的变量ver_str
	ver_empno out emp.empno%type;-- 定义一个和emp.empno一样类型的变量ver_empno
	ver_emp emp%rowtype;-- 类似于定义一个emp类型的class，名为ver_emp
	mylover constant varchar2(255):='坤坤'; -- 定义常量
	type record_emp is record(
        deptname TMP_DEPT.DEPTNAME%type,
        empno TMP_EMP.EMPNO%type,
        ename TMP_EMP.ENAME%type
    );-- 记录类型，类似于定义一个名为record_emp的实体类
    
    emp_row record_emp;-- 定义一个record_emp的实体类的变量emp_row
    
	cursor cur_emp 
	is 
		select *
		from emp; -- 定义一个遍历emp表的游标
begin -- 开始
	-- 运行代码
    -- 例如：
    ver_str:='hellworld'; -- （2）给变量赋值
	dbms_output.put_line('你好，世界'||ver_str );-- (3)打印
exception  --异常
	when others then
	--异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
	rollback;
	dbms_output.put_line(sqlcode);
	dbms_output.put_line(substr(sqlerrm, 1, 512));
end;-- 结束

PS：

- 声明变量时，请注意有长度的要定义长度，没有长度的不要定义长度。
- 类型：number、char、varchar2(255)、date等。
- in：默认值，只可传入；out：只传出值；in out：可传入可传出值。（测试见文章底）
赋值

   mylover:='坤坤';
   mylover default '坤坤';
   select ename into mylover from emp where empno = 105;

输入输出

   -- 输入
   select ename into mylover from emp where ename=&请输入坤坤;
   -- 输出
   dbms_output.put_line(mylover);

请注意分号的使用位置。

2.几个函数

-- 1.case
case 变量/不写 
	when 变量值/条件表达式
	then 语句块;
	when 变量值/条件表达式 
	then 语句块;
	…
	when 变量值/条件表达式 
	then 语句块;
    else 
    	语句块;
end case；
    
-- 2.if
IF 条件表达式
THEN
	语句块;
ELSIF 条件表达式
THEN
	语句块;
ELSE
	语句块;
END IF;

-- 3.for
for...in...loop
	语句块;
end loop;

-- 4.loop-if
loop
	if 条件表达式 then
		语句块;
		exit;
	end if;
	语句块;
end loop;
-- 或
loop
	语句块;
	exit when 条件表达式;
语句块;
end loop;

-- 5.while
while 条件表达式 loop
	语句块;
	-- 可以用 return 跳出循环
    语句块;
    end if;
end loop;

-- 6.GOTO
<<repeat_loop>> -- 循环点
	语句块;
	GOTO repeat_loop; -- 再次循环

2. 游标

类似于C++的指针，通过上下移动遍历数据，可以读写指向的数据。

declare
	-- 声明变量、游标 （1）
	cursor cur_emp -- （2）
	is 
		select *
		from emp; -- （3）
		
	ver_emp emp%rowtype;-- 类似于定义一个emp类型的class，名为ver_emp  
begin
    -- （4）
	-- 开始循环
	for ver_emp in cur_emp loop
		-- 输出数据
		dbms_output.put_line(ver_emp.empno||','||ver_emp.ename);
	end loop;
end;

PS：

该处用法同上declare，定义完游标往往需要定义一个class变量收集指针的数据。
游标参数

   cursor cur_emp(
       var_empno number，
       var_deptno number
   	) -- varchar不可以指定长度，其他类型也一样
   is 
   	select *
       from emp 
       where empno = var_empno and deptno = var_deptno;

若需要通过游标修改表，select语句后需要加上 for update。

for update 是把所有的表都锁定，for update of 根据 of 后表的条件锁定相对应的表。

单标查询使用 for update ，多表查询使用 for update of 锁定列。

   -- 建表：
   -- ----------------------------
   -- Table structure for EMP
   -- ----------------------------
   DROP TABLE "EMP";
   CREATE TABLE "EMP" (
     "EMPNO" NUMBER VISIBLE NOT NULL PRIMARY KEY,
     "ENAME" VARCHAR2(255 BYTE) VISIBLE,
     "DEPTNO" NUMBER VISIBLE,
     "SALA" NUMBER VISIBLE
   );
   
   -- ----------------------------
   -- Records of EMP
   -- ----------------------------
   INSERT INTO "EMP" VALUES ('101', '张三', '1', '1000');
   INSERT INTO "EMP" VALUES ('102', '李四', '2', '2000');
   INSERT INTO "EMP" VALUES ('103', '王五', '1', '3000');
   INSERT INTO "EMP" VALUES ('104', '赵六', '2', '4000');
   INSERT INTO "EMP" VALUES ('105', '坤坤', '1', '5000');
   
   
   -- ----------------------------
   -- Table structure for DEPT
   -- ----------------------------
   DROP TABLE "DEPT";
   CREATE TABLE "DEPT" (
     "DEPTNO" NUMBER VISIBLE NOT NULL PRIMARY KEY,
     "DEPTNAME" VARCHAR2(255 BYTE) VISIBLE
   );
   
   -- ----------------------------
   -- Records of DEPT
   -- ----------------------------
   INSERT INTO "DEPT" VALUES ('1', '唱');
   INSERT INTO "DEPT" VALUES ('2', '跳');
   INSERT INTO "DEPT" VALUES ('3', 'rap');

在这里插入图片描述

   -- 游标
   declare
   	cursor cur_emp 
   	is 
   		select * from emp for update; -- 下面的代码是修改此处
   	emp_row emp%rowtype;
   begin
   	for emp_row in cur_emp loop
   		update emp set sala = 3000 where current of cur_emp; -- 下面的代码是修改此处
   	end loop;
   end;
   
   -- 1.不锁定表
   select * from emp;
   -- 使用update时
   update emp set sala = 1000 where current of cur_emp; -- 报错。
   update emp set sala = 1000 for update -- 无论select加不加 for update 都会成功且全修改，没什么意义，不如不使用游标，下面不再测试。
   update emp set sala = 5000 where empno = 105; -- 无论select加不加 for update 都会成功，没什么意义，不如不使用游标，下面不再测试。
   
   -- 2.锁定整个表
   select * from emp for update;
   -- 使用update时
   update emp set sala = 1000 where current of cur_emp; -- 成功，全修改
   
   -- 3.锁定表的单行记录
   select * from emp where empno = 105 for update;
   -- 使用update时
   update emp set sala = 5000 where current of cur_emp; -- 成功，但只修改了坤坤的 sala
   
   -- 4.锁定表的一列，对于单标查询而言，2和4是一样的效果
   select * from emp for update of emp.sala;
   -- 使用update时
   update emp set sala = 5000 where current of cur_emp; -- 成功，全修改
   
   -- 5.锁定表的一行、一列，对于单标查询而言，3和5是一样的效果
   select * from emp where empno = 105 for update of emp.sala;
   -- 使用update时
   update emp set sala = 3000 where current of cur_emp; -- 成功，但只修改了坤坤的 sala
   
   -- 6.锁定表的其他一列
   select * from emp for update of emp.empno;
   -- 使用update时
   update emp set sala = 5000 where current of cur_emp; -- 成功，全修改
   
   -- 7.锁定两个表
   select e.*, d.deptname from emp e,dept d where e.deptno = d.deptno for update;
   -- 使用update时
   update emp set sala = 3000 where current of cur_emp; -- 成功，但是无效果，全没有修改
   
   -- 8.锁定两个表的一行
   select e.*, d.deptname from emp e,dept d where e.deptno = d.deptno and empno = 105 for update;
   -- 使用update时
   update emp set sala = 3000 where current of cur_emp; -- 成功，但是无效果，全没有修改
   
   -- 9.锁定两个表的锁定一列
   select e.*, d.deptname from emp e,dept d where e.deptno = d.deptno for update of e.sala;
   -- 使用update时
   update emp set sala = 2000 where current of cur_emp; -- 成功，全修改
   
   -- 10.锁定两个表的锁定一行、一列
   select e.*, d.deptname from emp e,dept d where e.deptno = d.deptno for update of e.sala;
   -- 使用update时
   update emp set sala = 6000 where current of cur_emp; -- 成功，但只修改了坤坤的 sala
   
   -- 11.锁定两个表的锁定其他一列
   select e.*, d.deptname from emp e,dept d where e.deptno = d.deptno for update of e.empno;
   -- 使用update时
   update emp set sala = 2000 where current of cur_emp; -- 成功，全修改

游标的开启方法：

   -- 1. 
   for ver_emp in cur_emp  -- 带参数:for ver_emp in cur_emp(105) 
   loop
   	dbms_output.put_line(ver_emp.empno||','||ver_emp.ename);
   end loop;
   -- 特点：
   -- 1.不需要手动开启\关闭游标，在for循环开始\结束时，游标自动开启\关闭。
   -- 2.不能使用 open、fetch、close，不然会报错.
   
   -- 2.
   -- 开启游标
   open cur_emp; -- 带参数：open cur_emp(105);
   -- 开始循环
   loop
   	-- 使用fetch游标，必须明确的打开和关闭，循环读取游标，并将值保存到emp_row中
   	fetch cur_emp into emp_row;	
       -- 输出数据
   	dbms_output.put_line(emp_row.empno);
   	-- 循环结束条件
   	exit when cur_emp%notfound;
   -- 结束循环
   end loop;
   -- 关闭游标
   close cur_emp ;
   -- 特点：
   -- 1.需要手动开启\关闭游标。
   -- 2.可以自定义游标循环结束条件。
   -- 3.循环结束条件
   	-- %FOUND（是否找到游标）：表示当前游标是否指向有效一行，是 -> true；否 -> false。
   	-- %NOTFOUND（是否没找到游标）：与%FOUND相反。
   	-- %ROWCOUNT（游标行数）：当前游标所在的行号，初始为0，此时可以找到游标；循环结束后，也就是找不到游标时，为最后一行的行号。
   	-- %ISOPEN（游标是否打开）
   	-- 请注意退出条件的位置，位置错会导致读取到的数据有些问题。

4. 自定义函数function

-- 函数定义
create or replace function fun_sum 
(
	-- 传入参数、可无
    a number,
    b number
)
return number -- 必须有返回值，且只能有一个
as -- 或is
	-- 定义变量
	ans number;
begin
	-- 语句块;
	ans:=a+b;
	return ans;
end;

-- 函数调用
declare
	-- 定义变量
	a number;
	b number;
begin
    a:=114;
    b:=514;
    dbms_output.put_line('a+b='||fun_sum(a,b));
end;

-- 函数删除
drop function fun_sum;

5.存储过程 procedure

CREATE OR REPLACE PROCEDURE pro_sum	
(
    -- 传入参数、可无
	a number,
    b number
) IS
	-- 这个区间可以用来定义变量和创建游标
	ans number;
BEGIN
	-- 语句块;
	ans:=a+b;
	dbms_output.put_line(ans);
	commit; -- 对上面的内容进行提交
exception -- 存储过程异常
	when others then
	-- 异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
	rollback;
	dbms_output.put_line(sqlcode);
	dbms_output.put_line(substr(sqlerrm, 1, 512));
END pro_sum	;
 
-- 调用
declare
	-- 定义变量
	a number;
	b number;
begin
    a:=114;
    b:=514;
	pro_sum(a,b);	
end;

6. FUNCTION与PROCEDURE区别

FUNCTION	PROCEDURE
默认有1个返回值，可以不返回	通过通过传入out 参数来传递需要返回的数值，可以有多个或者没有
用于特定的数据(如选择)	用于在数据库中完成特定的操作或者任务(如插入、删除)
声明用function	声明用procedure
程序头部声明要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句	程序头部声明时不需要描述返回类型
以编译后的形式存放在数据库中	以编译后的形式存放在数据库中
可以使用in/out/in out三种模式的参数	可以使用in/out/in out三种模式的参数
不能独立，执行在 PL/SQL 中必须指定变量接收返回，必须作为表达式的一部分调用	可以作为一个独立的PL/SQL语句来执行
通过return语句返回一个值，且该值要与申明部分一致，也可以是通过out类型的参数带出的变量	可以通过out/in out返回零个或者多个值
SQL语句(DML或SELECT)中可以调用函数	SQL语句(DML或SELECT)中不可调用存储过程

7. 其他一些测试

1. in、out、inout

create or replace function fun_sum 
(
    a in number,
    b out number,
	c in out number
)
return number
as
	ans number;
begin
	ans:=a+b+c;-- 由于b是out，不会传入，所以b是空值，所以ans是空值
	-- a:a + 1 这句话会报错，a是in，在这里面不能被赋值。
	b:=b + 1; -- b是out，不会传入，所以是空值
	c:=c + 1; -- 正常 +1
	dbms_output.put_line('aaa'||a); -- 1
	dbms_output.put_line('bbb'||b); -- 空
	dbms_output.put_line('ccc'||c); -- 3
	return ans;
end;

declare
	a number;
	b number;
	c number;
begin
    a:=1;
    b:=2;
	c:=3;
	dbms_output.put_line('a'||a); -- 1
	dbms_output.put_line('b'||b); -- 2
	dbms_output.put_line('c'||c); -- 3
    dbms_output.put_line('a+b+c='||fun_sum(a,b,c)); -- 空
    dbms_output.put_line('a'||a); -- 1
	dbms_output.put_line('b'||b); -- 空
	dbms_output.put_line('c'||c); -- 4
end;

累了，麻了，欢迎补充