Hinzufügen/Aktualisieren der gespeicherten Prozedur in Oracle einfügen einer null oder leeren Feld

Ich bin neu in Oracle verwenden, und ich bin versucht, erstellen Sie eine add/insert gespeicherte Prozedur für eine Tabelle. Die PROD_CD und PLAN_CD Felder in meiner Tabelle kann kein Wert (leer oder null)
Können Sie bitte überprüfen mein code und lassen Sie mich wissen, was ich falsch mache?

Tabelle definition:

CREATE TABLE DCWEB.USER_PLAN_PREFERENCE
(
  USERID        VARCHAR2(40) NOT NULL,
  PROD_CD       VARCHAR2(9)  NULL,
  PLAN_CD       VARCHAR2(9)  NULL,
  STATE_LST     VARCHAR2(2)  NOT NULL,
  STATE_NM      VARCHAR2(40) NOT NULL,
  LST_UPDATE_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

ALTER TABLE DCWEB.USER_PLAN_PREFERENCE
  ADD CONSTRAINT USER_PLAN_PREFERENCE_XPK PRIMARY KEY (USERID, PROD_CD, PLAN_CD);

-- Grant/Revoke object privileges 
grant select, insert, update, delete on DCWEB.USER_PLAN_PREFERENCE to HIGGIB1;

Gespeicherte Prozedur Definition:

  procedure setUserPlanPref (
    userid in varchar2,
    prod_cd in varchar2,
    plan_cd in varchar2,
    state_lst in varchar2,
    state_nm in varchar2
  )
  is
    currentTimestamp timestamp := current_timestamp;
  begin
       insert into user_plan_preference (userid, prod_cd, plan_cd, state_lst, state_nm, lst_update_ts)
       values (upper(userid), upper(prod_cd), upper(plan_cd), upper(state_lst), upper(state_nm), currentTimestamp);
       commit;
       exception
       when dup_val_on_index then
         begin
          update user_plan_preference up set
            up.userid = upper(userid),
            up.prod_cd = upper(prod_cd),
            up.plan_cd = upper(plan_cd),
            up.state_lst = upper(state_lst),
            up.state_nm = upper(state_nm),
            up.lst_update_ts = currentTimestamp
          where up.userid = upper(userid)
                and up.prod_cd = upper(prod_cd)
                and up.plan_cd = upper(plan_cd);
          commit;
          exception 
          when others then
            rollback;
        end;
      when others then
        rollback;
    end;
  end;

EINGABEDATEN

Ich bin nicht in der Lage einen Datensatz einfügen, aufrufen der gespeicherten Prozedur mit den Werten:
DCWEB4578, , 2P, CA, CALIFORNIA
aber wenn ich auf den string "NULL", wird der insert erfolgreich war.
Wenn ich versuche, rufen Sie die gespeicherte Prozedur zum aktualisieren der eingefügten Datensatz mit
Werte:
DCWEB4578, "NULL", 2P, CO, COLORODO
das update wird nicht passieren, da ich immer noch den ursprünglichen Datensatz in der Tabelle.

InformationsquelleAutor user1433167 | 2012-06-03

oracle sql

1

Ihre unmittelbare problem ist, dass wenn Sie PROD_CD und PLAN_CD im Primärschlüssel, die primary key-Einschränkung wird verlangt, dass beide Spalten NOT NULL. Sie können entweder zulassen, dass NULL Werte in diesen Spalten oder Sie fügen diese in den primär-Schlüssel, aber nicht beide.

Hätte dies klarer, Sie hatten Ihre exception-Handler nicht geschrieben worden zu schlucken Ausnahmen. Es ist fast immer ein Fehler warten, um zu geschehen, wenn Sie code so etwas wie dieses
```
when others then
  rollback;
end;
```
Wenn Sie gehen, um eine when others exception-handler, Sie fast immer wollen, um zumindest die exception erneut ausgelöst.
```
when others then
  rollback;
  raise;
end;
```
Sonst, der Anrufer hat keine Ahnung, dass ein Fehler aufgetreten ist und keine Ahnung hat, was der Fehler war. Hatten Sie re-die die Ausnahme ausgelöst hat, Sie hätte etwas gesehen wie
```
ORA-01400: cannot insert NULL into ("DCWEB"."USER_PLAN_PREFERENCE"."PROD_CD")
```
das würde zumindest zeigte Sie in die richtige Richtung und würde Euch gegeben haben, etwas hier zu posten.

Zusätzlich, Parameter zu deklarieren, die denselben Namen haben wie die Spalten in den Tabellen ist ein Fehler warten, um zu geschehen. Sie wirklich wollen, zu erlassen eine Art von übereinkommen zu differenzieren parameter-Namen aus Spalte Namen. Ich persönlich parameter prefix-Namen mit p_, d.h. p_userid in varchar2,. Ansonsten ist dein code ziemlicher Sicherheit nicht tun, was Sie erwarten.

Seit Spaltennamen haben Vorrang vor lokalen Variablen, die beim ausführen einer SQL-Anweisung, Ihre UPDATE - Anweisung löst upper(userid), upper(prod_cd), etc, um die Spalten in der USER_PLAN_PREFERENCE Tabelle nicht zu den Parametern, die übergeben wurden, in. Vorausgesetzt Ihre Daten sind immer im oberen "case", diese UPDATE - Anweisung aktualisiert wird jede Zeile der USER_PLAN_PREFERENCE, nicht nur die eine Zeile, die Sie erwarten, zu aktualisieren, und es wird jede Zeile LST_UPDATE_TS zu currentTimestamp.
```
 update user_plan_preference up 
    set up.userid = upper(userid),
        up.prod_cd = upper(prod_cd),
        up.plan_cd = upper(plan_cd),
        up.state_lst = upper(state_lst),
        up.state_nm = upper(state_nm),
        up.lst_update_ts = currentTimestamp
  where up.userid = upper(userid)
    and up.prod_cd = upper(prod_cd)
    and up.plan_cd = upper(plan_cd);
```
Wenn Ihr Namenskonvention unterscheidet Spalte-Namen und Parameternamen verwenden, wäre es viel schwieriger zu versehentlich den Namen einer Spalte aus, wenn Sie bestimmt sind zu verwenden, einen parameter oder eine lokale variable name.
- Justin. Danke für die info - sehr hilfreich. Die Tabelle ermöglicht nun die null in PROD_CD und PLAN_CD, und ich entfernt die Felder vom Primärschlüssel. Ich folgte auch Ihrem Vorschlag voranstellen von einem p_ meiner parameter-Namen. Das einzige problem, das bleibt, ist, dass das einfügen gelingt mit Werte: DCWEB4578, , 1P, CO, COLORODO, Wenn ich rufen Sie die setUserPlanPref ein zweites mal mit params: DCWEB4578, , 1P, CA, CALIFORNIA die Tabelle aktualisiert sich nicht. Weitere Vorschläge? Nochmals vielen Dank.
- In der Tabelle definition, die ich gestellt:PROD_CD VARCHAR2(9) , PLAN_CD VARCHAR2(9) , und ich habe auch versucht: PROD_CD VARCHAR2(9) NULL, PLAN_CD VARCHAR2(9) NULL ist, ist Der einzige Weg, ich war in der Lage, um die Zeile zu aktualisieren ist, wenn ich den string "NULL" als input-Wert für die PROD_CD anstatt Sie leer/null.
- Unter der Annahme, dass USERID wird nun der Primärschlüssel der Tabelle, das problem ist, dass das Prädikat up.prod_cd = upper(p_prod_cd) nicht wahr sein, wenn entweder up.prod_cd oder p_prod_cd ist NULL. NULL Werte werden nie einander gleich (Sie sind auch nie ungleich zu jedem anderen). Sie müssten entweder einen NVL oder explizit zu überprüfen, für NULL, d.h. (up.prod_cd = upper(p_prod_cd) or (up.prod_cd is null and p_prod_cd is null) oder NVL(up.prod_cd, 'NULL') = nvl(upper(p_prod_cd),'NULL').
InformationsquelleAutor Justin Cave

das problem ist hier

wenn Sie möchten, eine col nicht akzeptieren null-Werte, die Sie definieren es als NICHT NULL
und Umgekehrt

CREATE TABLE DCWEB.USER_PLAN_PREFERENCE
(
  USERID        VARCHAR2(40) NOT NULL,
  PROD_CD       VARCHAR2(9)  NULL,--**this should be NOT NULL** 
  PLAN_CD       VARCHAR2(9)  NULL,--**this should be NOT NULL** 
  STATE_LST     VARCHAR2(2)  NOT NULL,
  STATE_NM      VARCHAR2(40) NOT NULL,
  LST_UPDATE_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

InformationsquelleAutor shareef

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.