SHARE
TWEET

Untitled

a guest Apr 18th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE marire_bursa(
  2.     given_id IN NUMBER)
  3. AS
  4.   v_bursa number;
  5.   counter number;
  6.   bursa_mare exception;
  7.   student_inexistent exception;
  8.   PRAGMA EXCEPTION_INIT(bursa_mare, -20511);
  9.   PRAGMA EXCEPTION_INIT(student_inexistent, -20512);
  10. BEGIN
  11.    SELECT COUNT(*)  INTO counter FROM studenti WHERE given_id = id;
  12.    if(counter<1) then raise student_inexistent;
  13.    end if;
  14.  
  15.   UPDATE studenti  set bursa=bursa*2 where id=given_id;
  16.  
  17.   select bursa into v_bursa from studenti where id=given_id;
  18.   if(v_bursa>3000 and v_bursa is not null)
  19.         then update studenti set bursa=3000 where id=given_id;
  20.                  raise bursa_mare;
  21.   end if;
  22.   EXCEPTION
  23.     WHEN bursa_mare THEN
  24.         DBMS_OUTPUT.PUT_LINE('Studentul cu id ' || given_id ||' are bursa mare');
  25.     WHEN student_inexistent THEN
  26.         DBMS_OUTPUT.PUT_LINE('Studentul cu id ' || given_id ||' nu exista in baza de date');
  27. END marire_bursa;
  28.  
  29. DECLARE
  30. v_index number;
  31. v_id number;
  32. v_counter number;
  33. v_bursa studenti.bursa%type;
  34. bursa_mare exception;
  35. student_inexistent exception;
  36. PRAGMA EXCEPTION_INIT(bursa_mare, -20511);
  37. PRAGMA EXCEPTION_INIT(student_inexistent, -20512);
  38. BEGIN
  39.  
  40.   v_counter:=1;
  41.   FOR v_index in 1..100 LOOP
  42.     MARIRE_BURSA(v_index);
  43.     v_id:=v_index;
  44.     if(v_index<=100)
  45.     then
  46.       select bursa into v_bursa from studenti where id=v_index ;
  47.       if(v_bursa is not null)
  48.       then
  49.         if(v_counter<=10)
  50.         then
  51.           DBMS_OUTPUT.PUT_LINE(v_counter || '. Bursa studentului cu id '|| v_id||' este acum ' || v_bursa || ' (inainte era ' ||v_bursa/2 || ')' );
  52.           v_counter:=v_counter+1;
  53.         end if;
  54.       end if;
  55.     end if;
  56.   END LOOP;
  57. EXCEPTION
  58. WHEN bursa_mare THEN
  59.   DBMS_OUTPUT.PUT_LINE('Studentul cu id ' || v_id ||' are bursa mare');
  60. WHEN student_inexistent THEN
  61.   DBMS_OUTPUT.PUT_LINE('Studentul cu id ' || v_id ||' nu exista in baza de date');
  62. END;
  63.  ROLLBACK;
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