3 Execute immediate example 1 / delete any table
--execute immediate with dynamic string
drop table emp1;
create table emp1 ( emp_id number );
drop table emp2;
create table emp2 ( emp_id number );
begin
insert into emp1 values (1);
insert into emp1 values (2);
insert into emp1 values (3);
insert into emp2 values (1);
insert into emp2 values (2);
insert into emp2 values (3);
commit;
end;
select * from emp1;
select * from emp2;
create or replace procedure delete_any_table
( p_table_name varchar2)
is
v_no_rec number;
begin
execute immediate 'delete from '||p_table_name;
v_no_rec:=sql%rowcount;
commit; --same rules for commit and rollback
dbms_output.put_line(v_no_rec ||' record(s) deleted form '||p_table_name );
end;
execute delete_any_table('emp1');
select * from emp1;
--try to give dummy table which not exists
--so the parsing will be on runtime :)
execute delete_any_table('dfd');
4 Execute immediate example 2 / DDL in PLSQL
=======
--DDL and DCL not allowed in PLSQL block
--we also use execute immediate
drop table emp3;
begin
create table emp3 ( emp_id number );
end;
begin
--no need for ; at end of the statement
execute immediate 'create table emp3 ( emp_id number )' ;
end;
select * from emp3;
-------------------------------------------------------------
create or replace procedure create_any_table
(p_table_name varchar2,p_details varchar2)
is
v_details varchar2(30000);
begin
v_details:='create table '||p_table_name||' ('||p_details||')' ;
dbms_output.put_line(v_details);
execute immediate v_details;
end;
execute create_any_table ('emp4','emp_id number, name varchar2(100)' );
select * from emp4;
--it could give you this ORA-01031: insufficient privileges
select * from USER_SYS_PRIVS
--the create table PRIVS should be direct, not using a role
--conn sys as sysdba, grant create table to hr
execute create_any_table ('emp4','emp_id number, name varchar2(100)' );
select * from emp4;
5 Execute immediate example 3 / USING Clause
===
-- EXECUTE IMMEDIATE with USING
delete from emp1;
select * from emp1;
--this proc can work with any table contain 1 coulmn and this column should be number
create or replace procedure add_rows
( p_table_name varchar2,p_value number )
is
begin
--EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values('||p_value||') ';
EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:1) ' using p_value;
end;
execute add_rows ('emp1',10);
select * from emp1;
------------------------------
delete emp1;
create or replace procedure add_rows
( p_table_name varchar2,p_value number )
is
val1 number:=20;
val2 number:=30;
begin
EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:1) ' using p_value;
EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:yy) ' using val1;
EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:uuu) ' using val2;
end;
execute add_rows ('emp1',10);
select * from emp1;
-----------------------------------------------
6 Execute immediate example 4 / INTO Clause
========
--dynamic sql with single row query
declare
v_ename varchar2(100);
begin
execute immediate 'select first_name from employees where employee_id=100'
into v_ename;
DBMS_OUTPUT.put_line(v_ename);
end;
declare
v_ename varchar2(100);
vno number:=200;
begin
execute immediate 'select first_name from employees where employee_id=:c'
into v_ename using vno; --note: the into should be first then the using
DBMS_OUTPUT.put_line(v_ename);
end;
7 Execute immediate example 5 / single row query
=====
create or replace function get_emp
(p_id number)
return employees%rowtype
is
emp_rec employees%rowtype;
begin
select * into emp_rec
from employees
where employee_id=p_id;
return emp_rec;
end;
--you can not use this function in select, cz i retrive %rowtype
select get_emp(100) from dual;
declare
emp_rec employees%rowtype;
begin
emp_rec:=get_emp(100);
dbms_output.put_line(emp_rec.employee_id||' '||emp_rec.first_name );
end;
--------------------------------------------------------------
--dynamic sql with single row query
create or replace function get_emp2
(p_id number)
return employees%rowtype
is
emp_rec employees%rowtype;
v_query varchar2(1000);
begin
v_query:='select * from employees where employee_id=:1';
execute immediate v_query into emp_rec using p_id;
return emp_rec;
end;
declare
emp_rec employees%rowtype;
begin
emp_rec:=get_emp2(105);
dbms_output.put_line(emp_rec.employee_id||' '||emp_rec.first_name );
end;
8 What is REF Cursor ?
--dynamic sql with multi row query
--first we need to know what is REF cursor
--1 let us review the normal cursor
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_empno, v_first_name;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close c_emp_dept30;
END;
-----------------------------------
-- the ref cursor can be opend many times with dif query
DECLARE
type c_emp_dept is ref cursor;
d_cursor c_emp_dept;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
OPEN d_cursor for select employee_id,first_name
from employees
where department_id=10;
loop
fetch d_cursor into v_empno, v_first_name;
exit when d_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close d_cursor;
OPEN d_cursor for select employee_id,first_name
from employees
where department_id=30;
loop
fetch d_cursor into v_empno, v_first_name;
exit when d_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close d_cursor;
END;
--------------------------------
9 Dynamic SQL with multi row query
--dynamic sql with multi row query
create or replace procedure emp_list ( p_dept_id number default null )
is
type c_emp_dept is ref cursor;
d_cursor c_emp_dept;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
v_sql varchar2(1000):='select employee_id, first_name from employees';
begin
if p_dept_id is null then
open d_cursor for v_sql;
else
v_sql:=v_sql||' where department_id=:id';
open d_cursor for v_sql using p_dept_id;
end if;
loop
fetch d_cursor into v_empno, v_first_name;
exit when d_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close d_cursor;
end;
--to get all the employees
execute emp_list;
--to get all the employees in specific dept
execute emp_list (30);
10 Execute immediate/ more examples
--we can also use dynamic sql to execute anonymous-block
declare
v_code varchar2(100):=
'begin
dbms_output.put_line(''welcome'');
end;
';
begin
execute immediate v_code;
end;
-----------------------
-- we can do this statement to compile a procedure
alter procedure ADD_ROWS compile;
--we can do this statement to compile a function
alter function GET_SAL compile;
-- we can do this statement to compile a package specification
alter package AREA compile specification;
-- we can do this statement to compile a package body
alter package AREA compile body;
---------------------------------------------
create or replace procedure compile_any_plsql
(p_name varchar2, p_type varchar2, p_option varchar2 default null)
is
v_comp_code varchar2(1000):=
'alter '||p_type||' '||p_name||' compile '||p_option;
begin
execute immediate v_comp_code;
end;
execute compile_any_plsql ('AREA','package','specification');
execute compile_any_plsql ('AREA','package','body');
execute compile_any_plsql ('ADD_ROWS','procedure');
execute compile_any_plsql ('GET_SAL','function');
11 DBMS_SQL Package VS Execute immediate
drop table emp1;
create table emp1 ( emp_id number );
drop table emp2;
create table emp2 ( emp_id number );
begin
insert into emp1 values (1);
insert into emp1 values (2);
insert into emp1 values (3);
insert into emp2 values (1);
insert into emp2 values (2);
insert into emp2 values (3);
commit;
end;
select * from emp1;
select * from emp2;
create or replace procedure delete_any_table
( p_table_name varchar2)
is
v_no_rec number;
begin
execute immediate 'delete from '||p_table_name;
v_no_rec:=sql%rowcount;
commit; --same rules for commit and rollback
dbms_output.put_line(v_no_rec ||' record(s) deleted form '||p_table_name );
end;
execute delete_any_table('emp1');
select * from emp1;
----------------------
--we will do same but using DBMS_SQL
create or replace procedure delete_any_table2
( p_table_name varchar2)
is
v_no_rec number;
v_cur_id number;
begin
v_cur_id:=dbms_sql.open_cursor;
dbms_sql.parse(v_cur_id,'delete from '||p_table_name ,dbms_sql.native);
v_no_rec:=dbms_sql.execute(v_cur_id);
dbms_output.put_line(v_no_rec ||' record(s) deleted form '||p_table_name );
commit;
end;
execute delete_any_table2('emp1');
select * from emp1;
------------------------------------------------------------------------------------------------
delete emp1;
create or replace procedure add_rows
( p_table_name varchar2,p_value number )
is
begin
EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:1) ' using p_value;
end;
EXECUTE add_rows('emp1',10);
SELECT * FROM emp1;
---------------
--WE WILL DO SAME BUT DBMS_SQL
create or replace procedure add_rows2
( p_table_name varchar2,p_value number )
is
v_no_rec number;
v_cur_id number;
V_INSERT VARCHAR2(1000):='INSERT INTO '||p_table_name||' VALUES (:ID)';
begin
v_cur_id:=dbms_sql.open_cursor;
dbms_sql.parse(v_cur_id,V_INSERT ,dbms_sql.native);
dbms_sql.BIND_VARIABLE (v_cur_id,':ID',p_value);
v_no_rec:=dbms_sql.execute(v_cur_id);
dbms_output.put_line(v_no_rec ||' record(s) INSERTED TO '||p_table_name );
commit;
end;
EXECUTE add_rows2('emp1',90);
SELECT * FROM emp1;
No comments:
Post a Comment