在oracle中,函数和存储过程是经常使用到的,他们的语法中有很多相似的地方,但也有自己的特点。刚学完函数和存储过程,下面来和大家分享一下自己总结的关于函数和存储过程的区别。
一、存储过程
1.定义
存储过程是存储在数据库中提供所有用户程序调用的子程序,定义存储过程的关键字为procedure。
2.创建存储过程
create [or replace] procedure 存储过程名
[(参数1 类型,参数2 out 类型……)]
as
变量名 类型;
begin
程序代码体
end;
示例一:无参无返
create or replace procedure p1
--or replace代表创建该存储过程时,若存储名存在,则替换原存储过程,重新创建
--无参数列表时,不需要写()
as
begin
dbms_output.put_line('hello world');
end;
--执行存储过程方式1
set serveroutput on;
begin
p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();
示例二:有参有返
create or replace procedure p2
(name in varchar2,age int,msg out varchar2)
--参数列表中,声明变量类型时切记不能写大小,只写类型名即可,例如参数列表中的name变量的声明
--参数列表中,输入参数用in表示,输出参数用out表示,不写时默认为输入参数。
------------输入参数不能携带值出去,输出参数不能携带值进来,当既想携带值进来,又想携带值出去,可以用in out
as
begin
msg:='姓名'||name||',年龄'||age;
--赋值时除了可以使用:=,还可以用into来实现
--上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100); --有返回参数时,需要通过declare声明变量进行接收。
begin
p2('张三',23,msg);
dbms_output.put_line(msg);
end;
示例三:参数列表中有in out参数
create or replace procedure p3
(msg in out varchar2)
--当既想携带值进来,又想携带值出去,可以用in out
as
begin
dbms_output.put_line(msg); --输出的为携带进来的值
msg:='我是从存储过程中携带出来的值';
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100):='我是从携带进去的值';
begin
p3(msg);
dbms_output.put_line(msg);
end;
示例四:存储过程中定义参数
create or replace procedure p4
as
--存储过程中定义的参数列表
name varchar(50);
begin
name := 'hello world';
dbms_output.put_line(name);
end;
---执行存储过程
set serveroutput on;
execute p4();
总结:
1.创建存储过程的关键字为procedure。
2.传参列表中的参数可以用in,out,in out修饰,参数类型一定不能写大小。列表中可以有多个输入输出参数。3.存储过程中定义的参数列表不需要用declare声明,声明参数类型时需要写大小的一定要带上大小。
4.as可以用is替换。
5.调用带输出参数的过程必须要声明变量来接收输出参数值。
6.执行存储过程有两种方式,一种是使用execute,另一种是用begin和end包住。
存储过程虽然有很多优点,但是它却不能使用return返回值。当需要使用return返回值时,我们可以使用函数。
二、存储函数
1.函数与存储过程的结构类似,但是函数必须有一个return子句,用于返回函数值。
create or replace function f1
return varchar--必须有返回值,且声明返回值类型时不需要加大小
as
msg varchar(50);
begin
msg := 'hello world';
return msg;
end;
--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin
dbms_output.put_line(f1());
end;
三、存储过程与存储函数的区别和联系
相同点:
1.创建语法结构相似,都可以携带多个传入参数和传出参数。
2.都是一次编译,多次执行。
不同点:
1.存储过程定义关键字用procedure,函数定义用function。
2.存储过程中不能用return返回值,但函数中可以,而且函数中必须有return子句。
3.执行方式略有不同,存储过程的执行方式有两种(1.使用execute2.使用begin和end),函数除了存储过程的两种方式外,还可以当做表达式使用,例如放在select中(select f1() form dual;)
总结:如果只有一个返回值,用存储函数,否则,一般用存储过程。
另外补充一下在存储过程和函数中经常使用到的循环、判断的命令结构
1、无游标的循环方式
CREATE OR REPLACE PROCEDURE TEST4
(
Sin IN NUMBER,
Sou OUT NUMBER
)
as
begin
for app in (SELECT * FROM AE_APPS) loop --for循环嵌套
if(app.id = Sin) then --if语句
Sou := app.id+1;
DBMS_OUTPUT.PUT_LINE(Sou);
end if;
end loop;
COMMIT;
end;
--执行语句
DECLARE
Sin ae_apps.id%type; --声明变量为ae_apps的id类型。
Sou NUMBER;
begin
Sin:= 200;
TEST4(Sin,Sou); --调用存储过程
DBMS_OUTPUT.PUT_LINE(Sou+100); --输出到dbms
end;
2、带参数的游标for循环
定义带参数的游标:
cursor cur(v_codetype ldcode.Codetype%TYPE) is
select * from ldcode where codetype = v_codetype; --定义游标
定义游标格式:
cursor 游标名称(变量定义) is 查询语句;
注意:
where条件中的变量名v_codetype要与游标定义cur(v_codetype ldcode.Codetype%TYPE)中的一致。
编写for循环部分:
--游标for循环开始
for temp in cur('llmedfeetype') loop
--temp为临时变量名,自己任意起
--cur('llmedfeetype')为"游标名称(传入的变量)"
Dbms_Output.put_line(temp.Code); --输出某个字段,使用"变量名.列名"即可。
end loop;
--游标for循环结束
转载:
https://www.cnblogs.com/zlbx/p/4818007.html
https://www.cnblogs.com/xwb583312435/p/9054973.html?ivk_sa=1024320u