PHP Classes

File: fwphp/glomodul/blog/msgmkd/altervista002a.txt

Recommend this page to a friend!
  Classes of Slavko Srakocic   B12 PHP FW   fwphp/glomodul/blog/msgmkd/altervista002a.txt   Download  
File: fwphp/glomodul/blog/msgmkd/altervista002a.txt
Role: Documentation
Content type: text/plain
Description: Documentation
Class: B12 PHP FW
Manage database records with a PDO CRUD interface
Author: By
Last change: Update of fwphp/glomodul/blog/msgmkd/altervista002a.txt
Date: 1 year ago
Size: 31,949 bytes
 

Contents

Class file image Download
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 \*/ }