Liny_@NotePad

沉迷ACG中

PL/SQL课堂练习（过程、函数、包、触发器)

YOYO posted @ 2009年3月15日 23:56 in 【数据库】 with tags 过程 函数 包 触发器 PL/SQL oracle 练习 , 3485 阅读

1、过程练习，掌握过程参数(in/out/in out)及调用环境
 1.1）过程名字PROC_EMP(in hireYear,out manCount,in out commis_pct)
  需求描述：
   输入一个hireYear，计算出该年入职的员工数staffCount；
   给定一个commis_pct，上述范围的职员如果没有提成（commission_pct）的，
   用这个数作为他的提成，已有提成的，不做改动
   上述范围职员的提成最大值用commis_pct参数返回

  1. CREATE OR REPLACE PROCEDURE proc_emp(hireYear IN NUMBER, manCount OUT NUMBER, commis_pct IN OUT NUMBER) IS
  2.        
  4.        -- 计算出该年入职的员工数staffCount
  5.        SELECT COUNT(*)
  6.        INTO manCount
  7.        FROM employees
  8.        WHERE hireYear = TO_CHAR(hire_date,'yyyy');
  9.        
  10.        -- 给定一个commis_pct，上述范围的职员如果没有提成（commission_pct）的，
  11.        -- 用这个数作为他的提成，已有提成的，不做改动
  12.        UPDATE employees
  13.        SET COMMISSION_PCT = commis_pct
  14.        WHERE hireYear = TO_CHAR(hire_date,'yyyy') AND commission_pct IS NULL;
  15.        
  16.        -- 上述范围职员的提成最大值用commis_pct参数返回
  17.        SELECT MAX(commission_pct)
  18.        INTO commis_pct
  19.        FROM employees
  20.        WHERE hireYear = TO_CHAR(hire_date,'yyyy');
  21.        
  22.        RETURN;
  23.  
  24. END proc_emp;

 1.2）写一个匿名块来测试上述过程，要求：
  对应的过程的3个参数，定义3个变量，并为变量赋值
 1.3）调用上述过程，call  Pro**（*）
 1.4）打印3个变量
 1.5）请分析结果是否正确

  1. -- 测试用匿名块
  3.  
  4.    manCount NUMBER;
  5.    
  6.    i NUMBER := 0.32;
  7.  
  9.  
  10.    proc_emp( 1989, mancount, i);
  11.    
  12.    DBMS_OUTPUT.put_line('manCount: '||mancount);
  13.    
  14.    DBMS_OUTPUT.put_line('commis_pct: '||i);
  15.    

2、函数练习
 根据输入的身高（cm）、体重（kg）判断一个人的体形是“正常、偏瘦、偏胖”
 算法是：  
  体重指数（BMI） = 体重（公斤） / 身高的平方（平方米）
  正常范围为18-25，低于18为体重不足，25-28为体重偏高，超过28为超重。公式男女适用。
 2.1）定义函数Judge_BMI(身高，体重) return varchar2（30）
 2.2）处理可能的异常（除零异常）
 2.3）根据BMI的范围，返回结果参考：
  您的身材很标准，有成为大众情人的潜力！
  您看起来玉树凌风，如果加点肉，会帮助你抵御台风！
  您看起来很有福相，如果有碳，您有可能创造在地震废墟中存活时间的新记录！
 2.4）用select 【】 from dual测试上述函数

  2.  
  3.   str VARCHAR2(100); 
  4.   bmi NUMBER;
  5.  
  7.  
  8.   bmi := kg*10000 / (cm*cm);
  9.  
  10.   IF bmi < 18 THEN
  11.      str := '您看起来玉树凌风，如果加点肉，会帮助你抵御台风！';
  12.   ELSIF bmi < 25 THEN
  13.      str := '您的身材很标准，有成为大众情人的潜力！';
  14.   ELSIF bmi < 28 THEN
  15.      str := '体重偏高:您看起来很有福相，如果有碳，您有可能创造在地震废墟中存活时间的新记录！';
  16.   ELSE
  17.      str := '超重:您看起来很有福相，如果有碳，您有可能创造在地震废墟中存活时间的新记录！';
  18.   END IF;
  19.  
  20.   RETURN(str);
  21.  
  23.  
  25.        DBMS_OUTPUT.put_line('身高输入有错！');
  26.  
  27. END judgeBMI;

3、Package 练习
 题目要求：
 给定一个半径，计算周长和面积，并打印结果。
 1、定义包头、包体
 2、定义一个Public常量PI=3.1415926
 3、定义并实现两个Function：circle（周长）和area（面积）
 4、定义并实现一个Procedure:print，输出"PI=3.1415926,半径=**时，周长=**，面积=***"
 5、写一个匿名块，测试上述的包
 目的：掌握package的定义和实现，掌握package中的公用变量（常量）和私有变量的使用

  1. CREATE OR REPLACE PACKAGE CountCircle IS
  2.  
  3.   -- Author  : ADMINISTRATOR
  4.   -- Created : 2009-2-26 18:21:13
  5.   -- Purpose : 计算圆形周长面积。
  6.  
  7.   -- Public type declarations
  8.   -- type <TypeName> is <Datatype>;
  9.  
  10.   -- Public constant declarations
  11.   PI CONSTANT NUMBER := 3.1415926;
  12.  
  13.   -- Public variable declarations
  14.  
  15.  
  16.   -- Public function and procedure declarations
  19.  
  20.   PROCEDURE print(r IN NUMBER);
  21.  
  22. END CountCircle;
  23.  
  25.  
  26.   -- Private type declarations
  27.  
  28.   -- Private constant declarations
  29.  
  30.   -- Private variable declarations
  31.  
  32.  
  33.   -- Function and procedure implementations
  36.     RETURN(2*pi*r);
  37.   END;
  38.  
  41.     RETURN(pi*r*r);
  42.   END;
  43.  
  44.   PROCEDURE print(r IN NUMBER) IS
  46.     DBMS_OUTPUT.put_line('PI='||pi||',半径='||r||'时，周长='||circle(r)||'，面积='||area(r));
  47.     RETURN;
  48.   END;
  49. END CountCircle;

4、触发器练习
 4.1   为hr.employees表建个触发器trg_check_salary_adding
       当要调整薪水时，每次幅度不可超过正负5%

  1. CREATE OR REPLACE TRIGGER trg_check_salary_adding
  2.   before UPDATE OF salary
  3.   ON employees 
  4.   FOR each ROW
  6.   IF :NEW.salary > :old.salary*1.05 OR :NEW.salary < :old.salary*0.95 THEN
  7.      raise_application_error(-20089,'每次幅度不可超过正负5%');
  8.   END IF;
  9.  
  10. END trg_check_salary_adding;

 4.2   建个触发器，记录每个Session的登录日志
     步骤：
  1）、建日志表 user_login_logs，字段（username,login_time,logout_time,ip_addr)
  2）、写个database_event触发器，捕获用户的登录和登出事件,并插入到上表

  1. -- 登录
  2. CREATE OR REPLACE TRIGGER database_event_login
  3.   after logon ON database
  5.   INSERT INTO user_login_logs(username, login_time, logout_time, ip_addr)
  6.   SELECT sys.login_user, SYSDATE, NULL, SYS_CONTEXT('USERENV','IP_ADDRESS')
  7.   FROM v$session
  8.   WHERE AUDSID = USERENV('SESSIONID');
  9.  
  10.   DBMS_OUTPUT.put_line(sys.login_user||'登陆');
  11. END database_event_login;
  12.  
  13. -- 登出
  14. CREATE OR REPLACE TRIGGER database_event_logout
  15.   before logoff ON database
  17.   UPDATE user_login_logs
  18.   SET logout_time = SYSDATE
  19.   WHERE username = (
  20.                     SELECT sys.login_user
  21.                     FROM v$session
  22.                     WHERE AUDSID = USERENV('SESSIONID')
  23.                     );
  24.  
  25.   DBMS_OUTPUT.put_line(sys.login_user||'登出');
  26.  
  27. END database_event_logout;
Small_feed 评论 (0)

登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter