Differences

This shows you the differences between two versions of the page.

--- kurs:upsert_emp [2014/09/10 21:22]
127.0.0.1 external edit
+++ kurs:upsert_emp [2014/11/11 09:03] (current)
mh
@@ Line 1: / Line 1: @@
 <code>
-PROCEDURE upsert_salary
+CREATE OR REPLACE PROCEDURE UPSERT_TEILNEHMER (
-   ( p_first_name IN VARCHAR2,
+/* wir uebergeben an die prozedur
-     p_last_name  IN VARCHAR2,
+name,
-     p_salary     IN number)
+birthday
-   IS
+height
+gender
+und ENTWEDER inserten wir den teilnehmer ODER
+machen ein update auf den schon vorhandenen Teilnehmer
+*/
+    p_tln_name     in teilnehmer.tln_name%type,
+    p_tln_birthday in teilnehmer.tln_birthday%type,
+    p_tln_height   in teilnehmer.tln_height%type,
+    p_gen_name     in gender.gen_name%type
+)
+AS
+e_no_such_gender exception;
+pragma exception_init (e_no_such_gender, -20001);
+v_gen_id number;
 BEGIN
   begin
-  insert into emp (first_name, last_name, salary)
+  select gen_id into v_gen_id from gender
-    values (p_first_name, p_last_name, p_salary);
+    where gen_name = p_gen_name;
   exception
-    when DUP_VAL_ON_INDEX then
+  when no_data_found then
-      update emp set salary = p_salary where
+    raise e_no_such_gender;
-        first_name = p_first_name and
+    -- raise_application_error(e_no_such_gender, 'account past due.');
-        last_name  = p_last_name;
-    when others then
-       dbms_output.put_line('Fehler '||SQLERRM);
-  end;
-  begin
-  null;
   end;
+  insert into teilnehmer (
+    tln_id,
+    tln_name,
+    tln_birthday,
+    tln_height,
+    tln_gen_id
+  ) values (
+    seq.nextval,
+    p_tln_name,
+    p_tln_birthday,
+    p_tln_height,
+    v_gen_id
+  );
+exception
+when dup_val_on_index then
+  update teilnehmer set
+    tln_birthday = p_tln_birthday,
+    tln_height = p_tln_height,
+    tln_gen_id = v_gen_id
+  where tln_name = p_tln_name;
+END UPSERT_TEILNEHMER;
 END; -- Procedure
 </code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools