ORACLE存储过程

ORACLE提供了四种类型的可存储的程序：

函数, 过程. 包,触发器

一．声明部分(Declarationsection)

（1）声明部分包含了变量和常量的数据类型和初始值

（2）这个部分是由关键字DECLARE开始

（3）如果不需要声明变量或常量，那么可以忽略这一部分；

二．执行部分(Executablesection)

（1）执行部分是PL/SQL块中的指令部分，

（2）由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。

三．异常处理部分(Exceptionsection)

这一部分是可选的，在这一部分中处理异常或错误。

过程存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。

1.创建存储过程

CREATE[ORREPLACE]PROCEDURE过程名

(参数1{IN/OUT/INOUT}类型,

参数2{IN/OUT/INOUT}类型,

…….

参数N{IN/OUT/INOUT}类型,

)IS/AS

过程体

BEGIN

END存储过程名字

说明：

（1）ORREPLACE关键字可选，但一般会使用，功能为如果同名的过程已存在，则删除同名过程，然后重建，以此来实现修改过程的目的。

（2）过程可以包括多个参数，参数模式有IN/OUT/INOUT三种，默认为IN,也可以没参数。

（3）IS/AS键字也等价

（4）过程体为该过程的代码部分,是一个含有声明部分，执行部分和异常处理部分的PL/SQL块。但需要注意的是，在过程的声明体中不能使用DECLARE关键字，由IS或AS来代替。

注意事项：

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

类型可以使用任意Oracle中的合法类型。

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

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

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

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

2.查看过程

过程创建成功后，既说明编译已经成功，并把它作为一个ORACLE对象存储在数据库中，使用user_source视图查看过程的原程序代码信息，使用user_source可以查询到该数据库对象。

例：下面是VIEW_STU过程的源代码

SQL>DESC user_source

名称是否为空类型

NAMEVARCHAR2(30)

TYPE VARCHAR2(12)

LINE NUMBER

TEXTVARCHAR2(4000)

SQL>SELECT text from user_source where name=”VIEW_STU”

过程作为数据库对象，也可以用DESC命令列出关于过程结构的详细信息。如下“SQL>CREATE OR REPLACE PROCEDURE test1

(p1INNUMBER,

P2 OUT NYMBER,

P3 OUT DATE)

过程体

SQL>DESC test1

显示结果如下

PROCEDURE test1

参数名称类型输入/输出默认值

P1 NUMBER IN

P2 NUMBEROUT

P3 DDATEOUT

3.调用过程

一旦过程创建成功后，就可以在任何一个PL/SQL程序块中通过过程名直接调用

BEGIN

Proc_name(p1,p2…)

END

4.删除过程

DROP PROCEDURE

参数和模式

一，上面我们创建了DeptMesg过程，并且可以在以下PL/SQL块中调用它：DECLARE

e_name emp.ename%type:=’SMITH’;

BEGIN

DEPTMESG(e_name);

END;

（1）从上面块中声明的变量e_name作为参数传递给DEPTMESG,称为实际参数。DEPTMESG过程中的p_ename就是形式参数

（2）实际参数包含了在调用时传递给该过程的数值，同时他们也会接受在返回时过程的处理结果。

（3）形式参数只是实参的占位符，过程调用时，实参传递数值给形参，形参被赋予实参的取值

（4）返回过程时，实参被赋予形参的取值。

二．参数模式决定了形参的行为，PL/SQL块中参数模式有IN/ OUT/ IN OUT三种，默认为IN

（1）IN模式参数，输入参数，用于向过程传入一个值

（2）OUT模式参数，输出参数，用于从被调过程中返回一个值

（3）IN/OUT模式参数，用于向过程传入一个初始值，返回更新后的值。

参数规则

如果形式参数是IN模式的参数，实际参数可以是一个具体的值或一个有值的变量；

如果形式参数是OUT模式的参数，实际参数必须是一个变量，当调用过程后，此变量就被赋值了。可以输出此变量的值来测试过程执行的结果。

如果形式参数是IN OUT模式的参数，则实际参数必须是一个预

先已经赋值的变量。执行完过程后，该变量被重新赋值，可以输出此变量的值来测试过程执行结果

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;

...

CREATEORREPLACEPROCEDURE存储过程名

(

--定义参数

is_ymINCHAR(6),

the_countOUTNUMBER,

)

PL/SQL块语法

[DECLARE]---declarationstatementsBEGIN---executablestatements[EXCEPTION]---exceptionstatementsEND

每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。

注释由--标示。

PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以使

多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。

变量和常量

变量存放在内存中以获得值，能被PL/SQL块引用。你可以把变量想象成一个可储藏东西的容器，容器内的东西是可以改变的。

声明变量

变量一般都在PL/SQL块的声明部分声明，PL/SQL是一种强壮的类型语言，这就是说在引用变量前必须首先声明，要在执行或异常处理部分使用变量，那么变量必须首先在声明部分进行声明。

声明变量的语法如下：

Variable_name[CONSTANT]databyte[NOTNULL][:=|DEFAULTexpression]

注意:可以在声明变量的同时给变量强制性的加上NOTNULL约束条件，此时变量在初始化时必须赋值。

给变量赋值

给变量赋值有两种方式：.直接给变量赋值X:=200; Y=Y+(X*20);.通过SQLSELECTINTO或FETCHINTO给变量赋值

SELECTSUM(SALARY),SUM(SALARY*0.1)INTOTOTAL_SALAR

Y,TATAL_COMMISSIONFROMEMPLOYEEWHEREDEPT=10;

5.变量赋值

V_TEST:=123;

常量

常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，，他的声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。

这个语句定了一个名叫ZERO_VALUE、数据类型是NUMBER、值为0的常量。

ORACLE存储过程

ORACLE提供了四种类型的可存储的程序：函数, 过程. 包,触发器一．声明部分(Declarationsection) （1）声明部分包含了变量和常量的数据类型和初始值（2）这个部分是由关键字DECLARE开始（3）如果不需要声明变量或常量，那么可以忽略这一部分；二．执行部分(Executablesection) （1）执行部分是PL/SQL块中的指令部分，（2）由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。三．异常处理部分(Exceptionsection) 这一部分是可选的，在这一部分中处理异常或错误。过程存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。 1.创建存储过程 CREATE[ORREPLACE]PROCEDURE过程名 (参数1{IN/OUT/INOUT}类型, 参数2{IN/OUT/INOUT}类型, …….

参数N{IN/OUT/INOUT}类型, )IS/AS 过程体 BEGIN END存储过程名字说明：（1）ORREPLACE关键字可选，但一般会使用，功能为如果同名的过程已存在，则删除同名过程，然后重建，以此来实现修改过程的目的。（2）过程可以包括多个参数，参数模式有IN/OUT/INOUT三种，默认为IN,也可以没参数。（3）IS/AS键字也等价（4）过程体为该过程的代码部分,是一个含有声明部分，执行部分和异常处理部分的PL/SQL块。但需要注意的是，在过程的声明体中不能使用DECLARE关键字，由IS或AS来代替。注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出类型可以使用任意Oracle中的合法类型。 2，变量带取值范围，后面接分号 3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录 4，用select。。。into。。。给变量赋值 5，在代码中抛异常用raise+异常名 2.查看过程

ORACLE存储过程开发基础语法

ORACLE存储过程开发基础语法 create or replace procedure test(var_name_1 in type,var_name_2 out type) as --声明变量(变量名变量类型) begin --储备过程的执行体 end test; 打印出输入的时刻信息 E.g: create or replace procedure test(workDate in Date) is begin dbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd')); end test; 2、变量赋值变量名:= 值; E.g： create or replace procedure test(workDate in Date) is x number(4,2); begin x := 1; end test; 3、判定语句:

if 比较式then begin end; end if; E.g create or replace procedure test(x in number) is begin if x >0 then begin x := 0 - x; end; end if; if x = 0 then begin x: = 1; end; end if; end test; 4、For 循环 For ... in ... LOOP --执行语句 end LOOP; (1)循环遍历游标 create or replace procedure test() as Cursor cursor is select name from student; name varchar(20);

oracle存储过程讲解及实例

存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体。行3: BEGIN关键词表明PL/SQL体的开始。行4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句; 行5: END关键词表明PL/SQL体的结束

存储过程创建语法： create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; --vs_msg VARCHAR2(4000); 变量2 类型（值范围）; Begin Select count(*) into 变量1 from 表A where列名 =param1； If (判断条件) then Select 列名into 变量2 from 表A where列名 =param1； Dbms_output。Put_line(‘打印信息’); Elsif (判断条件) then Dbms_output。Put_line(‘打印信息’); Else Raise 异常名（NO_DATA_FOUND）; End if; Exception When others then Rollback;

End; 注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出类型可以使用任意Oracle中的合法类型。 2，变量带取值范围，后面接分号 3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录 4，用select 。。。into。。。给变量赋值 5，在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) ,

(Oracle管理)SQL中调用ORACLE存储过程

SQL Server 调用Oracle的存储过程收藏原文如下：通过SQL Linked Server 执行Oracle 存储过程小结 1 举例我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。 (1) Oracle Package PACKAGE Test_PACKAGE AS TYPE t_t is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER; PROCEDURE Test_procedure1 ( p_BATCH_ID IN VARCHAR2, p_Number IN number, p_MSG OUT t_t, p_MSG1 OUT t_t ); END Test_PACKAGE; PACKAGE BODY Test_PACKAGE AS PROCEDURE Test_procedure1 ( p_BATCH_ID IN VARCHAR2, p_Number IN number, p_MSG OUT t_t, p_MSG1 OUT t_t ) AS BEGIN p_MSG(1):='c'; p_MSG(2):='b'; p_MSG(3):='a';

p_MSG1(1):='abc'; RETURN; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END Test_procedure1; END Test_PACKAGE; (2) 在SQL Server中通过Linked Server 来执行Oracle 存储过程 declare @BatchID nvarchar (40) declare @QueryStr nvarchar (1024) declare @StatusCode nvarchar(100) declare @sql nvarchar(1024) set @BatchID='AAA' SET @QueryStr='{CALL GSN. Test_PACKAGE.Test_procedure1('''''+@BatchID +''''',''''4'''',{resultset 3, p_MSG},{resultset 1, p_MSG1})}' (3)执行结果 (a) select @sql='SELECT @StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,'' '+@QueryStr+''')' exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCod e output print @StatusCode 答案:@StatusCode=’a’ (b) select @sql='SELECT top 3 @StatusCode=p_msg FROM OPENQUERY (HI4DB

如何在Oracle中使用Java存储过程(详解)

如何在Oracle中使用Java存储过程 (详解) 一、如何缔造java存储过程？通常有三种步骤来缔造java存储过程。 1. 使用oracle的sql语句来缔造： e.g. 使用create or replace and compile java source named "" as 后边跟上java源程序。要求类的步骤必须是public static的，威力用于存储过程。 1.SQL> create or replace and compile java source named "javademo1 " 2. as 3. import java.sql.*; 4. public class JavaDemo1 5. { 6. public static void main(String[] argv) 7. { 8. System.out.println("hello, java demo1"); 9. } 10. } 11. / 12. 13.Java 已 14. 15.缔造。 16. 17.SQL> show errors java source "javademo1" 18.没有 19. 20.舛误。 21. 22.SQL> create or replace procedure javademo1 23. 2 as 24.3 language java name JavaDemo1.main(https://www.360docs.net/doc/006635612.html,ng.String[]); 25. 4 / 26. 27.过程已 28.

30. 31.SQL> set serveroutput on 32.SQL> call javademo1(); 33. 34.调用 35.实现。 36. 37.SQL> call dbms_java.set_output(5000); 38. 39.调用 40.实现。 41. 42.SQL> call javademo1(); 43.hello, java demo1 44. 45.调用 46.实现。 47. 48.SQL> call javademo1(); 49.hello, java demo1 50.调用 51.实现。 2. 使用外部class文件来装载缔造 e.g. 这里既然用到了外部文件，必定要将class文件放到oracle Server 的某一目录下边。 1.public class OracleJavaProc 2.{ 3. public static void main(String[] argv) 4. { 5. System.out.println("Its a Java Oracle procedure."); 6. } 7.} 8. 9. 10.SQL> grant create any directory to scott; 11. 12.授权 13.顺利。 14. 15.SQL> conn scott/tiger@iihero.oracledb

(Oracle管理)oracle存储过程

【IT168 技术文档】在我的上一个银行项目中，我接到编写ORACLE存储过程的任务，我是程序员，脑袋里只有一些如何使用CALLABLE接口调用存储过程的经验，一时不知如何下手，我查阅了一些资料，通过实践发现编写ORACLE存储过程是非常不容易的工作，即使上路以后，调试和验证非常麻烦。简单地讲，Oracle存储过程就是存储在Oracle数据库中的一个程序。一. 概述 Oracle存储过程开发的要点是： ?使用Notepad文本编辑器，用Oracle PL/SQL编程语言写一个存储过程; ?在Oracle数据库中创建一个存储过程; ?在Oracle数据库中使用SQL*Plus工具运行存储过程; ?在Oracle数据库中修改存储过程; ?通过编译错误调试存储过程; ?删除存储过程; 二.环境配置包括以下内容： ?一个文本编辑器Notepad; ? Oracle SQL*Plus工具，提交Oracle SQL和PL/SQL 语句到Oracle database。 ? Oracle 10g express数据库，它是免费使用的版本; 需要的技巧： ? SQL基础知识,包括插入、修改、删除等 ?使用Oracle's SQL*Plus工具的基本技巧; ?使用Oracle's PL/SQL 编程语言的基本技巧; 三.写一个存储过程

存储过程使用Oracle's PL/SQL 程序语言编写，让我们写一个什么工作都不做的存储过程，我们可以编译和运行它而不用担心会对数据库产生任何损害。在Notepad, 写下： CREATE OR REPLACE PROCEDURE skeleton IS BEGIN NULL; END; 把文件存为skeleton.sql. 让我们一行行遍历这个存储过程: 1 CREATE OR REPLACE PROCEDURE skeleton 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体。行3: BEGIN关键词表明PL/SQL体的开始。行4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;

oracle存储过程函数汇总().docx

oracle存储过程函数汇总() Character function return character value These functions all received is the character parameter type group (except CHR) and returns the character value? In addition to the special instructions, the function returns VARCHAR2 most numerical types? The restrictions on the return type of the character function are the same as those for the basic database type? The maximum value of character variable storage: The VARCHAR2 value is limited to 2000 characters (ORACLE 8 to 4000 characters) The CHAR value is limited to 255 characters (0RACLE8 2000) The long type is 2GB The Clob type is 4GB 1,CHR Syntax: Chr (x) Function: return in the database character set with numerical equivalence with the character of X. CHR and ASCII are a pair of inverse functions? After CHR conversion character after ASCII conversion and obtained the original word

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 wher e 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 WHE RE 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，如：

Oracle+PlSql存储过程

Oracl e 存储过程目录 Oracle 存储过程 (1) Oracle存储过程基础知识 (1) Oracle存储过程的基本语法 (2) 关于Oracle存储过程的若干问题备忘 (4) 1. 在Oracle中，数据表别名不能加as。 (4) 2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。 (5) 3. 在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 (5) 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 (5) 5. 在存储过程中，关于出现null的问题 (5) 6. Hibernate调用Oracle存储过程 (6) 用Java调用Oracle存储过程总结 (6) 一、无返回值的存储过程 (6) 二、有返回值的存储过程（非列表） (8) 三、返回列表 (9) 在存储过程中做简单动态查询 (11) 一、本地动态SQL (12) 二、使用DBMS_SQL包 (13) Oracle存储过程调用Java方法 (16) Oracle高效分页存储过程实例 (17) Oracle存储过程基础知识商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。要创建一个过程对象（procedural object），必须有CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有CREATE ANY PROCEDURE 权限。执行procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。

oracle存储过程的详细列子说明加分析

文章分类：数据库存储过程创建语法: （1）无参 create or replace procedure 存储过程名as 变量1类型（值范围）；变量2类型（值范围）； Begin Excepti on End; （2）带参 create or replace procedure 存储过程名（param1 in type，param2 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; Excepti on When others the n 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的最大值

oracle存储过程中的异常处理(精)

1.问题来源 Oracle中可以用dbms_output.put_line来打印提示信息，但是很容易缓冲区就溢出了。可以用DBMS_OUTPUT.ENABLE(1000000;来设置缓冲区的大小。但是有大小，就有可能再溢出（程序写得太烂，错误不断，不好意思）。于是想把异常信息写到一个表中。 2.建表这个容易 create table wErrorLog ( procedure_name varchar2(50 not null ,err_msg varchar2(255 not null ,sys_err_code varchar2(10 not null ,sys_err_msg varchar2(1000 not null ,create_time date not null ; comment on table wErrorLog is 'log表,用于记录存储过程的错误'; comment on column wErrorLog.procedure_name is '过程名,出错的存储过程或函数'; comment on column wErrorLog.err_msg is '自定义出错信息'; comment on column wErrorLog.sys_err_code is 'Oracle系统的出错代码'; comment on column wErrorLog.sys_err_msg is 'Oracle系统的出错信息'; comment on column wErrorLog.create_time is '错误发生时间'; 3.存储过程 CREATE OR REPLACE PROCEDURE prc_err_log ( i_procedure_name Varchar2 ,i_err_msg Varchar2 --写日志的过程,Albert Song 2005-06-28 --注意本过程没有进行commit或rollback操作 --用法 --Exception -- WHEN OTHERS -- Then -- rollbak; -- prc_err_log('prc_err_log','写日志表错误'; -- commit; As v_sqlcode Varchar(10; v_sqlerrm Varchar(1000; Begin v_sqlcode:=Sqlcode; v_sqlerrm:=Sqlerrm; Insert Into wErrorLog Values(i_procedure_name,i_err_msg,v_sqlcode,v_sqlerrm,Sysdate; Exception WHEN OTHERS Then v_sqlcode:=Sqlcode; v_sqlerrm:=Sqlerrm; Insert Into wErrorLog Values('prc_err_log','写日志表错误',v_sqlcode,v_sqlerrm,Sysdate; END; 4. 使用 create or replace procedure prc_test As v_data varchar2(255; Begin Insert Into wErrorLog Values('prc,'错误','test','test',Sysdate; Select err_msg Into v_data from wErrorLog where err_msg='no err msg'; Exception When Others Then Rollback; prc_err_log('prc_test','测试prc_err_log'; Commit; end ; 5.测试 exec prc_test; select * from wErrorLog; 6.说明为什么不能在prc_err_log中commit？目的是可以用在这样的地方 create or replace procedure prc_test_transaction As v_in Varchar2(255; begin Insert Into testsql Values('11','55'; If 1=1 Then begin Select code Into v_in From testsql Where code='12323'; exception when others then prc_err_log('prc_test_transaction','testsql表中不存在code为12323的记录'; end; END

oracle存储过程基本语法

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

oracle存储过程(语法实例)

Oracl e 存储过程学习资料（内部适用）目录 Oracle 存储过程1 Oracle存储过程基础知识2 Oracle存储过程的基本语法3 关于Oracle存储过程的若干问题备忘5 1.在Oracle中，数据表别名不能加as。5 2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。5 3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。5 4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错5 5.在存储过程中，关于出现null的问题6 6.Hibernate调用Oracle存储过程6 用Java调用Oracle存储过程总结6 一、无返回值的存储过程7 二、有返回值的存储过程（非列表）8 三、返回列表10 在存储过程中做简单动态查询11 一、本地动态SQL12 二、使用DBMS_SQL包14 Oracle存储过程调用Java方法16 Oracle高效分页存储过程实例17

Oracle存储过程基础知识商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。要创建一个过程对象（procedural object），必须有CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有CREATE ANY PROCEDURE 权限。执行procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。本质上没有区别,都是PL/SQL 程序,都可以有返回值。最根本的区别是：存储过程是命令, 而函数是表达式的一部分。比如： select max(NAME) FROM 但是不能exec max(NAME) 如果此时max是函数。 PACKAGE是function，procedure，variables 和sql 语句的组合。package允许多个procedure使用同一个变量和游标。创建procedure的语法：可以使用create or replace procedure 语句，这个语句的用处在于，你之前赋予的excute 权限都将被保留。 IN, OUT, IN OUT用来修饰参数。 IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。 OUT 表示PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。

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、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名变量类型) begin --存储过程的执行体 end test; 打印出输入的时间信息 E.g: create or replace procedure test(workDate in Date) is

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

Oracl e 存储过程学习目录 Oracle 存储过程 (1) Oracle存储过程基础知识 (1) Oracle存储过程的基本语法 (2) 关于Oracle存储过程的若干问题备忘 (4) 1. 在Oracle中，数据表别名不能加as。 (4) 2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。 (5) 3. 在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 (5) 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 (5) 5. 在存储过程中，关于出现null的问题 (5) 6. Hibernate调用Oracle存储过程 (6) 用Java调用Oracle存储过程总结 (6) 一、无返回值的存储过程 (6) 二、有返回值的存储过程（非列表） (8) 三、返回列表 (9) 在存储过程中做简单动态查询 (11) 一、本地动态SQL (12) 二、使用DBMS_SQL包 (13) Oracle存储过程调用Java方法 (16) Oracle高效分页存储过程实例 (17) Oracle存储过程基础知识商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。要创建一个过程对象（procedural object），必须有CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有CREATE ANY PROCEDURE 权限。执行procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。

oracle_存储过程练习题

1．创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限。 2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。其中币种代码如下： --RMB 人民币 --CNY 本位币 --USD 外币折美元如果币种为RMB，则取出人民币余额股作为本期余额；为CNY，则取本位币余额；为USD 则取外币折美元余额。请编写一个函数GetCurrBal( qrp_rq IN VARCHAR2, --报表日期 qrp_code IN VARCHAR2--币种 ) 实现此功能，并能在sqlplus里调用。其中建表语句如下： create table CCB_GYB ( ACCOUNTING_DATE DATE, RMB_YTD_BALANCE NUMBER, CNY_YTD_BALANCE NUMBER, USD_YTD_BALANCE NUMBER )；创建索引：create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE)； 3．假设有张学生成绩表(CJ)如下 [姓名] [学科] [成绩] 张三语文80 张三数学86 张三英语75 李四语文78 李四数学85 李四英语78 现有需求如下：（1）要求统计分数段的人数。显示结果为： [成绩] [人数] 0<成绩<60 0

60<成绩<80 0 80<成绩<100 5 （2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：姓名语文数学英语总成绩 ---------- ---------- ---------- ---------- ---------- 李四78 85 78 241 张三80 86 75 241 总分158 171 153 482 使用SQL语句或存储过程（显示结果时可用dbms_output打印出来）实现这两个功能。4．某一客户表包含如下信息： INDIVIDUALID 客户ID VARCHAR2(20)（唯一键）BIRTHDATE 出生日期Date GENDER 性别VARCHAR2(10) SALARY 月收入NUMBER（10，2） CERT-TYPE 证件类型VARCHAR2(10) CERT-NO 证件号码VARCHAR2(20) CREATED-TS 进入系统的时间TIMESTAMP 现要把该表数据导出成文件，导出的内容格式如下: 属性列列长度备注 INDIVIDUALID 20 BIRTHDATE 8 格式为：yyyymmdd GENDER 10 SALARY 13 CERT-TYPE 10 CERT-NO 20 CREATED-TS 17 格式为：yyyymmddhh24missff3 要求每个字段列的内容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格），数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0，日期默认为99991231,时间戳默认为99991231000000000。请编写程序实现该导出功能。创建表脚本： create table tb1010( INDIVIDUALID VARCHAR2(20), BIRTHDATE date, GENDER VARCHAR2(10), SALARY NUMBER(10,2), CERT_TYPE VARCHAR2(10),

Oracle存储过程语法学习(Procedure+实例)

存储过程创建语法：（1）无参 create or replace procedure 存储过程名 as 变量1 类型（值范围）; 变量2 类型（值范围）; Begin ........................ Exception ........................ End; （2）带参 create or replace procedure 存储过程名（param1 in type，param2 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 数据库时，提供了不正确的用户名或密码