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;
/