ORACLE SQL 学习笔记5

存储过程|存储函数

存储函数与存储过程的区别是：存储过程没有return 返回值，存储函数可以在存储过程中调用

1. 存储函数

CREATE [OR REPLACE] FUNCTION 函数名 [(参数名 in|out 参数类型)] 
    RETURN 返回值数据类型 AS 声明变量部分
​
BEGIN 
    代码块
    RETURN 结果变量
END [函数名]

EG:查询指定员工的年薪

// 创建存储函数
CREATE OR REPLACE FUNCTION YEARSAL (ENO IN NUMBER)
    RETURN NUMBER IS 
                    PSAL EMP.SAL%TYPE; 
                    PCOMM EMP.COMM%TYPE;
BEGIN 
    SELECT EMP.SAL，EMP.PCOMM INTO PSAL, PCOMM FROM EMP WHERE EMPNO = ENO;
    RETURN PSAL * 12 + NVL(PCOMM, 0);
​
END YEARSAL;
​
// 调用存储函数
DECLARE
    BEGIN 
    -- CALL THE FUNCTION 
    -- 变量赋值  变量名：=值
    :RESULT : = yearsal(734243);
    END;

EG: 修改数据格式

CREATE OR REPLACE FUNCTION DATE_FORMAT(INPUTDATE IN DATE)
    RETURN VARCHAR AS 
BEGIN 
    RETURN TO_CHAR(INPUTDATE, 'YYYYMMDD');
END DATE_FORMAT

EG: 获取 DATE_TIMEKEY

PARAMETER: P_TIME : YYYYMMDDHH24MISS

RETURN: EX 20140101 0600

CREATE OR REPLACE FUNCTION GET_DATE_TIMEKEY(P_TIME IN VARCHAR2)
    RETURN VARCHAR2 IS
                        P_RETURN_VALUE VARCHAR2(40);
                        P_TIME         VARCHAR2(40);
​
BEGIN 
    P_RETURN_VALUE := NULL; -- 把NULL 值赋给P_RETURN_VALUE
    
    SELECT  SUBSTR( REPLACE(REPLACE(REPLACE(REPLACE(PVVI_TIME, ':' ,''), '-' ,''), ' ' ,''),'_','')  ,1,14)  INTO P_TIME FROM DUAL
    
    IF P_TIME IS NULL 
        THEN RETURN '19000101';
    ELSIF P_TIME = '***'then
      RETURN '19000101';
    ELSIF LENGTH(P_TIME) < 14 then
      RETURN '19000101';
    END IF;
    
    P_RETURN_VALUE := TO_CHAR(TO_DATE(PVVT_TIME,'YYYYMMDDHH24MISS') - 1/4,'YYYYMMDD')
    
    RETURN P_RETURN_VALUE;
​
EXCEPTION 
    WHEN OTHERS THEN 
      RAISE_APPLICATION_ERROR (-20300, 'FUNCTION  ERROR! : '|| SQLERRM);
END GET_DATE_TIMEKEY;

EG: 获取工厂的顺序

CREATE OR REPLACE FUNCTION FACTORY_ORDER(FACTOR IN VARCHAR)
    RETURN VARCHAR
AS 
    BEGIN 
        RETURN CASE WHEN FACTORY = 'BP' THEN 1
                    WHEN FACTORY = 'EVEN' THEN 2
        END;
        
END FACTORY_ORDER

EG: 判断时间范围

CREATE OR REPLACE FUNCTION DAYS_AND_NOW(DATE_TIMEKEY IN VARCHAR, NUM IN NUMBER)
RETURN NUMBER AS
BEGIN 
    IF DATE_TIMEKEY BETWEEN DATE_FORMAT(SYSDATE - NUM) AND DATE_FORMAT(SYSDATE) THEN 
        RETURN 1
    ELSE RETURN 0;
    END IF 
END DAYS_AND_NOW

2. 存储过程

// 创建存储过程
CREATE [OR REPLACE] PROCEDURE 过程名 [(参数名 in|out 参数类型)] 
    AS 声明变量部分
​
BEGIN 
    代码块
END [过程名]
​
// 调用存储过程
BEGIN 
    过程名(参数值)
END;
​
//调用存储过程
EXEC 过程名(参数值)

CREATE OR REPLACE PROCEDURE UPDATE_INSERT
IS
    BEGIN 
        MERGE INTO DWHADM.AMANDA_TABLE1 A 
        USING DWHADM.AMANDA_TABLE2 B
        ON (A.ID = B.ID AND A.ID = 3)
        WHEN MATCHED THEN UPDATE SET A.NAME = B.NAME;
    
        COMMIT;
​
​
        INSERT INTO DWHADM.AMANDA_TABLE3 
            SELECT * FROM  (SELECT B.ID, B.NAME FROM DWHADM.AMANDA_TABLE1 B
                            MINUS 
                            SELECT B.ID, B.NAME FROM 
                            DWHADM.AMANDA_TABLE1 A ,DWHADM.AMANDA_TABLE1 B
                            WHERE A.ID = B.ID AND A.ID = 3);
        COMMIT;
​
        
END UPDATE_INSERT;
​
DECLARE
BEGIN
UPDATE_INSERT();
END;

EG: 指定员工涨工资

CREATE OR REPLACE PROCEDURE ADDSAL(EMPID IN NUMBER)
    AS
        PSAL EMP.SAL%TYPE;
BEGIN 
    SELECT SAL INTO PSAL FROM EMP WHERE EMPNO = EMPID;
    UPDATE EMP SET SAL = SAL * 1.1 WHERE EMPNO = EMPID;
    
    DBMS_OUTPUT.PUT_LINE(EMPID || '涨工资前' || PSAL || '涨工资后' || (PSAL * 1.1));
END;