oracle 存储过程的基本语法及注意事项

﹏ヽ暗。殇╰゛Y 2023-01-01 12:57 149阅读 0赞

# 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，如：

![None.gif][] select  a.appname  from  appinfo a; \--  正确   
![None.gif][] select  a.appname  from  appinfo  as  a; \-- 错误

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

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

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

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

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

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

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

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

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

![None.gif][] create   table  A(  
![None.gif][]id  **varchar2** ( **50** )  primary   key   not   null ,  
![None.gif][]vcount  **number** ( **8** )  not   null ,  
![None.gif][]bid  **varchar2** ( **50** )  not   null   \--  外键    
![None.gif][] );

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

![None.gif][] select sum(vcount) into fcount from A  where  bid = ' xxxxxx ' ;

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

![None.gif][] if  fcount  is   null  then  
![None.gif][]    fcount: = 0 ;  
![None.gif][]end  if ;

这样就一切ok了。

6.Hibernate调用oracle存储过程

![None.gif][]          this .pnumberManager.getHibernateTemplate().execute(  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][]                 new  HibernateCallback()  ... \{  
![InBlock.gif][]                    public Object doInHibernate(Session session)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]                            throws HibernateException, SQLException ...\{  
![InBlock.gif][]                        CallableStatement cs = session  
![InBlock.gif][]                                .connection()  
![InBlock.gif][]                                .prepareCall("\{call modifyapppnumber\_remain(?)\}");  
![InBlock.gif][]                        cs.setString(1, foundationid);  
![InBlock.gif][]                        cs.execute();  
![InBlock.gif][]                        return null;  
![ExpandedSubBlockEnd.gif][]                    \}  
![ExpandedBlockEnd.gif][]                \} );

[None.gif]: /images/20221120/6e17606bd9b648559df15da81f713331.png
[ExpandedBlockStart.gif]: http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: /images/20221120/9d07877a240741c7b10337a5c404f0ae.png
[InBlock.gif]: /images/20221120/68787b5e5f1a407ebd512f752e6e1892.png
[ExpandedSubBlockStart.gif]: http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif
[ContractedSubBlock.gif]: /images/20221120/a6120e725b25455baa42c13be0216d65.png
[ExpandedSubBlockEnd.gif]: /images/20221120/77bd0f7e53ca4474b0a2d49b65e5eff7.png
[ExpandedBlockEnd.gif]: /images/20221120/00e340d516c849a88109606952dbf445.png