This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:nested_table_als_column_type [2011/11/17 11:08] mh |
kurs:nested_table_als_column_type [2014/11/11 14:52] (current) mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | CREATE OR REPLACE PROCEDURE TEILNEHMER_EDUCATION | ||
+ | /* | ||
+ | + zusaetzlich zum teilnehmernamen eine zusätzliche | ||
+ | ausbildung (p_add_education) angeben und die an die schon vorhandenen | ||
+ | anhaengen und den Teilnehmer damit updaten | ||
+ | |||
+ | */ | ||
+ | (p_tln_name teilnehmer.tln_name%type, | ||
+ | p_add_education varchar2 | ||
+ | ) | ||
+ | AS | ||
+ | t_education education; | ||
+ | BEGIN | ||
+ | select coalesce(tln_education, education() ) into t_education | ||
+ | from teilnehmer | ||
+ | where tln_name = p_tln_name; | ||
+ | | ||
+ | -- dbms_output.put_line(t_education.count); | ||
+ | | ||
+ | t_education.extend; | ||
+ | t_education(t_education.last) := p_add_education; | ||
+ | | ||
+ | update teilnehmer set tln_education = t_education | ||
+ | where tln_name = p_tln_name; | ||
+ | | ||
+ | if t_education.count <> 0 then | ||
+ | for i in 1..t_education.last loop | ||
+ | -- dbms_output.put_line(t_education(1)||' '|| t_education.count); | ||
+ | dbms_output.put_line(t_education(i)); | ||
+ | end loop; | ||
+ | end if; | ||
+ | | ||
+ | | ||
+ | END TEILNEHMER_EDUCATION; | ||
+ | </code> | ||
+ | |||
+ | |||
<code> | <code> | ||
CREATE OR REPLACE | CREATE OR REPLACE | ||
Line 5: | Line 43: | ||
</code> | </code> | ||
+ | <code> | ||
+ | CREATE TABLE "HR"."KURSTEILNEHMER" | ||
+ | ("ID" NUMBER, | ||
+ | "NAME" VARCHAR2(100), | ||
+ | "AUSBILDUNG" "HR"."AUSBILDUNG" | ||
+ | ) NESTED TABLE ausbildung STORE AS store_ausbildung | ||
+ | </code> | ||
<code> | <code> | ||
- | CREATE TABLE "HR"."KURSTEILNEHMER" | + | CREATE SEQUENCE "HR"."S"; |
- | ("ID" NUMBER, | + | </code> |
- | "NAME" VARCHAR2(100), | + | |
- | "AUSBILDUNG" "HR"."AUSBILDUNG" | + | <code> |
- | ) NESTED TABLE ausbildung STORE AS store_ausbildung | + | PROCEDURE teilnehmer_ausbildung ( |
+ | p_name VARCHAR2, | ||
+ | p_add_ausbildung VARCHAR2) | ||
+ | IS | ||
+ | t_ausbildung ausbildung := ausbildung(); | ||
+ | c INTEGER; | ||
+ | f BOOLEAN := FALSE; | ||
+ | al number; | ||
+ | BEGIN | ||
+ | SELECT COUNT (id) | ||
+ | INTO c | ||
+ | FROM kursteilnehmer | ||
+ | WHERE name = p_name; | ||
+ | |||
+ | IF c = 0 | ||
+ | THEN | ||
+ | INSERT INTO kursteilnehmer (id, name, ausbildung) | ||
+ | VALUES (s.NEXTVAL, p_name, ausbildung (p_add_ausbildung)); | ||
+ | ELSE | ||
+ | SELECT ausbildung | ||
+ | INTO t_ausbildung | ||
+ | FROM kursteilnehmer | ||
+ | WHERE name = p_name; | ||
+ | |||
+ | FOR i IN 1 .. t_ausbildung.last | ||
+ | LOOP | ||
+ | IF t_ausbildung (i) = p_add_ausbildung | ||
+ | THEN | ||
+ | f := TRUE; | ||
+ | END IF; | ||
+ | END LOOP; | ||
+ | |||
+ | IF f = FALSE | ||
+ | THEN | ||
+ | |||
+ | t_ausbildung.EXTEND; | ||
+ | t_ausbildung (t_ausbildung.last) := p_add_ausbildung; | ||
+ | |||
+ | UPDATE kursteilnehmer | ||
+ | SET ausbildung = t_ausbildung | ||
+ | WHERE name = p_name; | ||
+ | END IF; | ||
+ | END IF; | ||
+ | |||
+ | /* FOR i IN 1 .. t_ausbildung.LAST | ||
+ | LOOP | ||
+ | DBMS_OUTPUT.put_line (t_ausbildung (i)); | ||
+ | END LOOP;*/ | ||
+ | END; -- Procedure | ||
</code> | </code> |