Advertisement
razvanth21

Untitled

Jan 24th, 2020
1,202
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET serveroutput ON SIZE 20000 word_wrapped;
  2.  
  3. CREATE OR REPLACE PROCEDURE adaugas(cods IN NUMBER, nume IN VARCHAR2, adresa IN VARCHAR2, datan IN DATE, bursa IN NUMBER, tel IN NUMBER, cnp IN NUMBER) AS
  4. BEGIN
  5.     INSERT INTO student VALUES (cods, nume, adresa, datan, bursa, tel, cnp);
  6. EXCEPTION
  7.     WHEN dup_val_on_index THEN
  8.         dbms_output.put_line('Acel student exista deja.');
  9. END;
  10. /
  11.  
  12. CREATE OR REPLACE PROCEDURE afisn(cod_s IN NUMBER) AS
  13.  
  14. CURSOR c_curs IS SELECT c.*, n.nota, n.data FROM curs c, note n WHERE c.codc = n.codc AND n.cods = cod_s;
  15. r_note c_curs%ROWTYPE; --pentru cursorul de mai sus
  16. r_student student%ROWTYPE; --pentru informatii student
  17. nr_curent NUMBER;
  18. medie NUMBER;
  19. suma_note NUMBER;
  20.  
  21. BEGIN
  22.     --initializare
  23.     nr_curent := 1;
  24.     suma_note := 0;
  25.    
  26.     --informatii student
  27.     SELECT * INTO r_student FROM student s WHERE s.cods = cod_s;
  28.    
  29.     --afisare
  30.     dbms_output.put_line('Nume Student: ' || r_student.nume || ', Bursa: ' || r_student.bursa);
  31.    
  32.     --deschidere cursor, fetching
  33.     OPEN c_curs;
  34.     LOOP
  35.         FETCH c_curs INTO r_note;
  36.         EXIT WHEN c_curs%notfound;
  37.        
  38.         dbms_output.put_line('Nr. Crt: ' || nr_curent || ', CodC: ' || r_note.codc || ', Titlu Curs: ' || r_note.titlu || ', Nota: ' || r_note.nota || ', Data: ' || r_note.data || ', Profesor: ' || r_note.numeprof);
  39.        
  40.         nr_curent := nr_curent + 1;
  41.         suma_note := suma_note + r_note.nota;
  42.     END LOOP;
  43.     CLOSE c_curs;
  44.    
  45.     medie := suma_note / (nr_curent - 1);
  46.     dbms_output.put_line('Medie: ' || medie);
  47. EXCEPTION
  48.     WHEN no_data_found THEN
  49.         dbms_output.put_line('Tabele nepopulate');
  50. END;
  51. /
  52.  
  53. CREATE OR REPLACE PROCEDURE acstatn(n IN NUMBER) AS
  54. BEGIN
  55.     FOR rand IN
  56.         (SELECT s.cods, s.nume, s.adresa, COUNT(n.cods) AS nr_note, AVG(n.nota) AS medie
  57.         FROM student s
  58.         join note n ON s.cods = n.cods
  59.         GROUP BY s.cods, s.nume, s.adresa, COUNT(n.cods), AVG(n.nota)
  60.         HAVING medie > n)
  61.     LOOP
  62.         dbms_output.put_line('CodS: ' || rand.cods || ', Nume: ' || rand.nume || ', Adresa: ' || rand.adresa || ', Nr. Note: ' || rand.nr_note || ', Media: ' || rand.medie);
  63.     END LOOP;
  64. END;
  65. /
Advertisement
RAW Paste Data Copied
Advertisement