源码-Oracle数据库管理-第十三章-子程序和包-Part 2（定义子程序）

继续努力！

--13.1.5 使用Return语句
--代码13.4 在过程中使用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 RaiseSalary;


--13.1.6 管理子程序

--查询user_objects获取过程和函数的信息
SELECT object_name, object_type, created, last_ddl_time, status, temporary
  FROM user_objects
 WHERE object_type IN ('PROCEDURE','FUNCTION');


--查询user_source获取对象的源代码
SELECT line, text,type
  FROM user_source
 WHERE name = 'GET_DEPT_COUNT'
 ORDER BY line;


--user_errors获取产生的错误消息
CREATE OR REPLACE PROCEDURE raise_salary(p_deptno NUMBER) AS
BEGIN
  SELECT COUNT(*) INTO v_deptcount FROM emp WHERE deptno = p_deptno;
END;

--查询user_errors获取错误信息
 SELECT sequence, line, position, text, attribute, message_number
   FROM user_errors
  WHERE name = 'RAISE_SALARY';

--移除子程序和函数
DROP PROCEDURE raise_salary;
DROP FUNCTION get_raise_salary;


--13.1.7 在SQL语句中使用函数
--代码13.5 创建可在SQL语句中使用的函数
CREATE OR REPLACE FUNCTION get_dept_info(p_deptno dept.deptno%TYPE) 
     RETURN VARCHAR2               --该函数返回VARCHAR2类型
AS
  v_dept_info VARCHAR2(100);       --定义一个v_dept_info的本地变量
BEGIN
  --查询dept表中指定deptno的记录
  SELECT dname || loc INTO v_dept_info FROM dept WHERE deptno = p_deptno;
  --INSERT INTO dept VALUES(90,'船务部','上海');  
  RETURN v_dept_info;              --返回该字符串变量
EXCEPTION                          --异常处理部分
  WHEN NO_DATA_FOUND THEN          --捕捉NO_DATA_FOUND异常进行处理
    RETURN '没有找到相应的部门';
END;


SELECT ename, job, sal, get_dept_info(deptno) as "部门信息"
  FROM emp
 WHERE job = '职员';


--如果函数违反了约束规则（如，在函数体中修改了任何数据库表），则不能直接在SQL语句中调用该函数
CREATE OR REPLACE FUNCTION get_dept_info(p_deptno dept.deptno%TYPE) 
     RETURN VARCHAR2               --该函数返回VARCHAR2类型
AS
  v_dept_info VARCHAR2(100);       --定义一个v_dept_info的本地变量
BEGIN
  --查询dept表中指定deptno的记录
  SELECT dname || loc INTO v_dept_info FROM dept WHERE deptno = p_deptno;
  INSERT INTO dept VALUES(90,'船务部','上海');  
  RETURN v_dept_info;              --返回该字符串变量
EXCEPTION                          --异常处理部分
  WHEN NO_DATA_FOUND THEN          --捕捉NO_DATA_FOUND异常进行处理
    RETURN '没有找到相应的部门';
END;