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

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; 
/

即便自主事务处理进行了回滚操作，也不影响主事务处理

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