`

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单步调试



9.一般在oracle中 select into用于存储过程中

如:select count(*) into v_count from table_name where id=1;

意思就是把id=1的数量放到一变量v_count中,在后续的过程中调用这个变量。


10.在Oracle存储过程中需要遍历一张表,有一个更方便的方法就是使用for in loop … end loop



11.无返回值的存储过程

存储过程为:CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN 

INSERT INTO HYQ.B_ID(I_ID,I_NAME) VALUES(PARA1,PARA2); END TESTA;


12.有返回值的存储过程 

要求:建张表TESTTB,里面两个字段(I_ID,I_NAME)

12.1有返回值的存储过程(非列表)

   

   存储过程为:CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS  BEGIN SELECT INTO PARA2 FROM TESTTB WHERE I_ID = PARA1; END TESTTB;

12.2返回列表

1.建立一个程序包。如下:

CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR;

END TESTPACKAGE;


2.建立存储过程,存储过程为:

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE Test_CURSOR) IS 

BEGIN OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;END TESTC;


可以看到,它是把游标(可以理解为一个指针),作为一个out参数来返回值的。


13.oracle 中 <> 为不等于的意思。


14.过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的主要区别不在于返回值,而在于他们的调用方式。过程是作为一个独立执行语句调用的:


pay_involume(invoice_nbr,30,due_date);


  函数以合法的表达式的方式调用:


order_volumn:=open_orders(SYSDATE,30);


  创建过程的语法如下:



CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name

[parameter_lister]

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION

exception_section]

END [procedure_name]


  每个参数的语法如下:


paramter_name mode datatype [(:=|DEFAULT) value]


  mode有三种形式:IN、OUT、INOUT。


  IN表示在调用过程的时候,实际参数的取值被传递给该过程,形式参数被认为是只读的,当过程结束时,控制会返回控制环境,实际参数的值不会改变。


  OUT在调用过程时实际参数的取值都将被忽略,在过程内部形式参数只能是被赋值,而不能从中读取数据,在过程结束后形式参数的内容将被赋予实际参数。


  INOUT这种模式是IN和OUT的组合;在过程内部实际参数的值会传递给形式参数,形势参数的值可读也可写,过程结束后,形势参数的值将赋予实际参数。


  创建函数的语法和过程的语法基本相同,唯一的区别在于函数有RETUREN子句


CREATE [ OR REPLACE] FINCTION [schema.]function_name

[parameter_list]

RETURN returning_datatype

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION]

exception_section

END [procedure_name]


  在执行部分函数必须有哟个或多个return语句。


  在创建函数中可以调用单行函数和组函数,例如:


CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)

RETURN NUMBER

IS 

pi NUMBER=ACOS(-1);

RadiansPerDegree NUMBER;


BEGIN

RadiansPerDegree=pi/180;

RETURN(SIN(DegreesIn*RadiansPerDegree));

END

  

15.包


  包是一种将过程、函数和数据结构捆绑在一起的容器;包由两个部分组成:外部可视包规范,包括函数头,过程头,和外部可视数据结构;另一部分是包主体(package body),


包主体包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。


  打包的PL/SQL程序和没有打包的有很大的差异,包数据在用户的整个会话期间都一直存在,当用户获得包的执行授权时,就等于获得包规范中的所有程序和数据结构的权限。


但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数,在包内可以用同一个名字声明多个程序,在运行时根据参数的数目和数据类型调用正确的程序。


  创建包必须首先创建包规范,创建包规范的语法如下:



CREATE [OR REPLACE] PACKAGE package_name

{AS|IS}

public_variable_declarations |

public_type_declarations |

public_exception_declarations |

public_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]


  创建包主体使用CREATE PACKAGE BODY语句:


CREATE [OR REPLACE] PACKAGE BODY package_name

{AS|IS}

private_variable_declarations |

private_type_declarations |

private_exception_declarations |

private_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]


  私有数据结构是那些在包主体内部,对被调用程序而言是不可见的。


帮助网址:http://dbajun.iteye.com/blog/256455


16.oracle函数与存储过程总结:


存储过程例子:根据ID查询学生信息

CREATE OR REPLACE PROCEDURE P_STU(

  V_ID IN OUT VARCHAR2,

  V_NAME OUT VARCHAR2,

  V_PROFESSION OUT VARCHAR2

)IS

BEGIN

  SELECT NAME , PROFESSION INTO V_NAME , V_PROFESSION FROM STUDENT WHERE ID = V_ID;

END P_STU;


FUNCTION函数例子:根据ID查询学生姓名


CREATE OR REPLACE FUNCTION F_STU(

                           V_ID IN VARCHAR2 

                           )RETURN VARCHAR2 IS

V_NAME VARCHAR2(50);

BEGIN

SELECT NAME INTO V_NAME FROM STUDENT WHERE ID = V_ID;

RETURN V_NAME;

EXCEPTION 

WHEN NO_DATA_FOUND THEN 

RAISE_APPLICATION_ERROR(-20001,'你输入的ID无效!');

END F_STU;


一般地,oracle存储过程可以返回多个值,函数只能返回一个值。


 

分享到:
评论

相关推荐

    oracle函数调用存储过程

    ### Oracle函数调用存储过程详解 #### 背景与目的 在开发Oracle应用程序时,经常需要使用到存储过程和函数。这两种类型的数据库对象各有优势,可以满足不同的业务需求。有时候,为了更好地组织代码和提高复用性,...

    oracle oracle函数 存储过程

    在学习和使用Oracle函数和存储过程时,建议从以下几个方面入手: 1. 熟悉基本的SQL语法和操作,这是使用函数和存储过程的前提。 2. 精读Oracle帮助文档,理解每个函数的用法和存储过程的结构。 3. 练习编写简单的...

    oracle函数大全与存储过程语法-中文.rar

    本资源包“oracle函数大全与存储过程语法-中文.rar”涵盖了从Oracle 9i到10G版本的函数和存储过程的详细信息,旨在帮助用户深入理解和应用这些关键概念。 1. **Oracle函数大全**: - 函数是预定义的代码块,接受一...

    oracle笔记存储函数和存储过程

    oracle笔记存储函数和存储过程,有具体的代码案例!存储函数和存储过程的知识笔记!

    oracle函数大全及存储过程语法.rar

    本资源"oracle函数大全及存储过程语法.rar"显然是一个关于Oracle函数和存储过程的详细指南,涵盖了各种常用和高级的函数以及存储过程的编写和使用方法。 一、Oracle函数 Oracle提供了丰富的内置函数,包括数值函数...

    oracle 函数大全 存储过程语法

    本篇文章将针对“Oracle函数大全”和“存储过程语法”进行深入的探讨。 首先,Oracle函数是预定义的代码块,它们接收输入参数并返回一个值。函数分为内置函数和自定义函数。内置函数包括数学函数(如:ROUND、TRUNC...

    java调用oracle存储过程或者函数

    调用Oracle函数的过程与调用存储过程类似,但创建CallableStatement时的SQL语句略有不同。由于函数会返回一个值,所以格式通常是`{? = call function_name(?, ?, ...)}`。在执行后,通过`CallableStatement....

    如何调用oracle的函数、存储过程

    调用Oracle函数和存储过程通常涉及以下步骤: 1. **创建函数和存储过程**: 使用SQL的CREATE FUNCTION或CREATE PROCEDURE语句来定义它们。例如: ```sql CREATE OR REPLACE FUNCTION my_function(param1 IN ...

    oracle函数大全及存储过程语法

    总结起来,Oracle函数和存储过程是数据库管理和开发中的重要工具,掌握它们的语法和应用对于优化数据库性能、提升开发效率以及保证数据安全具有重要意义。在实际工作中,应根据具体需求灵活运用,以实现更高效、更...

    oracle函数大全及存储过程语法 chm

    Oracle数据库是世界上最广泛使用的数据库系统之一,其...总的来说,Oracle函数和存储过程是数据库开发的核心工具,通过深入学习和实践,你将能够更有效地管理和操作Oracle数据库,提升你的数据库技能和项目实施能力。

    oracle笔记(存储过程函数触发器游标流程控制等)

    1. **存储过程与函数**:存储过程是预编译的SQL语句集合,用于执行特定任务。它们可以提高性能,减少网络流量,并提供模块化编程。函数则是返回值的存储过程,可以在SQL查询中直接使用。理解并熟练创建、调用和管理...

    Oracle存储过程和函数(最详细包含emp 表实例操作,边看边操作)

    **二、Oracle函数** 函数与存储过程相似,但函数必须返回一个值,而存储过程可以不返回。函数可以被用作查询的一部分,提升SQL语句的灵活性。 1. **创建函数**:使用`CREATE FUNCTION`语句定义函数,例如: ```...

    Oracle中的函数、存储过程、包

    3. **函数与存储过程的区别**: - **返回值**:函数必须有返回值,而存储过程可以没有返回值,或者通过OUT参数返回结果。 - **数据修改**:函数不能修改数据库中的数据,而存储过程可以。 - **参数类型**:两者都...

    oracle 存储过程 函数 dblink

    ### Oracle存储过程、函数与DBLink详解 #### 一、Oracle存储过程简介 在Oracle数据库中,存储过程是一种预编译好的SQL代码集合,它可以接受输入参数、返回单个值或多个值,并能够执行复杂的数据库操作。存储过程...

    Oracle触发器与存储过程高级编程-第3版itpub.rar

    《Oracle触发器与存储过程高级编程》第3版是一本深入探讨Oracle数据库中触发器和存储过程技术的专业书籍。在Oracle数据库系统中,触发器和存储过程是数据库管理员和开发人员进行复杂业务逻辑处理和数据管理的重要...

    oracle中存储函数与存储过程的区别介绍

    在Oracle数据库中,存储函数和存储过程是两种重要的PL/SQL编程组件,它们在数据库管理和应用程序开发中扮演着重要角色。虽然两者有许多相似之处,但它们之间也存在显著的差异。 首先,存储过程是一个预编译的PL/SQL...

    oracle 函数大全 参考函数 手册 速查 chm格式

    Oracle函数是数据库操作中的核心组成部分,它们允许开发人员和DBA执行各种计算、转换、查询和数据处理任务。手册中可能涵盖了以下几类函数: 1. 数学函数:如ABS(取绝对值)、MOD(取模)、ROUND(四舍五入)等,...

Global site tag (gtag.js) - Google Analytics