ORACLE存储过程、自定义函数简单介绍

存储过程中调用自定义函数？

CREATE OR REPLACE PROCEDURE sp_test1
IS
CURSOR c_fun_bigger 
IS 
SELECT empno,ename,fun_sal(deptno) AS fun_bigger
FROM emp;

BEGIN
FOR x IN c_fun_bigger LOOP 
  dbms_output.put_line(x.empno||x.ename||x.fun_bigger);
END LOOP;
END;

BEGIN 
  sp_test1;
END;

--查看当前用户下拥有的sp
select * from user_objects t where t.OBJECT_TYPE = 'PROCEDURE';
--比如：将 输出部门20的最高工资和最低工资的员工的工号，姓名，工资，部门编号 的功能封装成存储过程
CREATE OR REPLACE PROCEDURE sp_dept20(v_deptno NUMBER) IS
       
      CURSOR c_dept IS

      SELECT t1.empno,t1.ename,t1.sal,t1.deptno
      FROM emp t1
      JOIN (
      SELECT MAX(sal)AS max_sal ,MIN(sal)AS MIN_sal,deptno
      FROM emp
      WHERE deptno=v_deptno
      GROUP BY deptno) t2 ON t1.deptno=t2.deptno
      WHERE t1.sal IN (t2.max_sal,t2.min_sal);

      BEGIN
        FOR x IN  c_dept LOOP

        dbms_output.put_line('员工号'||x.empno);
        END LOOP;
        END;
--调用     
BEGIN 
  sp_dept20(20);
  END ;
  
CALL  sp_dept20(20);
  
 --小练习一把：创建一个sp，实现 将 emp表中的部门20的记录新增到 emp_bak3中，并成功调用该sp
CREATE OR REPLACE PROCEDURE sp_emp_bak3(v_deptno NUMBER) IS
       BEGIN
       INSERT INTO emp_bak3
       SELECT t1.empno,t1.empno,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno
       FROM emp t1
       WHERE deptno=v_deptno;
         END ;
    
BEGIN 
  sp_emp_bak3(20);
  END;
  SELECT * FROM emp_bak3;

--比如：创建一个自定义函数，实现 比较两个数字的大小，返回较大的那个数字

create or replace function fun_bigger(p_x number,p_y number)
return number
is
begin
  if p_x - p_y then
    return p_x ;
  else
    return p_y;
  end if;
end;

--调用自定义函数的语法结构：跟调用内置函数一样
select  fun_bigger(1,2)
from dual;

select e.*,
       fun_bigger(e.sal,nvl(e.comm,0)) as bigger
from emp e;
--1、创建一个自定义函数，实现返回emp表中部门的平均工资并取整
SELECT round(AVG(sal)) AS avg_sal
FROM emp 
WHERE deptno=p_deptno
GROUP BY deptno;
--自定义函数
CREATE OR REPLACE FUNCTION fun_sal (p_deptno NUMBER) 
  RETURN NUMBER
  IS
        v_avg_sal NUMBER;
  BEGIN 
SELECT round(AVG(sal)) AS avg_sal
INTO v_avg_sal
FROM emp 
WHERE deptno=p_deptno
GROUP BY deptno;
RETURN v_avg_sal;
END ;
--调用
select e.*,
       fun_sal(e.deptno) fun,
       round(avg(e.sal)over(partition by e.deptno)) 分析,
       length(e.ename)
from emp e;
--2、创建一个fun，实现：判断任务是否逾期。规则：如果两个日期之间相隔天数超过7天，则显示逾期，否则显示 正常
CREATE OR REPLACE FUNCTION fun_test2 (fun_x VARCHAR2 ,fun_y VARCHAR2)
RETURN VARCHAR2 
IS 
v_x NUMBER ;
BEGIN 
  v_x:=ABS(fun_x-fun_y);
  IF v_x>7 THEN RETURN '逾期';
  ELSE RETURN '正常';
  END IF;
  END ;
  
SELECT e.*,fun_test2(to_char(hiredate,'yyyymmdd'),to_char(LAG(hiredate)OVER(PARTITION BY deptno ORDER BY hiredate ASC),'yyyymmdd') )AS fun_test2
FROM emp e;
--3、创建一个fun，实现，跟分析函数一样的效果：返回emp表中的平均工资
--方法一
CREATE OR REPLACE FUNCTION fun_test3 (fun_deptno NUMBER)
RETURN NUMBER
IS
v_avg_sal NUMBER;
CURSOR c_fun_test3 IS
  SELECT t1.*,t2.avg_sal
  FROM emp t1
  JOIN (
    SELECT AVG(sal) AS avg_sal,DEPTNO
    FROM emp
    WHERE deptno=fun_deptno
    GROUP BY DEPTNO) t2
    ON t1.deptno =t2.deptno;
BEGIN
  FOR x IN c_fun_test3 LOOP
  v_avg_sal:=x.avg_sal;
  END LOOP;
  RETURN v_avg_sal;
  END ;
--调用
SELECT e.*,fun_test3(deptno)
FROM emp e;
--方法二
CREATE OR REPLACE FUNCTION fun_test4(fun_deptno NUMBER)
RETURN NUMBER
IS
 v_avg_sal NUMBER ;
 v_deptno  NUMBER;
 BEGIN
   SELECT AVG(sal)  ,deptno                          --多个值
   INTO v_avg_sal,v_deptno
   FROM emp
   WHERE deptno=fun_deptno
   GROUP BY deptno;
   RETURN v_avg_sal;
   END;
--调用
SELECT e.* ,fun_test4(deptno)
FROM emp e ;

SELECT ROUND(fun_test4(deptno),2)AS fun ,deptno
FROM emp e
GROUP BY deptno ;

----注意：
自定义函数中隐式转换和声明中隐式转换的区别
DECLARE 
 v_avg_sal NUMBER ;
 fun_deptno NUMBER:=10;
 BEGIN
   SELECT AVG(sal)                            --多个值
   INTO v_avg_sal
   FROM emp
   WHERE deptno=fun_deptno
   GROUP BY deptno;
   
   dbms_output.put_line(ROUND(v_avg_sal,2));
   END;
----------------------------------------------------------------
----------------------------------------------------------------
？？？？？？
--自定义函数中调用存储过程
--比较emp表同部门员工较前一个入职员工工资大小，并将大数添加test_bigger中，返回添加大数完成，否则返回添加小数；
CREATE TABLE test_bigger (empno NUMBER,sal NUMBER,hiredate DATE); 
CREATE TABLE test_smaller (empno NUMBER,sal NUMBER,hiredate DATE); 
SELECT * FROM test_bigger;

--查询表结构
select * from user_tab_columns where table_name='EMP';

--存储过程
CREATE OR REPLACE PROCEDURE sp_test_bigger (p_empno NUMBER)
IS
BEGIN
  INSERT INTO test_bigger 
  SELECT empno,sal,hiredate
  FROM emp 
  WHERE empno=p_empno;
  END ;
  
  CREATE OR REPLACE PROCEDURE sp_test_smaller (p_empno NUMBER)
IS
BEGIN
  INSERT INTO test_bigger 
  SELECT empno,sal,hiredate
  FROM emp 
  WHERE empno=p_empno;
  END ;

--自定义函数
CREATE OR REPLACE FUNCTION fun_test_bigger (fun_hiredate1 DATE,fun_hiredate2 DATE)
RETURN NUMBER
IS

CURSOR c_bigger IS 
SELECT empno,comm
FROM emp
WHERE hiredate=fun_hiredate1;

BEGIN
   FOR x IN c_bigger LOOP 
    IF x.comm>0 THEN  RETURN x.empno;

  ELSE RETURN null;
      END IF;
      END LOOP ;
      
    BEGIN
    sp_test_bigger(x.empno);
      END ;
      
END ;
--调用
CREATE OR REPLACE PROCEDURE sp_big_test 

SELECT fun_test_bigger(hiredate,LAG(hiredate)OVER(PARTITION BY deptno ORDER BY hiredate)) AS fun
FROM emp e;

SELECT * FROM test_bigger

----------------------------------------------------------------
----------------------------------------------------------------

create or replace procedure sp_test_type(p_in  number, --不写参数类型，默认是in类型的参数
                                         p_out out number,
                                         p_out2 OUT NUMBER,
                                         p_in_out in out number)
is
  v_out2 number ;
begin
  dbms_output.put_line(p_in);
  dbms_output.put_line(p_out);
  dbms_output.put_line(p_in_out);
  v_out2 :=p_in+p_in_out;
  dbms_output.put_line(v_out2);
end;

--调用sp
declare
  v_in NUMBER ;
  v_out number := 2;
  v_out2 NUMBER ;
  v_in_out NUMBER:=3 ;
begin
  sp_test_type(1,v_out,v_out2,v_in_out);  --out 类型/IN OUT 类型 的参数。该实参只能是变量
end;

create or replace procedure sp_test_type( --不写参数类型，默认是in类型的参数
                                         p_out out number,
                                         p_in_out in out number)
is
      p_in  NUMBER;                    --变量           
BEGIN 
  dbms_output.put_line(p_in);
  dbms_output.put_line(p_out);
  dbms_output.put_line(p_in_out);
  
  p_in := 11;  --错误。in类型的参数不能被赋值
  p_out := 22;                           --参数
  p_in_out := 33;
  
  dbms_output.new_line();
  
  dbms_output.put_line(p_in);
  dbms_output.put_line(p_out);
  dbms_output.put_line(p_in_out);
end;

------------------------------------------------
create or replace procedure sp_test_type( --不写参数类型，默认是in类型的参数
                                         p_out out number,
                                         p_in_out in out number)
is
p_in  NUMBER;
begin
  dbms_output.put_line(p_in);
  dbms_output.put_line(p_out);
  dbms_output.put_line(p_in_out);
  
  p_in := 11;  --错误。in类型的参数不能被赋值
  p_out := 22;
  p_in_out := 33;
  
  dbms_output.new_line();
  
  dbms_output.put_line(p_in);
  dbms_output.put_line(p_out);
  dbms_output.put_line(p_in_out);
end;

--调用sp
declare
  v_in number := 1;
  v_out number := 2;
  v_in_out number := 3;
begin
  sp_test_type(v_out,v_in_out); 
end;