[pl / SQL user defined function] practice + interview + work

Time:2020-8-14

After reading this chapter, you will learn the following:

1. Practice scene
2. Interview scene
3. Work application scenarios


Overview mind map:

[pl / SQL user defined function] practice + interview + work


Interview part:

1. Create a function to query the salary of the employee with the specified employee number from the EMP table

CREATE OR REPLACE FUNCTION CHECK_SAL(F_EMPNO IN EMP.EMPNO%TYPE) RETURN NUMBER IS
 V_SAL VARCHAR(50);

 BEGIN
     SELECT SAL INTO V_ SAL FROM EMP WHERE EMPNO = F_ Empno; --- call implicit cursor directly
     RETURN V_SAL;

    EXCEPTION WHEN NO_ DATA_ Found then -- exception handling
    V_ Sal: ='No results found ';
    RETURN V_SAL;
 END;

---Call function
SELECT CHECK_ Sal (7499) as salary from dual;

2. Create a function to return the salary and name of the specified employee in EMP table.
There are two return values. One can be returned by return, and the other can be brought back with the out parameter

CREATE OR REPLACE FUNCTION F_EMP_SALENAME(FEMPNO IN EMP.EMPNO%TYPE
                                                 ,V_ENAME OUT EMP.ENAME%TYPE
                                                 )RETURN NUMBER IS
V_SAL EMP.SAL%TYPE;
BEGIN
  
   SELECT SAL,ENAME INTO V_SAL,V_ENAME
   FROM EMP
   WHERE EMPNO = FEMPNO;
   RETURN V_SAL;

END;

---The first part borrows the in parameter and returns one of sal
---The second part redefines an out parameter, otherwise the program does not know which type of parameter to call
 

 DECLARE
 P_ENAME EMP.ENAME%TYPE;
 p_SAL EMP.SAL%TYPE;
 
 BEGIN
    P_ SAL:=F_ EMP_ SALENAME(7369,P_ Ename); --- define an out parameter to return the return value Sal of function
    DBMS_ OUTPUT.put_ Line ("employee" | p_ Ename||||||p_ SAL);
 END;

3. Create a function to calculate the average wage of all employees in the department according to the given department number (hint: Use &).

CREATE OR REPLACE FUNCTION F_INPUTEMPNO(F_DEPTNO IN DEPT.DEPTNO%TYPE)
RETURN NUMBER IS
V_SAL NUMBER;

BEGIN 

   SELECT AVG(SAL) INTO V_SAL FROM EMP WHERE DEPTNO = F_DEPTNO; 
   RETURN V_SAL;
END;

 ---When calling, add & before the input name, similar to the input function in VBA
  SELECT  F_ Inputempno (& Department No.) as average wage from dual;

4. Create a function with only one formal parameter. It receives the actual parameter — department number passed from the calling function. The return value of the function is a whole record information of the Department

CREATE OR REPLACE FUNCTION F_EMPINFO(F_DEPTNO IN DEPT.DEPTNO%TYPE)
    RETURN DEPT%ROWTYPE IS
  
    V_DEPT DEPT%ROWTYPE;
  BEGIN
    SELECT * INTO V_DEPT FROM DEPT WHERE DEPTNO = F_DEPTNO;
    RETURN V_DEPT;
  END;
  
  DECLARE 
  V_DEPT DEPT%ROWTYPE;
  BEGIN
  V_ DEPT := F_ Empinfo (20); --- passing parameters to newly defined variable V when calling function_ Dept;
  DBMS_ OUTPUT.PUT_ Line ('department Name: '| V_ DEPT.DNAME  ||'department location: '| V_ DEPT.LOC );
  END;

5 *. Create a function to add 200 to the salary of employees whose wages are lower than the average wage in EMP table, and return the total number of employees whose wages have been modified. Note: add the where statement after the update, otherwise everything will be updated. Secondly, we will make good use of% rowcount

CREATE OR REPLACE FUNCTION F_MODIFY 
RETURN NUMBER 
IS
BEGIN
 UPDATE EMP SET SAL=SAL+200 WHERE SAL<(SELECT AVG(SAL) FROM EMP);
 RETURN SQL%ROWCOUNT; 

/*SQL% rowcount is used to record the number of changes. It must be executed after an update or delete statement,
   If the select statement is used for query, it cannot be used,
   When you execute multiple modification statements, the number of modifications of the last statement executed before SQL% rowcount shall prevail. * /
END;
/
call
BEGIN
  DBMS_OUTPUT.PUT_LINE(F_MODIFY);
END; 
/

Interview process
1. Enter 2 integers to return the result of the multiplication between the minimum and the maximum (the two integers are between 1 and 20);

Idea: first set the range of two numbers, and judge with if plus and.
Then there are three possibilities for the two numbers:
A is greater than B, a is less than B, and a is equal to B. conditional judgment is made by using if elsif method.

Create or replace function funb (P1 in int, P2 in int) return integer is -- the two data types must be consistent
  V_RESULT INTEGER := 1;

BEGIN

 IF (P1 < 1 AND P1 > 20) AND (P2< 1 AND P2 > 20) THEN
    IF P2 > P1 THEN
      FOR I IN P1 .. P2 LOOP
        V_RESULT := V_RESULT * I;
      END LOOP;
    ELSIF P2 < P1 THEN
      FOR I IN P2 .. P1 LOOP
        V_RESULT := V_RESULT * I;
      END LOOP;
    ELSE
      V_RESULT := P1 * P2;
    END IF;
  ELSE 
  DBMS_ OUTPUT.put_ Line ('Please enter the correct range value ');
END IF; 
  RETURN V_RESULT;
END;

2. Enter 3 integers and take the median.

---Method 1      
    
  CREATE OR REPLACE FUNCTION FUNA(P1 IN INT,P2 IN INT,P3 IN INT)
  RETURN INTEGER IS  
  V_RESULT INTEGER;
  
  BEGIN    
  SELECT CASE WHEN P1 >= P2 AND P1 <= P3 THEN  P1
              WHEN P2 >= P1 AND P2 <= P3 THEN  P2
              WHEN P3 >= P1 AND P3 <= P2 THEN  P3 END
  INTO V_RESULT FROM DUAL;
  RETURN V_RESULT;
  
  END;
        
-----Method 2  
CREATE OR REPLACE FUNCTION FUN_middle(X1 IN NUMBER,X2 IN NUMBER,X3 IN NUMBER)
     RETURN number
    IS 
    BEGIN
    IF (X1-X2)*(X1-X3)<=0   then return x1;
    elsif (x2-x1)*(x2-x3)<=0 then return x2;
    elsif(x3-x1)*(x3-x2)<=0 then return x3;
    end if;
    end;

3. Compare two numbers and return a maximum value. You can set the parameters,Set default values by pass

--Pass in two parameters and return the maximum value
    CREATE OR REPLACE FUNCTION FUN_MAX (P_NUM1 IN NUMBER, P_NUM2 IN NUMBER DEFAULT 99)
    Return number -- the return type of the function
    IS
    BEGIN
        IF P_NUM1>P_NUM2 THEN 
            RETURN P_NUM1;    
        ELSE 
            RETURN P_NUM2; 
        END IF;
    END;

3. Working scene (1)
–Function return type is cursor(Corresponding report interface)

--Pass in the department number and return the employee information of the whole department (function)
CREATE OR REPLACE FUNCTION FUN_REF(P_DEPTNO EMP.DEPTNO%TYPE)
      RETURN SYS_REFCURSOR IS
      C_ EMP SYS_ REFCURSOR;  -- C_ EMP cursor type
    BEGIN
      OPEN C_ EMP for -- don't forget to add for
        SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO;
      RETURN C_EMP;
    END FUN_REF;

 --Call directly in the dual table

   SELECT FUN_REF(30) FROM DUAL;