This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:upsert_emp [2011/11/23 09:35] mh created |
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> |