POUWIEL|COM

CREATE TABLE ballage2 ( X NUMBER(10), Y VARCHAR2(24 CHAR), Z DATE )
/

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

CREATE OR REPLACE PACKAGE PNL_BALLAGE2 IS 
  type cg$row_type is record
    ( x                      ballage2.x%type
    , y                      ballage2.y%type
    , z                      ballage2.z%type
    ) ;
--------------------------------------------------------------------------------
  procedure ins( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure upd( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure del( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure lck( p_ballage2_old_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  function slct( p_x	    	in ballage2.x%type )
  return cg$row_type ;
--
  cg$tmp_rec cg$row_type ;
--
  type t_ballage2_tabtype is table of cg$row_type index by binary_integer ;
end PNL_BALLAGE2 ;
/

CREATE OR REPLACE PACKAGE BODY PNL_BALLAGE2 IS
  PROCEDURE ins( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    INSERT INTO ballage2
      ( x
      , y
      , z
      )
      VALUES
      (       p_ballage2_rec.x
      , NVL ( p_ballage2_rec.y , 'Initiële actie' )
      ,       TO_DATE(p_ballage2_rec.z, 'YYYY/MM/DD HH24:MI:SS') )
      ;
  --
  END ins ;
  --------------------------------------------------------------------------------
  PROCEDURE upd( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    UPDATE ballage2
    SET    y						= p_ballage2_rec.y
    ,      z            = p_ballage2_rec.z
    WHERE  x						= p_ballage2_rec.x
    ;
  --
  END upd ;
  --------------------------------------------------------------------------------
  PROCEDURE del( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    DELETE FROM ballage2
    WHERE  x						= p_ballage2_rec.x
    AND    y						= p_ballage2_rec.y
    AND    z						= p_ballage2_rec.z
    ;
  --
  END del ;
  --------------------------------------------------------------------------------
  PROCEDURE lck( p_ballage2_old_rec in cg$row_type )
  IS
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   cg$tmp_rec.x
    ,      cg$tmp_rec.y
    ,      cg$tmp_rec.z
    FROM   ballage2
    WHERE  x						= p_ballage2_old_rec.x
    AND    y						= p_ballage2_old_rec.y
    AND    z						= p_ballage2_old_rec.z
    FOR UPDATE
    ;
  --
  END lck ;
  --------------------------------------------------------------------------------
  FUNCTION slct(p_x in ballage2.x%type)
  RETURN cg$row_type
  IS
    r_ballage2_rec cg$row_type DEFAULT NULL;
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   r_ballage2_rec.x
    ,      r_ballage2_rec.y
    ,      r_ballage2_rec.z
    FROM   ballage2
    WHERE  x            = p_x
    ;
  --
    RETURN r_ballage2_rec ;
  --
  END slct;
END PNL_BALLAGE2;
/

DECLARE 
  my_data ballage2%rowtype;
BEGIN 
  my_data.x := 106; 
  my_data.y := 'JACKING';
  my_data.z := TO_DATE('2017/06/19 15:00:01', 'YYYY/MM/DD HH24:MI:SS');

  pnl_ballage2.ins( my_data ); 
END; 
/

DECLARE
  CURSOR my_data_cur
    IS
    SELECT x, y, z
      FROM ballage2
     WHERE x=102;

  my_data my_data_cur%ROWTYPE;
BEGIN
  OPEN my_data_cur;
  FETCH my_data_cur INTO my_data;
  my_data.y := 'altered via upd';
  CLOSE my_data_cur;
  commit;
  pnl_ballage2.upd( my_data );
END;
/

DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 102;

  my_data.y := 'altered again via upd';

  pnl_ballage2.upd( my_data );
END;
/


DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 103;

  pnl_ballage2.del( my_data );
END;
/

DECLARE
my_data ballage2%ROWTYPE;
p1      varchar2(16);
BEGIN
SELECT *
    INTO my_data
    FROM ballage2
   WHERE x = 102;

pnl_ballage2.lck( my_data );
my_data.y := 'altered using lck';
dbms_lock.sleep(20);
pnl_ballage2.upd( my_data );
commit;
END;
/


DECLARE
  v_ret ballage2%ROWTYPE;
BEGIN
  v_ret:=pnl_ballage2.slct(104);
  DBMS_OUTPUT.PUT_LINE(v_ret.x||', '||v_ret.y||', '||v_ret.z);
END;
/