`

存储过程、函数简单应用

阅读更多

 

create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) is
v_avgSal number(7,2):=0;
begin

     --按照部门计算该部门雇员的平均工资
     
     --单行SELECT ... INTO ...
     select  avg(nvl(sal,0)) into v_avgSal from emp where deptno=p_deptno;
     dbms_output.put_line(p_deptno||' 部门的平均工资为: '||v_avgSal);
     --异常情况
     exception when no_data_found then
     dbms_output.put_line(p_deptno||' 不存在...');
     dbms_output.put_line(sqlcode||' --- '|| Sqlerrm);
  
end avgSalaryForDept;

--执行

--可以通过游标调用一次列出所有部门的情况

declare cursor emp_cursor
is
select distinct deptno from emp order by deptno asc;
begin
   for idx in emp_cursor loop
      --执行存储过程
       avgSalaryForDept(idx.deptno);
   end loop;
end;

 

--带有输出模式的参数

--需要按照给定的雇员编号更新雇员的工资,工资上调10%,如果更新成功,显示输出OK
--否则输出FAILED

create or replace procedure raisedSalaryByEmpnoPROC(p_empno in emp.empno%type,
o_result out varchar2) is
begin
    --执行更新
    update emp set sal=sal*1.1 where empno=p_empno;
    if(sql%found) then
       o_result:='OK';
       commit;
    else
       o_result:='FAILED';
       rollback;
    end if;
end raisedSalaryByEmpnoPROC;

--调用,区分:从SQL Plus环境调用还是从PLSQL环境调用

--第一种情况,从SQL Plus环境调用
SQL> variable tmp varchar2(30);
SQL> exec raisedSalaryByEmpnoPROC(7369,:tmp);

PL/SQL 过程已成功完成。

SQL> print tmp;

TMP
--------------------------------
OK

SQL> select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80          10890                    20

SQL>

--通过验证发现7369号雇员的薪水已经涨了。

 

--第二种情况,从PLSQL环境调用

 

SQL> declare
  2     tmp varchar2(30);
  3  begin
  4     raisedSalaryByEmpnoPROC(7369,tmp);
  5     dbms_output.put_line('结果为: '||tmp);
  6  end;
  7  /
结果为: OK

PL/SQL 过程已成功完成。

SQL>  select * from emp where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80          11979                    20

SQL>

 

--调用存储过程,如果从SQL Plus环境中调用,可以有三种传参方法
--1、按照位置传参
--2、按照名字传参
--3、混合型传参


create or replace procedure avgSalaryForDept(p_deptno in emp.deptno%type) is
v_avgSal number(7,2):=0;
begin

     --按照部门计算该部门雇员的平均工资
     
     --单行SELECT ... INTO ...
     select  avg(nvl(sal,0)) into v_avgSal from emp where deptno=p_deptno;
     dbms_output.put_line(p_deptno||' 部门的平均工资为: '||v_avgSal);
     --异常情况
     exception when no_data_found then
     dbms_output.put_line(p_deptno||' 不存在...');
     dbms_output.put_line(sqlcode||' --- '|| Sqlerrm);
  
end avgSalaryForDept;

--执行调用的情况


--按照位置传参
exec avgSalaryForDept(10);

--按照名字传参

exec avgSalaryForDept(p_deptno=>30);

 




 

SQL> create or replace function tax(p_empno emp.empno%type) return number is
  2    Result number;
  3    v_ename emp.ename%type;
  4    v_sal emp.sal%type;
  5  begin
  6    
  7    --函数的功能是按照雇员编号计算雇员需要交纳的税金: 税金=工资*0.08
  8    select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  9    result:=v_sal*0.08;
 10    dbms_output.put_line(v_ename||' 需要缴纳的税金为: '||result);
 11    return(Result);
 12    exception when no_data_found then
 13    dbms_output.put_line('该雇员不存在...');
 14  end tax;
 15  /

函数已创建。


SQL> select object_name,object_type from user_objects
  2  where object_type='FUNCTION';

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TAX                  FUNCTION

SQL>

--通过数据字典查看函数定义的源码

SQL> select object_name,object_type from user_objects
  2  where object_type='FUNCTION';

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TAX                  FUNCTION

SQL> set pagesize 100;
SQL> select text from user_source
  2  where name='TAX';

TEXT
------------------------------------------------------------------------
function tax(p_empno emp.empno%type) return number is
  Result number;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin

  --函数的功能是按照雇员编号计算雇员需要交纳的税金: 税金=工资*0.08
  select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  result:=v_sal*0.08;
  dbms_output.put_line(v_ename||' 需要缴纳的税金为: '||result);
  return(Result);
  exception when no_data_found then
  dbms_output.put_line('该雇员不存在...');
end tax;

已选择14行。

SQL>


--执行调用

--第一种情况:从SQL Plus环境调用


SQL> variable v_result number;
SQL> exec :v_result:=tax(7369);
SMITH 需要缴纳的税金为: 1054.152

PL/SQL 过程已成功完成。

SQL> print v_result;

  V_RESULT
----------
  1054.152

SQL>


SQL> exec :v_result:=tax(1000);
该雇员不存在...
BEGIN :v_result:=tax(1000); END;

*
第 1 行出现错误:
ORA-06503: PL/SQL: 函数未返回值
ORA-06512: 在 "SCOTT.TAX", line 14
ORA-06512: 在 line 1


SQL>

函数存在缺陷,原因是因为在异常处理过程中,没有返回值。

create or replace function tax(p_empno emp.empno%type) return number is
  Result number;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  
  --函数的功能是按照雇员编号计算雇员需要交纳的税金: 税金=工资*0.08
  select ename,sal into v_ename,v_sal from emp where empno= p_empno;
  result:=v_sal*0.08;
  dbms_output.put_line(v_ename||' 需要缴纳的税金为: '||result);
  return(Result);
  exception when no_data_found then
  dbms_output.put_line('该雇员不存在...');
  result:=-1;
  return result;
end tax;


SQL>  exec :v_result:=tax(1000);
该雇员不存在...

PL/SQL 过程已成功完成。

SQL> print v_result;

  V_RESULT
----------
        -1

SQL>


--第二种情况:从PLSQL环境调用


--我们可以考虑使用游标

declare cursor emp_cursor
is
select ename, empno from emp order by empno asc;
v_result number(7,2);
begin
   
   for idx in emp_cursor loop
    v_result:=tax(idx.empno);
     dbms_output.put_line('****** '||idx.ename||' 需要缴纳的税金为: '||v_result);
   end loop;

end;
分享到:
评论
发表评论

文章已被作者锁定,不允许评论。

相关推荐

    存储过程,存储函数和触发器

    综上所述,存储过程、存储函数和触发器都是数据库中非常重要的组成部分,它们可以帮助开发者编写更加高效和健壮的应用程序。理解它们的用途和区别对于优化数据库性能和实现复杂业务逻辑至关重要。

    PLSQL程序设计-存储过程函数触发器

    ### PL/SQL程序设计——存储过程、函数与触发器 #### 概述 PL/SQL是一种专门为Oracle数据库设计的过程化语言,它结合了SQL的数据操纵功能和传统编程语言的流程控制功能,使得开发者能够在数据库环境中编写更为复杂...

    存储过程 函数

    编写存储过程和函数通常涉及SQL语法,例如在MySQL中,我们可以这样创建一个简单的存储过程: ```sql CREATE PROCEDURE `myProcedure`(IN param1 INT, OUT param2 VARCHAR(50)) BEGIN SELECT 'Hello, ' AS `msg`, ...

    MySQL数据库函数,触发器,存储过程

    通过深入学习和掌握MySQL的函数、触发器和存储过程,开发者能够更加高效地管理和操作数据库,解决复杂的数据处理问题,提升应用程序的性能。在实际项目中,熟练运用这些特性是数据库设计和优化的关键。通过"mysql...

    Oracle存储过程、游标、函数的详解

    ### Oracle存储过程、游标、函数的详解 #### 一、概述 在Oracle数据库中,存储过程、游标和函数是非常重要的组成部分,它们为数据库管理提供了强大的编程能力。通过学习这些概念,我们可以更加灵活地管理和操作...

    MYSQL的存储过程和函数简单写法

    【MySQL存储过程与函数简介】 MySQL的存储过程和函数是数据库管理中强大的工具,它们允许开发者将一组SQL语句组织在一起,...通过合理地使用存储过程和函数,开发者可以创建出结构清晰、易于维护的数据库应用程序。

    存储过程函数,触发器

    在开发过程中,合理设计和使用存储过程、函数和触发器可以提高应用程序的效率和可维护性。然而,过度依赖这些数据库级别的程序可能会导致代码难以理解和调试,因此需要平衡其使用,以达到最佳的软件架构。

    存储过程和函数

    总的来说,存储过程和函数在数据库应用中起着至关重要的作用,它们能提升代码的可维护性、复用性和性能。熟练掌握存储过程和函数的创建、调用以及管理,对于任何数据库开发者来说都是必要的技能。

    存储过程函数触发器

    在数据库系统中,存储过程、函数和触发器是三个核心概念,它们对于数据管理与业务逻辑的实现至关重要。本文将详细解析这些知识点,并通过提供的压缩包文件中的示例进行讲解。 1. 存储过程(Stored Procedures): ...

    练习利用PLSQL Developer编写和管理存储过程、存储函数和触发器等

    PLSQL Developer中存储过程、存储函数和触发器的编写和管理 PLSQL Developer是一种功能强大的Oracle数据库开发工具,提供了许多功能来帮助开发者快速编写和管理存储过程、存储函数和触发器等高级数据库对象。本文将...

    存储过程和存储函数1

    【存储过程和存储函数1】知识点详解 存储过程和存储函数是数据库管理中的重要概念,它们允许开发者将一系列SQL语句组合成一个可重用的模块。这些模块化操作不仅提高了代码的复用性,还能优化数据库性能,减少客户端...

    数据库函数、存储过程详解

    数据库函数和存储过程是数据库管理系统中的重要组成部分,它们在数据处理和业务逻辑实现中起着核心作用。在Sybase数据库系统中,这两者都提供了强大的功能,帮助开发者更有效地管理和操作数据。 首先,我们来详细...

    Orcale存储过程,游标,函数,简单易懂

    ### Orcale 存储过程、游标、函数详解 #### 一、存储过程概述 **存储过程**(Procedure)是一种数据库对象,它是由一系列 SQL 语句和过程性语句组成的预编译代码块,存储在数据库服务器上,并可以通过名称进行调用...

    第15章_存储过程与函数.docx

    存储过程和函数能够将复杂的 SQL 逻辑封装在一起,应用程序无须关心存储过程和函数内部复杂的 SQL 逻辑,而只需要简单地调用存储过程和函数即可。 什么是存储过程? 存储过程是指预先编译的一组 SQL 语句集,将其...

    java存储过程函数案例.ppt

    Java存储过程和函数是数据库操作中的重要组成部分,它们在Java应用程序与数据库交互时起到关键作用。存储过程是在数据库中预编译的SQL语句集合,而函数则返回一个值。这两种技术都允许开发者封装复杂的逻辑,提高...

    oracle 的函数、存储过程、游标、简单实例

    本主题将深入探讨Oracle中的几个核心概念:函数、存储过程、游标以及简单的实例,这些都是数据库管理员和开发人员日常工作中不可或缺的部分。 首先,我们来了解**Oracle函数**。函数是预定义的代码块,接受零个或多...

    oracle存储过程与函数

    总结,Oracle的存储过程和函数提供了强大的功能,使得数据库不仅仅是数据的存储库,还能执行复杂的业务逻辑和计算,从而提高了应用性能和安全性。在实际开发中,根据需求选择合适的数据处理方式,灵活运用存储过程和...

    oracle存储过程、函数和触发器

    触发器用于实现业务规则,数据验证和复杂的数据依赖逻辑,这些逻辑无法通过简单的约束条件或存储过程实现。在Oracle中,我们使用`CREATE TRIGGER`语句定义触发器,可以指定触发的事件、操作时间(BEFORE或AFTER)...

    java 函数 存储过程

    例如,一个Java应用程序可能会调用数据库的存储过程来执行复杂的查询,然后在函数中处理返回的数据。这样做可以充分利用两者的优势:Java函数处理业务逻辑和数据解析,而存储过程专注于高效地处理数据库操作。 通过...

    C#中调用ORACLE的PACKAGE里方法和存储过程的应用

    p_get存储过程使用游标返回数据集,而f_get函数则简单地将输入的字符串追加到'Good Luck!'后返回。 在C#部分代码中,我们首先需要建立一个到ORACLE数据库的连接,然后使用OracleCommand对象调用PACKAGE中的方法和...

Global site tag (gtag.js) - Google Analytics