PL/SQL练习题——plsqlAdd_Prac,Part A,1-10

weixin_30563917

于 2019-06-13 20:34:00 发布

阅读量470

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/AlleyMeowy/p/11019326.html

版权

　　本人最近在学PL/SQL，闲来无事做了一套练习题，写了点笔记，供大家参考。

　　第一题，要求选出不合法的声明。A多余声明，C缺少冒号，D类型不匹配，只有B是正确的的。

　　第二题，要求给出变量类型。答案如下，不多解释。

　　　　a. CHAR / VARCHAR2

　　　　b. DATE

　　　　c. NUMBER

　　　　d. BOOLEAN

　　　　e. BOOLEAN

　　　　f. DATE

　　第三题，要求分别给出在1和2位置，每个变量的取值。这里要注意每个变量的作用域，看清楚一个匿名块里套着另一个匿名块，以及匿名块的范围，很容易得到答案。

　　我的测试代码如下：

DECLARE 
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_new_custid NUMBER(3) := 500;
BEGIN
  DECLARE
    v_custid NUMBER(4) := 0;
    v_custname  VARCHAR2(300) := 'Shape up Sports Club';
    v_new_custid  NUMBER(3) := 300;
    v_new_custname  VARCHAR2(300) := 'Jansports Club';
  BEGIN
    v_custid := v_new_custid;
    v_custname := v_custname || ' ' || v_new_custname;
    DBMS_OUTPUT.put_line('(1) ' || v_custid || '——' || v_custname || '——' ||
                                   v_new_custid || '——' || v_new_custname);
  END;
  v_custid := (v_custid * 12) / 10;  
  DBMS_OUTPUT.put_line('(2) ' || v_custid || '——' || v_custname || '——' ||
                                 v_new_custid);
END;

　　屏幕上的输出如图：

　　因此，可以得出答案：

　　a. The value of V_CUSTID at position 1 is: 300

　　b. The value of V_CUSTNAME at position 1 is: Shape up Sports Club Jansports Club

　　c. The value of V_NEW_CUSTID at position 2 is: 500

　　d. The value of V_NEW_CUSTNAME at position 1 is: Jansports Club

　　e. The value of V_CUSTID at position 2 is: 1920

　　f. The value of V_CUSTNAME at position 2 is: Women Sports Club

　　第四题，要求写一个匿名块，根据输入的年份判断是否是闰年。

　　输入使用替代变量，IF...THEN的条件判断不多说，这里提一下那一长串的条件。

　　判断闰年的标准是：(1) 能被4整除且不被100整除；(2) 能被400整除。

　　条件(1)中是通过AND连接的，条件(2)是通过OR连接的。初步确定条件应该这么写： ((= 4 AND <> 100) OR (= 400))

　　由于AND的优先级是高于OR的，这里可以省去一层括号，即(= 4 AND <> 100 OR = 400)。

　　给出代码如下，测试通过。

DECLARE
v_year INTEGER(4);
BEGIN
  v_year := '&请输入年份：';
  IF MOD(v_year, 4) = 0 AND MOD(v_year, 100) <> 0 OR MOD(v_year, 400) = 0 THEN
    DBMS_OUTPUT.put_line(v_year || ' is a leap year.');
  ELSE 
    DBMS_OUTPUT.put_line(v_year || ' is not a leap year.');
  END IF;
END;

　　第五题，建一张临时表，通过匿名块插入数据。

　　这里需要声明两个变量，一个VARCHAR2类型的message，一个Date类型的date_written，分别赋值。

　　【这张表里没有主键、外键这些约束，INSERT一般不会报错吧……简单抛个异常好了】

　　处理EXCEPTION中的OTHERS表示任何类型的异常，当需要处理多个异常时，OTHERS一定放在最后；SQLCODE和SQLERRM分别表示错误代码和错误信息，下面给出一个例子。

　　其中100是错误代码，'ORA-01403：未找到任何数据'是错误信息。

DECLARE
  message VARCHAR2(35) := 'This is my first PL/SQL program';
  date_written DATE := SYSDATE;
BEGIN
  INSERT INTO Temp_22481(num_store, char_store, date_store)
  VALUES(NULL, message, date_written);
  
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;

　　第六题，输入一个部门ID，输出该部门的人数。

　　这里声明了三个变量，分别存储部门ID，部门名称和员工人数。

　　通过两个SELECT语句查询结果，第一个从departments表（部门表）获取部门名称，第二个从employees表（雇员表）获取员工人数。

　　因为用到了SELECT INTO，可能会有查不到的情况，因此，这里需要捕获一个no_data_found的异常。

DECLARE
  v_dept_no departments.department_id%TYPE := &v_dept_no;
  v_dept_name departments.department_name%TYPE;
  v_employees_num INTEGER;
BEGIN
  SELECT department_name
  INTO v_dept_name
  FROM departments d
  WHERE department_id = v_dept_no;
  
  SELECT COUNT(1)
  INTO v_employees_num
  FROM employees e
  WHERE department_id = v_dept_no;
  DBMS_OUTPUT.put_line('部门 ' || v_dept_name || ' 的员工人数为 ' || v_employees_num );
  EXCEPTION 
    WHEN no_data_found THEN
      DBMS_OUTPUT.put_line('There''s no such department...');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;

　　※由于接下来涉及到employees表的修改，因此，这里拷贝employees表和departments表，后面在复制的表中进行操作。※

　　复制表的代码如下：

CREATE TABLE copy_emp AS
  SELECT * FROM employees;
CREATE TABLE copy_dept AS
  SELECT * FROM departments;

　　第七题，输入员工的姓（last_name），查询对应的薪水。如果薪水高于3000，输出'<员工名> earns <薪水>'；如果薪水低于3000，涨薪500并输出'<员工名> 's salary updated'。最后的表格是四组测试数据。

　　通过输入接收员工的姓，再通过SELECT获取对应的salary，用IF来做判断……

　　因为题里没说要COMMIT，我也就没COMMIT……

-- 计算工资
DECLARE 
  v_last_name copy_emp.last_name%TYPE;
  v_salary    copy_emp.salary%TYPE;
BEGIN
  v_last_name := INITCAP('&请输入lastname：');
  SELECT salary
  INTO v_salary
  FROM copy_emp e
  WHERE e.last_name = INITCAP(v_last_name);
  IF v_salary < 3000 THEN
    UPDATE copy_emp e SET salary = salary + 500 WHERE e.last_name = v_last_name;
    -- COMMIT;
    DBMS_OUTPUT.put_line(v_last_name  || ' ''s salary updated ' || (v_salary + 500));
  ELSE 
    DBMS_OUTPUT.put_line(v_last_name  || ' earns ' || v_salary);
  END IF;
  EXCEPTION 
    WHEN no_data_found THEN
      DBMS_OUTPUT.put_line('There''s no such employee...');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;

　　最后附上一句查表的语句，用于验证结果。

-- 查表验证
SELECT * FROM copy_emp;

　　第八题，输入月薪，乘以12得到年薪，然后根据表1的对应关系，输出奖金。

　　没有SELECT INTO也没有UPDATE，不需要处理异常，甚至运算逻辑比第四题的闰年还简单……我一开始还以为这道题跟其他的题有联系，都做了一遍后，发现是我想多了……嗯，练习控制语句用的吧。

DECLARE
  v_salary          copy_emp.salary%TYPE;
  v_annual_salary NUMBER;
  v_bonus         NUMBER;
BEGIN
  v_salary := '&请输入月薪';
  v_annual_salary := v_salary * 12;
  IF v_annual_salary >= 20000 THEN
    v_bonus := 2000;
  ELSIF    v_annual_salary >= 10000 AND v_annual_salary < 20000 THEN
      v_bonus := 1000;
  ELSE
    v_bonus := 500;
  END IF;
  DBMS_OUTPUT.put_line('The bonus is $' || v_bonus);
END;

　　第九题，输入员工ID，新部门ID，以及薪水涨幅，更新employee表（雇员表）对应员工的部门和薪水，如果查不到，返回No Data Found。

　　这是第一个练习Exception异常的题（6 7都可以不写的）。故事背景是，假设一位员工因业绩优秀，不仅涨了薪还换到了新的部门，很快就可以出任CEO，迎娶白富美，走上人生巅峰……

　　嗯，扯远了。由于之前已经用过了Exception，这里不多介绍。通过输入获取员工ID、部门ID和涨幅三个变量，如果找不到对应的员工ID，抛出异常。

　　Exception中的RAISE_APPLICATION_ERROR，可以用我们自己定义的代码替换Oracle通用的报错信息，同时停止进程。在后面的触发器中，由于需要在UPDATE前拦截住不合法的修改，因此在Exception里写DBMS_OUTPUT是没用的，需要用RAISE_APPLICATION_ERROR停止进程。

　　RAISE_APPLICATION_ERROR有两个参数。第一个是错误代码，取值-20000~-20999，可以保证和Oracle已有的所有错误代码不冲突；第二个是报错信息，即一段自定义的文字。

DECLARE
  v_employee_id             copy_emp.employee_id%TYPE;
  new_department_id         copy_dept.department_id%TYPE;      
  increase                  NUMBER;
  v_emp_temp                copy_emp%ROWTYPE;
BEGIN
  v_employee_id := '&请输入employee_id：';
  new_department_id := '&请输入new_department_id：';
  increase := '&请输入increase：';
  SELECT *
  INTO v_emp_temp
  FROM copy_emp e
  WHERE e.employee_id = v_employee_id;
  
  UPDATE copy_emp e
  SET e.department_id = new_department_id, salary = ROUND(salary * (1 + increase/100), 2)
  WHERE e.employee_id = v_employee_id;
  
  EXCEPTION 
    WHEN no_data_found THEN
      RAISE_APPLICATION_ERROR(-20101, 'There''s no such employee!');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;

　　最后给出一个查表语句，用于验证。

-- 查表语句
SELECT * FROM copy_emp WHERE employee_id = 111;

　　第十题……终于开始游标了。定义一个游标，选出月薪高于15000且在1988年2月1日后入职的员工。

　　这里用了FOR循环的方式使用游标，省去了OPEN和CLOSE的过程，同时也不用专门声明一组变量用于保存列值了（这里就用 i 遍历游标的每一行）。

　　我写了两种方法。首先想到的是方法1，查询employee表（员工表）的所有行，然后在遍历游标过程中判断是否满足月薪高于15000，入职时间晚于1988年2月1日；后来改成了方法2，即在创建游标的过程中设置筛选条件。这样一方面节省了空间，同时减少了遍历次数，也节省了时间。

-- 方法 1
DECLARE
  CURSOR emp_cursor1 IS
    SELECT last_name, salary, hire_date
    FROM copy_emp e;
BEGIN
  FOR i IN emp_cursor1 LOOP
    IF i.salary > 15000 AND i.hire_date > TO_DATE('1988-02-01', 'YYYY-MM-DD') THEN
      DBMS_OUTPUT.put_line(i.last_name || ' earns ' || i.salary 
       || ' and joined the organization on ' 
       || TO_CHAR(i.hire_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'));
    END IF;
  END LOOP;  
END ;

-- 方法 2
DECLARE
  CURSOR emp_cursor1 IS
    SELECT last_name, salary, hire_date
    FROM copy_emp e
    WHERE e.salary > 15000 AND e.hire_date > TO_DATE('1988-02-01', 'YYYY-MM-DD');
BEGIN
  FOR i IN emp_cursor1 LOOP
    DBMS_OUTPUT.put_line(i.last_name || ' earns ' || i.salary 
       || ' and joined the organization on ' 
       || TO_CHAR(i.hire_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'));
  END LOOP;  
END;