Changes between Version 4 and Version 5 of DatabaseProgramming

Timestamp:

06/11/26 01:44:57 (6 hours ago)

Author:

231018

Comment:

--

DatabaseProgramming

@@ -315 +315 @@
     p_detektirana_brzina INTEGER,
     p_tip_prekrsok_id INTEGER,
-    p_kamera_id INTEGER
+    p_kamera_id INTEGER,
+    p_storitel_embg VARCHAR
 )
 AS $$
 DECLARE
     v_kazna_id INTEGER;
+    v_prekrsok_id INTEGER;
     v_iznos INTEGER;
     v_status_kazna_id INTEGER;
@@ -325 +327 @@
     v_postoi_tip INTEGER;
     v_postoi_kamera INTEGER;
+    v_postoi_gragjanin INTEGER;
 BEGIN
     IF p_opis IS NULL OR LENGTH(TRIM(p_opis)) = 0 THEN
@@ -338 +341 @@
     IF p_detektirana_brzina IS NULL OR p_detektirana_brzina <= 0 THEN
         RAISE EXCEPTION 'Detektiranata brzina mora da bide pogolema od 0';
+    END IF;
+
+
+    IF p_storitel_embg IS NULL OR LENGTH(TRIM(p_storitel_embg)) <> 13 THEN
+        RAISE EXCEPTION 'EMBG na storitelot mora da ima 13 karakteri';
+    END IF;
+
+
+    SELECT COUNT(*)
+    INTO v_postoi_gragjanin
+    FROM Gragjanin
+    WHERE embg = p_storitel_embg;
+
+    IF v_postoi_gragjanin = 0 THEN
+        RAISE EXCEPTION 'Ne postoi gragjanin so EMBG %', p_storitel_embg;
     END IF;
 
@@ -416 +434 @@
         p_kamera_id,
         v_kazna_id
+    )
+    RETURNING prekrsok_id INTO v_prekrsok_id;
+
+
+    INSERT INTO Prekrsok_Storitel(
+        prekrsok_id,
+        storitel_embg
+    )
+    VALUES(
+        v_prekrsok_id,
+        p_storitel_embg
     );
 END;