# [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: 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
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;``````

## How to share queues with hypertools 2.5

Share queue with swote To realize asynchronous IO between processes, the general idea is to use redis queue. Based on the development of swote, the queue can also be realized through high-performance shared memory table. Copy the code from the HTTP tutorial on swoole’s official website, and configure four worker processes to simulate multiple producers […]