create or replace package first_package
as
procedure greetings1(p_name IN varchar2);
function hello_function(p_name IN varchar2)return varchar2;
end;
create or replace package body first_package as
PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
function hello_function(p_name IN varchar2)
return varchar2
as
v_result varchar2(100);
begin
v_result:=’Hello ‘||p_name;
return v_result;
end hello_function;
end;
execute FIRST_PACKAGE.GREETINGS1(‘chandrasekar’);
select hr.first_package.hello_function(‘Chandra’) from dual;
———————————————————————-
create or replace package first_package
as
procedure greetings1(p_name IN varchar2);
function emp_fun(v_emp_id in number)return number;
end;
create or replace package body first_package as
PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
function emp_fun(v_emp_id in number)
return number
as
v_salary number(8);
v_new_sal number(10);
begin
select salary into v_salary from employees where employee_id=v_emp_id;
if(v_salary >10000) then
v_new_sal:= v_salary +(v_salary*0.1);
else
v_new_sal:= v_salary +(v_salary*0.2);
end if;
return v_new_sal;
end emp_fun;
end;
execute FIRST_PACKAGE.GREETINGS1(‘chandrasekar’);
select employee_id,salary,FIRST_PACKAGE.emp_fun(employee_id) from employees;
———————————————————————
CREATE OR REPLACE PACKAGE PKG_OVERLOAD_ADD_NUMBERS
IS
PROCEDURE ADD_NUM(A NUMBER, B NUMBER);
PROCEDURE ADD_NUM(A NUMBER, B NUMBER, C NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY PKG_OVERLOAD_ADD_NUMBERS
IS
PROCEDURE ADD_NUM(A NUMBER, B NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Sum of two numbers are: ‘||to_char(A+B));
END;
PROCEDURE ADD_NUM(A NUMBER, B NUMBER, C NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Sum of three numbers are: ‘||to_char(A+B+C));
END;
END;
exec PKG_OVERLOAD_ADD_NUMBERS.add_num(5,6,7);
— Forward reference
DECLARE
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
———————————————————
DECLARE
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
———————————————
DECLARE
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc2;
end;
———————————————-
DECLARE
procedure proc1 is
begin
proc2;
dbms_output.put_line(‘This is procedure 1’);
end;
procedure proc2 is
begin
proc1;
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
— error Procedure2 is not declared in this scope
———————————————-
set serveroutput on;
DECLARE
–procedure proc2;
procedure proc1 is
begin
dbms_output.put_line(‘This is procedure 1’);
–proc2;
end;
procedure proc2 is
begin
dbms_output.put_line(‘This is procedure 2’);
end;
begin
proc1;
end;
example 2:
Create or replace package body test_package_fdec as
procedure int_proc; — forward declaration
procedure int_proc2
is
begin
dbms_output.put_line(‘this is int_proc2’);
int_proc;
end int_proc2;
procedure int_proc
is
begin
dbms_output.put_line(‘this is int_proc2′);
int_proc2;
end int_proc;
end test_package_fdec;
/
=====================================================================
create or replace package ABC as
x number:=10;
function1
procedure1
procedure2
end package;
global package variable
public fuctions
—————————————————————-
create package body ABC as
Y number; — Private variable
function function1()
.
.
end;
Procedure procedure1
a number; — local variable
begin
.
.
end;
procedure P3 — Private procedure
.
.
end p3;
Procedure P2
p3;
end p2
end ABC;
==========================================================================
===========================================
— PRAGMA Autonomous transaction in Oracle:
===========================================
-This feature is available from Oracle 8i version
-It is an independent transaction and initiated by another transaction
-The main trasaction is temporarily suspended and Autonomous transaction -Invoked as child transaction
– Autonomous transaction must be committed or rollbacked
– It can be nested
The child transaction can run independently of its parent
The child transaction can commit/Rollback and parent txn resumes
The parent transaction can continue without affecting child transaction
create table customer (cust_id number(8), cust_name varchar2(30));
insert into customer values (1000,’Arun’);
insert into customer values (1001,’Sandeep’);
insert into customer values (1002,’John’);
insert into customer values (1003,’Rakesh’);
Commit;
select * from customer;
============================================================================
create table customer (cust_id number(8), cust_name varchar2(30));
insert into customer values (1000,’Arun’);
savepoint a;
insert into customer values (1001,’Sandeep’);
savepoint b;
insert into customer values (1002,’John’);
savepoint c;
insert into customer values (1003,’Rakesh’);
rollback to b;
select * from customer;
=====================================================
create table customer (cust_id number);
—Example 1:
insert into customer values(1000);
insert into customer values(1001);
select * from customer;
begin
for i in 1003..1010 loop
insert into customer values(i);
end loop;
rollback;
end;
—Example 2:
insert into customer values(1000);
insert into customer values(1001);
select * from customer;
declare
pragma autonomous_transaction;
begin
for i in 1003..1010 loop
insert into customer values(i);
end loop;
commit;
end;
rollback;
select * from customer;
======================================================================================
create table customer (cust_id number);
create or replace procedure proc_auto
is
PRAGMA autonomous_transaction;
begin
insert into customer values(100000);
commit;
end;
exec proc_auto;
select * from customer;
———————————————–
create table customer (cust_id number);
create or replace procedure proc_auto
is
PRAGMA autonomous_transaction;
begin
insert into customer values(100000);
commit;
end;
delete from customer;
exec proc_auto;
select * from customer;
declare
begin
insert into customer values(100001);
proc_auto();
commit;
end;
==========================================================
create table test (no1 number(3), no2 number(3));
insert into test values (1,2);
declare
pragma autonomous_transaction;
begin
insert into test values (3,4);
commit;
end;
insert into test values (5,6);
rollback;
======================================================
