oracle 存储过程的基本语法

oracle 存储过程的基本语法

1.基本结构  
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

3.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;

4.while 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

5.变量赋值
  V_TEST := 123;

6.用for in 使用cursor
  ...
  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;

7.带参数的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;

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

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

select  a.appname  from  appinfo a; --  正确
select  a.appname  from  appinfo  as  a; --  错误

 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

   select  af.keynode  into  kn  from  APPFOUNDATION af  where  af.appid = aid  and  af.foundationid = fid; --  有into，正确编译
   select  af.keynode  from  APPFOUNDATION af  where  af.appid = aid  and  af.foundationid = fid; --  没有into，编译报错，提示：Compilation 
  Error: PLS - 00428 : an  INTO  clause  is  expected  in  this  SELECT  statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

   可以在该语法之前，先利用 select count(*) from  查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

  select  keynode  into  kn  from  APPFOUNDATION  where  appid = aid  and  foundationid = fid; --  正确运行
select  af.keynode  into  kn  from  APPFOUNDATION af  where  af.appid = appid   and  af.foundationid = foundationid ; --  运行阶段报错，提示
ORA - 01422 :exact  fetch   returns  more than requested  number   of  rows

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

create   table  A(
id  varchar2 ( 50 )  primary   key   not   null ,
vcount  number ( 8 )  not   null ,
bid  varchar2 ( 50 )  not   null   --  外键 
);

如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A  where  bid = ' xxxxxx ' ;

如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if  fcount  is   null  then
    fcount: = 0 ;
end  if ;

这样就一切ok了。

6.Hibernate调用oracle存储过程

         this .pnumberManager.getHibernateTemplate().execute(
                 new  HibernateCallback()  {
                     public  Object doInHibernate(Session session)
                             throws  HibernateException, SQLException  {
                        CallableStatement cs  =  session
                                .connection()
                                .prepareCall( " {call modifyapppnumber_remain(?)} " );
                        cs.setString( 1 , foundationid);
                        cs.execute();
                         return   null ;
                    }
                } );

 原文：http://www.cnblogs.com/happyday56/archive/2007/07/05/806830.html

posted @ 2008-11-13 13:25  赖文华(SWPU小赖)  阅读(123)  评论(3)    编辑   收藏   网摘

发表评论

   回复    引用    查看     

#1楼  [ 楼主 ]2008-11-13 13:33 |  赖文华(SWPU小赖)        

ORACLE存储过程实例 

--创建存储过程 
CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p 
( 
--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。 
is_ym IN CHAR 
) 
AS 
--定义变量 
vs_msg VARCHAR2(4000); --错误信息变量 
vs_ym_beg CHAR(6); --起始月份 
vs_ym_end CHAR(6); --终止月份 
vs_ym_sn_beg CHAR(6); --同期起始月份 
vs_ym_sn_end CHAR(6); --同期终止月份 
--定义游标(简单的说就是一个可以遍历的结果集) 
CURSOR cur_1 IS 
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn 
FROM BGD_AREA_CM_M_BASE_T 
WHERE ym >= vs_ym_sn_beg 
AND ym <= vs_ym_sn_end 
GROUP BY area_code,CMCODE; 

BEGIN 
--用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函数。 
vs_ym_beg := SUBSTR(is_ym,1,6); 
vs_ym_end := SUBSTR(is_ym,7,6); 
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); 
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); 
--先删除表中特定条件的数据。 
DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym; 
--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount 
DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt) 
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 
FROM BGD_AREA_CM_M_BASE_T 
WHERE ym >= vs_ym_beg 
AND ym <= vs_ym_end 
GROUP BY area_code,CMCODE; 

DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条'); 
--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。 
FOR rec IN cur_1 LOOP 
UPDATE xxxxxxxxxxx_T 
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 
WHERE area_code = rec.area_code 
AND CMCODE = rec.CMCODE 
AND ym = is_ym; 
END LOOP; 

COMMIT; 
--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 
EXCEPTION 
WHEN OTHERS THEN 
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); 
ROLLBACK; 
--把当前错误记录进日志表。 
INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 
COMMIT; 
RETURN; 
END; 

   回复    引用    查看     

#2楼  [ 楼主 ]2008-11-13 13:40 |  赖文华(SWPU小赖)        

刘贤同学写的一个例子，很不错，所以就收藏一下：） 

sql 代码 
create or replace 
PROCEDURE p_insert_t 
(v_x in NUMBER) 
IS 
temp_id number; 
BEGIN 
FOR i IN 1..v_x LOOP 
select SEQ_B_USER_ID.nextval into temp_id from dual; 
insert into buser (id, username, nickname, status, createdtime) values(temp_id, to_char(temp_id), to_char(temp_id), 0, sysdate); 
END LOOP; 
END; 
--execute 

创建sequence的方法（备忘）： 

sql 代码 
CREATE SEQUENCE SEQ_B_USER_ID INCREMENT BY 1 START WITH 1; 

复制一个表的结构： 

sql 代码 
create table test as select * from bookmarkcategory where 1=2; 

随机数： 

sql 代码 
select dbms_random.value(1,20) from dual; 

游标的使用例子： 

sql 代码 
SET SERVERIUTPUT ON 
DECLARE 
CURSOR c_emp IS select title from bookmarkcategory where username='130'; 
r_emp c_emp%ROWTYPE; 
BEGIN 
OPEN c_emp; 
-- LOOP 
FETCH c_emp INTO r_emp; 
--EXIT WHEN c_emp%NOTFOUND; 
--DBMS_OUT.PUT.PUT_LINE('Salary of Employee is'|| r_emp.title); 
--END LOOP; 
update bookmarkcategory set title = r_emp.title where id=121; 
CLOSE c_emp; 
END; 

   回复    引用    查看     

#3楼  [ 楼主 ]2008-11-13 16:54 |  赖文华(SWPU小赖)        

--------------------------------- 
-- Author: liangCK 小梁 
-- Date : 2008-11-13 10:07:06 
--------------------------------- 

--> 生成测试数据: [A表] 
IF OBJECT_ID('[A表]') IS NOT NULL DROP TABLE [A表] 
CREATE TABLE [A表] (id INT,ProgramName VARCHAR(3)) 
INSERT INTO [A表] 
SELECT 1,'End' UNION ALL 
SELECT 2,'Chr' UNION ALL 
SELECT 3,'AAA' 

--> 生成测试数据: [B表] 
IF OBJECT_ID('[B表]') IS NOT NULL DROP TABLE [B表] 
CREATE TABLE [B表] (id INT,Person VARCHAR(5)) 
INSERT INTO [B表] 
SELECT 1,'Ken' UNION ALL 
SELECT 1,'Anne' UNION ALL 
SELECT 2,'blue' UNION ALL 
SELECT 3,'Wayne' 

--SQL查询如下: 

GO 
CREATE FUNCTION dbo.fn_Persons(@id INT) 
RETURNS VARCHAR(50) 
AS 
BEGIN 
DECLARE @re VARCHAR(50); 
SET @re=''; 

SELECT @re=@re+'<br>'+Person 
FROM [B表] 
WHERE id=@id; 

RETURN STUFF(@re,1,4,''); 
END 
GO 

SELECT id,ProgramName,dbo.fn_Persons(id) AS Persons 
FROM [A表] 

GO 
DROP TABLE [A表],[B表] 
DROP FUNCTION dbo.fn_Persons 

/* 
id ProgramName Persons 
----------- ----------- -------------------------------------------------- 
1 End Ken<br>Anne 
2 Chr blue 
3 AAA Wayne 

(3 行受影响) 

*/