SHARE
TWEET

oracle example

gin_kovacs Jul 18th, 2018 102 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION add_csaladtag(p_azon NUMBER, p_nev VARCHAR2) RETURN csaladtagok%ROWTYPE IS
  2.   return_row csaladtagok%ROWTYPE;
  3.   ugyfel_keresztnev OE.CUSTOMERS.CUST_FIRST_NAME%TYPE;
  4.   ugyfel_vezeteknev OE.CUSTOMERS.CUST_LAST_NAME%TYPE;
  5.   BEGIN
  6.     INSERT INTO CSALADTAGOK (AZON, NEV)
  7.       VALUES (p_azon, p_nev)
  8.       RETURNING AZON, NEV INTO return_row;
  9.      
  10.     RETURN return_row;
  11.      
  12.     EXCEPTION
  13.     WHEN DUP_VAL_ON_INDEX THEN
  14.     BEGIN
  15.       SELECT CUST_FIRST_NAME, CUST_LAST_NAME
  16.       INTO ugyfel_keresztnev, ugyfel_vezeteknev
  17.       FROM OE.CUSTOMERS
  18.       WHERE CUSTOMER_ID = p_azon;
  19.       DBMS_OUTPUT.PUT_LINE(ugyfel_keresztnev || ' ' || ugyfel_vezeteknev || '-hez mar tartozik ' || p_nev || ' nevu csaladtag');
  20.       RETURN NULL;
  21.     END;
  22.   END;
  23.  
  24. //
  25.  
  26. CREATE OR REPLACE PROCEDURE beszur(ugyfel_nev VARCHAR2, cstag_nev VARCHAR2) IS
  27.   sor csaladtagok%ROWTYPE;
  28.   ugyfel_id NUMBER(6, 0);
  29.   BEGIN
  30.     SELECT CUSTOMER_ID
  31.     INTO ugyfel_id
  32.     FROM CUSTOMERS
  33.     WHERE (CUST_FIRST_NAME || ' ' || CUST_LAST_NAME) = ugyfel_nev;
  34.    
  35.     sor := csaladtag_insert(ugyfel_id, cs_nev);
  36.     DBMS_OUTPUT.PUT_LINE(sor.azon || ' ' || sor.nev);
  37.    
  38.     EXCEPTION
  39.     WHEN TOO_MANY_ROWS THEN
  40.       RAISE TOO_MANY_ROWS;
  41.   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