funcation和procedure

一 存储过程与函数的区别 


1 存储过程 

定义： 
存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 
优 点： 
A 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 
B 当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句. 
C 安全性高,可设定只有某用户才具有对指定存储过程的使用权。 
D 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值，可以向程序返回错误原因。   

2 合理使用存储过程与函数 

存储过程: 参数可以有三种模式(IN、OUT、IN OUT),可返回多个参数值.可在过程中调用另一个存储过程. 
函数: 函数只有一种(IN),因为使用函数的目的是传入0或多个参数，它只有一条RETURN语句，只能返回单一的值,,也可用Out来返回值.可在SQL语句（DML或SELECT）中调用. 

Oracle中的函数与存储过程的区别: 
A:函数必须有返回值,而过程没有. 
B:函数可以单独执行.而过程必须通过execute执行. 
C:函数可以嵌入到SQL语句中执行.而过程不行. 
其实我们可以将比较复杂的查询写成函数.然后到存储过程中去调用这些函数. 
Oracle中的函数与存储过程的特点: 
A. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。 
B.对于存储过程来说可以返回参数，而函数只能返回值或者表对象。 
C.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 


3 存储过程与函数的相互使用 

示例代码: 
具体创建包及包体语法可参见链接: 
http://liuzidong.javaeye.com/admin/blogs/717050 

A 创建包 

Sql代码 

1. CREATE OR REPLACE PACKAGE davidliuzd  AS  --包头部分  
2. --声明一个函数  
3. --根据用户ID获取部门ID号  
4. FUNCTION   fun_getUserNDepartIdByUserID(UID in varchar2)return varchar2;  
5. -- 声明一个游标类型  
6. TYPE DEPARTALL_CURSOR IS REF CURSOR;  
7. --声明一个存储过程  
8. --根据用户ID获取所在部门的所有子部门信息集合  
9. PROCEDURE  proc_getDepartAllChildByUserID(UID in varchar2,dep_cur out DEPARTALL_CURSOR);  
10. END davidliuzd;--包体结束部分  

B 创建包体 

Sql代码 

1. CREATE OR REPLACE PACKAGE BODY davidliuzd  --包体部分  
2. Is  
3. --函数的具体实现  
4. FUNCTION fun_getUserNDepartIdByUserID  
5. (UID in varchar2)return varchar2  
6. Is  
7.   --返回值部门ID  
8.   RDID TDEPAR.TID%TYPE;  
9. BEGIN  
10.  --注意此处:传递进来的参数:userid与表的userid重复了,结果不对。  
11. --你要修改传递参数的名称,--将参数修改下就行了.  
12.  --select TID into RDID from TDEPART where userid=userid;--错误写法  
13. select TID into RDID from TDEPART_USER where userID=UID;  
14. END IF;  
15. return RDID;  
16. END fun_getUserNDepartIdByUserID;  

--存储过程的具体实现 

Sql代码 

1. PROCEDURE  proc_getDepartAllChildByUserID  
2. (UID,in varchar2,dep_cur out DEPARTALL_CURSOR)   
3. AS  
4. RDID TDEPAR.TID%TYPE;  
5. BEGIN  
6. --注意此处是：存储过程中调用函数,体现了函数与存储过程的相互作用.  
7.  Select fun_getUserNDepartIdByUserID(UID) into RDID from dual;  
8. --递归查询部门下所有子部门  
9. open dep_cur for   
10. select * from TDEPART connect by DID=prior  PARDID start with DID= RDID   
11. order by departmentID desc;  
12. END proc_getDepartAllChildByUserID;  
13. END davidliuzd;--包体结束  
14. --在使用动态拼接 SQL中需要注意的地方:  
15. --示例:  
16. PROCEDURE proc_test(message out varchar2,uid in varchar2,uname in varchar2, nmr in varchar2)  
17. IS  
18. sqlstr varchar2(2000);  
19. BEGIN  
20. --正常写法  
21. update 表名 set username=uname ,tag='1' where userid=uid and number like ‘%nmr%’;  
22. --拼接sql语句写法,特别注意以下的’是多少个呀  
23. --[ uname]二边用的是3个,[ 1]二边用的是2个.   
24. sqlstr : = 'update 表名 set username='''|| uname || ''',tag=''1'' where userid='''|| uid || '''  and number like  ''%' ||  nmr || '%'';  
25. --IF,elsif，end if写法:  
26. if(tag = 1) then  
27.      if a= 0 and  b=0 then         
28.          sqlstr := ‘select * from *  
29.      else  
30.          sqlstr := ' select * from *  where …‘;             
31.      end if;  
32. elsif(tag = 2) then  
33. end if;  

for游标写法1: 

Sql代码 

1. FOR tt in (select * from …) LOOP     
2.    if tt.id is not null and  tt.tag = 1 then              
3.        exit;  
4.    end if;     
5. END  LOOP;  

for游标写法2: 

Sql代码 

1. open cur for select * from *;  
2. fetch cur into tt;  
3.   while cur%found loop       
4.     inituname :=tt.uname;  
5.     initdid := tt.did;  
6.     initdname := tt.dname;   
7.   end loop;  
8. close cur;  

for游标写法3: 

Sql代码 

1. type my is ref cursor;  
2. myhcur my;  
3. begin  
4. open myhcur for select * from …;  
5. fetch myhcur into p;   
6. loop  
7.          
8.        fetch myhcur into obj;   
9.        进行取值操作…          
10.        exit when myhcur%NOTFOUND;   
11. end loop;  
12. close  res_cur;  

动态SQL传递参数:userid为传递进来的参数. 

Sql代码 

1. Sqlstr :=’select  *  from 表名 where id=1’;  
2. open  mycur  for  Sqlstr  using  userid;