begin
dbms_output.put_line('welcome Amit to PL/SQL');
dbms_output.put_line(sysdate);
dbms_output.put_line(1234);
dbms_output.put_line(1+2+3+4);
dbms_output.put_line(user);
dbms_output.put_line(systimestamp);
end;
show serveroutput;
set serveroutput on;
----------------------------------------------
declare
v1 number;
v2 varchar2(10);
begin
v1:= 25;
v2:= 'AMIT';
dbms_output.put_line(v1);
dbms_output.put_line(v2);
end;
/
--------------------------------Addition
declare
a number;
b number;
c number;
begin
a := 10;
b:= 20;
c := a+b;
dbms_output.put_line(c);
end;
/
---------------------------------
---------------------------------
declare
a number default 10;
b number default 20;
c number :=30;
d number :=40;
begin
dbms_output.put_line(a+b);
dbms_output.put_line(c+d);
end;
--=============================
declare
c number :=30;
d number :=40;
e constant number :=500; --'e' is constant and it's value can't be changed
begin
c:= 110; ---previous value is overridden
d:=190;
dbms_output.put_line(c+d+e);
end;
----------------------------------------------------------------------
--DECLARATION- Cursor,Exception,Type,Local Subprogram,Pragma
----------------------------------------------------------------------
Declare
--
CURSOR c1 is select * from dept; --Cursor declaration
--
e1 Exception; -- this is exception , user defined
--
PRAGMA exception_init(e1,-2001); -- Pragma ie. procedure call
--
Type t1 is table of number; ----TYPE (Collections)
Type t2 is table of varchar2(100);
Type t2 is table of emp%ROWTYPE;
--
Produre P1 is ---- Local sub program(must be the last on in declaration)
Begin
dbms_output.put_line(P1);
END;
---------------------------
---- Exception Predefined
---------------------------
declare
v1 number;
begin
v1:= 'character to number assignment'; -- we can't assign char to number
dbms_output.put_line('v1 : '||v1);
exception
when value_error then
dbms_output.put_line('Eception - Value error');
end;
------------------------
---- Exception Default
------------------------
declare
v1 number;
begin
v1:= 'character to number assignment'; -- we can't assign char to number
dbms_output.put_line('v1 : '||v1);
exception
when others then
dbms_output.put_line('Eception - others');
end;
------------------------
---- Local Variables /Gloabal variable
--Local var. are declared in pl blocks and globle variable comes from
--outside applications (java,dotNet application etc.)
------------------------
var v1 number;
var v2 date;
var; ---to see all the globle variables
begin
:x := 25; --globle var starts with colon (:)
end;
---------------------------------------------
--SQL in plsql
---------------------------------------------
-- DML -- select,Insert,Update,Delete,Merge
-- TCL -- Commit,Rollback,Savepoint,Set Transaction
--
desc RES_BOOK_LIST;
--
declare
v_id varchar2(10);
v_name varchar2(30);
v_author varchar2(30);
begin
select book_id,book_name,author
into v_id ,v_name,v_author
from RES_BOOK_LIST
where book_id ='109';
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
end;
/
set serveroutput on ;
--------------------------
desc RES_BOOK_LIST;
--
begin
insert into RES_BOOK_LIST(book_id,book_name,author)
values('9876','My Time','Manjhi');
end;
/
-------------------------------------------------
-------------------------------------------------
declare
v_id VARCHAR2(10);
v_name VARCHAR2(30);
v_author VARCHAR2(30);
begin
select BOOK_ID,BOOK_NAME,AUTHOR
into v_id,v_name,v_author
from RES_BOOK_LIST
where BOOK_ID= '100';
dbms_output.put_line('Original Values:-');
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
update RES_BOOK_LIST
set BOOK_NAME='Wings of Fire' where BOOK_ID= '100';
select BOOK_ID,BOOK_NAME,AUTHOR
into v_id,v_name,v_author
from RES_BOOK_LIST
where BOOK_ID= '100';
dbms_output.put_line('After Update:-');
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
rollback;
select BOOK_ID,BOOK_NAME,AUTHOR
into v_id,v_name,v_author
from RES_BOOK_LIST
where BOOK_ID= '100';
dbms_output.put_line('After Rollback:-');
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
commit;
end;
-----------------------------------------------
--Rollback
declare
savepoint s1;
rollback to s1;
end;
------------------------------------------------
---SET Transaction
declare
begin
set transaction read only; --must be first statement
end;
commit;
--
declare
begin
set transaction read write; --must be first statement
end;
update RES_BOOK_LIST
set BOOK_NAME='Wings of Fire' where BOOK_ID= '100';
rollback;
-------------------------------------------------------------------
--PL/SQL Attributes
-------------------------------------------------------------------
-- In above pl blocks we used fixed size and fixed data types which is not
-- good practice as any change in db causes error , Hence use Attributes.
-- Two Types
--1)%type
--2)%row type
--------------------Examples..
--
declare
--v_bname varchar2(14) ---- No attributes i.e fixed type
v_bname master_book_list.BOOK_NAME %TYPE; --- With attributes
Begin
select book_name
into v_bname
from master_book_list
where BOOK_ID = '100';
dbms_output.put_line('Book Name:'||v_bname);
end;
/
set serveroutput on;
---
declare
v_id RES_BOOK_LIST.book_id %type;
v_name RES_BOOK_LIST.book_name %type;
v_author RES_BOOK_LIST.author %type;
begin
select book_id,book_name,author
into v_id ,v_name,v_author
from RES_BOOK_LIST
where book_id ='109';
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
end;
/
------------------------
-- % ROW TYPE
------------------------
-- In % ROW Type we don't specify column name , we specify only table and on
--runtime the corrosponding datatype and size is taken dynamically.
--Example
declare
v_emp EMPLOYEES %rowtype;
begin
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL
into v_emp.EMPLOYEE_ID ,v_emp.FIRST_NAME,v_emp.LAST_NAME,v_emp.EMAIL
from EMPLOYEES
where EMPLOYEE_ID='100';
dbms_output.put_line('E_ID:'||v_emp.EMPLOYEE_ID||' :: '||'F_Name:'||v_emp.FIRST_NAME||' :: '||'L_Name:'||v_emp.LAST_NAME||' :: '||'Email:'||v_emp.EMAIL);
end;
/
--------------------------------------------------------------------------
--Named Block
-------------
--<<ABC>> this is lebel
--this level can be used for complete block starting with Declare or Begin
--can be used any where in Begin or Exception part
--cannot be used in Declaration part
----we can call globle variables from local/sub blocks.
--------------------------------------------------------------------------
<<ABC>>
Declare
v1 number;
v2 number;
Begin
v1:= 10;
v2:= 11;
dbms_output.put_line('Globle v1: '||v1);
dbms_output.put_line('Globle v2: '||v2);
<<XYZ>>
Declare
V3 number;
v4 number;
Begin
v3:= 20;
v4:= 21;
dbms_output.put_line('Local V3 '||v3);
dbms_output.put_line('Local V4 '||v4);
dbms_output.put_line('Globle var from local area v1: '||v1);
dbms_output.put_line('Globle from local area v2: '||v2);
-----
dbms_output.put_line('Local XYZ: '||XYZ.v4);
dbms_output.put_line('Globle ABC: '||ABC.v1);
end;
end;
/
--------------------------------------------------------------------------------
--IDENTIFIERS
---------------
--Identifiers are names of Procedures,Functions,Variables,Constants,Cursors,Exceptions etc.
--Max Length = 30 characters
--
---------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--Control Structures
--------------------
--if/if-else/nested If/
--Loop/While/For
--Goto/Null
---------------------------------------------------------------------------------
--IF Else
---------
Declare
v1 number := 1;
v2 number;
v3 number;
begin
v2:= 10;
v3:= 20;
if v1>=1 then
dbms_output.put_line(v2+v3);
end if;
exception
when others then
dbms_output.put_line('Eception - others');
end;
/
-----------------------------
declare
v1 varchar(10) := 'AMIT';
v2 varchar(10) := 'MANJHI';
begin
if v1 = 'AMIT' then
if v2 ='MANJHI' then
dbms_output.put_line('Eligible');
end if;
end if;
end;
/
------------------------------------------------
--Loop
--..
--End Loop;
------------------------------------------------
declare
v1 number := 1;
begin
loop
dbms_output.put_line(v1);
exit when v1>=10;
v1 := v1 + 1;
end loop;
end;
/
------------------------------------------------
--For Loop
-----------
-- for i in 1 ..10 Loop
-- End loop;
-- for i in reverse 1 .. 10 loop
-- End loop;
-------------------------------------------------
declare
--v1 number := 10;
begin
for i in 1 .. 10 loop
dbms_output.put_line(i);
--i := i+2; ----------------------> Not allowed to assign to loop variable
end loop;
end;
/
----------------------
declare
v1 number := 10;
begin
for i in reverse 1 .. v1 loop
dbms_output.put_line(i);
end loop;
end;
/
-------------------------------------------------------------------
-- While Loop
-------------------------------------------------------------------
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/
----------------------------
--Goto & Null
----------------------------
Declare
v1 number := 1;
begin
<<abc>>
begin
dbms_output.put_line(v1);
end;
if v1>=5 then
return;
end if;
v1:= v1+1;
goto abc;
end;
/
----
begin
if 1=1 then
null; --->does noting
end if;
end;
------------------------------------------------------------------------------
-- OPERATORS
-- := assignment
-- .. for loop
-- != , <> , ~= Inequality/Not Equal to
-- => Association (4 passing value to parameters(function, procedure,cursor))
-- ** Exponential (used for power ^2)
--Examples
--var1 number := 10;
--for in 1 .. 10 loop;
-- if v1 ~= V2
-- v1:= 2**3;
--
------------------------------------------------------------------------------
-- SUB PROGRAMS
----Procedures (Used for Actions)
----Function (Used for Calculations mainly)
------------------------------------------------------------------------------
--Procedure
--It has its Name/Parameters/DB Object/stored in db/reusable
--/seperate Compilation+Execution
--Errors not displayed directly .. use showErrors(Functions)
--or query User_Errors(data dictionary)
--cannot be called in DML
-----------
--Example
Create or replace procedure proc_p1 is
begin
dbms_output.put_line('Welcome to my First Proc.');
dbms_output.put_line('Proc Created');
end;
/
set serveroutput on;
execute proc_p1;
show error;
-----Procedure with Error
create or replace procedure proc_p2 is
--declare
num1 number;
begin
num1 : 10; ----Error :=
dbms_output.put_line(10/2);
exception
when others then
dbms_output.put_line('OOPs! something went wrong!');
end;
/
--------------
exec PROC_P2;
show errors; ---shows most resent compiled pl sql objects.
--------------
-- user_errors
-- user_source
-- user_objects
--user_procedures
--------------
select * from user_errors; --predefined table for errors
select * from user_source; --Data Dictionary to stored all PL-SQL Objects(proc,trigger,functions,etc.)
select text from user_source where name ='ADD_JOB_HISTORY'; -- to check Procedure coding
select * from user_objects; --Data dictionary to get all DB objects (Both sql +plsql)
select * from user_procedures;--Data dictionary to get all PLSQL Objects. (Procedures, Fuctions, Triggers,...)
--------------------------------------------------------------
---Extra
---Merging query (dept to dept1)
Merge into dept1 A
using dep B
on (A.deptno = B.deptno)
when matched then update set loc = B.loc
when not matched then insert(deptno,dname,loc,loc_id)
values(B.deptno,B.dname,B.loc,B.loc_id);
---------------------------------------------------------------
alter user hr identified by hr account unlock;
grant debug connect session to hr;
grant debug any procedure to hr
------------------------------------------------------------------------------------------------------------------
continued.....
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- SUB PROGRAMS
----Procedures (Used for Actions)
----Function (Used for Calculations mainly)
------------------------------------------------------------------------------
--Procedure
--It has its Name/Parameters/DB Object/stored in db/reusable
--/seperate Compilation+Execution
--Errors not displayed directly .. use showErrors(Functions)
--or query User_Errors(data dictionary)
--cannot be called in DML
-----------
--Example
Create or replace procedure proc_p1 is
begin
dbms_output.put_line('Welcome to my First Proc.');
dbms_output.put_line('Proc Created');
end;
/
set serveroutput on;
execute proc_p1;
show error;
-----Procedure with Error
create or replace procedure proc_p2 is
--declare
num1 number;
begin
num1 : 10; ----Error :=
dbms_output.put_line(10/2);
exception
when others then
dbms_output.put_line('OOPs! something went wrong!');
end;
/
--------------
exec PROC_P2;
show errors; ---shows most resent compiled pl sql objects.
--------------
-- user_errors
-- user_source
-- user_objects
--user_procedures
--------------
select * from user_errors; --predefined table for errors
select * from user_source; --Data Dictionary to stored all PL-SQL Objects(proc,trigger,functions,etc.)
select text from user_source where name ='ADD_JOB_HISTORY'; -- to check Procedure coding
select * from user_objects; --Data dictionary to get all DB objects (Both sql +plsql)
select * from user_procedures;--Data dictionary to get all PLAQL Objects.
------------------------------------------------------------
-----------------------------------------------
select * from EMP_SAL;
create procedure proc_sum (a IN number , b IN number, c OUT number)
AS
begin
c := a+b;
end;
/
---
var k number; --
execute proc_sum(100,5000,:k);
print :k;
-----------------
-----------------
create or replace procedure proc_p2
AS
v_id EMPLOYEES.EMPLOYEE_ID %type;
v_fname EMPLOYEES.FIRST_NAME %type;
v_lname EMPLOYEES.LAST_NAME %type;
v_email EMPLOYEES.EMAIL %type;
BEGIN
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL
into v_id ,v_Fname,v_Lname,v_Email
from Employees
where EMPLOYEE_ID = 100;
dbms_output.put_line('EMPLOYEE_ID: '||v_id||' FIRST_NAME: '||v_fname||' LAST_NAME: '||v_lname||' EMAIL: '||v_email);
end;
execute proc_p2;
---------------------
---------------------
create or replace procedure proc_p3
AS
v_id RES_BOOK_LIST.book_id %type;
v_name RES_BOOK_LIST.book_name %type;
v_author RES_BOOK_LIST.author %type;
begin
select book_id,book_name,author
into v_id ,v_name,v_author
from RES_BOOK_LIST
where book_id ='109';
dbms_output.put_line('ID: '||v_id||' :: '||'Name: '||v_name||' :: '||'Author: '||v_author);
end;
-----------------------
-----------------------
-- Procedures with IN OUT Parameter
------------------------------
create or replace procedure increase_salary(Eid IN number,Amt IN Number,S out Number) IS
begin
update emp_sal set salary = salary+Amt
where e_id= Eid;
commit;
select salary into S from Emp_sal where e_id = Eid;
end;
/
--------
var k number; --
execute increase_salary(100,5000,:k);
print :k;
--------
----------------------------------------------------------------
--Function
----------------------------------------------------------------
----------Example 1 ---
create or replace function f1 return number Is
begin
return 123; --Number type
end;
/
---
var x number;
execute :x := f1;---calling function (f1) and storing returned value to x variable
print x;
--or--
declare
v number;
begin
v := f1;
dbms_output.put_line(v);
end;
--or--
select f1 from dual;
--or--
begin
dbms_output.put_line(f1);
end;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
create function f2 (id IN Number) return varchar IS
v_id emp_sal.e_id %Type;
F_name emp_sal.f_name %Type;
L_name emp_sal.l_name %Type;
v_salary emp_sal.salary %Type;
begin
select e_id,f_name,l_name,salary
into v_id,f_name,l_name,v_salary
from Emp_sal where e_id = id;
return 'EID:'||v_id||' F_NAME:'||f_name||' L_NAME:'||l_name||' Salary:'||v_salary;
end;
/
---
select f2(100) from dual;
---
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--Cursors
--3 types
--1)Static - select statement is fixed
-- 2 types - 1)Implecit 2)Expicit
--2)Dynamic - select statement is not fixed
--3)Ref Cursors - Provide storage + return result set
-- 2 types - 1) Strong 2) weak (Strong has return type but weak does not)
--sys_refcursor is data type for ref Cursor
-----------------------------------
---Example weak ref cursor
create or replace procedure proc_ref_cursor (v_id IN Number, vref_cursor OUT sys_refcursor)
IS
Begin
open vref_cursor for select * from emp_sal where e_id = v_id;
end;
/
--
var vref refcursor; --globle variable
execute proc_ref_cursor(100,:vref);
print vref;
------------------------------------------------------------------------
------------------------------------------------------------------------
create or replace procedure pc (vref_cursor OUT sys_refcursor)
IS
Begin
open vref_cursor for select * from emp_sal;
end;
/
--
var vref refcursor; --globle variable
execute pc(:vref);
print vref;
------------------------------------------------------------------------------
