Oracle存储过程（一）基本概念及语法

一.概念

什么是存储过程?

商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。  

存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。

权限

操作	权限
建立存储过程	create procedure
建立可以被其他用户schema访问的存储过程	create any procedure
执行存储过程	execute procedure
执行其他schema建立的存储过程	execute any procedure

二.语法

1. 基本结构 

CREATE OR REPLACE PROCEDURE 存储过程名字
 ( 
	参数1 IN NUMBER,     
	参数2 IN NUMBER 
) AS 
变量1 INTEGER :=0; 
变量2 DATE; 
BEGIN 
NULL;
END 存储过程名字

注意：

1）可以使用 create or replace procedure 语句， 这个语句的用处在于，之前赋予的excute权限都将被保留。 
2）IN, OUT, IN OUT用来修饰参数。 
IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。 OUT 表示PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。

3）begin与end之间为PL/SQL程序体，null不能删去，pl/sql体中至少要有一句。

4）参数不带取值范围，变量带取值范围，后跟分号

2. SELECT INTO STATEMENT 
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 
例子：

BEGIN 
 	SELECT col1,col2 into 变量1,变量2 FROM typestruct where col=参数1;   
	EXCEPTION 
  	WHEN NO_DATA_FOUND THEN       ROOLBACK;   
END;   ... 

3. IF 判断  if ... then  begin ... end;  end if;

BEGIN
	IF V_TEST=1 THEN     
		BEGIN  
       			do something     
		END;   
	END IF; 
END

4. while 循环 while - loop - begin - end; - end loop;

WHILE V_TEST=1 LOOP   
	BEGIN  XXXX   END; 
END LOOP; 

5. 变量赋值 

V_TEST := 123; 

6. 带参数的cursor ----可遍历的结果集 

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;   
OPEN C_USER(变量值);   
LOOP 
 	FETCH C_USER INTO V_NAME;  
	EXIT FETCH C_USER%NOTFOUND;     
	do something   
END LOOP;   
CLOSE C_USER; 

7. 用for in ---- for...in ... loop begin ... end; end loop;

遍历游标 

 ...   
IS 
CURSOR cur IS SELECT * FROM xxx;   
BEGIN 
	FOR cur_result in cur LOOP   
		BEGIN 
   			V_SUM :=cur_result.列名1+cur_result.列名2   
		END; 
 	END LOOP;   
END; 

遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as 

i number; 

begin 

i := 1;  

for i in 1..varArray.count LOOP      

dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));    

 end LOOP; 

end test; 

8.数组

Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。

create or replace procedure test(y out array) is 

 x array;   

 begin 

x := new array(); 

y := x; 

end test; 

9. 用pl/sql developer debug 

  连接数据库后建立一个Test WINDOW 
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试  

10. Pl/Sql中执行存储过程 在sql*plus中： 

declare    
      --必要的变量声明，视你的过程而定      
begin    
	execute   yourprocudure(parameter1,parameter2,...);      
end      
/  

  

在SQL/PLUS中调用存储过程，显示结果：   

SQL>set serveoutput on    --打开输出   
SQL>var info1 number;     --输出1     
SQL>var info2 number;     --输出2     
SQL>declare   
          	var1  varchar2(20);       --输入1             
		var2  varchar2(20);     --输入2             
		var3  varchar2(20);       --输入2             
		BEGIN   
              		pro(var1,var2,var3,:info1,:info2);             
		END;             
		/     
SQL>print  info1;     
SQL>print  info2; 

 注：在EXECUTE IMMEDIATE STR语句是SQLPLUS中动态执行语句，它在执行中会自动提交，类似于DP中FORMS_DDL语句，在此语句中str是不能换行的,只能通过连接字符"||"，或着在在换行时加上"-"连接字符。

11.错误处理

EXCEPTION 


   WHEN OTHERS THEN 
      vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);

 
   ROLLBACK;