Oracle存储过程、函数、包的使用

Oracle存储过程、函数、包的使用注意:存储过程是没用返回值的。 注意: 先进行按位置,再按名称,并且一旦开始按照名称传递就不能再使用按位置传递。 函数定义时必须有返回对应参数类型,存储过程则没用要求。 函数执行操作后必须有返回变量值,存储过程则没用要求。

Oracle存储过程

1、创建存储过程(无参数)

-- 创建存储过程
create or replace procedure proc_emp as
       cursor cur_cus is select * from emp where deptno = 10;
begin
  for v_row in cur_cus loop
    dbms_output.put_line(v_row.empno||' | '||v_row.ename);
    end loop;
end proc_emp;

PLSQL执行存储过程

-- 先在Command Window窗口打开显示输出
set serveroutput on;
-- 再执行存储过程
exec proc_emp;

JDBC调用执行存储过程(在PLSQL中执行此命令,将查询到的结果存到缓存中,当再次调用 exec proc_emp时,将缓存中存到的所有数据展示出来,总共有两次记录)

当过程无参时,也需要小括号

call proc_emp();

2、创建存储过程(带参数)

注意:形参不能写精度,否则编译错误

create or replace procedure proc_emp(dno number) as
       cursor cur_cus is select * from emp where deptno = dno;
begin
  for v_row in cur_cus loop
    dbms_output.put_line(v_row.empno||' | '||v_row.ename);
    end loop;
end proc_emp;

PLSQL执行存储过程

exec proc_emp(30);

jdbc执行存储过程

call proc_emp(30);

使用PLSQL存储过程对话框建立存储过程生成标准的sql语句

创建多个参数的存储过程

create or replace procedure proc_emp(dno number,dname varchar2) as
begin
  insert into dept values(dno,dname);
  -- DDL语句不会自动提交事务,必须手动提交事务
  commit;
end proc_emp;

注意:存储过程是没用返回值的。

3、形参的分类:

  • 1- 输入类型参数: 参数名 IN 参数类型; (默认的参数类型)
  • 2- 输出类型参数: 参数名 OUT 参数类型;
  • 3- 输入输出类型参数: 参数名 IN OUT 参数类型;

定义带输出类型参数的过程

create or replace procedure proc_get_sal(eno number, esal out number) is
begin
  select sal into esal from emp where empno = eno;
end proc_get_sal;

— 执行带输出类型参数的过程

  • 1- 定义一个变量

    SQL> var v_sal number;
    
    • 2- 使用变量承载输出类型参数的信息; 注意:变量的使用格式: :变量名

      SQL> exec proc_get_sal(7788, :v_sal);
      -- call方式调用
      SQL> call proc_get_sal(7839,:v_sal);
      
      • 3- 打印输出变量

        print v_sal;
        

定义输入输出类型参数的过程

create or replace procedure proc_get_sal(v_val in out number) is
begin
  select sal into v_val from emp where empno = v_val;
end proc_get_sal;
  • 调用带输入输出类型参数

    -- 1- 定义变量
    var v_val number;
    -- 2- 赋予输入的值
    begin 
       :v_val:=7839; 
    end;
    -- 3- 执行
    exec proc_get_sal(:v_val);
    -- 再次查看结果
    print v_val;
    

4、实参的传值方式

  • 1- 按位置传值

    * SQL> exec proc_add_dept('dev', 50, 'lintong');
    
    • 2- 按照名称传值
    SQL> exec proc_add_dept(p_loc => 'lintong',p_dname => 'dev',p_dno => 50);
    
  • 3- 混合传递

    SQL> exec proc_add_dept('dev2',p_loc => 'lintong',p_dno => 70);
    

注意: 先进行按位置,再按名称,并且一旦开始按照名称传递就不能再使用按位置传递。

错误的示范:

SQL> exec proc_add_dept('dev3',p_dno => 80,'changan');

使用参数类型的规范:

  • 1- 形参尽量不要使用 out 或者 in out 类型参数
  • 2- 使用按照位置传递实参,不要使用按名称和混合传递;

存储函数

存储过程和存储函数的两个区别:

  • 函数定义时必须有返回对应参数类型,存储过程则没用要求。
  • 函数执行操作后必须有返回变量值,存储过程则没用要求。

创建存储函数

create or replace function fun_get_sal(eno number) return number
is
  v_result number;     
begin
  select sal into v_result from emp where empno=eno;
  return v_result;
end;

执行函数的三种方式

  • 1、定义变量将函数返回值赋值给变量

    -- 1-定义接收函数返回值的变量
    SQL> variable v_sal number;
    -- 2-调用函数将返回值传递给变量
    SQL> exec :v_sal := fun_get_sal(7566);
    
  • 2、直接输出的方式调用函数(记得输出语句在语句块中)

SQL> begin 
          dbms_output.put_line(fun_get_sal(7566));
     end;
     /
  • 3、在SQL中调用

    查询工资占本部门总工资的百分比

    一、先创建函数

    注意:形参和返回类型不能带精度,定义的其他变量可以带精度

    create or replace function fun_get_dept_sal(esal number, dno number) return varchar2 is
      FunctionResult varchar2(7);
      
    begin
       select round(esal / (select sum(sal) from emp where deptno=dno)*100,2)||'%' into FunctionResult from dual;
      return(FunctionResult);
    end fun_get_dept_sal;
    

    二、调用函数返回结果

    select e.*,fun_get_dept_sal(e.sal,e.deptno) per_sal
    from emp e;
    

    PL/SQL包

    包就是一个PL/SQL的相关对象的逻辑分组和单个对象存储在数据库中的存储程序单元

    相关的PL/SQL对象可以是常量、变量、游标、异常、过程和函数

    包的组成:

    1、包规范部分(也成为包的声明部分)

    2、包主体部分

    包的定义,分为声明(定义)部分(packages文件夹下)

    注意:包分为声明部分和主体部分,声明部分全为public,外部能直接访问的,主体部分为私有部分,外部不能直接访问,声明部分和私有部分的名称要一致,当主体部分调用声明部分的公有方法时,公有函数内部调用主体部分的私有函数时,私有部分必须在公有方法的前面,才能调用,是面向过程调用,顺序执行

    create or replace package pak_emp is
    
      -- Public variable declarations
      fie_dno number;
      fie_sal number;
    
      -- Public function and procedure declarations
      function get_sal(eno number) return number;
    
      procedure print_sal(eno number);
    
    end pak_emp;
    

    主体(实现)部分(package bodies文件夹下)

    create or replace package body pak_emp is
    
      -- Private variable declarations
      fie_ename emp.ename%type;
    
      -- Function and procedure implementations
       function get_sal(eno number) return number is
         begin
           select sal into fie_sal from emp where empno = eno;
           return fie_sal;
         end get_sal;
    
      -- private function or procedure
      procedure print_private_sal(eno number) is
        begin
          dbms_output.put_line(get_sal(eno));
        end print_private_sal;
    
      -- public procedure
      procedure print_sal(eno number) is
        begin
          print_private_sal(eno);
        end print_sal;
        
    end pak_emp;
    

调用包中的函数或者过程、属性等

注意:只能调用声明部分的公有属性、函数、过程等,不能调用主体部分的私有数据

调用过程: 使用包名.过程名调用

exec pak_emp.print_sal(7566);

对主体部分属性进行赋值调用并输出

注意: 对变量进行赋值操作使用 := 在语句块中执行完必须写/

begin pak_field.field1 := 10;pak_field.field2 := 'abc'; 
dbms_output.put_line(pak_field.field1||' | '||pak_field.field2);
end;
/

当更改了主体部分和声明部分的sql时,就要在packages和package bodies文件夹下分别右键点击recompare重新编译

声明部分有的属性或者函数,主体部分必须实现,否则主体部分自定义的私有函数,外部无法调用,允许函数或者过程只在声明部分声明,不去主体部分实现也是可以的

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/37170.html

(0)

相关推荐

  • mysql全局变量和局部变量「建议收藏」

    mysql全局变量和局部变量「建议收藏」全局变量和局部变量 在服务器启动时,会将每个全局变量初始化为其默认值(可以通过命令行或选项文件中指定的选项更改这些默认值)。然后服务器还为每个连接的客户端维护一组会话变量,客户端的会话变量在连接时使用

    2022-12-16
    104
  • 最高检发布检察机关知识产权保护典型案例[亲测有效]

    最高检发布检察机关知识产权保护典型案例[亲测有效]检察机关知识产权保护典型案例目录1. 中某科技有限公司、陆某昌等三人侵犯商业秘密案2. 纪某民等四人侵犯商业秘密案3. 湖北双某鼓风机股份有限公

    2023-06-25
    109
  • python基础2练习(python第二章题库)

    python基础2练习(python第二章题库)有朋友说for循环 以及if 判断使用的不太好,作为python中的基础课程,今天给大家详细讲解for循环判断,并进行部分for练习介绍。

    2023-11-19
    100
  • Python GUI编程:安装使用Tkinter进行界面设计

    Python GUI编程:安装使用Tkinter进行界面设计Graphical User Interface(图形用户界面)是现代软件中不可缺少的一部分,它提供了一种更亲近、直观的界面,使用户能够以更方便的方式与程序交互。而Python Tkinter(Toolkit Interface)是Python标准库中内置的一款GUI工具包,它提供了创建基本GUI应用程序所需的组件和控件,并且容易学习和使用。

    2024-03-10
    67
  • Python字典元素添加方法掌握

    Python字典元素添加方法掌握Python是一门易于学习和阅读的编程语言,整个语言的学习曲线很平缓,这得益于Python的可读性和干净简洁的语法。字典是Python的一种内置类型,在Python中,字典是一种可变的容器,用于存储键值对(key-value pairs)。它类似于其他语言中的哈希表或关联数组,在Python中使用花括号({})表示,键和值用冒号(:)隔开。

    2024-06-19
    24
  • mysql定时备份 任务计划_mysql 定时任务与事件

    mysql定时备份 任务计划_mysql 定时任务与事件简介 在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解crontab指令,如果你会了…

    2023-03-15
    143
  • vba通配符打开文件_正则表达式 回车

    vba通配符打开文件_正则表达式 回车Word VBA|通配符查找、替换、VBA及正则表达式

    2023-06-28
    108
  • sql,group by_sql什么时候用having

    sql,group by_sql什么时候用having前言 group by的可以帮助我们在特定场景下查询到我们需要的数据,但group by的用法一直给人一种“飘忽”感,究其原因,还是对于该关键字缺乏深入理解。 下面通过一个例子,来简单解释group…

    2023-02-25
    121

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注