delete from DEPARTMENTS
declare
e_fk_err exception;
pragma EXCEPTION_INIT (e_fk_err, -02292);
begin
delete from DEPARTMENTS;
exception
when e_fk_err then
RAISE_APPLICATION_ERROR (-20001, 'error');
end;
declare
e_fk_err exception;
pragma EXCEPTION_INIT (e_fk_err, -02292);
begin
delete from DEPARTMENTS;
exception
when e_fk_err then
RAISE_APPLICATION_ERROR (-20001, 'error',true);
end;
-----------------------------------------------------------------------------------------------------------------------
drop table hr_table;
create table hr_table
( id number,
name varchar2(100)
);
---------
create or replace procedure add_hr_table
( p_id number, p_name varchar2 )
is
begin
insert into hr_table values (p_id,p_name);
end;
--now conn as sys as sysdba and create user called jake (orclpdb)
--alter session set container=orclpdb
--create user ford identified by ford;
-- grant create session, connect to ford;
--user hr will give jake execute to add_hr_table
grant execute on add_hr_table to ford;
--now connect as ford
-- execute hr.add_hr_table(1,'khaled');
--he can execute even if he doesnt access to table hr_table
select * from hr_table;
-------------------------------------------------------------
--now user hr will create the proc again but adding authid current_user
create or replace procedure add_hr_table
( p_id number, p_name varchar2 ) authid current_user
is
begin
insert into hr.hr_table values (p_id,p_name);
end;
--now connect as ford
-- execute hr.add_hr_table(1,'khaled');
--now he can not insert, he should priv insert to hr_table
-----------------------------------------------------------------------------------------------------------------
--case one without using PRAGMA AUTONOMOUS_TRANSACTION
drop table t;
CREATE TABLE t (test_value VARCHAR2(25));
CREATE OR REPLACE PROCEDURE child_block IS
BEGIN
INSERT INTO t(test_value)
VALUES ('Child block insert');
COMMIT;
END child_block;
CREATE OR REPLACE PROCEDURE parent_block IS
BEGIN
INSERT INTO t(test_value)
VALUES('Parent block insert');
child_block;
ROLLBACK;
END parent_block;
/
-- run the parent procedure
execute parent_block;
-- check the results
SELECT * FROM t;
------------------------------------------------------------
--case 2 using PRAGMA AUTONOMOUS_TRANSACTION
--this mean the PROCEDURE child_block is independent
CREATE OR REPLACE PROCEDURE child_block IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO t(test_value)
VALUES('Child block insert');
COMMIT;
END child_block;
CREATE OR REPLACE PROCEDURE parent_block IS
BEGIN
INSERT INTO t(test_value)
VALUES('Parent block insert');
child_block;
ROLLBACK;
END parent_block;
-- empty the test table
TRUNCATE TABLE t;
-- run the parent procedure
exec parent_block;
-- check the results
SELECT * FROM t;
------------------------------------------------------------------------------------------
--1 IN parameter always passed by reference
create or replace procedure pass_by_ref
( p_id in number ) --p_id called formal parameter
is
begin
--p_id :=555; --this is not valid
dbms_output.put_line(p_id);
end;
execute pass_by_ref (10);
--10 is called actual parameter
/* Pass By Reference:no temporary buffer is needed and
no copy forward and copy back operations happen
*/
--------------------------------------------------------------------------------
/*2
out/in out parameters can be passed
1-Pass By Value ( default)
2-Pass By Reference using no copy
/*
Pass By Value : The default action is to create a temporary buffer (formal parameter),
copy the data from the parameter variable (actual parameter) to that buffer and work
on the temporary buffer during the lifetime of the procedure.
On successful completion of the procedure,
the contents of the temporary buffer are copied back into the parameter variable.
In the event of an exception occurring, the copy back operation does not happen.
*/
create or replace procedure pass_by_value1
( P_id IN out number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;
declare
v number:=20;---try first 5 then 20
begin
pass_by_value1(V);
dbms_output.put_line('NO ISSUE '||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('EXCEPTION '||v);
end;
---------------------------------------------
create or replace procedure pass_by_ref
( P_id IN out nocopy number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;
declare
v number:=20;
begin
pass_by_ref(V);
dbms_output.put_line('NO ISSUE '||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('EXCEPTION '||v);
end;
---------------------------------------------------------------
--nocopy will have no effects
--case 1
create or replace procedure pass_by_value3
( P_id IN out nocopy number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;
declare
v number(10):=20; --if the actual has size(FOR NUMBER ONLY ),nocopy not working
begin
pass_by_value3(V);
dbms_output.put_line('NO ISSUE '||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('EXCEPTION '||v);
end;
--------------------------------------------------------------
--case 2
create or replace procedure pass_by_value4
( P_id IN out nocopy number)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;
declare
v number NOT NULL :=20; --if the actual NOT NULL,nocopy not working
begin
pass_by_value4(V);
dbms_output.put_line('NO ISSUE '||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('EXCEPTION '||v);
end;
------------------------------------------
----case 3
create or replace procedure pass_by_value5
( P_id IN out nocopy VARCHAR2)
is
begin
P_id:=P_id*10;
if P_id>100 then
raise VALUE_ERROR;
end if;
end;
declare
v number :=20; --if the actual NEED IMPLICIT CONV ,nocopy not working
begin
pass_by_value5(V);
dbms_output.put_line('NO ISSUE '||v);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('EXCEPTION '||v);
end;
------------------------------------------------
create or replace package nocopy_test
is
type number_t is table of varchar2(32767) index by binary_integer;
procedure pass_by_vale(nums in out number_t);
procedure pass_by_refernce(nums in out nocopy number_t);
procedure init;
end;
------------
create or replace package body nocopy_test
is
l_numbers number_t;
c_array_size number:=1000000;
c_it number:=20;
procedure pass_by_vale(nums in out number_t)
is
indx pls_integer;
begin
indx:=nums.count;
end;
procedure pass_by_refernce(nums in out nocopy number_t)
is
indx pls_integer;
begin
indx:=nums.count;
end;
procedure init
is
begin
l_numbers.delete;
for i in 1..c_array_size
loop
l_numbers(i):='s'||i;
end loop;
dbms_output.put_line('start '||to_char(sysdate,'hh:mi:ss') );
for i in 1..1000
loop
pass_by_vale(l_numbers);
end loop;
dbms_output.put_line('end '||to_char(sysdate,'hh:mi:ss') );
dbms_output.put_line('start '||to_char(sysdate,'hh:mi:ss'));
for i in 1..1000
loop
pass_by_refernce(l_numbers);
end loop;
dbms_output.put_line('end '||to_char(sysdate,'hh:mi:ss'));
end;
end;
-----------------------------
begin
nocopy_test.init;
end;
---------------------------------------------------------------------------------------------------------
--1 Cache and parallel are only in oracle enterprise editions
--also the DBA make changes in these values
--parameter shared_pool_size
--parameter result_cache_max_size
--parameter result_cache_mode
create or replace function get_sum_sal_dept
( dept_id number )
return number result_cache
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;
end;
select get_sum_sal_dept(10) from dual;
select get_sum_sal_dept(20) from dual;
select get_sum_sal_dept(30) from dual;
--now when you do :
select get_sum_sal_dept(10) from dual;
--it should be faster because the resulte is stored in cashe,
--relies_on (employees) is optional
--This option has become obsolete since version 11G release 2.
--The database figures out where the function relies on.
--You can still include the relies_on clause,
--but it will be for documentation purposes only.
create or replace function get_sum_sal_dept
( dept_id number )
return number result_cache relies_on (employees)
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;
end;
-------------------------------------------------------------------------------------------------------------------------
select * from employees
where employee_id in (100,101,102);
create or replace procedure update_sal_withoutbulk
( p_amount number )
is
type emp_table_type is table of number index by binary_integer;
emp_table emp_table_type;
begin
emp_table(1):=100;
emp_table(2):=101;
emp_table(3):=102;
for i in emp_table.first..emp_table.last
loop
update employees
set salary=salary+p_amount
where employee_id =emp_table(i);
end loop;
commit;
end;
execute update_sal_withoutbulk(10);
select * from employees
where employee_id in (100,101,102);
-------------------------------------------------------------
--here with bulk using forall
--no need for the loop
create or replace procedure update_sal_withbulk
( p_amount number )
is
type emp_table_type is table of number index by binary_integer;
emp_table emp_table_type;
begin
emp_table(1):=100;
emp_table(2):=101;
emp_table(3):=102;
forall i in emp_table.first..emp_table.last
update employees
set salary=salary+p_amount
where employee_id =emp_table(i);
commit;
end;
execute update_sal_withbulk(10);
select * from employees
where employee_id in (100,101,102);
-------------------------------------------------------------------
desc employees;
drop table ename;
create table ename
as select distinct first_name from employees;
select first_name
from ename;
declare
type ename_t is table of varchar2(100);
ename_table ename_t:=ename_t();
c number:=0;
begin
for i in (select * from ename )
loop
c:=c+1;
ename_table.extend;
ename_table(c):=i.first_name;
end loop;
for i in ename_table.first.. ename_table.last
loop
---begin
update ename
set first_name=first_name||' to be added:)' --14 char
where first_name=ename_table(i);
--exception
---end
end loop;
exception
when others
then null;
end;
select * from ename
----------------------------
drop table ename;
create table ename
as select distinct first_name from employees;
select first_name
from ename;
declare
type ename_t is table of varchar2(100);
ename_table ename_t:=ename_t();
c number:=0;
begin
for i in (select * from ename )
loop
c:=c+1;
ename_table.extend;
ename_table(c):=i.first_name;
end loop;
forall i in ename_table.first.. ename_table.last save exceptions
update ename
set first_name=first_name||' to be added:)' --14 char
where first_name=ename_table(i);
exception
when others
then null;
end;
select * from ename
-----------------------------------------------------------------------------------------------------------------------
begin
dbms_output.put_line(sqlerrm (-12899));
end;
-------------------------------------------------------------
--------------------------------------------------------------
drop table ename;
create table ename
as select distinct first_name from employees;
select first_name
from ename;
declare
type ename_t is table of varchar2(100);
ename_table ename_t:=ename_t();
c number:=0;
errors number;
begin
for i in (select * from ename )
loop
c:=c+1;
ename_table.extend;
ename_table(c):=i.first_name;
end loop;
forall i in ename_table.first.. ename_table.last save exceptions
update ename
set first_name=first_name||' to be added:)' --14 char
where first_name=ename_table(i);
exception
when others then
errors := sql%bulk_exceptions.count;
dbms_output.put_line ('The total number of errors occured are '|| errors);
for j in 1..errors loop
dbms_output.put_line ('The error iteration is '
|| sql%bulk_exceptions(j).error_index
|| ' and the error code is '
|| sql%bulk_exceptions(j).error_code
|| ' and the error message is '
|| sqlerrm ( -sql%bulk_exceptions(j).error_code)
);
end loop;
end;
select * from ename
-----------------------------
drop table ename;
create table ename
as select distinct first_name from employees;
select first_name
from ename;
declare
type ename_t is table of varchar2(100);
ename_table ename_t:=ename_t();
c number:=0;
errors number;
begin
/*
for i in (select * from ename )
loop
c:=c+1;
ename_table.extend;
ename_table(c):=i.first_name;
end loop;
*/
select first_name bulk collect into ename_table
from ename;
forall i in ename_table.first.. ename_table.last save exceptions
update ename
set first_name=first_name||' to be added:)' --14 char
where first_name=ename_table(i);
exception
when others then
errors := sql%bulk_exceptions.count;
dbms_output.put_line ('The total number of errors occured are '|| errors);
for j in 1..errors loop
dbms_output.put_line ('The error iteration is '
|| sql%bulk_exceptions(j).error_index
|| ' and the error code is '
|| sql%bulk_exceptions(j).error_code
|| ' and the error message is '
|| sqlerrm ( -sql%bulk_exceptions(j).error_code)
);
end loop;
end;
select * from ename
-----------------------------------------------------------------------------------------------------
drop table ename2;
create table ename2
as select employee_id, first_name
from employees;
select * from ename2;
declare
type emp_t is table of varchar2(100) index by binary_integer;
emp_table emp_t;
c number:=0;
begin
for i in (select employee_id, first_name from ename2)
loop
c:=c+1;
emp_table(c):=i.first_name;
end loop;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line(emp_table(i));
end loop;
end;
----------------------------
declare
type emp_t is table of varchar2(100) index by binary_integer;
emp_table emp_t;
begin
select first_name bulk collect into emp_table
from ename2;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line(emp_table(i));
end loop;
end;
---------------------------------------------------------------------------------------------------------------------------
--using bulk colelct in cursor
--------without bulk-------------
declare
type emp_t is table of employees%rowtype;
emp_table emp_t;
cursor emp_c is select * from employees;
c number:=0;
begin
emp_table:=emp_t();
open emp_c;
loop
emp_table.extend;
c:=c+1;
fetch emp_c into emp_table(c);
exit when emp_c%notfound;
dbms_output.put_line(emp_table(c).first_name);
end loop;
close emp_c;
end;
--------------------------------
-----------with bulk collect-----------
declare
type emp_t is table of employees%rowtype;
emp_table emp_t;
cursor emp_c is select * from employees;
begin
open emp_c;
fetch emp_c bulk collect into emp_table;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line(emp_table(i).first_name);
end loop;
end;
------------------------------------
---------with bulk and limit------------------
declare
type emp_t is table of employees%rowtype;
emp_table emp_t;
cursor emp_c is select * from employees;
begin
open emp_c;
fetch emp_c bulk collect into emp_table limit 5;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line(emp_table(i).first_name);
end loop;
end;
---------------------------------------------------------------------------------------------------------------
--using returning in bulk collect
--but first returning it can be used lik this
create or replace procedure update_sal_x (emp_id number,p_amount number)
is
v_new_sal number;
begin
update employees
set salary=salary +p_amount
where employee_id=emp_id
returning salary into v_new_sal;
dbms_output.put_line('the new sal now is '||v_new_sal);
end;
select * from employees
where employee_id=200;
exec update_sal_x(200,1);
--using returning in bulk collect
drop table ename_test;
create table ename_test
as select employee_id,salary
from employees;
select * from ename_test
order by 1;
declare
type emp_t is table of ename_test%rowtype;
emp_table emp_t:=emp_t();
emp_new_sal emp_t:=emp_t();
begin
select * bulk collect into emp_table from ename_test ;
forall i in emp_table.first..emp_table.last
update ename_test
set salary=salary/2
where employee_id=emp_table(i).employee_id
returning employee_id,salary bulk collect into emp_new_sal;
for i in emp_new_sal.first..emp_new_sal.last
loop
dbms_output.put_line(emp_new_sal(i).employee_id||' '||emp_new_sal(i).salary);
end loop;
end;
-------------------------------------------------------------------------------------------------
--INDICES OF
select * from employees
where employee_id in (100,101,102,103);
declare
type emp_table_type is table of number index by binary_integer;
emp_table emp_table_type;
begin
emp_table(1):=100;
emp_table(2):=101;
emp_table(3):=102;
emp_table(100):=103;
dbms_output.put_line(emp_table.first);
dbms_output.put_line(emp_table.last);
forall i in emp_table.first..emp_table.last save exceptions
update employees
set salary=salary+5
where employee_id =emp_table(i);
commit;
end;
------------------------------------
declare
type emp_table_type is table of number index by binary_integer;
emp_table emp_table_type;
begin
emp_table(1):=100;
emp_table(2):=101;
emp_table(3):=102;
emp_table(100):=103;
dbms_output.put_line(emp_table.first);
dbms_output.put_line(emp_table.last);
forall i in INDICES OF emp_table
update employees
set salary=salary+5
where employee_id =emp_table(i);
commit;
end;
---------
select * from employees
where employee_id in (100,101,102,103);
No comments:
Post a Comment