Oracle初学者笔记(十四)--子程序和程序包
以前我们写的PL/SQL语句程序多是瞬时的，没命名的；
现在我们把命名的PL/SQL块叫做子程序；
子程序
结构没什么区别：声明，执行，异常；但声明也是必须的；
优点：模块化；可重用性；可维护性；
类型：1，过程，用于执行某项操作；
2，函数，用于执行某项操作并返回值；
过程
使用create procedure语句创建
语法：create or replace procedure <proc_name>
[parameter list]
Is|as
<local declarations>;
Begin
(executable statements)
End;
这里的is|as就相当于declare;
除了拥有前面的一个过程声明语句外，其他和以前的PL/SQL一样；
参数模式：
In 接受值，默认值；
Out 将值返回给子程序的调用程序
In out 接受值并返回已更新的值
参数的书写格式：[(参数1 in|out|in out 参数类型, 参数2 in|out|in out 参数类型,…)]
创建不带参数过程的例子：
create or replace procedure xiaojiujiu
as
i integer;
j integer;
begin
dbms_output.put_line('print xiaojiujiu');
for i in 1..9 loop
for j in 1..9 loop
if i>=j then
dbms_output.put(to_char(j)||'*'||
to_char(i)||'='||to_char(i*j)||' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end;
/
创建的过程就象你创建的表一样，属于当前操作的用户，其他连接的用户将可以通过 用户名.过程名 来调用过程；数据字典是user_source; drop同样可以象删除表一样删除存储过程
注意，创建过程的时候并不会执行过程，必须在这之后调用过程来执行；
调用的方法：
1， execute procedure_name(list of parameters) 比如execute items(‘i201’);
2， 可以在匿名块中调用；比如begin items(‘i201’) end;
创建带参数过程的例子：
create or replace procedure queryEmpName(sFindNo emp.EmpNo%type)
as
sName emp.ename%type;
sJob emp.job%type;
begin
select ename,job into sName,sJob from emp
where empno=sFindNo;
dbms_output.put_line('ID is '||sFindNo||' de zhigong name is '||
sName||' gongzuo is '||sJob);
exception
when no_data_found then
dbms_output.put_line('no data');
when too_many_rows then
dbms_output.put_line('too many data');
when others then
dbms_output.put_line('error');
end;
/
所以，我们发现带参数的过程真正实现了运行的交互性；
函数
Create or replace function <fuction-name>
[parameters list]
Return datatype
is|as
…….
注意函数和过程的输入参数以及函数的返回参数的定义都不能定义精度；默认的参数模式是输入；
其实和过程完全一样；只是函数一般不会用输出参数，因为他本身就会返回数据嘛，何必慢慢地用参数返回数据呢；
例子：
create or replace function getName(sno varchar2)
return varchar
is
name varchar(12);
begin
select ename into name from emp
where empno=sno;
return name;
exception
when too_many_rows then
dbms_output.put_line('too many data');
when others then
dbms_output.put_line('error');
end;
/
调用的时候必须接受返回值：
declare
name varchar(12);
begin
name:=getname('7902');
dbms_output.put_line(name);
end;
/
或者
select getname(7369) from dual
或者
Select * from emp where ename=getname(‘7369’);
过程和函数的区别
过程作为PL/SQL语句块来执行；
函数作为表达式的一部分调用；
在规则说明中不包含return;
必须包含return;
可以返回任何值；
必须返回单个值；
可以包含return语句，但是与函数不同，不能返回值；必须包含至少一条return;
出现编译错误的时候可以show errors或者desc user_errors来调试；
创建：
调用：
我们发现必须使得输入参数和定义的顺序一致，但是也不一定要这样，可以用符号=>来乱序传入参数；
但是注意，是过程定义的参数=>调用块的值或参数而不是相反，Oracle 太不懂语言了；
自主事务:pragma autonomous_transaction;
第一个事务：
create or replace procedure p1
as
[pragma autonomous_transaction]
begin
insert into student values(105,'luweiyu','男');
rollback;
end;
/
第二个事务调用了第一个事务：
create or replace procedure p2
as
begin
update student set se='女';
p1;
end;
/
但是我们看到的是，p1中执行rollback的时候把p2中的update操作也给回滚从而结束了事务；
所以：事务是互相影响的；如果我们不希望发生这样的情况，我们引进了自主事务的做法：
在as和begin中间加入pragma autonomous_transaction;语句用于表示p1的事务是自主结束的，它将不会影响调用它的p2的事务的结束等；
程序包
程序包是模块化的数据类型，游标，子程序，变量等数据对象的集合；
包括两个部分：
1， 说明部分，可以只说明，类似接口；
a) 使用create package进行创建
b) 包含公用对象和类型
c) 声明类型，变量，常量，异常，游标和子程序
d) 可以在没有程序包主体的情况下存在
2， 主体，可以没有实现的主体部分；
a) 使用create package body
b) 包含子程序和游标的定义
c) 包含私有声明
d) 不能在没有程序包规格说明的情况下存在
开发者用只是说明的程序包定义规则，然后由别人实现，他吗的接口思想：
1， 可以使用函数和过程的纯度来限定函数和过程的主体部分的权限；
2， 函数或者可以重载
个人觉得这里的程序包更象是个类而不是前面所声明的对象类型；
程序包不能嵌套；
综合试验：
定义声明：
1， create or replace package student_package is
2， type student_cur is ref cursor return student%rowtype;
3， procedure insert_student(stu in student%rowtype);
4， procedure update_student(stu student%rowtype);
5， procedure delete_student(sno student.stuid%type);
6， procedure select_student(stucur in out student_cur);
7， function getStudentCount return number;
8， end student_package;
9， /
定义主体：
create or replace package body student_package is
procedure insert_student(stu student%rowtype) is
icount int;
begin
select count(*) into icount from student where stuid=stu.stuid;
if icount>0 then
dbms_output.put_line('insert data is already exsist');
else
insert into student values(stu.stuid,stu.stuname,stu.se);
commit;
end if;
exception
when too_many_rows then
dbms_output.put_line('insert data is already exsist');
end insert_student;
procedure update_student(stu student%rowtype) is
icount int;
begin
select count(*) into icount from student where stuid=stu.stuid;
if icount>0 then
update student set stuname=stu.stuname,se=stu.se where stuid=stu.stuid;
commit;
else
dbms_output.put_line('update data not exist!');
end if;
end update_student;
procedure delete_student(sno student.stuid%type) is
icount int;
begin
if icount>0 then
delete from student where stuid=sno;
commit;
else
dbms_output.put_line('delete data not exist');
end if;
end delete_student;
procedure select_student(stucur in out student_cur) is
begin
open stucur for select * from student;
end select_student;
function getStudentCount return number is
icount int;
begin
select count(*) into icount from student;
return icount;
end getStudentCount;
end student_package;
/
调用程序包：
declare
stu student%rowtype;
begin
stu.stuid:=1009;
stu.stuname:='tonglei';
stu.se:='f';
student_package.insert_student(stu);
end;
/
编辑：一起学习网
标签：过程,参数,函数,子程序,事务