oracle 存储过程,函数,自主事务处理

本文详细介绍了PL/SQL中的子程序概念,包括存储过程和函数的定义、使用及权限管理。探讨了不同类型的参数传递模式及其应用场景,并通过具体示例展示了如何创建和调用过程与函数,还涉及了查看函数编译错误的方法以及自主事务处理的特点。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.子程序

1.1 子程序

  子程序的3个部分:声明部分、可执行部分、异常处理部分(可选)
  子程序的分类:存储过程(执行某些操作),函数(执行操作并返回结果)

过程函数
作为PL/SQL语句执行作为表达式的一部分调用
return语句不必须return语句必须
可包含return,但不能返回值必须包含至少一个return返回值

注:在过程和函数中,变量不能设置精度

1.2 过程

create [or replace] procedure 过程名 [(参数列表)]
is|as  声明变量
begin 执行语句
[exception 异常处理]
end;
1.2.1 过程参数的三种模式

  打开oracle自带的dbms_output.put_line输出,使打印的内容在界面显示需要执行:

set serverout on;
in 用于接收调用程序的值,默认的参数模式
create or replace procedure p1(i in number) 
as  a varchar2(50);
begin
a:='';
for j in 1..i loop
a:=a||'*';
dbms_output.put_line(a);
end loop; 
end;
/

在这里插入图片描述

out 用于向调用程序返回值,使用PL/SQL程序段调用

sqlplus中只能调用in类型参数的存储过程

create or replace procedure p2(j out int) 
is  
	begin
	 j:=100;
	dbms_output.put_line(j);
	end;
/
declare k number;
begin 
	p2(k);
end;
/

在这里插入图片描述

exec dbms_output.put_line('你好')

在这里插入图片描述

in out 用于接收调用程序的值,并向调用程序返回更新的值
create or replace procedure p3(p1 in out number,p2 in out number) 
is tmp number;
	begin
	tmp:=p1;
	p1:=p2;
	p2:=tmp;
	end;
/
declare
	num1 number :=100;
	num2 number :=50;
begin 
	p3(num1,num2);
	dbms_output.put_line('num1:'||num1||' num2:'||num2);
	end;
	/

在这里插入图片描述

1.2.2 执行存储过程语句以及授权

sqlplus中执行语句:

execute 存储过程名(参数列表);

授权:
如hr用户无法执行scott下的存储过程
在这里插入图片描述
scott用户授权hr用户执行过程

grant execute on p1 to hr;

在这里插入图片描述
hr用户执行成功
在这里插入图片描述

1.3 函数

1.3.1 定义

语法:

create [or replace] function 方法名 [(参数列表)]
	return <数据类型> is|as 变量声明
	begin 
		执行语句;
		return 结果;
		exception
		异常处理;
		end;

定义函数的限制:
  函数只接收IN参数,不接受IN OUT 和OUT参数;
  形参不能是PL/SQL类型,只能是数据库类型;
  函数的返回类型必须是数据库类型;

create or replace function fun_hello return varchar2
	is
	begin 
 	 return '智慧';
 	 end;
  	/

在这里插入图片描述

1.3.2 访问函数的两种方式:

使用PL/SQL块:

declare 
	str varchar2(20);
	begin 
	 str:=fun_hello();
	 dbms_output.put_line(str);
	  end;
	  /

在这里插入图片描述

使用sql语句:
在这里插入图片描述

如:

create table sgrade (student_no number, name varchar2(20),score number);
insert into sgrade  values (1,'cz',89);
insert into sgrade  values (2,'fd',59);
insert into sgrade  values (3,'tr',39);
insert into sgrade  values (4,'tr',79);
commit;

要求: 创建一个函数,接受用户输入的学号,得到学生的名次,并输出

create or replace function fun_grade (num int) return number
	is sn number;
		myscore numbr;
	begin 
	select score into myscore from sgrade   where student_no =num;
   select count(*) into sn from sgrade where score> myscore ;
   sn:=sn+1;
 	 return sn;
 	 end;
  	/
编译函数报错如何查看

在这里插入图片描述
查看编译错误:

 show error;

在这里插入图片描述
发现number写错了

create or replace function fun_grade (num int) return number
	is sn number;
		myscore number;
	begin 
	select score into myscore from sgrade   where student_no =num;
   select count(*) into sn from sgrade where score> myscore ;
   sn:=sn+1;
 	 return sn;
 	 end;
  	/

在这里插入图片描述

oracle排名
select m.* ,rownum 排名 from
(select * from sgrade  order by score  desc) m;

在这里插入图片描述

2.自主事务处理

  发生在调用的存储过程调用 标记子程序为自主事务处理的存储过程中;

事务处理的顺序:
  主事务处理启动独立事务处理;然后主事务处理被暂停;自主事务处理子程序内的sql操作;然后终止自主事务处理;恢复主事务处理。

PRAGMA AUTONOMOUS_TRANSACTION

如:

自主事务 中进行了回滚操作

create or replace procedure proc_tran1  
as  
  v_sal emp.sal%type;  
  pragma autonomous_transaction;
begin  
  select sal into v_sal from emp where empno=7788;  
  dbms_output.put_line('proc_tran1中7788的薪水:'||v_sal);  
  rollback; 
end;
/

主事务中进行了update操作:

create or replace procedure proc_tran2  
as  
    v_sal emp.sal%type;  
begin  
  update emp set sal=6666 where empno=7788;
  proc_tran1(); 
  commit;  
  select sal into v_sal from emp where empno=7788;  
  dbms_output.put_line('proc_tran2中显示7788的薪水:'||v_sal);  
end; 
/

即便自主事务处理进行了回滚操作,也不影响主事务处理
在这里插入图片描述

注:
   自主事务处理的特征: 与主事务处理的状态无关,提交或回滚不影响主事务处理,自主事务处理的结果对其他事务是可见的,能够启用其他自主事务处理

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

但行益事莫问前程

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值