daily pastebin goal
68%
SHARE
TWEET

Untitled

a guest Mar 2nd, 2018 110 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION FindClient
  2.    (  N_CLIENT IN NUMBER,
  3.       C_CITY IN VARCHAR2 )
  4.    RETURN NUMBER
  5. IS
  6.    TMP_C_CITY_CLIENT VARCHAR2; -- название города у клиента
  7.    TMP_N_CITY NUMBER; -- ID города, для возможной замены у клиента
  8. BEGIN
  9.     /* Узнали название города у пользователя */
  10.     SELECT ci.c_city INTO TMP_C_CITY_CLIENT
  11.     FROM client cl
  12.     LEFT JOIN city ci ON ci.n_city = cl.n_city
  13.     WHERE cl.n_client = N_CLIENT;
  14.  
  15.     IF (TMP_C_CITY_CLIENT IS NOT NULL) THEN -- если пользователь существует
  16.     BEGIN
  17.       IF (TMP_C_CITY_CLIENT = C_CITY) THEN -- совпали введенный город и город у пользователя
  18.         RETURN 1;
  19.       ELSE
  20.       BEGIN
  21.         SELECT ci.n_city INTO TMP_N_CITY -- узнали ID введенного города
  22.         FROM city ci
  23.         WHERE ci.c_city = C_CITY;
  24.  
  25.         UPDATE client cl -- обновим ID города у клиента
  26.         SET cl.n_city = TMP_N_CITY
  27.         WHERE cl.n_client = N_CLIENT;
  28.  
  29.         RETURN 0;
  30.       END
  31.     END
  32.     ELSE
  33.       RETURN 0;
  34. END;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top