oracle自定义函数和存储过程

合集下载

oracle 的function方法

Oracle的Function方法是Oracle数据库中一种非常重要的功能，它允许用户在数据库中定义自己的函数，以实现特定的业务需求。本文将介绍Oracle的Function方法的定义、使用场景以及一些常见的示例。

我们来了解一下Oracle的Function方法的定义。Function方法是一种存储过程，它接收输入参数并返回一个值。与存储过程不同的是，Function方法必须返回一个值，并且可以直接在SQL语句中调用。它可以用于计算、转换数据、执行复杂的业务逻辑等。

接下来，我们来看一些使用Oracle的Function方法的场景。首先，Function方法可以用于计算某个数据列的总和、平均值、最大值、最小值等统计信息。例如，我们可以定义一个名为get_total_sales 的Function方法，用于计算某个销售表中所有销售额的总和。

Function方法还可以用于数据转换。例如，我们可以定义一个名为convert_currency的Function方法，用于将某个货币金额转换为其他货币的金额。这在跨国企业的财务报表中非常常见。

Function方法还可以用于执行复杂的业务逻辑。例如，我们可以定义一个名为check_stock的Function方法，用于检查某个产品的库存是否充足。如果库存不足，则返回一个提示信息；如果库存充足，则返回一个成功信息。

下面，我们来看一些具体的Oracle的Function方法的示例。首先，我们定义一个名为get_total_sales的Function方法，用于计算某个销售表中所有销售额的总和。该方法的定义如下：

Oracle创建函数和过程

第一篇：Oracle创建函数和过程

ORACLE PL/SQL编程之六：把过程与函数说透(穷追猛打，把根儿都拔起!)

继上篇：ORACLE PL/SQL编程之八：把触发器说透得到了大家的强力支持，感谢。接下来再下猛药，介绍下一篇，大家一定要支持与推荐呀~！我也才有动力写后面的。

本篇主要内容如下：

6.1 引言 6.2 创建函数 6.3 存储过程 6.3.1 创建过程 6.3.2 调用存储过程 6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 开发存储过程步骤 6.3.6 删除过程和函数 6.3.7 过程与函数的比较

6.1 引言

过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：

1．创建存储过程和函数。2．正确使用系统级的异常处理和用户定义的异常处理。

3．建立和管理存储过程和函数。

6.2 创建函数

1.创建函数

语法如下：

CREATE [OR REPLACE] FUNCTION function_name(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], [arg2 [ { IN | OUT | IN

oracle function的编写和应用

在Oracle数据库中，可以使用PL/SQL语言编写函数。函数是一段可重用的代码块，它可以接受输入参数，并返回一个值。函数可以应用于各种场景，例如计算、数据转换和复杂查询等。

以下是编写和应用Oracle函数的一般步骤：

1. 创建函数：

使用CREATE FUNCTION语句创建函数。指定函数的名称、参数和返回类型。函数体由BEGIN和END关键字包围。

例如，下面是一个简单的函数，用于计算两个数值的和：

```sql

CREATE FUNCTION sum_numbers(a NUMBER, b NUMBER) RETURN NUMBER

result NUMBER;

BEGIN

result := a + b;

RETURN result;

END;

```

2. 存储函数：

使用该CREATE FUNCTION语句创建函数时，它将被存储

在数据库中以供后续使用。可以通过使用替代CREATE OR REPLACE FUNCTION语句来修改已存在的函数。

例如，可以使用以下语句修改上面的函数：

```sql

CREATE OR REPLACE FUNCTION sum_numbers(a NUMBER, b NUMBER)

RETURN NUMBER

result NUMBER;

BEGIN

result := a + b;

RETURN result;

END;

```

3. 调用函数：

使用SELECT语句调用函数。将函数名和参数传递给SELECT语句，可以将函数的返回值嵌入到查询结果中。

oracle自定义函数

oracle⾃定义函数

函数和存储过程类似，可以简单的理解为⼀段可以执⾏某个活动/动作的⼦程序，可以作为⼀个系统对象被存储在数据库中，可以重复调⽤。与存储过程不同的是，函数总是向调⽤者返回⼀个值，⽽存储过程不能有返回值。

CREATE[OR REPLACE]FUNCTION function_name

[(argment [ { IN| IN OUT }] type,

argment [ { IN | OUT | IN OUT } ] type]

RETURN return_type

{ IS|AS }

<类型.变量的说明>

BEGIN

FUNCTION_body

EXCEPTION

异常处理语句

END;

--IN:输⼊参数，向存储过程传递值，默认类型，可以不写

--OUT:输出参数，⽤于返回结果。

--IN OUT：作为IN参数向存储过程传递值，同时作为OUT参数返回值。

--REPLACE：指明若已有同名的存储过程存在，那么将被替换成当前创建的版本。

--RETURN只能返回单个值，不能返回多个值。

--可以使⽤DEFAULT关键字为输⼊参数指定默认值。

--没有参数的函数

create or replace function get_user return varchar2is

v_user varchar2(50);

begin

select username into v_user from user_users;

return v_user;

end get_user;

--测试

⽅法⼀

select get_user from dual;

Oracle创建存储过程、创建函数、创建包

Oracle创建存储过程、创建函数、创建包⼀、Oracle创建存储过程

1、基本语法

create or replace procedure update_emp_sal

(Name in out type,

Name in out type, ...

) is

begin

end update_emp_sal;

2、写⼀个简单的例⼦修改emp表的ename字段

create or replace procedure update_emp

(

v_empno varchar2,

v_ename varchar2

) is

begin

update emp set ename=v_ename where empno=v_empno;

end update_emp;

调⽤⽅法如下：

SQL>exec update_emp('7935','test');

2、有返回值的存储过程

就写⼀个简单的返回empno=7935的sal值

create or replace procedure emp_out_sal

(

v_empno in varchar2,

v_sal out number

) is

vsal number(7,2);

begin

select sal into vsal from emp where empno=v_empno;

v_sal:=vsal;

end;

调⽤有返回值的过程

SQL>var vsal number

SQL>exec emp_out_sal('7935',:vsal);

PL/SQL procedure successfully completed

在Oracle中使用存储过程和函数

Oracle中使用存储过程和函数

摘要：存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。函数也是存储程序的一种，它建立后存储在数据库服务器中，用户可以直接调用。熟练地使用存储过程能帮助DBA更好地管理数据库。

关键字：存储过程函数

一．为什么要使用存储过程呢？

因为课程设计中需要用到存储过程和函数，所以就选择了这个题目，好让自己对这个知识点更加熟悉。在SQL*Plus中编写并执行PL/SQL块，PL/SQL块的代码存放在SQL*Plus的缓冲区中。如果在SQL*Plus中执行其它SQL语句或者PL/SQL块，SQL*Plus的缓冲区中就会存放新的内容，原来的内容会被从缓冲区中清除出去。如果希望PL/SQL块能被随时调用执行，并能与数据库中的其它用户共享，那就需要创建有名字的PL/SQL块，并经过编译与优化，存放在数据库中，这就是存储程序。具体的优点如下：

1.存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2.可保证数据的安全性和完整性。

3.通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

4.通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

5.再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

Oracle的存储过程和自定义函1

Oracle的存储过程和自定义函数

存储过程、存储函数

数据库对象：表、视图、索引、序列、同义词

存储过程：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

创建和使用存储过程

用create procedure命令建立存储过程和存储函数。

语法：

create [or replace] procedure 过程名（参数列表）

PLSQL子程序体;

第一个存储过程：打印Hello world

两种调用存储过程：

1.exec sayhelloworld();

2.begin

sayhelloworld();

end;

3.set serveroutput on:”打开输出开关”

4.sqlplus scott/tiger@localhost:1521/orcl：“连接数据库”

5.host cls

带参数的存储过程

举例：为指定的员工，涨100块钱的工资；并且打印涨前和涨后的薪水

desc dbms_output程序包（desc查看命令，desc表示按“表排序字段名”倒序显示，不加desc,表示正序显示）参数是输入还是输出参数

如何调用:

begin

raisesalay(7839);

raisesalay(7566);

commit;

end;

如何调试存储过程

不推介远程调试

本地调试（虚拟机调试）

调试必须有debug connect session和debug any procedure用户权限

grant DEBUG CONNECT SESSION,DEBUG ANY PROCEDURE to scoot;

oracle中procedure（存储过程）和function（函数）本质区别

oracle中procedure（存储过程）和function（函数）本质

区别

Oracle function⾥⾯是可以允许有DML语句的，但是不能在查询的时候使⽤。

我们常⽤的function如：

select max(a) from table ;

这种调⽤⽅式是不能执⾏带有DML的FUNCTION的。

但是如果不⽤在SQL⾥⾯是可以有的

⽐如dbms_output.put_line(func(...));

如果函数⾥⾯采⽤⾃治事务，是可以有DML 的。

什么是“⾃治事务”：

⾃治事务是可以在其他事务中调⽤的独⽴事务。

⾃治事务可以使事务离开调⽤事务的上下⽂执⾏SQL操作、提交或回滚其他操作并返回到调⽤事务的上下⽂然后继续调⽤事务。

⾃治事务调⽤后，事务完全与调⽤它的主事务独⽴。

不会看到任何主事务尚未提交的改变、不会共享主事务的锁或资源。⾃治事务的改变在⾃治事务提交后可以被其他事务可见。

⾃治事务可以调⽤其他⾃治事务，嵌套的层数没有限制。

Oracle FUNCTION与PROCEDURE的最⼤区别⼩结

1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。

2、函数中⼀般不⽤变量形参，⽤函数名直接返回函数值；⽽过程如有返回值，则必须⽤变量形参返回。

3、过程⽆类型，不能给过程名赋值；函数有类型，最终要将函数值传送给函数名。

4、函数在定义时⼀定要进⾏函数的类型说明，过程则不进⾏过程的类型说明。

5、调⽤⽅式不同。函数的调⽤出现在表达式中，过程调⽤，由独⽴的过程调⽤语句来完成。

6、过程⼀般会被设计成求若⼲个运算结果，完成⼀系列的数据处理，或与计算⽆关的各种操作；⽽函数往往只为了求得⼀个函数值

oracle存储过程的创建和使用方法

oracle存储过程的创建和使用方法Oracle存储过程是一组预编译SQL语句，通过一个名称调用执行。以下是Oracle存储过程的创建和使用方法：

1. 创建存储过程：

sqlCopy code

CREATE OR REPLACE PROCEDURE procedure_name (parameter_name1 IN data_type, parameter_name2 OUT data_type, ...) IS -- 声明变量 BEGIN -- SQL语句 EXCEPTION -- 异常处理 END procedure_name;

其中，parameter_name1和parameter_name2为存储过程的输入和输出参数。声明变量和编写SQL语句的代码都应该放在BEGIN和END之间。

2. 调用存储过程：

scssCopy code

EXEC procedure_name(parameter_name1, parameter_name2, ...);

其中，parameter_name1和parameter_name2是存储过程的输入和输出参数。使用EXEC语句调用存储过程。

3. 示例：

以下是一个简单的Oracle存储过程示例，该存储过程将向一个名为employee的表中插入新的记录：

sqlCopy code

CREATE OR REPLACE PROCEDURE insert_employee ( emp_id IN NUMBER, emp_name IN VARCHAR2, emp_salary IN NUMBER,

Oracle存储过程和自定义函数

存储过程、存储函数概述

存储过程和存储函数

指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

存储过程和存储函数的相同点：完成特定功能的程序。

存储过程和存储函数的不同点：存储函数可以用return语句返回，存储过程不能用return 语句返回。

存储过程的创建和调用

创建和使用存储过程

语法：

create [or replace] procedure 过程名（参数列表） --or replace替换一个存储过程

as --声明部分，同declare同一个意思

plsql子程序体;

注意：当程序体执行完之后在plsql当中会有一个存储数据库对象。

例子：

使用存储过程打印helloword

create or replace procedure sayhelloword

as 说明部分

begin

dbms_output.put_line('helloword');

end;

调用存储过程：

方法1、exec syhellloword();

方法2、begin

sayhelloworld();

end;

通过sqlplus调用存储过程打印出结果

带参数的存储过程

创建带参数的存储过程

给指定员工涨100工资，并且打印涨前涨后的薪水

实例：

create or replace procedure raisesala(eno in number) --in为输入参数as --声明部分

psal emp.sal%type; --声明变量

begin

--查询员工涨前薪水

select psal into sal from emp where empno = eno;

Oracle11g第09讲存储过程及自定义函数(异常)讲述

--存储过程的定义默认in

Create or Replace Procedure 过程名(变量名[in,out,inout] 数据类型)

[is,as]

--自定义变量

Begin

End [过程名];

--自定义函数的定义默认in

Create or Replace Function 函数名(变量名[in,out,inout] 数据类型)

Return 数据类型

[is,as]

自定义变量

Begin

Return 值;

End [函数名];

Create function XXX(saljia number) return number

Begin

…………………Sal+20

end

select XXX(sal) from emp;

函数调用限制

1、SQL语句中只能调用函数

2、SQL只能调用带有输入参数,不能带有输出,输入输出函数

3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)

4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

异常错误处理

一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。

1.1 异常处理概念

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.

有三种类型的异常错误：

自写简单oracle数据库函数

⾃写简单oracle数据库函数1、返回两个数中较⼤者

create or replace function chooseMax(v1 in number,v2 in number)

return number

is v_max number;

begin

if(sign(v1-v2)>=0)

then v_max:=v1;

elsif(sign(v1-v2)<0)

then v_max:=v2;

end if;

return v_max;

end;

--例⼦

select choosemax(2,3) from dual; 结果：3

2、输⼊⼩写字符串返回相应的⼤写字符串

create or replace function up(v_str1 in varchar2)

return varchar2

v_str2 varchar2(200);

v1 varchar2(200);

v2 varchar2(200);

v_count number;

begin

v_count:=length(v_str1);

if(v_count>0)

then

for i in 2..v_count-1 loop

v1:=chr(ASCII(subStr(v_str1,i,1))-32);

v2:=v2||v1;

end loop;

v_str2:=v2;

else

v_str2:='';

end if ;

return v_str2;

end;

--例⼦

select up('aaa') from dual; 结果AAA’

oracle实验8 存储过程与函数的创建

一、实验目的

1.掌握存储过程与函数的概念

2.能够熟练创建和调用存储过程与函数。

二、实验内容

1.教材：第八章实验和练习题（全做）

2.补充练习题：

.编写函数get_salary，根据emp表中的员工编号，获取他的工资。输入参数为员工编号，如果找到该员工，屏幕显示已找到的信息，函数返回值为该员工的工资。如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调用该函数查看效果。

.编写函数get_cnt，根据输入参数部门编号，输出参数输出该部门的人数，返回值是该部门的工资总和。如果如果找不到，捕获并处理异常，函数返回值为0。函数创建成功后，调用该函数查看效果。

.编写存储过程DelEmp，删除emp表中指定员工记录。输入参数为员工编号。如果找到该员工，则删除他的记录，并在屏幕显示该员工被删除。如果没找到，则使用异常处理。

存储过程定义成功后，调用该存储过程查看结果。

.编写存储过程QueryEmp，查询指定员工记录；输入参数为员工编号，输出参数是员工的姓名和工资。如果找到该员工，在屏幕显示该员工已经查到。如果没找到，则捕获异常并处理。存储过程定义成功后，调用该存储过程查看结果。

三、实验环境

Windows 10，Oracle 11g

四、实验步骤

1.创建存储过程，根据职工编号删除scott.emp表中的相关记录。

（1）以scott 用户连接数据库，然后为system 用户授予delete 权限。

语句：

connect scott/tiger;

grant delete on emp to system;

oracle存储过程和函数.docx

Oracle存储过程和函数

一、存储过程

过程：将一些内部联系的命令组成一个个过程，通过参数在过程Z间传递数据。

存储过程的特点：

1、存储过程里的代码都己经被编译过。可以直接执行。使用吋无需编译，提高工作效率

2、客户端通过调用存储过程，可以减少网络流量，加快了系统执行速度。

3、可以减少SQL注入，提高系统的安全性。

4、在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效

利用SQL中的事务处理的机制；

5、使用存储过程，可以实现存储过程设计和编码工作分开进行

存储过程包含

1、声明部分：在声明部分可以对不同数据类型的数据进行声明，包括类型、游标、常量、变量、界常

等。

2、执行部分：主要是为了完成或者达到一个特定的1=1的或功能

3、异常处理部分：在执行过程中，对于引发错误的操作，进行异常捉示。

Oracle中创建存储过程的语法：

Create or replace procedure 存储过程名（参数1 in type,参数2 out type）

声明语句

Begin

执行语句

Exception

异常处理语句

End;

注意：

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

2、as后的语句声明变量，并且变量有取值范围，后面加上分号

3、begin关键字表示PL/SQL的开始

4、exception关键字用来处理异常，异常信息常用raise +异常名的方式

5、end关键字表示存储过程的结束

预定义异常的简单描述

uname varchar(20);

begin

select name into uname from test where id = 23;

Oracle的函数和存储过程

【】主键就是区别这个表的唯一关键字比如一个学生表学号不能重复且唯一学号就是关键字(此时学号就可以作为主键)

【】外键就是跟其他表联系的字段还是比如有一张学生表还有一张选课表

这个时候要修改学生表中的学号选课表里对应的就也得变

这样就需要给选课表加学号作为外键约束

这样当你修改学号时所有外键关联的就都改了

【】视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。

视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

【】在Java程序中设置事务处理

setAutoCommit(false);不让其自动提交。

【函数：】

①字符函数：

lower(char):将字符串转化为小写的格式

upper(char):将字符串转化为大写的格式

length(char):返回字符串的长度

substr(char,m,n):取字符串的子串

写sql语句

1分析清晰思路

【replace函数】

replace(char1,search_string，replace，)

例子：

select replace(ename,'A','我是老鼠')from emp;----->含义：把字段ename中凡是‘A’用'我是老鼠'替代。

【日期函数】

sysdate 返回系统当前时间

add_months 超过了指定月份的日期

to_char 把其他的转换成字符串的函数

【数据库的导入和导出】

以.dmp的格式导出数据库到磁盘

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）

变量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;

注意事项：