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

调试nocopy功能时,为了看到实际效果,设置了一个比较大的循环次数,结果悲剧了:

运行了近1个小时没出结果,电脑死机(任务管理器都打不开);无奈只能强行关机,开机时间又特别长,一度让我以为系统崩溃。

看来,PL/SQL developer下调试这种暴力计算的程序风险很高啊,我在Eclipse下调试Java程序时从来没碰到过这种造成电脑死机的情况。


不过,这章的内容实用性很强,死机也值了(有点心疼电脑了委屈

-代码13.11 使用%Type定义形式参数
CREATE OR REPLACE PROCEDURE calcRaisedSalaryWithTYPE(
         p_job IN emp.job%TYPE,
         p_salary IN OUT emp.sal%TYPE               --定义输入输出参数
)
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;
  END IF;
  p_salary:=v_sal;                                   --将调整后的结果赋给输入输出参数
END calcRaisedSalaryWithTYPE;


--过程调用受形参的约束,违反约束,将触发异常
DECLARE
   v_sal NUMBER(8,2);                 --薪资变量
   v_job VARCHAR2(10);                 --职位变量
BEGIN
   v_sal:=1232945.45;
   v_job:='职员';
   calcRaisedSalaryWithTYPE(v_job,v_sal);                             --计算调薪
   DBMS_OUTPUT.put_line('计算后的调整薪水为:'||v_sal);    --获取调薪后的结果
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM);   
END;   


--参数传递有两种方式:按位置传递(类似Java的方法签名,实参与形参的类型一一对应)和按名称传递(如下例所示)
DECLARE
   v_sal NUMBER(8,2);                 --薪资变量
   v_job VARCHAR2(10);                 --职位变量
BEGIN
   v_sal:=123294.45;
   v_job:='职员';
   calcRaisedSalaryWithTYPE(p_job=>v_job,p_salary=>v_sal);                             --计算调薪
   DBMS_OUTPUT.put_line('计算后的调整薪水为:'||v_sal);    --获取调薪后的结果
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM);   
END;   

--按名称传递参数
DECLARE
   v_sal NUMBER(7,2);                 --薪资变量
   v_job VARCHAR2(10);                 --职位变量
BEGIN
   v_sal:=124.45;
   v_job:='职员';
   calcRaisedSalaryWithTYPE(p_salary=>v_sal,p_job=>v_job);                             --计算调薪
   DBMS_OUTPUT.put_line('计算后的调整薪水为:'||v_sal);    --获取调薪后的结果
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM);   
END;   


--混合使用按名称传递和按位置传递
DECLARE
   v_sal NUMBER(7,2);                 --薪资变量
   v_job VARCHAR2(10);                 --职位变量
BEGIN
   v_sal:=1224.45;
   v_job:='职员';
   calcRaisedSalaryWithTYPE(p_salary=>v_sal,v_job);                             --计算调薪
   DBMS_OUTPUT.put_line('计算后的调整薪水为:'||v_sal);    --获取调薪后的结果
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLCODE||' '||SQLERRM);   
END;   



--代码13.12 指定形式参数的默认值
CREATE OR REPLACE PROCEDURE newdeptwithdefault (
   p_deptno   dept.deptno%TYPE DEFAULT 57,    --部门编号
   p_dname    dept.dname%TYPE:='管理部',     --部门名称
   p_loc      dept.loc%TYPE DEFAULT '江苏'        --位置
)
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);--插入记录
END;

BEGIN
   newdeptwithdefault;       --不指定任何参数,将使用形参默认值
END;

BEGIN
   newdeptwithdefault(58,'事务组');       --不指定任何参数,将使用形参默认值
END;


BEGIN
   newdeptwithdefault(58,'事务组');       
END;

--按名称传递参数,灵活性更高
BEGIN
   newdeptwithdefault(p_deptno=>59,p_loc=>'南海');       --让dname使用默认值
END;


SELECT * FROM dept;


--代码13.13 Nocopy使用示例
DECLARE
   TYPE emptabtyp IS TABLE OF emp%ROWTYPE;               --定义嵌套表类型
   emp_tab   emptabtyp  := emptabtyp (NULL);             --定义一个空白的嵌套表变量
   t1        NUMBER (5);                                 --定义保存时间的临时变量
   t2        NUMBER (5);
   t3        NUMBER (5);

   PROCEDURE get_time (t OUT NUMBER)                     --获取当前时间
   IS
   BEGIN
      SELECT TO_CHAR (SYSDATE, 'SSSSS')                  --获取从午夜到当前的秒数
        INTO t
        FROM DUAL;
      DBMS_OUTPUT.PUT_LINE(t);        
   END;
   PROCEDURE do_nothing1 (tab IN OUT emptabtyp)          --定义一个空白的过程,具有IN OUT参数
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE do_nothing2 (tab IN OUT NOCOPY emptabtyp)   --在参数中使用NOCOPY编译提示
   IS
   BEGIN
      NULL;
   END;
BEGIN
   SELECT *
     INTO emp_tab (1)
     FROM emp
    WHERE empno = 5093;                                  --查询emp表中的员工,插入到emp_tab第1个记录
   emp_tab.EXTEND (90000, 1);                            --拷贝第1个元素N次
   get_time (t1);                                        --获取当前时间
   do_nothing1 (emp_tab);                                --执行不带NOCOPY的过程
   get_time (t2);                                        --获取当前时间
   do_nothing2 (emp_tab);                                --执行带NOCOPY的过程
   get_time (t3);                                        --获取当前时间
   DBMS_OUTPUT.put_line ('调用所花费的时间(秒)');
   DBMS_OUTPUT.put_line ('--------------------');
   DBMS_OUTPUT.put_line ('不带NOCOPY的调用:' || TO_CHAR (t2 - t1));
   DBMS_OUTPUT.put_line ('带NOCOPY的调用:' || TO_CHAR (t3 - t2));
END;
/



--代码13.14 定义可被SQL语句调用的子程序(不知道这样做有什么实际意义)
CREATE OR REPLACE FUNCTION getempdept(
        p_empno emp.empno%TYPE
) RETURN VARCHAR2                                 --参数必须是Oracle数据库类型
AS
  v_dname dept.dname%TYPE;               
BEGIN
   SELECT b.dname INTO v_dname FROM emp a,dept b 
   WHERE a.deptno=b.deptno
   AND a.empno=p_empno;
   RETURN v_dname;                                --查询数据表,获取部门名称
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;                                --如果出现查询不到数据,返回NULL
END;        


SELECT empno 员工编号,getempdept(empno) 部门名称 from emp;


--代码13.15 使用嵌套子程序示例
CREATE OR REPLACE FUNCTION getraisedsalary_subprogram (p_empno emp.empno%TYPE)
   RETURN NUMBER
IS
   v_salaryratio   NUMBER (10, 2);             --调薪比率  
   v_sal           emp.sal%TYPE;            --薪资变量     
   --定义内嵌子函数,返回薪资和调薪比率  
   FUNCTION getratio(p_sal OUT NUMBER) RETURN NUMBER IS
      n_job           emp.job%TYPE;            --职位变量
      n_salaryratio   NUMBER (10, 2);          --调薪比率       
   BEGIN
       --获取员工表中的薪资信息
       SELECT job, sal INTO n_job, p_sal FROM emp WHERE empno = p_empno;
       CASE n_job                               --根据不同的职位获取调薪比率
          WHEN '职员' THEN
             n_salaryratio := 1.09;
          WHEN '销售人员' THEN
             n_salaryratio := 1.11;
          WHEN '经理' THEN
             n_salaryratio := 1.18;
          ELSE
             n_salaryratio := 1.1;
       END CASE; 
       RETURN n_salaryratio;    
   END;        
BEGIN
   v_salaryratio:=getratio(v_sal);          --调用嵌套函数,获取调薪比率和员工薪资
   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;



BEGIN
   --调用函数获取调薪后的记录
   DBMS_OUTPUT.PUT_LINE('7369员工调薪记录:'||getraisedsalary_subprogram(7369));
   DBMS_OUTPUT.PUT_LINE('7521员工调薪记录:'||getraisedsalary_subprogram(7521));   
END;

--代码13.17 使用前向声明进行互调用(这是在踢皮球啊,呵呵)
DECLARE
   v_val BINARY_INTEGER:=5;
   PROCEDURE B(p_counter IN OUT BINARY_INTEGER);            --前向声明嵌套子程序B
   PROCEDURE A(p_counter IN OUT BINARY_INTEGER) IS          --声明嵌套子程序A
   BEGIN
      DBMS_OUTPUT.PUT_LINE('A('||p_counter||')');
      IF p_counter>0 THEN
         B(p_counter);                                      --在嵌套子程序中调用B
         p_counter:=p_counter-1;
      END IF;
   END A;
   PROCEDURE B(p_counter IN OUT BINARY_INTEGER) IS          --声明嵌套子程序B
   BEGIN
      DBMS_OUTPUT.PUT_LINE('B('||p_counter||')');
      p_counter:=p_counter-1;
      A(p_counter);                                          --在嵌套子程序中调用A
   END B;
BEGIN
   B(v_val);                                                 --调用嵌套子程序B
END;

--代码13.18 嵌套子程序重载示例
DECLARE
    PROCEDURE GetSalary(p_empno IN NUMBER) IS                       --带一个参数的过程
    BEGIN
      DBMS_OUTPUT.put_line('员工编号为:'||p_empno);      
    END;
    PROCEDURE GetSalary(p_empname IN VARCHAR2) IS                    --重载的过程
    BEGIN
      DBMS_OUTPUT.put_line('员工名称为:'||p_empname);
    END;
    PROCEDURE GETSalary(p_empno IN NUMBER,p_empname IN VARCHAR) IS   --生的过程
    BEGIN
      DBMS_OUTPUT.put_line('员工编号为:'||p_empno||' 员工名称为:'||p_empname);
    END;       
BEGIN 
    GetSalary(7369);                                                 --调用重载方未予
    GetSalary('史密斯');
    GetSalary(7369,'史密斯');        
END;   

SELECT * FROM emp;

CREATE TABLE emp_history AS SELECT * FROM emp WHERE 1=2;

SELECT * FROM emp_history;

--代码13.19 自治事务使用示例(提交自治事务,不影响主事务)
DECLARE
   PROCEDURE TestAutonomous(p_empno NUMBER) AS
     PRAGMA AUTONOMOUS_TRANSACTION;         --标记为自治事务
   BEGIN
     --现在过程中是自治的事务,主事务被挂起
     INSERT INTO emp_history SELECT * FROM emp WHERE empno=p_empno;
     COMMIT;                                --提交自治事务,不影响主事务
   END TestAutonomous;
BEGIN
   --主事务开始执行
   INSERT INTO emp_history(empno,ename,sal) VALUES(1011,'测试',1000);
   TestAutonomous(7369);                    --主事务挂起,开始自治事务
  ROLLBACK;                                --回滚主事务
END;

select * from emp_history where ename='测试';

--代码13.20 在PL/SQL中实现递归阶乘(这个挺有意思!)
DECLARE
  v_result INTEGER;
  FUNCTION fac(n POSITIVE)
       RETURN INTEGER IS                       --阶乘的返回结果
   BEGIN
      IF n=1 THEN                              --如果n=1则终止条件
         DBMS_OUTPUT.put('1!=1*0!');      
         RETURN 1;                         
      ELSE
       DBMS_OUTPUT.put(n||'!='||n||'*');
      RETURN n*fac(n-1);                      --否则进行递归调用自身
      END IF;
   END fac; 
BEGIN
  v_result:= fac(10);                          --调用阶乘函数
  DBMS_OUTPUT.put_line('结果是:'||v_result); --输出阶乘结果
END;




--代码13.21 使用递归查找职员列表示例(这个貌似挺有意思,可惜数据不够,调试时没看到效果)
DECLARE
    PROCEDURE find_staff (mgr_no NUMBER, tier NUMBER := 1)
    IS
       boss_name   VARCHAR2 (10);                  --定义老板的名称
       CURSOR c1 (boss_no NUMBER)                  --定义游标来查询emp表中当前编号下的员工列表
       IS
          SELECT empno, ename
            FROM emp
           WHERE mgr = boss_no;
    BEGIN
       SELECT ename INTO boss_name FROM emp
        WHERE empno = mgr_no;                      --获取管理者名称
       IF tier = 1                                 --如果tier指定1,表示从最顶层开始查询
       THEN
          INSERT INTO staff                          
               VALUES (boss_name || ' 是老板 ');   --因为第1层是老板,下面的才是经理
       END IF;
       FOR ee IN c1 (mgr_no)                       --通过游标FOR循环向staff表插入员工信息
       LOOP
          INSERT INTO staff
               VALUES (   boss_name
                       || ' 管理 '
                       || ee.ename
                       || ' 在层次 '
                       || TO_CHAR (tier));
          find_staff (ee.empno, tier + 1);        --在游标中,递归调用下层的员工列表
       END LOOP;
       COMMIT;
    END find_staff;
BEGIN
  find_staff(7566);                           --查询7839的管理下的员工的列表和层次结构
END;

CREATE TABLE staff(emplist VARCHAR2(1000));



SELECT * FROM staff;

SELECT * FROM emp where empno;
truncate table staff;

--代码13.22 子程序依赖性示例
CREATE OR REPLACE PROCEDURE TestDependence AS
BEGIN
   --向emp表插入测试数据
   INSERT INTO emp(empno,ename,sal) VALUES(1011,'测试',1000);
   TestSubProg(7369);                   
   --ROLLBACK;                               
END;
--被另一个过程调用,用来向emp_history表插入数据
CREATE OR REPLACE PROCEDURE TestSubProg(p_empno NUMBER) AS
 BEGIN
     INSERT INTO emp_history SELECT * FROM emp WHERE empno=p_empno;
 END TestSubProg;
 
select * from emp; 
select count(*) from emp_history;

--查看依赖
 SELECT name,type FROM user_dependencies WHERE referenced_name='EMP';
 
 
EXEC deptree_fill('TABLE','SCOTT','EMP');

--查看直接和间接依赖(需要先运行utldtgree.sql脚本)
SELECT nested_level, NAME, TYPE
  FROM deptree
 WHERE TYPE IN ('PROCEDURE', 'FUNCTION');
 
 
--过程和函数所依赖的表更改后,会失效,需重新编译(原代码顺序不对,容易误导,现已调试成功)

ALTER TABLE emp_history ADD emp_desc VARCHAR2(200) NULL;  
ALTER TABLE emp_history DROP COLUMN emp_desc;

alter procedure testsubprog compile;
alter procedure TESTDEPENDENCE compile;

SELECT object_name, object_type, status
  FROM user_objects
 WHERE object_name in ('TESTDEPENDENCE','TESTSUBPROG');

--子程序权限管理(留待后续学习) 
CREATE USER userb IDENTIFIED BY userb;                 --创建用户userb,密码也为userb
GRANT RESOURCE,CONNECT TO userb;                       --为userb分配角色
GRANT EXECUTE ON find_staff TO userb;                  --使得userb可以执行find_staff
DROP USER userb;


GRANT EXECUTE ON find_staff TO userb;

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
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
五、快照         前面实验说明了处理维度的扩展。本节讨论两种事实表的扩展技术。         有些用户,尤其是管理者,经常要看某个特定时间点的数据。也就是说,他们需要数据的快照。周期快照和累积快照是两种常用的事实表扩展技术。         周期快照是在一个给定的时间对事实表进行一段时期的总计。例如,一个月销售订单周期快照汇总每个月底时总的销售订单金额。         累积快照用于跟踪事实表的变化。例如,数据仓库可能需要累积(存储)销售订单从下订单的时间开始,到订单中的商品被打包、运输和到达的

SQL语句学习 - 2016-07-23 14:07:42

1、说明:创建数据库 CREATE DATABASE database - name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk' , 'testBack' , 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4、说明:创建新表 cre