2a. Oracle 11g PL/SQL Tutorial
==============================

**[HOME – download 1\_pl\_sql.zip](http://phporacle.altervista.org/php-oracle-main-menu/ "Main menu")**

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

1.  Three [loops](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#loopbranch)  and three branches
2.  [Function](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#fn), procedure, package, array object
3.  [Number](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#num), string, date, boolean
4.  [DDL](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#ddl)
5.  [CRUD](http://phporacle.altervista.org/2a-oracle-11g-plsql-tutorial/#crud)

1\. Three  loops  and three branches
====================================

/\*
start ...path...**02\_02Hello\_var\_loop\_if\_exception\_date.sql**

**O U T P U T** :

~~~~~~~~ **Three  L O O P S** ~~~~~~~~
--- _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 \*/ }