Oracle数据库PL/SQL学习笔记——函数定义

简介: Oracle数据库PL/SQL学习笔记——函数定义

小技巧:


如果在sql window窗口下创建函数,但是创建的函数一直是无效函数(有个小红叉),

那么可以再新建 -》program window-》 function  窗口中重写创建函数,这里面有相信的错误信息


--事务作用域


/*是执行线程 ——进程,当连接至Oracle 11g数据库时,

建立一个会话,在会话中做的任何事只有自己可见,直到提交操作完成,

其他会话才知道所作的改变。*/


调用子例程时,参数的传递:

1.位置表示法:为形参列表中的每个变量提供值,这些值必须是有顺序的,且必须匹配数据类型

2.命名表示法:可通过使用形参名,联合运算符(=>)和值传递实参。无序

create or replace function add_three_numbers
(a number:=0,b number :=0,c number :=0)
return number is
begin
   return a+b-c;
end;
--位置表示法
begin
   dbms_output.put_line(add_three_numbers(3,4,5));
end;
--命名表示法
begin
   dbms_output.put_line(add_three_numbers(c =>5,b=>4,a=>3));
end;
--混合表达法(第一个参数使用位置表达法直接传值,后两个参数使用命名表达法赋值)
begin
   dbms_output.put_line(add_three_numbers(3,c=>5,b=>4));
end;
--排除表达法(没有提供的第二个参数b用null代替)
begin
   dbms_output.put_line(add_three_numbers(3,c=>5));
end;
/*sql中调用函数时也能使用调用表示法*/
select add_three_numbers(3,c=>5,b=>4) from dual;

函数

有值传递和引用传递。

注意:不能在sql查询中调用包含DML操作的存储函数,会出现不能再查询中执行DML的错误。

     但是,可以再插入,更新和删除的sql语句中调用执行DML操作的函数。

--管道函数的使用(速度更快)
create or replace function Pipelined_numbers
return numbers
pipelined is
  list numbers := numbers(0,1,2,3,4,5,6,7,8,9);
begin
   for i in 1..list.last loop
      pipe row(list(i));          --管道函数的使用
   end loop;
   return;
end;   

包的使用:


--例:在包规范中定义了记录结构体、记录结构体的集合以及返回集合类型的函数:
--定义包pipelined
create or replace package pipelined is
  type user_record is record         --在包中定义记录record
  (name varchar2(30),
   age number
  );  
  type user_collection is table of user_record;
  function pf return user_collection pipelined;   --函数声明,返回的类型要指明所在的包pipelined
end pipelined;
--在包主体实现pf函数(当函数返回集合时,pipelined子句提供了改进的性能)
create or replace package body pipelined is     --注意包的主体声明中是   package body
 function pf
   return user_collection pipelined is
      counter number := 1;
      userlist user_collection := user_collection();  --初始化为空的集合
    cursor c is
       select t.name,t.age  from tb_user t;
begin
  for i in c loop
    userlist.extend;   --extend为一个元素分配空间,然后值被指派给被索引元素的组件
    userlist(counter).name := i.name;
    userlist(counter).age := i.age;
    pipe row(userlist(counter));--在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成。
    counter := counter +1;
  end loop;
   return;
 end pf;
end pipelined;
--在包体中的函数调用            包名.函数名
select pipelined.pf from dual;        --返回记录结构体的行为聚集表 , collection集合


--使用relies_on子句实现跨会话结果缓存:


--注意事项:

1.不能是管道表函数  

2.不能有引用传递,如in out或out

3.不能使用blob,clob,nclob,ref cursor,集合,对象,或记录数据类型的形参

create or replace type strings as table of varchar2(30);  --创建用户级别的type
create or replace function getnames
  (par_name varchar2) return strings
   result_cache relies_on(tb_user) is               --  result_cache 声明缓存结果,relies_on(tb_user)确保了对底层表的任何改变都会使缓存无效,确保了缓存是动态的
 counter number := 1;   --oracle数据库中集合从1开始遍历
 return_value strings := strings();
 cursor name_cur (v_name varchar2) is
    select name from tb_user where upper(name) like '%'||upper(v_name)||'%';
 begin
    for i in name_cur(par_name) loop
       return_value.extend;
       return_value(counter):=i.name;
       counter := counter + 1;
    end loop;
    return return_value;
 end getnames; 
 create or replace function getnames
  (par_name varchar2) return strings
   result_cache is               --  可省略relies_on(tb_user)提高查询性能,
 counter number := 1;   --oracle数据库中集合从1开始遍历
 return_value strings := strings();
 cursor name_cur (v_name varchar2) is
    select name from tb_user where upper(name) like '%'||upper(v_name)||'%';
 begin
    for i in name_cur(par_name) loop
       return_value.extend;
       return_value(counter):=i.name;
       counter := counter + 1;
    end loop;
    return return_value;
 end getnames;
--测试(第二次执行,耗时0s,说明缓存成功)
declare
 list strings;
begin 
  list := getnames('a');
  for i in 1..list.last loop
     dbms_output.put_line('list('||i||'):['||list(i)||']');
  end loop;
end;


--值传递函数


--接受输入列表(形参),返回单个输出变量,  所有形参必须使用in模式定义


--ref游标

隐式游标和显示游标都是静态定义的,他们在编译的时候结果集就已经确定。

如果想在运行的时候动态确定结果集,就要使用ref游标和游标变量


--系统引用游标

--例子:使用函数返回游标(这里只能是系统游标,也只有系统游标能作为参数和返回值)

--因为:sys_refcursor 是oracle数据库预定义的类型


--利用此函数能给另外一个系统游标赋值
create or replace function get_full_names
 return sys_refcursor is
   names sys_refcursor;    --变量声明
begin
   open names for
    select name,age from tb_user;
   return names;
end;



--递归函数
--例:返回任意数的阶乘值
create or replace function factorial
(n binary_double) return binary_double is
begin
   if n<=1 then
      return 1;
   else
      return n * factorial(n-1);
   end if;
end factorial;
select factorial(5) from dual;--120


--引用传递函数


传递引用,被调用时接受值的副本,能够返回实参的新值给调用者使用至少有一个形参通过使用out或in out模式定义。

总结:共有两种引用传递参数,一种在进入和退出时都有值:in out 模式变量;

          另一种退出时有一个值:out模式参数

create or replace function counting
(number_in in out number) return varchar2 is         --传入的参数经过函数处理后返回给调用者
  type numbers is table of varchar2(5);
  ordinal numbers := numbers('one','two','three','four','five');
  retval varchar2(9) := 'not found';
begin
   if number_in is null then
      number_in := 1;
   end if;
   if number_in < 4 then
      retval := ordinal(number_in);
      number_in := number_in +1;
   else
      retval := ordinal(number_in);
   end if;
   return retval;
end;
--测试
declare
  counter number :=1;
begin
   for i in 1..5 loop
     dbms_output.put_line('counter['||counter||']');
     dbms_output.put_line('counter['||counting(counter)||']');  
   end loop;
end;  
--out模式下的引用传递
create or replace function counting2
(
  number_out out number
) return varchar2 is
  type numbers is table of varchar2(5);
  ordinal numbers := numbers('one','two','three','four','five');
  retval varchar2(9):= 'not found';
begin
   if number_out is null then       --不管输入的number_out的值是多少,由于是out类型的,所以始终是null
      number_out := 1;
   end if;
   if number_out < 4 then
       retval := ordinal(number_out);
       number_out:=number_out+1;
   else
      retval := ordinal(number_out);
   end if;
   return retval;
end;
--测试
declare
 counter number :=1;
begin
   for i in 1..5 loop
     dbms_output.put('counter['||counter||']');
     dbms_output.put_line('['||counting2(counter)||']');
   end loop;
end;


目录
相关文章
|
1天前
|
SQL 存储 移动开发
HTML5 Web SQL 数据库详解
Web SQL 数据库是 HTML5 中的一种本地存储技术,允许在浏览器中使用 SQL 语言操作本地数据,支持离线访问和事务处理,适用于缓存数据和小型应用。然而,其存储容量有限且仅部分现代浏览器支持,标准已不再积极维护,未来可能被 IndexedDB 和 localStorage 等技术取代。使用时需谨慎考虑兼容性和发展前景。
|
24天前
|
SQL 存储 数据管理
SQL Server数据库
SQL Server数据库
41 11
|
1月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
59 0
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
58 0
|
2月前
|
SQL 数据处理 数据库
|
2月前
|
SQL 存储 调度
|
17天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
19天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
160 11
|
15天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
58 13
MySQL的安装&数据库的简单操作

推荐镜像

更多
下一篇
无影云桌面