`
langgufu
  • 浏览: 2305710 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

存储过程基本语法和须注意事项

 
阅读更多

存储过程创建语法

1)无参

create or replace procedure
存储过程名

as
变量1 类型(值范围)
;
变量2 类型(值范围)
;
Begin
       ........................
Exception
       ........................
End;



2)带参

create or replace procedure
存储过程名(param1 in typeparam2 out type

as
变量1 类型(值范围)
;
变量2 类型(值范围)
;
Begin
    Select count(*) into
变量1 from A where列名=param1

    If (
判断条件
) then
       Select
列名 into 变量2 from A where列名=param1

       Dbms_output.Put_line(‘
打印信息
’);
    Elseif (
判断条件
) then
       Dbms_output.Put_line(‘
打印信息
’);
    Else
       Raise
异常名(NO_DATA_FOUND
;
    End if;
Exception
    When others then
       Rollback;
End;



注意事项:


1
  存储过程参数不带取值范围,in表示传入,out表示输出


2
  变量带取值范围,后面接分号


3
  在判断语句前最好先用count*)函数判断是否存在该条操作记录


4
  select 。。。into。。。给变量赋值


5
  在代码中抛异常用 raise+异常名




以命名的异常


命名的系统异常                          产生原因


ACCESS_INTO_NULL                  
未定义对象


CASE_NOT_FOUND                     CASE
中若未包含相应的 WHEN ,并且没有设置


ELSE


COLLECTION_IS_NULL               
集合元素未初始化


CURSER_ALREADY_OPEN         
游标已经打开


DUP_VAL_ON_INDEX                  
唯一索引对应的列上有重复的值


INVALID_CURSOR                
在不合法的游标上进行操作


INVALID_NUMBER                      
内嵌的 SQL 语句不能将字符转换为数字


NO_DATA_FOUND                       
使用 select into 未返回行,或应用索引表未初始化的
 



TOO_MANY_ROWS                     
执行 select into 时,结果集超过一行


ZERO_DIVIDE                             
除数为
0

SUBSCRIPT_BEYOND_COUNT    
元素下标超过嵌套表或 VARRAY 的最大值


SUBSCRIPT_OUTSIDE_LIMIT      
使用嵌套表或 VARRAY 时,将下标指定为负数


VALUE_ERROR                            
赋值时,变量长度不足以容纳实际数据


LOGIN_DENIED                           PL/SQL
应用程序连接到 oracle 数据库时,提供了不正确的用户名或密码


NOT_LOGGED_ON                       PL/SQL
应用程序在没有连接 oralce 数据库的情况下访问数据


PROGRAM_ERROR                       PL/SQL
内部问题,可能需要重装数据字典& pl./SQL系统包


ROWTYPE_MISMATCH               
宿主游标变量与 PL/SQL 游标变量的返回类型不兼容


SELF_IS_NULL                            
使用对象类型时,在 null 对象上调用对象方法


STORAGE_ERROR                       
运行 PL/SQL 时,超出内存空间


SYS_INVALID_ID                        
无效的 ROWID 字符串


TIMEOUT_ON_RESOURCE         Oracle
在等待资源时超时
 



例子:


1  create or replace procedure runbyparmeters  (isal in emp.sal%type,
                            sname out varchar,sjob in out varchar)
2  as icount number;
3  begin
4       select count(*) into icount from emp where sal>isal and job=sjob;
5       if icount=1 then
6         ....
9       else
10         ....
12       end if;
13  exception
14       when too_many_rows then
15       DBMS_OUTPUT.PUT_LINE('
返回值多于1
');
16       when others then
17       DBMS_OUTPUT.PUT_LINE('
RUNBYPARMETERS过程中出错!
');
18  end;


过程调用



 
方式一

1  declare
2        realsal emp.sal%type;
3        realname varchar(40);
4        realjob varchar(40);
5  begin
6        realsal:=1100;
7        realname:='';
8        realjob:='CLERK';
9        runbyparmeters(realsal,realname,realjob);    
--必须按顺序

10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11  END;
12

 
方式二

1 declare
2       realsal emp.sal%type;
3       realname varchar(40);
4       realjob varchar(40);
5 begin
6       realsal:=1100;
7       realname:='';
8       realjob:='CLERK';
9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); 
--指定值对应变量顺序可变

10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11 END;



说明:


1)使用
%TYPE
在许多情况下,PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下,变量应该拥有与表列相同的类型。例如,students表的first_name列的类型为VARCHAR2(20),我们可以按照下述方式声明一个变量

DECLARE
v_FirstName VARCHAR2(20);
但是如果first_name列的定义改变了会发生什么(比如说表改变了,first_name现在的类型变为VARCHAR2(25))?那就会导致所 有使用这个列的PL/SQL代码都必须进行修改。如果你有很多的PL/SQL代码,这种处理可能是十分耗时和容易出错的。

这时,你可以使用”%TYPE”属性而不是将变量类型硬性编码。


2)使用%ROWTYPE  (相当于定义一个struct 来进行存放,以对象来看对数据)

PL/SQL中将一个记录声明为具有相同类型的数据库行的作法是很常见的。PL/SQL提供了%ROWTYPE运算符,使得这样的操作更为方便。

例如:

DECLARE
v_StudentRecord students%ROWTYPE;
将定义一个记录,该记录中的字段将与students表中的列相对应。

例如:

declare
    v_jobs hr.jobs%rowtype;
begin
    select
      *
    into
      v_jobs
    from
      hr.jobs
where job_id ='&aa';
    dbms_output.put_line('
序号
'||v_jobs.job_id );
    dbms_output.put_line('
名称
'||v_jobs.job_title);
end;
执行,我们输入aa变量的值:
AD_VP
输出结果为:

序号
AD_VP
名称Administration Vice President

 

 

 

存储过程实例

 

 

存储过程 包含三部分: 声明,执行部分,异常。    
可以有无参数程序和带参数存储过程。
    
无参程序语法
    
1 create or replace procedure NoParPro   
2 as   ;   
3 begin   
4  ;   
5 exception   
6      ;   
7 end;   
8    
  
  
带参存储过程实例
    
1 create or replace procedure queryempname(sfindno emp.empno%type) as   
2        sName emp.ename%type;   
3        sjob emp.job%type;   
4 begin   
5        ....   
7 exception   
          ....   
14 end;   
15    
  
  
带参数存储过程含赋值方式
    
1 create or replace procedure runbyparmeters  (isal in emp.sal%type,    
                            sname out varchar,sjob in out varchar)   
2  as icount number;   
3  begin   
4       select count(*) into icount from emp where sal>isal and job=sjob;   
5       if icount=1 then   
6         ....   
9       else  
10         ....   
12       end if;   
13  exception   
14       when too_many_rows then   
15       DBMS_OUTPUT.PUT_LINE('
返回值多于1
');   
16       when others then   
17       DBMS_OUTPUT.PUT_LINE('
RUNBYPARMETERS过程中出错!
');   
18  end;   
19    
  
 
过程调用
   
 
方式一
   
1 declare   
2        realsal emp.sal%type;   
3        realname varchar(40);   
4        realjob varchar(40);   
5  begin   
6        realsal:=1100;   
7        realname:='';   
8        realjob:='CLERK';   
9        runbyparmeters(realsal,realname,realjob);    
--必须按顺序
   
10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);   
11  END;   
12    
  
 
方式二
   
1 declare   
2       realsal emp.sal%type;   
3       realname varchar(40);   
4       realjob varchar(40);   
5 begin   
6       realsal:=1100;   
7       realname:='';   
8       realjob:='CLERK';   
9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); 
--指定值对应变量顺序可变
   
10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);   
11 END;   
12   



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;
                    }
                });

分享到:
评论

相关推荐

    SQL Server存储过程基本语法

    以上内容主要介绍了 SQL Server 中存储过程中的一些基本语法和操作方法,包括变量定义及赋值、表和临时表的操作、循环以及条件语句等,这些知识点对于理解和使用 SQL Server 存储过程至关重要。

    Oracle存储过程基本语法

    Oracle 存储过程基本语法 Oracle 存储过程是一种可以在 Oracle 数据库中创建和执行的程序单元,它...通过本文,我们可以了解 Oracle 存储过程的基本语法和注意事项,从而更好地使用 Oracle 存储过程来完成不同的操作。

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

    ### Oracle存储过程的基本语法及注意事项 #### 一、Oracle存储过程概述 Oracle存储过程是一种预编译的SQL脚本集合,它可以包含复杂的逻辑控制结构,如条件语句、循环等,并且可以在数据库内部执行,从而提高性能并...

    oracle存储过程语法

    创建存储过程的基本语法 CREATE OR REPLACE PROCEDURE 存储过程名 IS BEGIN NULL; END; 存储过程的组成部分 * `CREATE OR REPLACE PROCEDURE`:创建或替换存储过程的语句。 * `IS`:关键词,表明后面将跟随一个...

    关于oracle存储过程的基本语法

    ### Oracle存储过程基础语法详解及注意事项 #### 一、Oracle存储过程概述 Oracle存储过程是一种在Oracle数据库中存储的一段可执行的SQL代码或PL/SQL代码,它可以帮助开发人员实现复杂的业务逻辑处理,提高应用程序...

    oracle存储过程基本语法.txt

    根据提供的文件信息,我们可以深入探讨Oracle存储过程的基本语法与关键概念。存储过程是数据库中预编译的一组SQL语句及过程化结构的集合体,它可以在数据库服务器上执行,以此来提高性能并减少网络流量。下面将详细...

    Oracle存储过程基本语法.docx

    Oracle存储过程基本语法是指在Oracle数据库中创建和管理存储过程的基本语法规则和结构。存储过程是数据库中的一种程序单元,可以执行特定的数据库操作和逻辑处理任务。 CREATE OR REPLACE PROCEDURE语句 CREATE OR...

    sql存储过程学习,详细的解说存储过程的语法,结构和用法。

    SQL存储过程是数据库管理系统中的一种重要工具,它允许程序员和数据库管理员预先定义并存储一系列复杂的SQL语句,以便后续调用。存储过程不仅能够提高数据处理的效率,还能增强数据库的安全性和可维护性。以下是对...

    SQL server存储过程语法及实例

    存储过程的基本语法包括创建、调用和删除存储过程。创建存储过程使用CREATE PROCEDURE语句,调用存储过程使用CALL语句,而删除存储过程则使用DROP PROCEDURE语句。以下是具体的知识点: 1. 创建存储过程(Create ...

    oracle存储过程基本语法

    ### Oracle存储过程基本语法详解 #### 一、存储过程简介 在Oracle数据库中,存储过程是一种重要的编程单元,它可以被...通过深入理解存储过程的基本语法和最佳实践,开发者可以构建更加高效、可靠的数据库应用程序。

    oracle存储过程语法.pdf

    Oracle 存储过程语法详解 Oracle 存储过程是一种编程对象,可以在 Oracle...通过这篇文章,我们了解了 Oracle 存储过程的语法和执行机制,了解了存储过程的创建、存储过程体、游标、存储过程执行和注意事项等知识点。

    存储过程学习经典[语法+实例+调用]

    - 调用一个存储过程的基本语法为 `EXECUTE 存储过程名(参数列表)`。 ##### 4. 存储过程与函数的区别 - 函数总是有一个返回值,可以在SQL查询中直接使用。 - 存储过程没有返回值,主要用于执行一系列操作。 #### 三...

    oracle存储过程(语法实例).pdf

    本文将深入探讨Oracle存储过程的基础知识、语法以及常见问题。 1. **基础知识** 存储过程是由SQL和PL/SQL语句组合而成的程序,它可以包含声明、变量、条件控制、循环结构等。通过存储过程,商业规则和业务逻辑被...

    oracle 存储过程的基本语法

    2. **`SELECT INTO`语法注意事项**: - 在使用`SELECT INTO`时,必须紧跟`INTO`关键字。 - 必须确保查询返回恰好一行数据,否则将触发异常。 3. **处理`NO_DATA_FOUND`异常**: - 使用`SELECT INTO`前应确保查询...

    DB2存储过程语法大全

    基本结构包括存储过程的定义、参数、局部变量、控制流语句、游标、异常处理和动态SQL的使用。这些构成了存储过程的核心组成部分,允许开发者设计出复杂的数据处理逻辑,提高应用程序的效率和响应速度。 #### ...

    Oracle存储过程语法与注意事项宣贯.pdf

    Oracle存储过程是数据库管理系统Oracle中的一种...理解并熟练运用这些Oracle存储过程的语法和注意事项,对于编写高效、稳定的PL/SQL代码至关重要。在实际开发中,应时刻注意错误处理和异常捕获,以确保程序的健壮性。

Global site tag (gtag.js) - Google Analytics