$tmp_imgpath=/srv/disk16/3266814/www/phporacle.eu5.net/fwphp/glomodul/post/Uploads/iphone.jpg
$tmp_imgurlrel=Uploads/iphone.jpg

Post content :

2a. Oracle 11g PL/SQL Tutorial

HOME � download 1_pl_sql.zip

Oracle 11g PL/SQL Tutorial about testing developing DB procedures

1. Three loops and three branches
2. Function, procedure, package, array object
3. Number, string, date, boolean
4. DDL
5. CRUD

1. Three loops and three branches

/* start ...path...02_02Hello_var_loop_if_exception_date.sql

O U T P U T :

--- _loop1. LOOP,EXIT WHEN v\_cntr>=2 (v\_cntr=0,1)_
Hello 28.05.2017
Hello 28.05.2017
---
--- _loop2. FOR v\_cntr IN 1..2 LOOP - IF v\_cntr = 2 THEN_ 
 _RAISE\_APPLICATION\_ERROR(-20001, v\_errmsg);_
- EXCEPTION - WHEN others THEN - IF sqlcode = -20001...
Hello
Hello , \*\*\*v\_cntr=2 custom EXCEPTION **!!!at!!!** IF v\_cntr=2 <-- !!
---
--- _loop2a. FOR ii IN REVERSE 3..9 LOOP, mod(ii,3) != 0_
ii=9
ii=6
ii=3
---
--- _loop3. From WHILE v\_cntr < 2 LOOP_
Hello
Hello
---
---
~~~~~~~~ **Three  B R A N C H E S** ~~~~~~~~
--- _branch1. IF - ELSIF - ELSE - ENDIF_
x message
---
--- _branch2. searched CASE - WHEN - ELSE - END CASE_
aaa
---
--- _branch3. simple "CASE x" - WHEN - ELSE - END CASE_ (as PHP switch - case - default)
I don't know what v\_msg is
\*/

set linesize 200
set serveroutput oFF
set serveroutput on size 1000000

------------

DECLARE
  v\_cntr NUMBER := 0;
  v\_system\_date CONSTANT DATE := sysdate;
  v\_errmsg VARCHAR2(255);
  v\_msg VARCHAR2(255) := 'Hello ';

  BEGIN
    msg('---');
    msg('~~~~~~~~ **Three  L O O P S** ~~~~~~~~'); 
    msg('--- **_loop1. LOOP,EXIT WHEN v\_cntr>=2 (v\_cntr=0,1)_**');
  LOOP
    EXIT WHEN v\_cntr >= 2; -- 2 loops : v\_cntr = 0, 1
    v\_cntr := v\_cntr + 1;

    msg(v\_msg || to\_char(v\_system\_date, 'DD.MM.YYYY'));
  END LOOP;

  BEGIN
    msg('---');
    msg('--- **_loop2. FOR v\_cntr IN 1..2 LOOP - IF v\_cntr = 2_ 
** _**THEN RAISE\_APPLICATION\_ERROR(-20001, v\_errmsg);**');_
    msg('      - EXCEPTION - WHEN others THEN - IF sqlcode = -20001...');

    FOR v\_cntr IN 1..2 LOOP
     IF v\_cntr = 2 THEN
       v\_errmsg:=v\_msg || ', \*\*\*v\_cntr=2 custom EXCEPTION !!!at!!! IF v\_cntr=2 <----------- !!!!!!!!';
       RAISE\_APPLICATION\_ERROR(-20001, v\_errmsg);
     END IF;
     msg(v\_msg);
    END LOOP;

  EXCEPTION
  WHEN no\_data\_found THEN  msg(
    'no\_data\_found EXCEPTION --- 2 From FOR v\_cntr IN 1..2 LOOP');
  WHEN others THEN
    IF sqlcode = -20001 THEN msg(v\_errmsg);
    ELSE RAISE; -- go to end script err handler
    END IF;
  END;
                --O U T P U T S :
                --Hello
                --Hello , \*\*\*v\_cntr=2 custom EXCEPTION !!!at!!! IF v\_cntr=2 
  msg('---');
  msg('--- **_loop2a. FOR ii IN REVERSE 3..9 LOOP,_ 
 _mod(ii,3) != 0');_**
  DECLARE
    v\_increment NUMBER := 3;
  BEGIN
    FOR ii IN REVERSE 3..9 LOOP
      IF mod(ii,v\_increment) != 0 THEN CONTINUE; END IF;
      msg('ii=' || ii);
    END LOOP;
  END;

  msg('---');
  msg('--- **_loop3. From WHILE v\_cntr < 2 LOOP_**');
  v\_cntr := 0 ;
  WHILE v\_cntr < 2 LOOP
   v\_cntr := v\_cntr + 1;
   msg(v\_msg);
  END LOOP;

  msg('---'); msg('---');
  msg('~~~~~~~~ **Three  B R A N C H E S** ~~~~~~~~');
  msg('--- **_branch1. IF - ELSIF - ELSE - ENDIF_**');
  v\_msg := 'x message';
  IF v\_msg LIKE 'x%' THEN msg(v\_msg);
  ELSIF v\_msg = 'aaa' THEN msg(v\_msg);
  ELSE msg('I don''t know what v\_msg is');
  END IF;

  msg('---');
  msg('--- **_branch2. searched CASE - WHEN - ELSE - END CASE_**');
  v\_msg := 'aaa';
  CASE 
  WHEN v\_msg LIKE 'x%' THEN msg(v\_msg);
  WHEN v\_msg = 'aaa' THEN msg(v\_msg);
  ELSE msg('I don''t know what v\_msg is');
  END CASE;

  msg('---');
  msg('--- **_branch3. simple "CASE x" - WHEN - ELSE - END CASE_ 
 _(as PHP switch - case - default)_**');
  v\_msg := 'x';
  CASE v\_msg -- same as PHP switch - case - default
  WHEN 'aaa' THEN msg(v\_msg);
  ELSE msg('I don''t know what v\_msg is');
  END CASE;

END;
/
set serveroutput oFF

[go top](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#top)

2\. Function, procedure, package, array object
==============================================

/\*
start ...path...\\02\_03fn\_proc\_pck\_assoc\_arr\_object.sql

**O U T P U T** :

--- 1. fn return\_1 SAYS: 1

DML A S S O C I A T I V E  A R R A Y
--- 2. anonymus pl/sql block SAYS: DML associative\_array v\_array(2)=Hello Again!
--------2.1 robust loop through array
Hello World!
Hello Again!
--------2.2 simple loop through array
Hello World!
Hello Again!

--------2.3 robust loop through array
Doe
King

--------2.4 robust loop through array with delete
Hello World! Date and time: Sunday    on 28 May, 2017 @ 08:12:34

--- 3.1 anonymus pl/sql block SAYS: DDL object\_o\_person v\_person.lname=Doe2

--- 3.2 anonymus pl/sql block SAYS: DDL object\_o\_person v\_person(1).lname=Doe3

--- 4. my\_package.crerow\_t;  and read it
This is a message

--- 5. print business days
Tuesday 4 of July, 2017
Monday 25 of December, 2017
\*/

set linesize 200
set serveroutput OFF
set serveroutput on size 1000000
------------

-- c a l l  f u n c t i o n :
--BEGIN msg('---aaaaa'); END;
BEGIN msg('--- 1. fn return\_1 SAYS: '||return\_1); END;
/
-- c a l l  p r o c e d u r e :
--BEGIN insert\_a\_rec(...); END;

-- DML associative\_array
DECLARE
  TYPE t\_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY\_INTEGER;
  v\_array t\_array;
  v\_index BINARY\_INTEGER;    
BEGIN
  v\_array(1) := 'Hello World!';
  v\_array(2) := 'Hello Again!';
  msg('DML A S S O C I A T I V E  A R R A Y');
  msg('--- 2. anonymus pl/sql block SAYS: DML associative\_array v\_array(2)='||v\_array(2));

  msg('--------2.1 robust loop through array');
  v\_index := v\_array.FIRST;
  LOOP
    EXIT WHEN v\_index IS NULL;
    msg( v\_array( v\_index ) );
    v\_index := v\_array.NEXT(v\_index);
  END LOOP;

  msg('--------2.2 simple loop through array');
  FOR ii IN v\_array.FIRST..v\_array.lAST 
  LOOP
    msg( v\_array(ii) );
  END LOOP;

END;
/

DECLARE
  TYPE t\_varchar2 IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY\_INTEGER;

  TYPE t\_number IS TABLE OF NUMBER
    INDEX BY BINARY\_INTEGER;

  v\_first\_name t\_varchar2;
  v\_last\_name  t\_varchar2;
  v\_salary     t\_number;

  v\_index BINARY\_INTEGER;    
BEGIN

  SELECT first\_name, last\_name, salary 
    BULK COLLECT INTO v\_first\_name, v\_last\_name, v\_salary
    FROM employees where rownum < 3;

  msg('--------2.3 robust loop through array');
  v\_index := v\_last\_name.FIRST;
  LOOP
    EXIT WHEN v\_index IS NULL;
    msg( v\_last\_name( v\_index ) );
    v\_index := v\_last\_name.NEXT(v\_index);
  END LOOP;

END;
/

DECLARE
  TYPE t\_array IS TABLE OF VARCHAR2(30);
  v\_array t\_array;    
  v\_index BINARY\_INTEGER;
  v\_date DATE := SYSDATE;
BEGIN
  msg('--------2.4 robust loop through array with delete');
  v\_array := t\_array();
  v\_array.extend; v\_array(1) := 'Hello World!';
  v\_array.extend; v\_array(2) := 'Hello Again!';

  v\_array.DELETE(2);
  v\_index := v\_array.FIRST;
  LOOP
    EXIT WHEN v\_index IS NULL;
    --msg( v\_array( v\_index ) );
    msg(
      v\_array( v\_index ) ||
      ' Date and time: ' ||
      to\_char(v\_date, 'Day') || ' on ' ||
      to\_char(v\_date, 'FMDD Month, YYYY') ||
      ' @ ' ||
      to\_char(v\_date, 'HH24:MI:SS')
    );
    v\_index := v\_array.NEXT(v\_index);
  END LOOP;  
END;
/

--------
DECLARE
  v\_person o\_person;    
BEGIN 
  v\_person := o\_person('John', 'Doe', 21);
  v\_person := o\_person('John2', 'Doe2', 22);

  msg('--- 3.1 anonymus pl/sql block SAYS: DDL object\_o\_person v\_person.lname='||v\_person.lname);
END;
/

DECLARE
  v\_person o\_person\_collec := o\_person\_collec();    -- table
BEGIN
  v\_person.extend;
  --row is TYPE OBJECT :
  v\_person(1) := o\_person('John3', 'Doe3', 23); 

  msg('--- 3.2 anonymus pl/sql block SAYS: DDL object\_o\_person v\_person(1).lname='||v\_person(1).lname);

END;
/

BEGIN
  msg('--- 4. my\_package.crerow\_t;  and read it');
  --in sql+ : exec my\_package.delrow\_t('xx')
  my\_package.crerow\_t;
  for rx in (select \* from t where country\_id = 'xx') loop
    msg( rx.country\_name );
  end loop;
END;
/

begin
  msg('--- 5. print business days ');
  print\_business\_days(
     to\_date('31-DEC-2016', 'DD-MON-YYYY'),
     to\_date('31-DEC-2017', 'DD-MON-YYYY')
  );
end;
/

set serveroutput oFF

[go top](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#top)

3\. Number, string, date, boolean
=================================

\-- ...path...03\_01num\_string\_date\_boolean.sql
-- 1. Valid numbers
DECLARE
  vNum NUMBER;
BEGIN
  vNum := 100;
  vNum := 98989898989898;
  vNum := 0.00000000000000001;
  vNum := 10928383.9028282772722626262;
END;
/

-- 2. CHAR versus VARCHAR2
DECLARE
  vc2\_Name  VARCHAR2(10) := 'Lewis';
  char\_Name CHAR(10)   := 'Lewis';
BEGIN
  IF vc2\_Name = char\_Name THEN
    dbms\_output.put\_line('VARCHAR2, CHAR Variables Match');
  ELSE dbms\_output.put\_line(
     'VARCHAR2, CHAR Variables Do Not Match');
  END IF;  
END;
/

-- 3. String to number conversion
DECLARE
  v\_string\_var VARCHAR2(10) := '15';
  v\_number\_var NUMBER;
BEGIN
   v\_number\_var := TO\_NUMBER(v\_string\_var);
END;

-- 4. Date conversion
DECLARE
  v\_string VARCHAR2(30) := '10/30/1998 12:34:03 PM';
  v\_date DATE;
BEGIN
  v\_date := to\_date(v\_string, 'MM/DD/YYYY HH:MI:SS AM');
  v\_date := to\_date(v\_string, 'DD.MM.YYYY HH24:MI:SS');
END;
/

-- 5. Timestamp conversion
DECLARE
  v\_string VARCHAR2(30) := '10/30/1998 12:34:03.987654 PM';
  v\_date timestamp;
BEGIN
  v\_date := to\_timestamp(v\_string, 'MM/DD/YYYY HH:MI:SS.FF AM');
END;

-- 6. Date to char conversion
DECLARE
  v\_string VARCHAR2(30) := '10/30/1998 12:34:03 PM';
  v\_date DATE;
BEGIN
  v\_date := to\_date(v\_string, 'MM/DD/YYYY HH:MI:SS AM');
  dbms\_output.put\_line(  to\_char(v\_date, 'FMDD Month, YYYY') );
END;

-- 7.1 BOOLEAN good example of direct usage
DECLARE
  v\_var1 NUMBER := 0;
  v\_var2 NUMBER := 1;
BEGIN
  IF v\_var1 = v\_var2 THEN NULL; END IF;
END;

-- 7.2 BOOLEAN good example for variable reuse
DECLARE
  v\_boolean BOOLEAN;
  v\_var1 NUMBER := 0;
  v\_var2 NUMBER := 1;
BEGIN
  v\_boolean := v\_var1 = v\_var2;
  IF v\_boolean THEN NULL; END IF;
END;

-- 7.3 BOOLEAN Poor Example
DECLARE
  v\_boolean BOOLEAN;
  v\_var1 NUMBER := 0;
  v\_var2 NUMBER := 1;
BEGIN
  IF v\_var1 = v\_var2 THEN v\_boolean := TRUE;
  ELSE v\_boolean := FALSE;
  END IF;

  IF v\_boolean THEN NULL; END IF;
END;
/

[go top](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#top)

4\. DDL
=======

/\*
HR@ora7 27.05.2017 21:21:16> start ...path...\\01\_02DDL.sql
O U T P U T :
~~~~~ 1. c r e  LOG\_ TABLE ~~~~~
~~~~~ 2. c r e  tbl t2 ~~~~~
~~~~~ 3. c r e  tbl emp\_names\_nds ~~~~~

~~~~~ 1. c r e  p r o c e d u r e  m s g ~~~~~
~~~~~ 2. c r e  p r o c e d u r e  l o g i t ~~~~~
~~~~~ 3. c r e  p r o c PRINT\_ BUSINESS\_ DAYS ~~~~~
~~~~~ 4. cre fn return\_1 ~~~~~
~~~~~ 5. cre R O W TYPE o\_ person AS OBJECT ~~~~~
~~~~~ 6. cre T B L TYPE o\_ person\_ collec AS TABLE OF ~~
~~~~~ 6.1 cre pck my\_ package ~~~~~
~~~~~ 6.2 cre pck body my\_ package ~~~~~
~~~~~ 7.1 cre pck t2\_ dyn\_ api ~~~~~
~~~~~ 7.2 cre pck body t2\_ dyn\_ api ~~~~~
\*/
drop TABLE LOG\_TABLE;
prompt ~~~~~ 1. c r e  LOG\_ TABLE ~~~~~
CREATE TABLE LOG\_TABLE
  (
    DATUM DATE,
    MESSAGE VARCHAR2(255 BYTE)
 ) ;
--insert into log\_table (date\_and\_time, message) 
--    VALUES (sysdate, 'Hello World!');
prompt ~~~~~ 2. c r e  tbl t2 ~~~~~
drop TABLE t2 ;
BEGIN
  EXECUTE IMMEDIATE 
  'CREATE TABLE t2 (
       col1 VARCHAR2(25)
     , col2 VARCHAR2(25)
     , col3 VARCHAR2(25)
  )';
END;
/

prompt ~~~~~ 3. c r e  tbl emp\_names\_nds ~~~~~
drop TABLE emp\_names\_nds;
CREATE TABLE emp\_names\_nds (
  last\_name VARCHAR2(25), first\_name VARCHAR2(20) );

prompt ~~~~~ 1. c r e  p r o c e d u r e  m s g ~~~~~
CREATE OR REPLACE PROCEDURE msg(
   p\_msg IN VARCHAR2 DEFAULT 'NO MESSAGE')
AS
BEGIN
   dbms\_output.put\_line(p\_msg);
END;
/
--show error

prompt ~~~~~ 2. c r e  p r o c e d u r e  l o g i t ~~~~~
create or replace PROCEDURE logit(
  v\_message IN VARCHAR2 DEFAULT 'Hello World!',
  v\_output\_target IN VARCHAR2 DEFAULT 'T')
AS
  -- v\_output target may be T for table or
  --    D for dbms\_output
  PRAGMA AUTONOMOUS\_TRANSACTION;
  v\_date DATE := SYSDATE;

  PROCEDURE do\_output(
    v\_message IN VARCHAR2,
    v\_date IN DATE )
  AS
  BEGIN

    DBMS\_OUTPUT.put\_line(
        v\_message ||
        ' On date ' ||
        to\_char(v\_date, 'Day') || ' on ' ||
        to\_char(v\_date, 'FMDD Month, YYYY') ||
        ' @ ' ||
        to\_char(v\_date, 'HH24:MI:SS')
        ); 
  END;    

  PROCEDURE do\_insert(
    v\_message IN VARCHAR2,
    v\_date IN DATE )
  AS
  BEGIN
    insert into log\_table
      (datum, message)
      VALUES (v\_date, v\_message);
  END;
-------------
BEGIN
  CASE
  WHEN v\_output\_target = 'T'
  THEN
    do\_insert(v\_message, v\_date);
  WHEN v\_output\_target = 'D'
  THEN
    do\_output(v\_message, v\_date);
  WHEN v\_output\_target = 'TD'
    OR v\_output\_target = 'DT'
  THEN
    do\_insert(v\_message, v\_date);
    do\_output(v\_message, v\_date);
  ELSE

    logit('ERROR v\_output\_target: ' ||
          v\_output\_target ||
          ' not found.', 'T' );

  END CASE;

  COMMIT;

END logit;
/

prompt ~~~~~ 3. c r e  p r o c PRINT\_ BUSINESS\_ DAYS ~~~~~
create or replace PROCEDURE PRINT\_BUSINESS\_DAYS
  (
      P\_START\_DATE IN DATE 
    , P\_END\_DATE IN DATE 
  )
AS
  TYPE t\_holidays IS TABLE OF VARCHAR2(100)
    INDEX BY VARCHAR2(30);
  v\_dates t\_holidays;
  v\_loop\_increment NUMBER := 0;
BEGIN
  v\_dates('01-JAN') := 'Mew Years';
  v\_dates('04-JUL') := 'Independence Day';
  v\_dates('25-DEC') := 'Christmas Day';

  LOOP
    EXIT WHEN p\_start\_date + v\_loop\_increment > p\_end\_date;

    /\*
    IF to\_number(to\_char(p\_start\_date 
          + v\_loop\_increment, 'd'))
      IN (2,3,4,5,6)
    THEN 
      dbms\_output.put\_line(to\_char(p\_start\_date 
        + v\_loop\_increment, 'FMDay DD "of" Month, YYYY'));
    END IF;
    \*/    

    -- or :
    IF v\_dates.EXISTS(
      to\_char(p\_start\_date + v\_loop\_increment, 'DD-MON'))
    THEN

      IF to\_number(to\_char(p\_start\_date 
          + v\_loop\_increment, 'd'))
        IN (2,3,4,5,6)
      THEN 
        dbms\_output.put\_line(to\_char(p\_start\_date 
          + v\_loop\_increment, 'FMDay DD "of" Month, YYYY')); 
      END IF;

    END IF;

    v\_loop\_increment := v\_loop\_increment + 1;

  END LOOP;

END PRINT\_BUSINESS\_DAYS;
/

prompt ~~~~~ 4. cre fn return\_1 ~~~~~
CREATE OR REPLACE FUNCTION return\_1
RETURN NUMBER
AS
BEGIN
  RETURN 1;
END;
/
--show error

prompt ~~~~~ 5. cre R O W TYPE o\_ person AS OBJECT ~~~~~
drop TYPE o\_person\_collec ;
CREATE OR REPLACE TYPE o\_person AS OBJECT (
    fname VARCHAR2(30),
    lname VARCHAR2(30),
    age NUMBER 
);
/
--show error

prompt ~~~~~ 6. cre T B L TYPE o\_ person\_ collec AS TABLE OF ~~
CREATE OR REPLACE TYPE o\_person\_collec --table 
  AS TABLE OF o\_person; --row is TYPE OBJECT 
/
--show error

prompt ~~~~~ 6.1 cre pck my\_ package ~~~~~
CREATE OR REPLACE PACKAGE my\_package
AS
  FUNCTION return\_1 RETURN NUMBER;
  PROCEDURE crerow\_t;
  PROCEDURE delrow\_t(p\_id in varchar2);
END;
/
prompt ~~~~~ 6.2 cre pck body my\_ package ~~~~~
CREATE OR REPLACE PACKAGE BODY my\_package
AS
  FUNCTION return\_1 RETURN NUMBER AS
  BEGIN
    RETURN 1;
  END;

  PROCEDURE crerow\_t AS
  BEGIN
    INSERT INTO t (country\_id, country\_name)
      VALUES ('xx', 'This is a message');
  END;

  PROCEDURE delrow\_t(p\_id in varchar2) AS
  BEGIN
    delete t where country\_id = p\_id; --'xx'
  END;

END;  
/

prompt ~~~~~ 7.1 cre pck t2\_ dyn\_ api ~~~~~
create or replace PACKAGE t2\_dyn\_api IS
 PROCEDURE insert\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  );

 PROCEDURE update\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  );

 PROCEDURE delete\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  );  
END;
/

prompt ~~~~~ 7.2 cre pck body t2\_ dyn\_ api ~~~~~
create or replace PACKAGE BODY t2\_dyn\_api IS
  PROCEDURE insert\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  ) 
  AS 

    v\_dml\_string CLOB;

    -- DBMS\_SQL variables
    v\_cursor\_id NUMBER;
    v\_rows\_fetched NUMBER;

  BEGIN

    v\_cursor\_id := DBMS\_SQL.open\_cursor;

    v\_dml\_string := 'INSERT INTO t2 (col1, col2, col3) ';
    v\_dml\_string := v\_dml\_string || 'VALUES (:col1
         , :col2, :col3) ';

    -- Display the string
    logit( v\_dml\_string, 'D');

    DBMS\_SQL.PARSE(v\_cursor\_id, v\_dml\_string
       , DBMS\_SQL.NATIVE);

    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col1', p\_col1);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col2', p\_col2);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col3', p\_col3);

    v\_rows\_fetched := DBMS\_SQL.EXECUTE(v\_cursor\_id);
    DBMS\_SQL.CLOSE\_CURSOR(v\_cursor\_id);

    COMMIT;

    logit( 'Rows Fetched: ' || to\_char(v\_rows\_fetched), 'D');

  END;

  PROCEDURE update\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  ) 
  AS 

    v\_dml\_string CLOB;

    -- DBMS\_SQL variables
    v\_cursor\_id NUMBER;
    v\_rows\_fetched NUMBER;

  BEGIN

    v\_cursor\_id := DBMS\_SQL.open\_cursor;

    v\_dml\_string := 'UPDATE t2 ';
    v\_dml\_string := v\_dml\_string || 
               'SET col1 = :col1, 
                    col2 = :col2,
                    col3 = :col3 ';

    -- Display the string
    logit( v\_dml\_string, 'D');

    DBMS\_SQL.PARSE(v\_cursor\_id, v\_dml\_string
       , DBMS\_SQL.NATIVE);

    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col1', p\_col1);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col2', p\_col2);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col3', p\_col3);

    v\_rows\_fetched := DBMS\_SQL.EXECUTE(v\_cursor\_id);
    DBMS\_SQL.CLOSE\_CURSOR(v\_cursor\_id);

    COMMIT;

    logit( 'Rows Fetched: ' || to\_char(v\_rows\_fetched), 'D');

  END;  
  PROCEDURE delete\_row(
    p\_col1 IN t2.col1%TYPE,
    p\_col2 IN t2.col2%TYPE,
    p\_col3 IN t2.col3%TYPE
  ) 
  AS 

    v\_dml\_string CLOB;

    -- DBMS\_SQL variables
    v\_cursor\_id NUMBER;
    v\_rows\_fetched NUMBER;

  BEGIN

    v\_cursor\_id := DBMS\_SQL.open\_cursor;

    v\_dml\_string := 'DELETE FROM t2 ';
    v\_dml\_string := v\_dml\_string || 
               'WHERE col1 = :col1 AND 
                      col2 = :col2 AND
                      col3 = :col3 ';

    -- Display the string
    logit( v\_dml\_string, 'D');

    DBMS\_SQL.PARSE(v\_cursor\_id, v\_dml\_string
       , DBMS\_SQL.NATIVE);

    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col1', p\_col1);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col2', p\_col2);
    DBMS\_SQL.bind\_variable( v\_cursor\_id, 'col3', p\_col3);

    v\_rows\_fetched := DBMS\_SQL.EXECUTE(v\_cursor\_id);
    DBMS\_SQL.CLOSE\_CURSOR(v\_cursor\_id);

    COMMIT;

    logit( 'Rows Fetched: ' || to\_char(v\_rows\_fetched), 'D');

  END; 

END t2\_dyn\_api;
/

/\*
-- ORA-01031: insufficient privileges
CREATE OR REPLACE PROCEDURE CREATE\_TABLE 
(
  P\_TABLE\_NAME IN VARCHAR2  
, P\_COLUMNS IN DBMS\_SQL.varchar2a  
) AS 
  v\_ddl\_string CLOB;
  v\_index PLS\_INTEGER;
  -- DBMS\_SQL variables
  v\_cursor\_id NUMBER;
  v\_rows\_fetched NUMBER;
BEGIN
  IF p\_table\_name IS NULL OR p\_columns.COUNT = 0
  THEN RETURN; END IF;

  v\_ddl\_string := 'CREATE TABLE ';
  v\_ddl\_string := v\_ddl\_string || p\_table\_name || '( '; 
  v\_index := p\_columns.FIRST;

  LOOP
    EXIT WHEN v\_index IS NULL;
    IF v\_index != p\_columns.FIRST
    THEN
      v\_ddl\_string := v\_ddl\_string || ', ';
    END IF;

    v\_ddl\_string := v\_ddl\_string || p\_columns(v\_index);
    v\_index := P\_columns.NEXT(v\_index);
  END LOOP;

  v\_ddl\_string := v\_ddl\_string || ')';
  -- Display the string
  logit( v\_ddl\_string, 'D');

  -- Create the table
  v\_cursor\_id := DBMS\_SQL.open\_cursor;
  DBMS\_SQL.PARSE(v\_cursor\_id, v\_ddl\_string, DBMS\_SQL.NATIVE);
  v\_rows\_fetched := DBMS\_SQL.EXECUTE(v\_cursor\_id);
  DBMS\_SQL.CLOSE\_CURSOR(v\_cursor\_id);

END CREATE\_TABLE;
/

DECLARE
  v\_table\_name VARCHAR2(30) := 't3';
  v\_columns DBMS\_SQL.varchar2a;
BEGIN
  v\_columns(1)  := 'COL1 VARCHAR2(10)';
  v\_columns(5)  := 'COL2 VARCHAR2(2)';
  v\_columns(10) := 'COL3 NUMBER';
  -- ORA-01031: insufficient privileges
  CREATE\_TABLE(v\_table\_name, v\_columns); 
END;
/
\*/

[go top](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#top)

5\. CRUD
========

/\*
start ...path...\\02\_01CRUd\_rowtype.sql

O U T P U T :
--- 1. CRUD R employee\_id= 115
r\_person.salary=3246,86
--- 2. CRUD U employee\_id =115
r\_person.salary\*,95=3084,52
r\_person.LAST\_NAME=Khoo
--- 3. CRUD D (if exists) employee\_id=999
--- 4. CRUD C employee\_id=999
r\_person.salary=3084,52
r\_person.LAST\_NAME=Doe
\*/
set linesize 200
set serveroutput oFF
set serveroutput on size 1000000
------------
/\*
-- rowtype cursor
DECLARE
  CURSOR c1 IS
    SELECT first\_name, last\_name, salary
    FROM employees;
  r\_person c1%ROWTYPE;    
BEGIN
 r\_person.first\_name := 'John';
 r\_person.last\_name  := 'Doe';
 r\_person.salary     := 2200.00; 
END;

-- rowtype record
DECLARE
  r\_person employees%ROWTYPE;    
BEGIN
 r\_person.first\_name := 'John';
 r\_person.last\_name  := 'Doe';
 r\_person.salary     := 2200.00; 
END;
\*/

BEGIN
  EXECUTE IMMEDIATE 'BEGIN logit(:mybindvar, :displaymode);
    END;' USING 'This is my dynamic message.', 'D';
END;

delete t2;
BEGIN
  execute immediate 'insert into t2 (col1, col2) 
    values (:col1val, :col2val)' using 'A', 'B';
  logit('Created Rows: ' || SQL%ROWCOUNT, 'D' );

  execute immediate 'update t2 set col1 = :newcol1
    where col2 = :oldcol2' using 'C', 'B';
  logit('Updated Rows: ' || SQL%ROWCOUNT, 'D' );

  execute immediate 
  'delete from  t2 where col2 = :oldcol2' using 'B';  
  logit('Deleted Rows: ' || SQL%ROWCOUNT, 'D' );
END;
select \* from emp\_names\_nds;

delete t2;
BEGIN
  t2\_dyn\_api.insert\_row('A', 'B', 1);
  t2\_dyn\_api.insert\_row('D', 'E', 2);
  t2\_dyn\_api.insert\_row('G', 'H', 3);
END;

select \* from t2;
BEGIN t2\_dyn\_api.update\_row('X', 'B', 1); END;

select \* from t2;
BEGIN t2\_dyn\_api.delete\_row('X', 'B', 1); END;

select \* from t2;

DECLARE v\_output VARCHAR2(1);
BEGIN
  execute immediate 'insert into t2 (col1, col2) 
    values (:col1val, :col2val) RETURNING col1 INTO :colret'
  using 'A', 'B', OUT v\_output;
  logit('v\_output: ' || v\_output, 'D' );   
END;

DECLARE
  v\_output VARCHAR2(1);
BEGIN
  execute immediate 
      'insert into t2 (col1, col2) 
         values (:col1val, :col2val)
         RETURNING col1 INTO :colret'
    using 'A', 'B'
    RETURNING INTO v\_output;

  logit('v\_output: ' || v\_output, 'D' );

END;

DECLARE
  v\_dml\_sel VARCHAR2(8000) := '
    SELECT last\_name, first\_name FROM employees
    WHERE rownum < :rowsproc';
  TYPE r\_emp IS RECORD (
    last\_name VARCHAR2(25),
    first\_name VARCHAR2(20) );
  TYPE v\_emp\_tbltyp IS TABLE OF r\_emp;
  v\_emp\_tbl v\_emp\_tbltyp;
BEGIN
  EXECUTE IMMEDIATE v\_dml\_sel BULK COLLECT INTO v\_emp\_tbl
  USING 10;

  FORALL ii IN v\_emp\_tbl.FIRST..v\_emp\_tbl.LAST
    EXECUTE IMMEDIATE 'INSERT INTO emp\_names\_nds (last\_name, first\_name) VALUES (:lastname, :firstname)'
    USING v\_emp\_tbl(ii).last\_name, v\_emp\_tbl(ii).first\_name;
END;

select \* from emp\_names\_nds;
--delete emp\_names\_nds; -- 99 deleted

-- measure bind performance
declare
  v\_char\_null varchar2(10);
  v\_num\_null number;
  v\_not\_null number := 0;
  v\_value number;
  v\_loop\_cnt PLS\_INTEGER := 10000;
  v\_start\_time timestamp(9);
  v\_end\_time timestamp(9);

  v\_rand number := abs(dbms\_random.random);

  v\_sql\_stmt VARCHAR2(1000);  
begin

  v\_loop\_cnt := v\_loop\_cnt + v\_rand;

  dbms\_output.put\_line('Random: ' || to\_char(v\_rand) );

  v\_start\_time := systimestamp;

  FOR i IN v\_rand..v\_loop\_cnt
  LOOP
    v\_sql\_stmt := 'SELECT NVL(to\_number(''''), to\_number(''' || i || ''')) FROM DUAL';
    EXECUTE IMMEDIATE v\_sql\_stmt INTO v\_value;
  END LOOP;  

  v\_end\_time := systimestamp;

  dbms\_output.put\_line( 'No Binds, Conversion         ' ||  
              to\_char(v\_end\_time - v\_start\_time) );

  v\_start\_time := systimestamp;

  FOR i IN v\_rand..v\_loop\_cnt
  LOOP
    v\_sql\_stmt := 'SELECT NVL(null, ' || i || ') FROM DUAL';
    EXECUTE IMMEDIATE v\_sql\_stmt  INTO v\_value;
  END LOOP;  

  v\_end\_time := systimestamp;

  dbms\_output.put\_line( 'No Binds, No Conversion      ' ||  
              to\_char(v\_end\_time - v\_start\_time) );

  v\_start\_time := systimestamp;

  v\_sql\_stmt := 'SELECT NVL(:v\_num\_null, :v\_not\_null) FROM DUAL' ;

  FOR i IN v\_rand..v\_loop\_cnt
  LOOP
    v\_not\_null := i;
    EXECUTE IMMEDIATE v\_sql\_stmt  INTO v\_value USING IN v\_num\_null, IN v\_not\_null;
  END LOOP;  

  v\_end\_time := systimestamp;

  dbms\_output.put\_line( 'Binds, No Conversion         ' ||  
              to\_char(v\_end\_time - v\_start\_time) );

  v\_start\_time := systimestamp;

  FOR i IN v\_rand..v\_loop\_cnt
  LOOP
    v\_not\_null := i;
    SELECT NVL(v\_num\_null, v\_not\_null) INTO v\_value FROM DUAL;
  END LOOP;  

  v\_end\_time := systimestamp;

  dbms\_output.put\_line( 'Not Dynamic                  ' ||  
              to\_char(v\_end\_time - v\_start\_time) );

end;

DECLARE
  cursor c\_person(c\_id in number) is
    SELECT \* FROM employees WHERE employee\_id = c\_id;
  r\_person employees%ROWTYPE;    
  v\_koef NUMBER;    
  v\_upd\_employee\_id NUMBER;    
  v\_cre\_employee\_id NUMBER;    
BEGIN
  v\_koef  := 0.95;
  v\_upd\_employee\_id := 115;
  v\_cre\_employee\_id := 999;
  -- 1. r e a d
  for rx in c\_person(v\_upd\_employee\_id) loop
    r\_person := rx ;
  end loop ;
                dbms\_output.put\_line(
                 '--- 1. CRUD R employee\_id= '
                 ||v\_upd\_employee\_id||chr(10)
                 ||'r\_person.salary='||r\_person.salary);

  -- 2. u p d a t e
  r\_person.salary := r\_person.salary \* v\_koef ;
  UPDATE employees SET salary = r\_person.salary 
  WHERE employee\_id = r\_person.employee\_id;
              for rx in c\_person(v\_upd\_employee\_id) loop
                r\_person := rx ;
              end loop ;
              dbms\_output.put\_line(
                '--- 2. CRUD U employee\_id ='
              ||v\_upd\_employee\_id||chr(10)
              ||'r\_person.salary\*'||v\_koef||'='
              ||r\_person.salary
              ||chr(10)
              ||'r\_person.LAST\_NAME='||r\_person.LAST\_NAME
              );

  -- 3. d e l e t e
  BEGIN DELETE employees WHERE employee\_id = v\_cre\_employee\_id;
  EXCEPTION WHEN others THEN null; END;
               dbms\_output.put\_line(
 '--- 3. CRUD D (if exists) employee\_id='||v\_cre\_employee\_id);

  -- 4. c r e a t e
  r\_person.employee\_id := v\_cre\_employee\_id;
  r\_person.last\_name   := 'Doe';
  r\_person.first\_name  := 'John';
  r\_person.email       := 'doe@aaa.aa';
  INSERT INTO employees VALUES r\_person;
              for rx in c\_person(v\_cre\_employee\_id) loop
                r\_person := rx ;
              end loop ;
               dbms\_output.put\_line(
               '--- 4. CRUD C employee\_id='||v\_cre\_employee\_id
               ||chr(10)
              ||'r\_person.salary='||r\_person.salary||chr(10)
              ||'r\_person.LAST\_NAME='||r\_person.LAST\_NAME
               );

  BEGIN
    --logit;
    BEGIN
      logit('Inserted new employee\_id='||v\_cre\_employee\_id);
      DECLARE
        v\_a\_different\_msg VARCHAR2(100);
      BEGIN
        rollback; -- Notice rollback
        v\_a\_different\_msg := 'Inserted new employee\_id='||v\_cre\_employee\_id;
        logit( v\_a\_different\_msg );
      END;
    END;
  END;

  --------------- 
END;
/

DECLARE
  v\_dml\_sel VARCHAR2(8000) := 
    'SELECT \* FROM employees WHERE rownum < :rnum1';
  TYPE v\_emp\_tbltyp IS TABLE OF employees%ROWTYPE;
  v\_emp\_tbl v\_emp\_tbltyp;
BEGIN
  EXECUTE IMMEDIATE 
    v\_dml\_sel BULK COLLECT INTO v\_emp\_tbl USING 3; --rnum1
  FOR ii IN 1..v\_emp\_tbl.LAST
  LOOP msg('On Loop: '||ii||', '||v\_emp\_tbl(ii).last\_name);
  END LOOP;     
END;

DECLARE
  v\_dml\_sel VARCHAR2(8000) := 
  'SELECT first\_name, last\_name, email, phone\_number, hire\_date
   FROM employees WHERE employee\_id = :p\_employee\_id';
  r\_emp employees%ROWTYPE;
BEGIN
  r\_emp.employee\_id := 101;
  execute immediate v\_dml\_sel 
     into r\_emp.last\_name,
          r\_emp.first\_name,
          r\_emp.email,
          r\_emp.phone\_number,
          r\_emp.hire\_date
     using r\_emp.employee\_id;
    logit(r\_emp.last\_name||', email='||r\_emp.email, 'D');
END;

DECLARE
  v\_dml\_sel VARCHAR2(8000) := 
  'SELECT first\_name, last\_name, email, phone\_number, hire\_date
   FROM employees WHERE employee\_id = :p\_employee\_id';
  --r\_emp\_refcurs\_id NUMBER;
  r\_emp\_refcurs sys\_refcursor;
  r\_emp employees%ROWTYPE;
  --v\_rows\_fetched NUMBER;
BEGIN
  --r\_emp\_refcurs\_id := DBMS\_SQL.open\_cursor;
  OPEN r\_emp\_refcurs FOR v\_dml\_sel USING 101; 
  -- see (1)
  LOOP 
    FETCH r\_emp\_refcurs INTO 
         r\_emp.first\_name, 
         r\_emp.last\_name, 
         r\_emp.email, 
         r\_emp.phone\_number, 
         r\_emp.hire\_date;
    EXIT WHEN r\_emp\_refcurs%NOTFOUND; 
    -- see (2)    
  END LOOP; 

    --DBMS\_SQL.close\_cursor(r\_emp\_refcurs\_id);
  CLOSE r\_emp\_refcurs;

    logit(r\_emp.last\_name||', email='||r\_emp.email, 'D');

END;

  -- see (1)
  /\*
  DBMS\_SQL.parse(r\_emp\_refcurs\_id, v\_dml\_sel, DBMS\_SQL.native );

  DBMS\_SQL.define\_column( r\_emp\_refcurs\_id, 1, r\_emp.first\_name, 20);
  DBMS\_SQL.define\_column( r\_emp\_refcurs\_id, 2, r\_emp.last\_name, 25);
  DBMS\_SQL.define\_column( r\_emp\_refcurs\_id, 3, r\_emp.email, 20);
  DBMS\_SQL.define\_column( r\_emp\_refcurs\_id, 4, r\_emp.phone\_number, 25);
  DBMS\_SQL.define\_column( r\_emp\_refcurs\_id, 5, r\_emp.hire\_date);

  DBMS\_SQL.bind\_variable( r\_emp\_refcurs\_id, 'p\_employee\_id', 101);

  v\_rows\_fetched := DBMS\_SQL.execute(r\_emp\_refcurs\_id);
  \*/
  -- see (2)
  /\*
  IF DBMS\_SQL.FETCH\_ROWS(r\_emp\_refcurs\_id)> 0 THEN 
    DBMS\_SQL.COLUMN\_VALUE( r\_emp\_refcurs\_id, 1, r\_emp.first\_name);
    DBMS\_SQL.COLUMN\_VALUE( r\_emp\_refcurs\_id, 2, r\_emp.last\_name);
    DBMS\_SQL.COLUMN\_VALUE( r\_emp\_refcurs\_id, 3, r\_emp.email);
    DBMS\_SQL.COLUMN\_VALUE( r\_emp\_refcurs\_id, 4, r\_emp.phone\_number);
    DBMS\_SQL.COLUMN\_VALUE( r\_emp\_refcurs\_id, 5, r\_emp.hire\_date);
  ELSE 
    EXIT; 
  END IF;
  \*/

set serveroutput oFF

 kod[ edit]($web_docroot_url/inc/utl/edservertxt.php)[ phpinfo]($web_docroot_url/phpinfo_inc.php) EOKOD; /\* call it so: kod\_edit\_run( $idx // script\_dir\_path , $idxscript // script\_name , MDURL); // web\_docroot\_url = (Apache) web server URL \*/ }

TXT AFTER EACH POST : I have odd cosmic thoughts every day

Science is an enterprise that should be cherished as an activity of the free human mind...

Venus has a runaway greenhouse effect. ... we're twiddling knobs here on Earth without knowing the consequences of it. Mars once had running water. Something bad happened there as well.