文章目录
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;
/
即便自主事务处理进行了回滚操作,也不影响主事务处理
注:
自主事务处理的特征: 与主事务处理的状态无关,提交或回滚不影响主事务处理,自主事务处理的结果对其他事务是可见的,能够启用其他自主事务处理