Re: assigning nulls to records

From: Noel <tbal_at_go2.pl>
Date: Wed, 21 Jan 2004 09:40:00 -0000
Message-ID: <buldnb$11v$1@inews.gazeta.pl>

Użytkownik "Haximus" <14356256546_at_spam.org> napisał w wiadomości news:uAePb.192217$X%5.2906_at_pd7tw2no...
> The PL/SQL documentation for 9.2 states that "to set all the fields in a
> record to null, simply assign to it an uninitialized record of the same
> type" and gives the following example:
>
> DECLARE
> TYPE EmpRec IS RECORD (
> emp_id emp.empno%TYPE,
> job_title VARCHAR2(9),
> salary NUMBER(7,2));
> emp_info EmpRec;
> emp_null EmpRec;
> BEGIN
> emp_info.emp_id := 7788;
> emp_info.job_title := 'ANALYST';
> emp_info.salary := 3500;
> emp_info := emp_null; -- nulls all fields in emp_info
> ...
> END;
>
> Is it acceptable to set fields in a record to null by assigning null to
the
> record itself? Like:
>
> emp_info := null;

Yes. An simple example:

CREATE TABLE T_XXX
 (

  F_1 NUMBER,
  F_2 NUMBER,
  F_3 VARCHAR2(10)

  );

---

DECLARE  TYPE TP_XXX IS
  RECORD
   (

    f_1 NUMBER,
    f_2 NUMBER,
    f_3 VARCHAR2(10)

    );
  v_tp_xxx tp_xxx;
  CURSOR C_XXX IS
   SELECT *
     FROM T_XXX;
  v_xxx C_XXX%ROWTYPE;

BEGIN

  v_xxx.f_1 := 6;
  v_xxx.f_2 := 8;
  v_xxx.f_3 := 'xxx';

  INSERT INTO T_XXX
  VALUES(v_xxx.f_1,v_xxx.f_2, v_xxx.f_3);

 v_xxx := NULL;
  INSERT INTO T_XXX
  VALUES(v_xxx.f_1,v_xxx.f_2, NVL(v_xxx.f_3,'NULL'));

  v_tp_xxx.f_1 := 16;
  v_tp_xxx.f_2 := 7;
  v_tp_xxx.f_3 := 'RECORD';

  INSERT INTO T_XXX
  VALUES(v_tp_xxx.f_1,v_tp_xxx.f_2, v_tp_xxx.f_3);

  v_tp_xxx := NULL;
  INSERT INTO T_XXX
  VALUES(v_tp_xxx.f_1,v_tp_xxx.f_2, NVL(v_tp_xxx.f_3,'REC:NULL'));

  COMMIT;
END;
/

---

SELECT *
  FROM T_XXX; Received on Wed Jan 21 2004 - 03:40:00 CST