Changes between Initial Version and Version 1 of AdvancedSqlAndStoredProcedures

Timestamp:

08/24/25 16:27:14 (2 months ago)

Author:

221007

Comment:

--

AdvancedSqlAndStoredProcedures

@@ +1 @@
+== Напредни Процедури
+=== 1. Складирана процедура за додавање на на нов MedicalReport објект за конкретен Person запис
+{{{
+CREATE OR REPLACE PROCEDURE insert_medical_report(
+    IN param_person_id INT,
+    IN param_summary TEXT,
+    IN param_doctor_id INT,
+    IN param_next_control_date DATE,
+    IN param_diagnosis_ids INT[]
+)
+    LANGUAGE plpgsql
+AS
+$$
+DECLARE
+    new_report_id         INT;
+    variable_diagnosis_id INT;
+BEGIN
+    IF NOT EXISTS (SELECT 1
+                   FROM person
+                   WHERE person_id = param_person_id) THEN
+        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
+    END IF;
+    IF NOT EXISTS (SELECT 1 FROM doctor WHERE doctor_id = param_doctor_id) THEN
+        RAISE EXCEPTION 'Doctor with ID % does not exist', param_doctor_id;
+    END IF;
+
+    INSERT INTO report (report_type, summary, created_at, person_id)
+    VALUES ('Medical', param_summary, CURRENT_TIMESTAMP, param_person_id)
+    RETURNING report_id INTO new_report_id;
+
+    INSERT INTO medicalreport (report_id, doctor_id, next_control_date)
+    VALUES (new_report_id, param_doctor_id, param_next_control_date);
+
+    IF param_diagnosis_ids IS NOT NULL THEN
+        FOREACH variable_diagnosis_id IN ARRAY param_diagnosis_ids
+            LOOP
+                IF NOT EXISTS (SELECT 1 FROM diagnosis WHERE variable_diagnosis_id = variable_diagnosis_id) THEN
+                    RAISE EXCEPTION 'Diagnosis ID % does not exist', variable_diagnosis_id;
+                END IF;
+                INSERT INTO medicalreport_diagnosis (report_id, diagnosis_id, added_on)
+                VALUES (new_report_id, variable_diagnosis_id, CURRENT_TIMESTAMP);
+            END LOOP;
+    END IF;
+END;
+$$;
+}}}
+
+=== 2. Складирана процедура за додавање на на нов EmploymentReport објект за конкретен Person запис
+{{{
+CREATE OR REPLACE PROCEDURE insert_employment_report(
+    IN param_person_id INT,
+    IN param_start_date DATE,
+    IN param_end_date DATE,
+    IN param_job_role TEXT,
+    IN param_income NUMERIC,
+    IN param_summary TEXT
+)
+    LANGUAGE plpgsql
+AS
+$$
+DECLARE
+    new_report_id INT;
+BEGIN
+    IF NOT EXISTS (SELECT 1
+                   FROM person
+                   WHERE person_id = param_person_id) THEN
+        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
+    END IF;
+
+    IF param_end_date is not null and param_end_date < param_start_date then
+        raise exception 'End date can not be before starting date';
+    end if;
+
+    IF param_income is not null and param_income <= 0 then
+        raise exception 'Income must be greater than 0';
+    end if;
+
+    INSERT INTO report (report_type, summary, created_at, person_id)
+    VALUES ('Employment', param_summary, CURRENT_TIMESTAMP, param_person_id)
+    RETURNING report_id INTO new_report_id;
+
+    INSERT INTO employmentreport (report_id, start_date, end_date, job_role, income_per_month)
+    VALUES (new_report_id,
+            param_start_date,
+            param_end_date,
+            param_job_role,
+            param_income);
+END;
+$$;
+}}}
+
+=== 3. Складирана процедура за додавање на на нов CriminalReport објект за конкретен Person запис
+{{{
+CREATE OR REPLACE PROCEDURE insert_criminal_report(
+    IN param_person_id INT,
+    IN param_case_summary TEXT,
+    IN param_location TEXT,
+    IN param_is_resolved BOOLEAN,
+    IN param_crime_type_id INT,
+    IN param_punishment_type TEXT,
+    IN param_fine_to_pay NUMERIC,
+    IN param_release_date DATE
+)
+    LANGUAGE plpgsql
+AS
+$$
+DECLARE
+    new_report_id     INT;
+    new_punishment_id INT;
+BEGIN
+    IF NOT EXISTS (SELECT 1
+                   FROM person
+                   WHERE person_id = param_person_id) THEN
+        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
+    END IF;
+
+    IF NOT EXISTS (SELECT 1
+                   FROM crimetype
+                   WHERE crime_type_id = param_crime_type_id) THEN
+        RAISE EXCEPTION 'CrimeType with ID % does not exist', param_crime_type_id;
+    END IF;
+
+    INSERT INTO report (report_type, summary, created_at, person_id)
+    VALUES ('Criminal', param_case_summary, CURRENT_TIMESTAMP, param_person_id)
+    RETURNING report_id INTO new_report_id;
+
+    INSERT INTO criminalreport (report_id, location, resolved, crime_type_id)
+    VALUES (new_report_id, param_location, param_is_resolved, param_crime_type_id);
+
+    IF param_punishment_type = 'PRISON' AND param_release_date IS NOT NULL THEN
+        INSERT INTO punishment (report_id, value_unit, punishment_type, fine_to_pay, release_date)
+        VALUES (new_report_id, 'years', LOWER(param_punishment_type), NULL, param_release_date)
+        RETURNING punishment_id INTO new_punishment_id;
+    END IF;
+
+    IF param_punishment_type = 'FINE' AND param_fine_to_pay IS NOT NULL THEN
+        INSERT INTO punishment (report_id, value_unit, punishment_type, fine_to_pay, release_date)
+        VALUES (new_report_id, 'euros', LOWER(param_punishment_type), param_fine_to_pay, NULL)
+        RETURNING punishment_id INTO new_punishment_id;
+    END IF;
+
+END;
+$$;
+}}}
+
+=== 4. Складирана процедура за додавање на на нов AcademicReport објект за конкретен Person запис
+{{{
+CREATE OR REPLACE PROCEDURE insert_academic_report(
+    IN param_person_id INT,
+    IN param_institution_id INT,
+    IN param_academic_field TEXT,
+    IN param_description_of_report TEXT
+)
+    LANGUAGE plpgsql
+as
+$$
+DECLARE
+    new_report_id INT;
+BEGIN
+    IF NOT EXISTS (SELECT 1
+                   FROM person
+                   WHERE person_id = param_person_id) THEN
+        RAISE EXCEPTION 'Person with ID % does not exist', param_person_id;
+    END IF;
+    IF NOT EXISTS (SELECT 1
+                   FROM institution
+                   WHERE institution_id = param_institution_id) THEN
+        RAISE EXCEPTION 'Institution with ID % does not exist', param_institution_id;
+    END IF;
+
+    INSERT INTO report (report_type, summary, created_at, person_id)
+    VALUES ('Academic', param_description_of_report, CURRENT_TIMESTAMP, param_person_id)
+    RETURNING report_id INTO new_report_id;
+
+    INSERT INTO academicreport (report_id, institution_id, academic_field, description_of_report)
+    VALUES (new_report_id, param_institution_id, param_academic_field, param_description_of_report);
+END;
+$$;
+}}}