子程序和程序包

⼦程序和程序包⼦程序
现在我们把命名的PL/SQL块叫做⼦程序；
结构没什么区别：声明，执⾏，异常；但声明也是必须的；
优点：模块化；可重⽤性；可维护性；
类型：1，过程，⽤于执⾏某项操作；
2，函数，⽤于执⾏某项操作并返回值；
过程
使⽤create procedure语句创建
语法：create or replace procedure
[parameter list]
Is|as
;
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
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
[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
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;
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;
/。