User Tools

Site Tools


kurs:upsert_emp

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
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>​ <​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
-  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   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;   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 END; -- Procedure
 </​code>​ </​code>​
kurs/upsert_emp.txt · Last modified: 2014/11/11 09:03 by mh