Revised December 2021, published May 2017
Main menu – download 27 May 2017 I uploaded 1_pl_sq.zip Oracle 11g PL/SQL Tutorial
Oracle 11g PL/SQL Tutorial about testing developing DB procedures
- Three loops and three branches
- Function, procedure, package, array object
- Number, string, date, boolean
- DDL
- CRUD
- Ref Cursor pointer to cursor (ee to record set object variable) – December 2021
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
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
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;
/
4. DDL
/* [email protected] 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; / */
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 := '[email protected]';
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
6. Ref Cursor pointer to cursor (ee to record set object variable)
CREATE OR REPLACE PACKAGE PCK_EMPLOYEES IS
--Returned cursor variable of type SYS_REFCURSOR :
-- 1. can be opened for any query 2. it is like view with parameters id, rc...
PROCEDURE getrc_itms_by_mast_id(id IN NUMBER, rc IN OUT SYS_REFCURSOR) ;
--also works FUNCTION getrc_itms_by_mast_id (p_sifra IN NUMBER) RETURN SYS_REFCURSOR ;
PROCEDURE tbl_displ (p_rows IN SYS_REFCURSOR);
END PCK_EMPLOYEES ;
/
sho err
CREATE OR REPLACE PACKAGE BODY PCK_EMPLOYEES IS
PROCEDURE getrc_itms_by_mast_id (id IN NUMBER, rc IN OUT SYS_REFCURSOR) IS
BEGIN
OPEN rc FOR
select * from EMPLOYEES emp where emp.DEPARTMENT_ID = id
order by emp.DEPARTMENT_ID, emp.EMPLOYEE_ID
;
END getrc_itms_by_mast_id;
PROCEDURE tbl_displ(p_rows IN SYS_REFCURSOR) IS
v_row EMPLOYEES%ROWTYPE ;
BEGIN
dbms_output.put_line('-- ********************** O U T P U T :' );
-- process each row
LOOP
FETCH p_rows INTO v_row ;
EXIT WHEN p_rows%notfound;
dbms_output.put_line('id_dep=' || v_row.DEPARTMENT_ID ||', id_emp=' || v_row.EMPLOYEE_ID);
END LOOP;
CLOSE p_rows;
EXCEPTION
WHEN OTHERS THEN
IF p_rows%ISOPEN THEN CLOSE p_rows; END IF;
RAISE;
END tbl_displ;
END PCK_EMPLOYEES;
/
sho err
--CALL CLS "PCK_EMPLOYEES", METHODS getrc_itms_by_mast_id, tbl_displ
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
DECLARE
itms_by_mast_id_rc SYS_REFCURSOR ;
BEGIN
PCK_EMPLOYEES.getrc_itms_by_mast_id(30, itms_by_mast_id_rc); --1281, 1282
--itms_by_mast_id_rc := PCK_EMPLOYEES.getrc_itms_by_mast_id (1282); --1281, 1282
PCK_EMPLOYEES.tbl_displ(itms_by_mast_id_rc);
END;
/
-- ********************** O U T P U T :
id_dep=30, id_emp=114
id_dep=30, id_emp=115
id_dep=30, id_emp=116
id_dep=30, id_emp=117
id_dep=30, id_emp=118
id_dep=30, id_emp=119
--NATIVE DYNAMIC SQL – NDS : EXECUTE IMMEDIATE statement example:
CREATE FUNCTION F_row_cnt(tablica_p VARCHAR2) RETURN NUMBER IS
naredba VARCHAR2 (1000);
broj_redaka NUMBER;
BEGIN
naredba := 'SELECT COUNT (*)' || ' FROM ' || tablica_p;
EXECUTE IMMEDIATE naredba INTO broj_redaka;
RETURN broj_redaka;
END;
/
sho err
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
exec dbms_output.put_line('-- ********************** O U T P U T :' );
exec dbms_output.put_line('row_cnt emps=' || F_row_cnt('employees'));
-- ********************** O U T P U T :
row_cnt emps=107
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
-- create dynamic cursor varijable
DECLARE
rcdyn SYS_REFCURSOR;
stmt_str VARCHAR2(200);
LAST_NAME VARCHAR2(100);
SALARY NUMBER;
BEGIN
stmt_str := 'SELECT LAST_NAME, SALARY' || ' FROM employees' || ' WHERE JOB_ID = :1' ;
-- create dynamic cursor varijable :
dbms_output.put_line('-- ********************** O U T P U T :' );
OPEN rcdyn FOR stmt_str USING 'SA_MAN';
LOOP
FETCH rcdyn INTO LAST_NAME, SALARY;
EXIT WHEN rcdyn%NOTFOUND;
--procesiranje podataka
dbms_output.put_line('LAST_NAME=' || LAST_NAME ||', SALARY=' || SALARY);
END LOOP;
CLOSE rcdyn;
END;
/
-- ********************** O U T P U T :
LAST_NAME=Russell, SALARY=14000
LAST_NAME=Partners, SALARY=13500
LAST_NAME=Errazuriz, SALARY=12000
LAST_NAME=Cambrault, SALARY=11000
LAST_NAME=Zlotkey, SALARY=10500