`

存储过程、函数简单应用

阅读更多

 

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;
分享到:
评论
发表评论

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

相关推荐

    Oracle数据库存储过程技术文档.doc

    第一章 oracle存储过程概述 2 1.1 存储过程基本结构(PROCEDURE) 3 1.1.1创建存储过程 3 1.1.2 存储过程删除 5 1.1.3 调用存储过程 5 1.2存储函数(FUNCTIONE) 6 1.2.1 创建存储函数 6 1.2.2 删除存储函数 7 1.3 包...

    MySQL存储过程和函数的操作(十二)

    数据库对象表时存储和操作数据的逻辑结构,而数据库对象存储过程和函数,则是用来实现将一组关于表操作的sql语句... 存储过程和函数可以简单理解为一条或多条sql语句的集合。存储过程和函数就是事先经过编译并存储在数

    在ASP.NET中调用存储过程方法新解

    存储过程简介 简单的说,存储过程是由一些SQL语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数...

    毕业设计:ASP.NET基于WEB的选课系统(源代码)

    简单的说,存储过程是由一些SQL语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似,存储...

    ASP开发中存储过程应用全接触

    ASP开发中存储过程应用全接触 ASP与存储过程(Stored Procedures)的文章不少,但是我怀疑作者们是否真正实践过。我在初学时查阅过大量相关资料 ,发现其中提供的很多方法实际操作起来并不是那么回事。对于简单的...

    SQL Server基础:存储过程

    简单来说,存储过程是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。  一:存储过程概述  SQL Server...

    dbforge studio for mysql 7.2.63带补丁

    对于某些存储过程很多且复杂的SQL的应用,在短时间内要使得所有MySQL存储过程和函数正常运行,那么如果能找到一个比较好的调试工具,就可以事半功倍, 这里介绍的是dbForge Studio for MySQL。 1. 首先说明dbForge ...

    基于PHP Sofee米表程序的毕业设计,使用HTML、CSS和JavaScript来构建用户界面,使用MySQL数据库来存储

    它允许用户进行各种数学计算,包括简单的加减乘除、三角函数、指数函数和对数函数等。本毕设将使用该程序作为基础,扩展其功能并应用到实际的场景中。 本毕设的主要目标是开发一个新的应用程序,该程序将包括以下...

    WAP 无线应用协议

    WAP无线应用协议 英文 目 录 译者序 序 前言 第一部分 体系结构 第1章 无线应用协议体系结构规范 …1 1.1 范围 1 1.2 研究背景 1 1.2.1 研究目的 1 1.2.2 要求 2 1.3 体系结构概述 3 1.3.1 万维网模型 3 1.3.2 WAP...

    嵌入式实时操作系统small RTOS51原理及应用

    22.10 用户可修改、可调整的应用层函数 22.11 CAN通信在DP-51上运用的例子 第23章 PDIUSBD12 USB驱动程序的实现 23.1 简介 23.2 USB设备的枚举过程 23.3 USB标准设备请求 23.4 程序实现方法 第24章 完整的程序—...

    VBSCRIP5 -ASP用法详解

    ReDim 语句 声明动态数组变量,并分配或重新分配过程级的存储空间。 RegExp 对象 提供简单正则表达式的支持。 Rem 语句 包含程序中的注释。 Replace 函数 返回一个字符串,其中某个指定的子串被另一个子串替换,...

    精通sql结构化查询语句

    讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中的具体应用。...

    MySQL存储过程使用实例详解

    例1、一个简单存储过程游标实例 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$CREATE PROCEDURE getUserInfo(in date_day datetime)— — 实例— 存储过程名为:getUserInfo— 参数为:date_day...

    基于记录的应用程序设计

    决定了内部数据结构以及外部文件格式后,将整个任务分解成单独的函数,并对他们进行逐步精化就可以了,完整的程序存储在文件teach .cpp中。在设计程序代码注意以下问题:  程序怎样利用外部数据文件中的数据创建...

    VBSCRIPT中文手册

    ReDim 语句 声明动态数组变量,并分配或重新分配过程级的存储空间。 RegExp 对象 提供简单正则表达式的支持。 Rem 语句 包含程序中的注释。 Replace 函数 返回一个字符串,其中某个指定的子串被另一个子串替换,...

    vb Script参考文档

    ReDim 语句 声明动态数组变量,并分配或重新分配过程级的存储空间。 RegExp 对象 提供简单正则表达式的支持。 Rem 语句 包含程序中的注释。 Replace 函数 返回一个字符串,其中某个指定的子串被另一个子串替换,...

    SQL查询技巧(范例宝典)

     实例375 应用存储过程删除数据 550  实例376 C#应用查询存储过程 551  实例377 获取数据库中全部的存储过程 552  实例378 加密存储过程 553  10.23 触发器的应用 555  实例379 Insert触发器...

    Visual C++实践与提高——串口通信与工程应用篇1

    11.4.3 MCM200/MCM500读写操作过程 338 11.4.4 ZLG500A读写模块介绍 339 11.5 射频卡应用的设计 340 11.5.1 读写器成品的设计方法 340 11.5.2 使用射频卡模块的设计方法 340 11.6 小结 340 第12章 组态王的应用 341 ...

    VBScript 语言参考

    ReDim 语句 声明动态数组变量,并分配或重新分配过程级的存储空间。 RegExp 对象 提供简单正则表达式的支持。 Rem 语句 包含程序中的注释。 Replace 函数 返回一个字符串,其中某个指定的子串被另一个子串替换,...

Global site tag (gtag.js) - Google Analytics