随书光盘中的源码没有序号,部分找不到或者有bug,调试过程中一并更正。
其中,还有两个彩蛋:“双十九”乘法口诀表和“双九九”加法口诀表的输出(PL/SQL实现),以及一个附加(加薪函数的调用)
代码如下:
--代码2.9 使用嵌套块更新和插入部门表
/*说明:
1.随书源代码中v_deptno前缺少&符号,导致异常处理模块报错“ORA-01400: 无法将 NULL 插入”。
本人调试了半天,才发现问题所在,现已修复。
2.此段代码为手工录入,因为在随书光盘中未找到*/
/*以下为调试过程中使用的sql语句:
create table dept2 as select * from dept;
select * from dept2;
drop table dept2;*/
DECLARE
v_deptno NUMBER(2);
v_deptname VARCHAR2(14);
BEGIN
--内部嵌套块
BEGIN
SELECT dname
INTO v_deptname
FROM dept2
WHERE deptno = &v_deptno;
DBMS_OUTPUT.PUT_LINE('您查询的部门名称为:' || v_deptname);
END;
--内部嵌套块
DECLARE
v_loc VARCHAR2(13) := '上海浦东';
BEGIN
UPDATE dept2
SET loc = v_loc
WHERE deptno = v_deptno;
DBMS_OUTPUT.PUT_LINE('在内部嵌套块中成功更新部门资料');
COMMIT;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
INSERT INTO dept2
VALUES (&v_deptno,'IT开发','全球');
DBMS_OUTPUT.PUT_LINE('在异常处理嵌套块成功新增部门资料');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
--代码2.10 命名嵌套块(在代码2.9中增加了<<查询与员工名称块>>等三个标签)
DECLARE
v_deptno NUMBER(2);
v_deptname VARCHAR2(14);
BEGIN
--内部嵌套块
<<查询员工名称块>>
BEGIN
SELECT dname
INTO v_deptname
FROM dept2
WHERE deptno = &v_deptno;
DBMS_OUTPUT.PUT_LINE('您查询的部门名称为:' || v_deptname);
END;
--内部嵌套块
<<更新员工记录块>>
DECLARE
v_loc VARCHAR2(13) := '上海浦东';
BEGIN
UPDATE dept2
SET loc = v_loc
WHERE deptno = v_deptno;
DBMS_OUTPUT.PUT_LINE('在内部嵌套块中成功更新部门资料');
COMMIT;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
<<插入员工记录块>>
BEGIN
INSERT INTO dept2
VALUES (&v_deptno,'IT开发','全球');
DBMS_OUTPUT.PUT_LINE('在异常处理嵌套块成功新增部门资料');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
--代码2.11 PL/SQL变量定义示例
DECLARE
v_DeptName VARCHAR2(10); --定义标量变量
v_LoopCounter BINARY_INTEGER; --使用PL/SQL类型定义标量变量
--定义记录类型
TYPE t_Employee IS RECORD (EmpName VARCHAR2(20),EmpNo NUMBER(7),Job VARCHAR2(20));
v_Employee t_Employee; --定义记录类型变量
TYPE csor IS REF CURSOR; --定义游标变量
v_date DATE NOT NULL DEFAULT SYSDATE;--定义变量并指定默认值
BEGIN
NULL;
END;
/
--代码2.12 使用CASE进行条件判断
CREATE OR REPLACE FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2)
RETURN NUMBER AS
v_Result NUMBER(7,2);
BEGIN
CASE p_Job --使用CASE WHEN语句进行条件判断
WHEN 'CLERK' THEN --职员
v_Result:=0.10;
WHEN 'SALESMAN' THEN --销售
v_Result:=0.12;
WHEN 'MANAGER' THEN --经理
v_Result:=0.15;
WHEN 'cont' THEN --consultant,翻倍
v_Result:=1;
END CASE;
RETURN v_Result; --返回值
END;
--代码2.13 使用FOR循环打印九九乘法口诀表
DECLARE
v_Number1 NUMBER(3); --外层循环变量
v_Number2 NUMBER(3); --内存循环变量
BEGIN
FOR v_Number1 IN 1..9 --开始外层循环
LOOP
--进行内存循环
FOR v_Number2 IN 1..v_Number1
LOOP
--打印口决内容
DBMS_OUTPUT.PUT(v_Number1||'*'||v_Number2||'='||v_Number1*v_Number2||' ');
END LOOP;
--输出换行
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END;
--彩蛋1 使用FOR循环打印“双十九”乘法口诀表(印度乘法口诀表)
DECLARE
v_Number1 NUMBER(3); --外层循环变量
v_Number2 NUMBER(3); --内存循环变量
BEGIN
FOR v_Number1 IN 1..19 --开始外层循环
LOOP
--进行内存循环
FOR v_Number2 IN 1..v_Number1
LOOP
--打印口决内容
--第十行缩进,使得输出更美观
DBMS_OUTPUT.PUT (v_Number1||'*'||v_Number2||'='||v_Number1*v_Number2||' ');
END LOOP;
--输出换行
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END;
--彩蛋2 使用FOR循环打印“双九九”加法口诀表
DECLARE
v_Number1 NUMBER(3); --外层循环变量
v_Number2 NUMBER(3); --内存循环变量
BEGIN
FOR v_Number1 IN 1..19 --开始外层循环
LOOP
--进行内存循环
FOR v_Number2 IN 1..v_Number1
LOOP
--打印口决内容
--第十行缩进,使得输出更美观
DBMS_OUTPUT.PUT (v_Number1||'+'||v_Number2||'='||(v_Number1+v_Number2)||' ');
END LOOP;
--输出换行
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END;
--创建为员工加薪的过程
CREATE OR REPLACE PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER)
AS
BEGIN
IF p_Ratio>0 THEN --判断传入的参数是否大于0
--如果大于0,则更新Emp表中的数据
UPDATE scott.emp3 SET sal2=sal2*(1+p_Ratio) WHERE EMPNO=p_EmpNo;
END IF;
--提示加薪成功。
DBMS_OUTPUT.PUT_LINE('加薪成功!');
END;
--附加:调用加薪函数
DECLARE
v_empno NUMBER(4);
v_sal2 VARCHAR2(14);
v_ratio VARCHAR2(14);
BEGIN
SELECT sal2
INTO v_sal2
FROM emp3
WHERE empno = &v_empno;
addempsalary(&v_ratio, &v_empno);
DBMS_OUTPUT.PUT_LINE('员工'||&v_empno||'已成功加薪百分之'||&v_ratio*100);
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('未找到该员工!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
update emp3 set sal2=5120000 where empno=7509;
--代码2.15 员工加薪管理保代码
/*包规范定义,不包括实现代码*/
CREATE OR REPLACE PACKAGE EmpSalary
AS
--执行实际的加薪动作
PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER);
--使用IF-ELSIF语句得到加薪比率
FUNCTION GetAddSalaryRatio(p_Job VARCHAR2) RETURN NUMBER;
--使用CASE语句得到加薪比率
FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2) RETURN NUMBER;
END EmpSalary;
/
/*包体定义*/
CREATE OR REPLACE PACKAGE BODY EmpSalary
AS
PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER)
AS
BEGIN
IF p_Ratio>0 THEN --判断传入的参数是否大于0
--如果大于0,则更新Emp表中的数据
UPDATE scott.emp SET sal=sal*(1+p_Ratio) WHERE EMPNO=p_EmpNo;
END IF;
--提示加薪成功。
DBMS_OUTPUT.PUT_LINE('加薪成功!');
END;
FUNCTION GetAddSalaryRatio(p_Job VARCHAR2)
RETURN NUMBER AS
v_Result NUMBER(7,2);
BEGIN
IF p_Job='CLERK' THEN --如果为职员,加薪10%
v_Result:=0.10;
ELSIF p_Job='SALESMAN' THEN --如果为销售职员,加薪12%
v_Result:=0.12;
ELSIF p_Job='MANAGER' THEN --如果为经理,加薪15%
v_Result:=0.15;
END IF;
RETURN v_Result;
END;
FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2)
RETURN NUMBER AS
v_Result NUMBER(7,2);
BEGIN
CASE p_Job --使用CASE WHEN语句进行条件判断
WHEN 'CLERK' THEN --职员
v_Result:=0.10;
WHEN 'SALESMAN' THEN --销售
v_Result:=0.12;
WHEN 'MANAGER' THEN --经理
v_Result:=0.15;
END CASE;
RETURN v_Result; --返回值
END;
END EmpSalary;