I m sophomore in be cse. I was from non tech background till 12 but technology attracts me the way it is,hence exploring different tech stacks.
What is a Procedural Structured Query?
PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language. The basic unit in PL/SQL is called a block and is made up of three parts: a declarative part, an executable part and an exception-building part.
How does PL/SQL work?
PL/SQL blocks are defined by the keywords DECLARE, BEGIN, EXCEPTION and END.
Where you can run commands in PL/SQL?
Go to SQL-DEVELOPER [install it]
set password and id and run PL/SQL Commands
Go to "Oracle LiveServer" on the tab.
run the commands in that online compiler
CREATE TABLE COMMAND
create table emp(emp_id number(5) , emp_name varchar(10),dept_id number(10), salary number(10,2) );
INSERT THE VALUES INTO TABLE
insert into emp(emp_id,emp_name,dept_id,salary) values(1,'pinak', 102, 10.00); insert into emp(emp_id,emp_name,dept_id,salary) values(2,'charvi', 106, 106.00); insert into emp(emp_id,emp_name,dept_id,salary) values(3,'jatin', 107, 186.00); insert into emp(emp_id,emp_name,dept_id,salary) values(4,'knoo', 107, 186.00);
TO SHOW THE TABLE
select * from emp;
desc emp(table name);
TO DELETE DATA
truncate table emp;
DROP TABLE
drop emp;
TO COMMENT OUT IN PL/SQL
by "--"
;
to terminate the statement(end)
dbms_output.put_line
for printing the statement
set serveroutput
for initializing in sqldeveloper
CASE STATEMENT
declare grade varchar(2):='B';
begin
case grade when 'A' then dbms_output.put_line('excellent');
when 'B' then dbms_output.put_line('good');
else dbms_output.put_line('grade not found');
end case;
end;
LOOP
declare num1 number :=1;
begin
loop dbms_output.put_line(num1);
num1 := num1 +1;
exit when num1 =10;
end loop;
end;
FOR LOOP
declare x number(3);
begin
for x in 1 .. 10
loop
dbms_output.put_line('value of x is : '||x); end loop;
end;
While Loop
declare x number(9) := 1;
begin
while x>10 l
oop dbms_output.put_line(x); x := x+1;
end loop;
end;
CURSOR
declare
cursor c1
is select
emp_name, salary from emp;
name emp.emp_name%type;
esal emp.salary%type;
begin
open c1;
loop
fetch c1 into name,esal;
exit when c1%notfound;
dbms_output.put_line(name || ' '||esal);
end loop;
close c1;
end;
** type --> for selecting column datatype
**c1%notfound --> If the value does not exist in the loop then it exits the loop
** SYNTAX:
declare
begin
open c1
loop->fetch->endloop->
close
end
EXAMPLE
declare
cursor c1
is select
from emp;
r emp%rowtype;
begin
open c1;
loop
fetch c1 into r;
exit when c1%notfound;
dbms_output.put_line(r.emp_name ||' '||r.salary);
close c1;
end;
EXAMPLE 2:
[USE OF FOR LOOP]
declare
cursor c1 is select from emp;
r emp%rowtype;
begin
for r in c1 loop
dbms_output.put_line(r.emp_name|| ' '||r.salary);
end loop;
end;
EXAMPLE 4
[if-else-endif]
**other loops are
[if-elsif-end]
declare
vempno emp.emp_id %type;
begin
vempno := 1;
\*['&vempno']**--> It is for SQL developers not for Oracle it does not provide you with & an option*
delete from emp where vempno= emp_id;
if sql%found then
dbms_output.put_line('recorded');
else
dbms_output.put_line(' not recorded');
end if;
commit;
end;
select * from emp;
System Defined Exceptions
zero_divide c=a/b;
value_error 20000
invalid_number string +10; sysdate+10
no_data_found most of data 679
too_many_rows
dup_val_on_index
invalid_cursor
User Defined Exception
1. raise statement
2.raise_applicatiom_error
EXCEPTION EXAMPLE
declare
a number(2);
b number(2);
c number(2);
one_divide exception;
begin
a :=10;
*\[In Oracle you have to mention value it can't be user defined]*
** a := &a;[in SQL ]
b := 22222222;
if b=1 then raise one_divide;
end if;
c := a/b;
dbms_output.put_line(c);
exception when zero_divide then
dbms_output.put_line('zero divide');
when one_divide then dbms_output.put_line('one divide');
when others then dbms_output.put_line('others');
end;
3***.Pragma Function[pragma exception_init]--> to define error with code***
declare
vdno emp.dept_id%type;
child_found exception;
pragma exception_init(child_found, -8999);
begin vdno := 107;
execute immediate 'delete from emp where dept_id = :dept_id' using vdno;
dbms_output.put_line('Deletion unsuccessful');
delete from emp where dept_id= vdno;
exception
when child_found then
dbms_output.put_line('child record found');
end;
Dynamic SQL
Use dynamic SQL
to delete records based on the variable
vdno execute immediate 'delete from emp where dept_id = :dept_id' using vdno;
dbms_output.put_line('Deletion successful');
exception
when child_found then
dbms_output.put_line('Child record found');
end;
Procedure[in/out/inout]
[may or may not return value]
create or replace procedure
raise_salary (e in number, atm in number, s out number)
is begin
update emp set salary =salary +atm where emp_id=e;
commit;
select salary into s from emp where emp_id=e;
end;
EXAMPLE
create or replace procedure
update_sal(e in number )
is begin
update emp set salary =salary+1000 where emp_id=e;
end;
select emp_id ,salary from emp;
begin update emp set salary=salary +1000 where emp_id=1;
update_sal(2);
commit;
end;
[IT WILL END THE MAIN TRANSACTION]
ROLLBACK
create or replace procedure update_sal(e in number )
is pragma autonomous_transaction;
begin
update emp set salary =salary+1000 where emp_id=e;
rollback;
end;
select emp_id ,salary from emp;
begin update emp set salary=salary +1000 where emp_id=1;
update_sal(2); commit;
end;
[it will not rollback the main transaction]
rollback starts a new transaction
FUNCTION
create or replace function calc(a number, b number, op char )
return number
is begin
if op='+' then
-- [ return expression]--syntax
return a+b;
elsif op='-' then
return a-b;
PACKAGE
1.package specification
2.package body
create or replace package mypack as function addnum(a number,b number) return number; function addnum(a number,b number, c number) return number; end;
create or replace package body mypack
as function addnum(a number, b number) return number
is begin return(a+b);
end addnum;
function addnum(a number,b number, c number)
return number is begin return(a+b+c);
end addnum;
end mypack;
HOW TO CALL FUNCTION?
select mypack.addnum(10,40) from dual;
What if you don't know the table name until runtime?
create or replace procedure drop_object(t in varchar2, n in varchar2)
is begin
execute immediate 'DROP' ||t ||' '||n;
end drop_object;
How to call procedure?
begin
drop_table('student2');
end;
TRY!!!
drop table tname ;
drop index indname;
drop view viewname;
FILE[working]
you can select any drive and folder name where you want to init a folder
create DIRECTORY D10 as 'D:\pinak';
grant, read,write on directory D10 to pinak;
FOR WRITING THE FILE
declare
f1 utl_file.file_type;
begin f1 := utl_file.fopen('D10','abc.txt', 'w');
utl_file.put_line(f1,'hello');
utl_file.put_line(f1, 'welcome');
utl_file.fclose(f1);
end;
FOR READING THE FILE
declare f1 utl_file.file_type;
s varchar2(100);
begin f1 := utl_file.fopen ('D10','abc.txt','r');
GETFILE --> For Reading
PUTFILE -->For Writing
loop utl_file.get_line(f1, s);
dbms_output_line(s);
end loop;
exception when no_data_found then
utl_file.fclose(f1);
end;
WORKING WITH LOB'S
Can you store Photos?
we can store photos path
create directory D11 as 'D:\pinak';
grant, read,write on directory D11 to pinak;
conn scott create table cust( cid number(2), cname varchar2(20), cphoto BFILE); )
insert into cust values(10,'ANI',BFILENAME('D11', 'tulips.jpg'));
select from cust;
COLLECTIONS
collections are to store different values of the same datatypes,it plays a role of arrays in PL/SQL
SYNTAX
type name is table of datatype
index of datatype;
EXAMPLE
declare type dname_array is table of varchar2(50) index by binary_integer;
d dname_array; begin for i in 1..4 select emp_name into d(i) from emp where dept_id=110;
end loop;
for i in 1..4 dbms_output.put_line(d(i)));
end loop;
end;
USING OF BULK COLLECT
to fetch all records at one time, it stops the overloading in the database.
USING OF e.first..e.last
declare type empno_array is table of emp2.emp_id%type index by binary_integer; e empno_array;
begin select emp_id bulk collect into e from emp2 where emp_id=e;
for i in e.first ..e.last loop
update emp2 set salary=salary+1000 where emp_id=e(i);
end loop;
commit;
end;
USING OF FORALL LOOP--> to fetch data at one go
declare type empno_array
is table of emp2.emp_id%type index by binary_integer;
e empno_array;
begin
forall i in e.first .. e.last
update emp2 set salary = salary + 1000 where emp_id = e(i);
commit;
end;
select * from emp2;[now see the changes]
create table temp( empno number(5), ename varchar2(100)
); in
DEFINER AND INVOKER'S RIGTH PROCEDURE
create or replace procedure myproc
authid current_user
is s varchar2(20);
begin
select ename into s from temp where empno = 100;
dbms_output.put_line(s); end;
Calling Procedure
begin
myproc;
end;
Granting Permisions
Grant execute privilege on myproc to user2 g
grant execute on myproc to user1;
Execute myproc as user1 ;
begin u=
user1.myproc;
end;
TRIGGER[*on insert /update/delete]*
1.create table stu(sid number(5), sname varchar2(10));
2.create table backup_stu(sid number(5), sname varchar2(10), timestamp date); create trigger backup_trigger
before update on stu
for each row begin
insert into backup_stu values (:old.sid,:old.sname,sysdate);
\**Keyword:old and : new is used to store old and new values in the database*
end;
4.insert into stu values(1011,'pinak');
4.insert into stu values(1012,'pakuu');
5.select from stu;
6.update stu set sid=2001 where sid=1011;
select from backup_stu;
delete from stu where sid=2001;
Now "stu" will get an update
and
backup_stu will have the old values with timestamp as we use [sysdate]
Now keep practising these commands
#practice makes a man perfect -- > maybe
But,
#Practice makes a lot of improvement in creating better versions.
--> Pinak Dhir
