SHARE
TWEET

Untitled

a guest Jan 22nd, 2020 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PACKAGE pkg_uni IS
  2.     PROCEDURE proc_add_stud (stud_args students%ROWTYPE);
  3.     PROCEDURE proc_add_lect (lect_args lecturers%ROWTYPE);
  4.     PROCEDURE proc_add_course (course_args courses%ROWTYPE);
  5.     PROCEDURE proc_add_faculty (fac_args faculties%ROWTYPE);
  6.     PROCEDURE proc_choose_course (chosen_args chosen_courses%ROWTYPE);
  7.     PROCEDURE proc_add_grade (grades_args grades%ROWTYPE);
  8.     FUNCTION grade_in_course (v_chosen_id NUMBER) RETURN NUMBER;
  9.     FUNCTION stud_total_credits (stud_id CHAR) RETURN NUMBER;
  10.     PROCEDURE change_course_status (v_chosen_id NUMBER, v_status CHAR) RETURN NUMBER;
  11.     FUNCTION get_lect (v_email VARCHAR2) RETURN NUMBER;
  12.     FUNCTION get_stud_id (v_email VARCHAR2) RETURN NUMBER;
  13.     FUNCTION get_chosen_course_id (v_course_id NUMBER, v_stud_id NUMBER, v_num NUMBER) RETURN NUMBER;
  14.     FUNCTION get_course_id (v_name VARCHAR2) RETURN NUMBER;
  15. END pkg_uni;
  16.  
  17. CREATE OR REPLACE PACKAGE pkg_uni IS
  18.     PROCEDURE proc_add_stud (stud_args students%ROWTYPE) IS
  19.         BEGIN
  20.             INSERT INTO students
  21.             VALUES (stud_args.id, --student's id is 11 cifriani piradi nomeri piradobashi rac weria, amitom me ver davugenerireb sequencit ;)
  22.                     stud_args.first_name,
  23.                     stud_args.last_name,
  24.                     stud_args.email,
  25.                     stud_args.birth_date,
  26.                     stud_args.grant_percentage,
  27.                     stud_args.major_id,
  28.                     stud_args.minor_id,
  29.                     stud_args.stud_status);
  30.         END;
  31.    
  32.     PROCEDURE proc_add_lect (lect_args lecturers%ROWTYPE) IS
  33.         BEGIN
  34.             INSERT INTO lecturers
  35.             VALUES (lect_args.id,
  36.                     lect_aegs.first_name,
  37.                     lect_args.last_name,
  38.                     lect_args.email,
  39.                     lect_args.hire_date,
  40.                     lect_args.salary);
  41.         END;
  42.    
  43.     PROCEDURE proc_add_course (course_args courses%ROWTYPE) IS
  44.         BEGIN  
  45.             INSERT INTO courses
  46.             VALUES (courses_id_seq.NEXTVAL,
  47.                     course_args.course_name,
  48.                     course_args.lecturer_id,
  49.                     course_args.seminarist_id,
  50.                     course_args.credits,
  51.                     course_args.faculty_id);
  52.         END;
  53.    
  54.     PROCEDURE proc_add_faculty (fac_args faculties%ROWTYPE) IS
  55.         BEGIN  
  56.             INSERT INTO faculties
  57.             VALUES (facs_id_seq.NEXTVAL,
  58.                     fac_args.faculty_name
  59.                     fac_args.dean_id,
  60.                     fac_args.price_per_semester);
  61.         END;
  62.        
  63.     PROCEDURE proc_choose_course (chosen_args chosen_courses%ROWTYPE)
  64.         IS
  65.             x_not_active EXCEPTION;
  66.             v_stud_idnum NUMBER;
  67.         BEGIN
  68.             SELECT students.id INTO v_stud_idnum
  69.             FROM students
  70.            
  71.             IF v_stud_idnum = chosen_args.id_num THEN
  72.                 RAISE x_not_active;
  73.             END IF;
  74.            
  75.             INSERT INTO chosen_courses
  76.             VALUES (chos_id_seq.NEXTVAL,
  77.                     chosen_args.course,
  78.                     chosen_args.students
  79.                     chosen_args.passed);
  80.                    
  81.             EXCEPTION
  82.                 WHEN x_not_active THEN
  83.                     DBMS_OUTPUT.PUT_LINE('cannot choose new course for an inactive student');
  84.         END;
  85.        
  86.     PROCEDURE proc_add_grade (grades_args grades%ROWTYPE)
  87.         IS
  88.             x_invalid_grade EXCEPTION; -- თუ ჯამურად 100-ზე მეტი ჰყავს ვერ ჩაამატებს ქულას
  89.             x_inactive_course EXCEPTION; -- თუ კურსი უკვე გავლილი აქვს ვერ ჩაამატებს ქულას
  90.             v_course_status chosen_courses.passed%TYPE;
  91.         BEGIN  
  92.             SELECT passed INTO v_course_status
  93.             FROM chosen_courses
  94.             WHERE id = grades.chosen_id;
  95.            
  96.             IF passed IS NOT NULL THEN
  97.                 RAISE x_inactive_course;
  98.             END IF;
  99.            
  100.             IF grade_in_course(grades_args.chosen_id) + grades_args.score_grade > 100 THEN
  101.                 RAISE x_invalid_grade;
  102.             END IF;
  103.            
  104.             INSERT INTO grades
  105.             VALUES (grades_args.chosen_id,
  106.                     grades_args.score_name,
  107.                     grades_args.score_grade);
  108.                    
  109.             EXCEPTION
  110.                 WHEN x_invalid_grade THEN
  111.                     DBMS_OUTPUT.PUT_LINE('cannot submit new grade, because the sum exceeds 100.');
  112.                    
  113.             EXCEPTION
  114.                 WHEN x_inactive_course THEN
  115.                     DBMS_OUTPUT.PUT_LINE('cannot submit a new grade to the passed or failed course');
  116.            
  117.         END;
  118.        
  119.     -- am funqcias gamoidzaxebs leqtori da chauwers students archeuli kursi chaabara tu vera, tavidan arafera chawers da null iqneba:
  120.     -- (chosen_courses table, passed column, yes or no)
  121.     PROCEDURE passed_or_failed_course(v_chosen_id NUMBER, v_done VARCHAR2) IS
  122.         BEGIN
  123.             INSERT INTO chosen_courses (passed)
  124.             VALUES v_done
  125.             WHERE id = v_chosen_id;
  126.         END;
  127.        
  128.     FUNCTION grade_in_course (v_chosen_id NUMBER) RETURN NUMBER
  129.         IS
  130.             v_grade NUMBER;
  131.         BEGIN
  132.             SELECT SUM(gr.score_grade) INTO v_grade
  133.             FROM grades gr
  134.             WHERE gr.chosen_id = v_chosen_id
  135.             RETURN v_grade
  136.         END;
  137.        
  138.     FUNCTION stud_total_credits (stud_id CHAR) RETURN NUMBER
  139.         IS
  140.             v_credits course.credits%TYPE;
  141.         BEGIN
  142.             SELECT SUM(co.credits) INTO v_credits
  143.             FROM course co join chosen_courses ch
  144.             ON co.id = ch.course
  145.             WHERE ch.student = stud_id;
  146.             RETURN v_credits;
  147.         END;
  148.        
  149.     --student passed or failed the course
  150.     PROCEDURE change_course_status (v_chosen_id NUMBER, v_status CHAR) RETURN NUMBER
  151.         IS
  152.             x_insufficient_score EXCEPTION;
  153.         BEGIN
  154.             IF grade_in_course(v_chosen_id) < 51 THEN
  155.                 RAISE x_insufficient_score;
  156.             END IF;
  157.        
  158.             UPDATE chosen_courses
  159.             SET passed = v_status
  160.             WHERE id = v_chosen_id
  161.            
  162.             EXCEPTION
  163.                 WHEN x_insufficient_score THEN
  164.                     DBMS_OUTPUT.PUT_LINE('cannot pass a course with less than 51 points');
  165.         END;
  166.        
  167.     FUNCTION get_lect_id (v_email VARCHAR2) RETURN NUMBER
  168.         IS
  169.             v_lect_id lecturers.id&TYPE;
  170.         BEGIN
  171.             SELECT id INTO v_lect_id
  172.             FROM lecturers
  173.             WHERE email = v_email;
  174.             RETURN v_lect_id;
  175.         END;
  176.        
  177.     FUNCTION get_stud_id (v_email VARCHAR2) RETURN NUMBER
  178.         IS
  179.             v_stud_id students.id&TYPE;
  180.         BEGIN
  181.             SELECT id INTO v_stud_id
  182.             FROM students
  183.             WHERE email = v_email;
  184.             RETURN v_stud_id;
  185.         END;
  186.        
  187.     --რადგან ამ სტუდენტ აიდის ადამიანმა ეს საგანი შეიძლება რამდენჯერმე გაიაროს, ამიტომ გადმოგვეცემა რიცხვი: მერამდენედ გავლილის ამოღება გვინდა
  188.     FUNCTION get_chosen_course_id (v_course_id NUMBER, v_stud_id NUMBER, v_num NUMBER) RETURN NUMBER
  189.         IS
  190.             v_chosen_course_id chosen_courses.id&TYPE;
  191.         BEGIN
  192.             SELECT id INTO v_chosen_course_id
  193.             FROM chosen_course
  194.             WHERE course = v_course_id AND student = v_stud_id AND ROWNUM = v_num
  195.             ORDER BY id;
  196.             RETURN v_chosen_course_id;
  197.         END;
  198.        
  199.     FUNCTION get_course_id (v_name VARCHAR2) RETURN NUMBER
  200.         IS
  201.             v_name course.id&TYPE;
  202.         BEGIN
  203.             SELECT id INTO v_name
  204.             FROM courses
  205.             WHERE course_name = v_name;
  206.             RETURN v_name;
  207.         END;
  208.                
  209. END pkg_uni;
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