源码-PL/SQL从入门到精通-第十三章-子程序-Part 1

对于PL/SQL这种基本上面向过程的语言来讲,子程序(过程和函数)就是其核心所在了。

整个解决方案可通过组织、调用由过程和函数构成的模块来实现。

在“Divide and conquer”(分而治之)的编程世界中,子程序就是程序员手中的兵器,用的越熟练越好。

--代码 13.1 创建过程示例

CREATE OR REPLACE PROCEDURE newdept (
   p_deptno   dept.deptno%TYPE,    --部门编号
   p_dname    dept.dname%TYPE,     --部门名称
   p_loc      dept.loc%TYPE        --位置
)
AS
   v_deptcount   NUMBER;           --保存是否存在员工编号
BEGIN
   SELECT COUNT (*) INTO v_deptcount FROM dept
    WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
   IF v_deptcount > 0              --如果存在相同的员工记录
   THEN                            --抛出异常
      raise_application_error (-20002, '出现了相同的部门记录');
   END IF;
   INSERT INTO dept(deptno, dname, loc)  
        VALUES (p_deptno, p_dname, p_loc);--插入记录
   COMMIT;                          --提交事务
END;

SELECT * FROM dept;

--代码13.2 调用过程示例
BEGIN
   newdept(10,'成本科','深圳');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('产生了错误:'||SQLERRM);
END;


--查询Oracle中命名块的列表
SELECT object_type 对象类型, object_name 对象名称, status 状态
 FROM user_objects
 WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY object_type, status, object_name;


--代码13.3 创建NewDept过程
CREATE OR REPLACE PROCEDURE newdept (
   p_deptno IN  NUMBER,    --部门编号
   p_dname  IN  VARCHAR2,     --部门名称
   p_loc    IN  VARCHAR2        --位置
)
AS
   v_deptcount     NUMBER(4);           --保存是否存在员工编号
   e_duplication_dept EXCEPTION;
BEGIN
   SELECT COUNT (*) INTO v_deptcount FROM dept
    WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
   IF v_deptcount > 0              --如果存在相同的员工记录
   THEN                            --抛出异常
      RAISE e_duplication_dept;
   END IF;
   INSERT INTO dept(deptno, dname, loc)  
        VALUES (p_deptno, p_dname, p_loc);--插入记录
   COMMIT;                          --提交事务
EXCEPTION   
   WHEN e_duplication_dept THEN
      ROLLBACK;
      raise_application_error (-20002, '出现了相同的员工记录');
END;

SHOW ERRORS;


SELECT * FROM emp;

--代码 13.4 Getraisedsalary函数示例
CREATE OR REPLACE FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
   RETURN NUMBER
IS
   v_job           emp.job%TYPE;            --职位变量
   v_sal           emp.sal%TYPE;            --薪资变量
   v_salaryratio   NUMBER (10, 2);          --调薪比率
BEGIN
   --获取员工表中的薪资信息
   SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = p_empno;
   CASE v_job                               --根据不同的职位获取调薪比率
      WHEN '职员' THEN
         v_salaryratio := 1.09;
      WHEN '销售人员' THEN
         v_salaryratio := 1.11;
      WHEN '经理' THEN
         v_salaryratio := 1.18;
      ELSE
         v_salaryratio := 1.05;
   END CASE;
   IF v_salaryratio <> 1                    --如果有调薪的可能
   THEN
      RETURN ROUND(v_sal * v_salaryratio,2);         --返回调薪后的薪资
   ELSE
      RETURN v_sal;                         --否则不返回薪资
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN 0;                             --如果没找到原工记录,返回0
END;

--代码13.5 函数调用示例
DECLARE
   v_raisedsal NUMBER(10,2);     --定义保存调薪记录的临时文件
BEGIN
   --调用函数获取调薪后的记录
   DBMS_OUTPUT.PUT_LINE('7369员工调薪记录:'||getraisedsalary(7369));
   v_raisedsal:=getraisedsalary(7521);
   DBMS_OUTPUT.PUT_LINE('7521员工调薪记录:'||getraisedsalary(7521));   
END;

SELECT * FROM emp;

--代码13.6 在过程中使用Return语句
CREATE OR REPLACE PROCEDURE RaiseSalary(
              p_empno emp.empno%TYPE             --员工编号参数
              )
AS
   v_job emp.job%TYPE;                           --局部的职位变量
   v_sal emp.sal%TYPE;                           --局部的薪资变量
BEGIN
   --查询员工信息
   SELECT job,sal INTO v_job,v_sal FROM emp WHERE empno=p_empno;
   IF v_job<>'职员' THEN                         --仅为职员加薪
      RETURN;                                    --如果不是职员,则退出
   ELSIF v_sal>3000 THEN                         --如果职员薪资大于3000,则退出
      RETURN;
   ELSE     
     --否则更新薪资记录
     UPDATE emp set sal=ROUND(sal*1.12,2) WHERE empno=p_empno;
   END IF; 
EXCEPTION
   WHEN NO_DATA_FOUND THEN                       --异常处理
      DBMS_OUTPUT.PUT_LINE('没有找到员工记录');     
END;              


--查看过程和函数列表
SELECT object_name, created, last_ddl_time, status
  FROM user_objects
 WHERE object_type IN ('FUNCTION','PROCEDURE');
 
 
--查看过程的源代码
SELECT   line, text
    FROM user_source
   WHERE NAME = 'RAISESALARY'
ORDER BY line;


--查看过程的编译错误
SELECT   line, POSITION, text
    FROM user_errors
   WHERE NAME = 'RAISESALARY'
ORDER BY SEQUENCE;



--删除子程序(函数或过程)
DROP FUNCTION getraisedsalary ;
DROP PROCEDURE NewDept;

SELECT * FROM dept;

--代码13.7 Insert过程示例
CREATE OR REPLACE PROCEDURE insertdept( 
   p_deptno NUMBER,                                     --定义形式参数
   p_dname VARCHAR2,
   p_loc VARCHAR2
)
AS
   v_count NUMBER(10);
BEGIN
   SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=p_deptno;
   IF v_count>1 THEN
      RAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号!');
   END IF;
   INSERT INTO dept VALUES(p_deptno,p_dname,p_loc);    --在过程体中使用形式参数
   COMMIT;
END;

--实参和形参必须类型兼容,否则报错
BEGIN
   insertdept('ABC','行政部','德克萨斯');
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM);   
END;


--代码13.8 使用In模式
CREATE OR REPLACE PROCEDURE insertdept( 
   p_deptno IN NUMBER:=55,                             --定义形式参数,并赋初值
   p_dname IN VARCHAR2,
   p_loc IN VARCHAR2
)
AS
   v_count NUMBER(10);
BEGIN
   --p_dname:='市场策略部';                            --错误,不能对IN模式参数进行赋值
   SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=p_deptno;
   IF v_count>1 THEN
      RAISE_APPLICATION_ERROR(-20001,'数据库中存在相同名称的部门编号!');
   END IF;
   INSERT INTO dept VALUES(p_deptno,p_dname,p_loc);    --在过程体中使用形式参数
   COMMIT;
END;

--以下过程的编译错误查询不到
SELECT   line, POSITION, text
    FROM user_errors
   WHERE NAME = 'insertdept'
ORDER BY SEQUENCE;

--调用过程时传递的参数称为实参
BEGIN
   insertdept(55,'勤运部','西北');
END;

select * from dept;

--代码 13.9 使用out模式
CREATE OR REPLACE PROCEDURE OutRaiseSalary(
    p_empno IN NUMBER,
    p_raisedSalary OUT NUMBER                     --定义一个员工加薪后的薪资的输出变量
)
AS
    v_sal NUMBER(10,2);                           --定义本地局部变量
    v_job VARCHAR2(10);
BEGIN
    p_raisedSalary:=0;                            --变量赋初值
    SELECT sal,job INTO v_sal,v_job FROM emp WHERE empno=p_empno;   --查询员工信息
    IF v_job='职员' THEN                          --仅对职员加薪
       p_raisedSalary:=v_sal*1.12;                --对OUT模式的参数进行赋值是合法的
       UPDATE emp SET sal=p_raisedSalary WHERE empno=p_empno;
    ELSE
       p_raisedSalary:=v_sal*1.1;                     --否则赋原来的薪资值
       UPDATE emp SET sal=p_raisedSalary WHERE empno=p_empno;
    END IF;
EXCEPTION    
   WHEN NO_DATA_FOUND THEN                         --异常处理语句块
     DBMS_OUTPUT.put_line('没有找到该员工的记录');
END;    


SELECT * FROM emp;

DECLARE 
   v_raisedsalary NUMBER(10,2);            --定义一个变量保存输出值
BEGIN
   v_raisedsalary:=100;                     --这个赋值在传入到OutRaiseSalary后会被忽略
   OutRaiseSalary(5093,v_raisedsalary);     --调用函数
   DBMS_OUTPUT.put_line(v_raisedsalary); --显示输出参数的值
END;

--代码13.10 使用In Out模式
CREATE OR REPLACE PROCEDURE calcRaisedSalary(
         p_job IN VARCHAR2,
         p_salary IN OUT NUMBER                         --定义输入输出参数
)
AS
  v_sal NUMBER(10,2);                               --保存调整后的薪资值
BEGIN
  if p_job='职员' THEN                              --根据不同的job进行薪资的调整
     v_sal:=p_salary*1.12;
  ELSIF p_job='销售人员' THEN
     v_sal:=p_salary*1.18;
  ELSIF p_job='经理' THEN
     v_sal:=p_salary*1.19;
  ELSE
     v_sal:=p_salary*1.1;
  END IF;
  p_salary:=v_sal;                                   --将调整后的结果赋给输入输出参数
END calcRaisedSalary;



本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
调试nocopy功能时,为了看到实际效果,设置了一个比较大的循环次数,结果悲剧了: 运行了近1个小时没出结果,电脑死机(任务管理器都打不开);无奈只能强行关机,开机时间又特别长,一度让我以为系统崩溃。 看来,PL/SQL developer下调试这种暴力计算的程序风险很高啊,我在Eclipse下调试Java程序时从来没碰到过这种造成电脑死机的情况。 不过,这章的内容实用性很强,死机也值了(有点心疼电脑了 ) -代码13.11 使用%Type定义形式参数CREATE OR REPLACE PROCEDURE
Less - 54 Challenge-1 Test Sourse Code Solution Less - 55 Challenge-2 Test Sourse Code Solution Less - 56 Challenge-3 Test Sourse Code Solution Less - 57 Challenge-4 Test Sourse Code Solution Less - 58 Challenge-5 Test Sourse Code Solution Less - 59 Chall

CentOS7安装配置hadoop2.7.2 - 2016-07-23 18:07:08

linux 虚拟机配置 系统配置: 虚拟机:一个master(Master.Hadoop),两个slave(Slave1.Hadoop, Slave2.Hadoop) 网络设置:我使用的是桥接的方式 内存:每个虚拟机配置4G内存 分区:手动配置 软件选择:最小安装,注意选择开发工具 用户设置:密码都设置为:hadoophadoop, 不创建任何用户,操作时使用root直接进行 安装完后各个虚拟机的ip配置(参考用) 主机 ip地址 master.hadoop 192.168.200.30 slave1.h
最近写的项目中有用到数据库,写了不少蛋疼的sql语句,每次都是好几行代码,而且每次都是重复的没有一点技术含量的代码,虽然也有不少基于sqlite的封装,不过用起来还是感觉不够面向对象! 为了不再写重复的代码,花了几天时间,基于SQLite3简单封装了下,实现了一行代码解决增删改查等常用的功能!并没有太过高深的知识,主要用了runtime和KVC: 首先我们创建个大家都熟悉的Person类,并声明两个属性,下面将以类此展开分析 @interface Person : NSObject @property (

MySQL原来也有内存数据库 - 2016-07-23 18:07:20

以前只知道MySQL可以做分库分表,支持多种数据库引擎。这次听了来自台湾的MySQL专家Ivan Tu的讲座,有两点印象深刻,一是MySQL具有丰富的高可用方案,二是MySQL也有内存数据库,即MySQL Cluster CGE。Ivan形象的将内存数据库比喻成浅浅的盘子,而传统的MySQL磁盘存储方式比喻成深深的瓶子。 另外,也探讨了MySQL作为开源数据库,和Oracle数据库的关系。MySQL并行发展企业版和社区版,企业版增加了很多企业级的特性,如安全,备份和恢复,监控,以及CGE(电信运营商版)。
CleverCode研究了一下,怎么kill掉慢的sql语句。 1 单个kill mysql show processlist; mysql kill 251; #批量kill 1)查找Lockd语句 mysql -uroot -p123456 -h 192.1.20.101 -e "show processlist" | grep -i 'Locked' locked_log.txt 2)组合kill for line in `cat locked_log.txt | awk '{print $1}'`
前段时间把项目中的JPA从EclipseLink换成Hibernate。这其中遇到了很多问题,也记录了一部分在其他的文章中。这里介绍一个UT中遇到的问题。 当时的测试是忘H2数据库中插入多项数据,然后用Query的方式删除,assert读出来的数据是null。这个测试用例在EclipseLink下面是可以通过的,但换成Hibernate之后发现数据仍然可以读出这个数据。 测试用例如下: @Test public void shouldRemovesAllFmReportFromDb () { FmRepo
实施逻辑复制软件时对在目的端数据库的字符集(排序规则)的要求 1.当目的端数据库是Oracle数据库时,务必保证目的端Oracle数据库的字符集与源头Oracle数据库的字符集保持一致. 2.当目的端数据库是MSSQLServer数据库时,务必保证目的端MSSQLServer 用户数据库的排序规则与源头MSSQLServer 用户数据库的排序规则一致. 3.当目的端数据库是MSSQLServer数据库时,务必保证目的端MSSQLServer master数据库的排序规则与源头MSSQLServer mas
前言 最近在学习使用Hive(版本0.13.1)的过程中,发现了一些坑,它们或许是Hive提倡的比关系数据库更加自由的体现(同时引来一些问题),或许是一些bug。总而言之,这些都需要使用Hive的开发人员额外注意。本文旨在列举我发现的2个通过查询语句向表中插入数据过程中的问题,希望大家注意。 数据准备 为了验证接下来出现的问题,需要先准备两张表 employees和 staged_employees ,并准备好测试数据。首先使用以下语句创建表 employees: create table employe
五、快照         前面实验说明了处理维度的扩展。本节讨论两种事实表的扩展技术。         有些用户,尤其是管理者,经常要看某个特定时间点的数据。也就是说,他们需要数据的快照。周期快照和累积快照是两种常用的事实表扩展技术。         周期快照是在一个给定的时间对事实表进行一段时期的总计。例如,一个月销售订单周期快照汇总每个月底时总的销售订单金额。         累积快照用于跟踪事实表的变化。例如,数据仓库可能需要累积(存储)销售订单从下订单的时间开始,到订单中的商品被打包、运输和到达的