PL/SQL INTRODUCTION:
-------------------------------------
--> PL/SQL STANDS FOR PROCEDURAL LANGUAGE EXTENSIONS TO SQL
--> PL/SQL CAN BE USED FOR BOTH SERVER-SIDE AND CLIENT-SIDE DEVELOPMENT.
--> PL/SQL IS A PART OF THE ORACLE RDBMS AND HENCE CAN BE RESIDE ON TWO ENVIRONMENTS, THE CLIENT AND THE SERVER.
--> ANY MODULE THAT IS DEVELOPED USING PL/SQL CAN BE MOVED EASILY B/W SERVER SIDE AND CLIENT SIDE APPLICATION.
--> PL/SQL BLOCK STRUCTURE
declare (OPTIONAL)
variable declarations;
cursor declarations;
user_defined exceptions;
begin (MANDATORY)
sql statements;
exception (OPTIONAL)
action to perform when errors occur;
end;
/
--> DECLARATION
DATA TYPES --> NUMBER, VARCHAR2,BOOLEAN,DATE;
EX:
---
NO NUMBER(5);
NO NUMBER(5):=1000;
NAME VARCHAR2(10);
NAME VARCHAR2(10):='SAI';
DOB DATE;
DOB DATE DEFAULT SYSDATE;
FLAG BOOLEAN;
FLAG BOOLEAN:=TRUE;
--> BEGIN
EX:
---
DBMS_OUTPUT.PUT_LINE('HI WELCOME TO PL/SQL PROGRAM');
--> EXAMPLES
1) BEGIN
DBMS_OUTPUT.PUT_LINE('HI WELCOME TO PL/SQL PROGRAM');
END;
/
2) DECLARE
NO NUMBER(5):=100;
BEGIN
DBMS_OUTPUT.PUT_LINE(NO);
END;
/
3) DECLARE
NO NUMBER(5):=100;
NAME VARCHAR2(10):='SAI';
BEGIN
DBMS_OUTPUT.PUT_LINE('NUMBER : ' || NO ||' NAME : '||NAME );
END;
/
4)DECLARE
NO NUMBER(5):='&enter_number';
NAME VARCHAR2(10):='&enter_name';
BEGIN
DBMS_OUTPUT.PUT_LINE('NUMBER : ' || NO ||' NAME : '||NAME );
END;
/
5) DECLARE
NO NUMBER :='&enter_number';
NAME VARCHAR2(30) :='&enter_name';
BEGIN
DBMS_OUTPUT.PUT_LINE('NUMBER : ' || NO ||' NAME : '||NAME );
END;
/
NOTE: FOR NUMBER DATA TYPE NO NEED TO SPECIFY LENGHT WHERE AS FOR VARCHAR2 WE MUST SPECIFY THE LENGTH.
6) TO FIND BIG NUMBER
DECLARE
A NUMBER :='&ENTER_A_VALUE';
B NUMBER :='&ENTER_B_VALUE';
C NUMBER :='&ENTER_C_VALUE';
BEGIN
IF (A>B) THEN
DBMS_OUTPUT.PUT_LINE('A IS BIG');
ELSE
DBMS_OUTPUT.PUT_LINE('B IS BIG');
END IF;
END;
/
7)TO FIND BIG NUMBER
DECLARE
A NUMBER :='&ENTER_A_VALUE';
B NUMBER :='&ENTER_B_VALUE';
C NUMBER :='&ENTER_C_VALUE';
BEGIN
IF (A>B) AND (A>C) THEN
DBMS_OUTPUT.PUT_LINE('A IS BIG');
ELSIF (B>C) THEN
DBMS_OUTPUT.PUT_LINE('B IS BIG');
ELSE
DBMS_OUTPUT.PUT_LINE('C IS BIG');
END IF;
END;
/
8)FOR FINDING SUM OF 1 TO 100 NUMBERS9USING FOR LOOP)
declare
sum1 number(5):=0;
begin
for i in 1 ..100
loop
sum1:=sum1 + i;
end loop;
dbms_output.put_line(sum1);
end;
9) FOR FINDING SUM OF 1 TO 100 NUMBER(USING WHILE LOOP)
declare
sum1 number(5):=0;
i number:=1;
begin
while i<=100
loop
sum1:=sum1+i;
i:=i+1;
end loop;
dbms_output.put_line(sum1);
end;
/
procedures & functions:
-----------------------
--> a subprogram is a named block of code block made up of logically grouped sql and pl/sql statements that perform a specific task.
--> subprograms can be classified into
-- procedures
-- functions
--> these subprograms are compiled and stored in the oracle database as stored programs.
procedures:
-----------
--> can return one or more values through parameters or may not return values at all.
--> it includes
-- declarative section
-- executatble section
-- exception section
--> dictionary view
-- user_procedures
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
PROCEDURE_NAME VARCHAR2(30)
OBJECT_ID NUMBER
SUBPROGRAM_ID NUMBER
OVERLOAD VARCHAR2(40)
OBJECT_TYPE VARCHAR2(19)
AGGREGATE VARCHAR2(3)
PIPELINED VARCHAR2(3)
IMPLTYPEOWNER VARCHAR2(30)
IMPLTYPENAME VARCHAR2(30)
PARALLEL VARCHAR2(3)
INTERFACE VARCHAR2(3)
DETERMINISTIC VARCHAR2(3)
AUTHID VARCHAR2(12)
--> syntax:
create or replace procedure <schema.procedurename>
(<arguments>{in,out,in out} <datatype>,...){is,as}
variable dealcaratios;
constant declaration;
begin
<body code block>;
exception
<exception code block>;
end;
--> examples:
1) procedure to fine no of present days of a employee
create or replace procedure calleaves(varempno in varchar2,varleaves out number) is
begin
select count(*) into varleaves from EmployeeMuster where Employeeno=varempno and attendance='Present';
end;
2) procedure to find the age of a student
create or replace procedure stdinfo(sno in varchar2,ag out number)is
begin
select age into ag from students where studentno=sno;
end;
cursor:
-------
--> cursor is a handle or pointer to the context area.
--> context area contains information needed to process the statement.
--> using cursor , the pl/sql program can control the context area, as the sql statement is being processed.
--> cursor allows to fetch and process rows returned by a select statement , one row at a time.
-->
--> cursors are
-- server side(database level)
-- client side(application level)
--> cursors are
-- read only
-- no row level locks
-- no row id of the records
-- updatable
-- row level locks at tabe level
-- contains row id of the records
--> cursors are 2 types
-- implicit (internal)
-- explicit (external)
--> implicit cursors
-- created by oracle associted with DML statement
-- SQL cursor - to hold the status of the DML statements.
found not found row count
----- ---------- ---------
T F 4
SQL%found
SQL%notfound
SQL%rowcount
--> explicit cursors
-- it is a cursor that is defined by the programmer within the program for any query that returns more than one row of data.
-- generated using a name with association of a select statement in the declaration section of the pl/sql block.
-- more efficient in implementation, hence easy to trap errors.
-- oracle maintain inernal cursor for every external cursor with same name.
open found notfound rowcount
---- ----- -------- --------
T T F 4
-- four steps that should be performed by as explicit cursor s.
-- cursor declaration
-- cursor opening
-- cursor fetching
-- cursor closing
syntax
------
declare
cursor declaration
begin
opening of a cursor(open cursorname)
fetching of cursor
closing cusor(close cursorname)
end;
example
-------
declare
v_name emp.ename%type;
v_sal emp.sal%type;
cursor empcur is select ename,sal from emp where empno=&empno;
begin
open empcur;
fetch empcur into v_name,v_sal;
dbms_output.put_line(v_name||' salary is '||v_sal);
close empcur
end;
declare
uname varchar2(20) :='&Enter-username :'
table_name varchar2(20);
cursor tabcur is select table_name from dba_tables where owner= uname;
begin
open tabcur;
loop
fetch tabcur into table_name
dbms_output.put_line('table name : '||table_name);
end loop;
close tabcur;
end;
/
example
-------
declare
v_name emp.ename%type;
v_sal emp.sal%type;
cursor empcur is select ename,sal from emp;
begin
open empcur;
loop
fetch empcur into v_name,v_sal;
exit when empcur%notfound;
dbms_output.put_line(v_name||' salary is '||v_sal);
end loop;
close empcur;
end;
--> refer cursors
-- used for different tables with same data
-- types
-- strong ref cursors
-- weak refer cursors
example
-------
declare
type emprefcur is ref cursor;
v_emprefcur emprefcur;
v_name emp.ename%type;
begin
open v_emprefcur for select ename from emp;
loop
fetch v_emprefcur into v_name;
exit when v_emprefcur%notfound;
dbms_output.put_line('emplpyee number '||to_char(v_emprefcur%rowcount,'09')||':'||v_name);
end loop;
close v_emprefcur;
end;
bulck collect for multiple row query
------------------------------------
--> retrives multiple rows of data with a single round trip to and from the database.
--> reduces the nof of i/o cycles b/w pl/sql engine and sql engine and reduces the over head of retrieving data.
example
-------
declare
type emptable is table of emp%rowtype;
v_emptable emptable;
begin
select * bulck collect into v_emptable from emp;
loop
dbms_output.put_line(v_emptable(indexvariable).ename||' is fixed with a salary of '||v_emptable(indexvariable).sal);
end loop;
end;
example:
---------
declare
uname varchar2(20) :='&username';
table_name varchar2(20);
count1 number(9);
var varchar2(100);
cursor tabcur is select table_name from dba_tables where owner= uname;
begin
open tabcur;
dbms_output.put_line('table name no of rows');
dbms_output.put_line('----------- ------------');
loop
fetch tabcur into table_name;
exit when tabcur%notfound;
var :='select count(*) from '||uname||'.'||table_name;
execute immediate var into count1;
dbms_output.put_line(table_name||' ' ||count1);
end loop;
close tabcur;
end;
/
triggers:
---------
--> a database trigger is a named pl/sql block stored in a database and executed implicitly when triggering event occurs.
--> exucuted automatically
--> for data security
--> wrong data will be avoided
--> trigger has 4 parts
-- triggers events
--
--
-- triggering body
--> dictionary view
-- user_triggers
--> triggers can fire befor or after a trigger event
--> when a table is dropped then the associated triggers are also dropped.
--> instead of triggers for views
--> row triggers
insert update delete
------ ------ -------
new yes yes no
old no yes yes
syntax:
-------
create or replace trigger triggername
before/after eventtype on tablename
[for each row]
[when condition]
declare
begin
exception
end;
1)example for after trigger
----------------------------
create or replace trigger weekendcheck
after insert or delete or update on emp
declare
v_weekday varchar2(20);
begin
v_weekday:=to_char(sysdate,'dy');
if v_weekday='SAT' or v_weekday='SUN' then
raise_application_error(-20010,'An illegal intrusion into the system was detected');
end if;
end;
2)example for before trigger
----------------------------
create or replace trigger illegaltimes
before insert or update or delete on emp
declare
v_time number;
begin
v_time:=to_char(sysdate,'HH24');
if v_time not between 10 and 17 then
raise_application_error(-20010,'Illegal intrusion,not business hours');
end if;
end;
instead of triggers
-------------------
--> defined on database views
--> created as row triggers only
--> fires insead of the triggering statement, that has been issued against a view
syntax:
-------
create or replace trigger triggername
instead of triggerevent on viewname
for each row
begin
execution statement
end;
example:
--------
create or replace trigger deptdel
instead of delete on deptview
for each row
begin
delete from dept where deptno=:OLD.deptno;
dbms_output.put_line('department deleted...');
end;
row triggers
------------
--> a row trigger is fired as many times as there are rows affected by triggering evvent
--> when the statement for each row is present in the create trigger clause, the trigger is row trigger
example
-------
create or replace trigger deptupdate
after update on dept
for each row
statement level triggers
------------------------
--> fired once for the entire triggering statement
--> this trigger fires once, regardless of the no of rows affected by the triggering statement.
example;
--------
create or replace trigger deptdelete
after delete on dept
example for row triggers
------------------------
sql> create table rebin(empno number(10),ename varchar2(30),job varchar2(20),sal number(6),deptno number(2));
sql> create or replace trigger empbin
before delete on emp
for each row
begin
insert into rebin values(:OLD.empno,:OLD.ename,:OLD.job,:OLD.sal,:OLD.deptno);
end;
for disable triggers
--------------------
sql> alter table emp disable all triggers;
sql> alter trigger triggername enale/disable;
for drop a trigger
------------------
sql> drop trigger triggername;
sql shell scripts:
-----------------
1)
#!/bin/bash
echo "Enter user name :"
read username
var=`sqlplus -s / as sysdba <<EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select username||'|'||account_status||'|'||default_tablespace from dba_users where username=upper('$username');
exit
EOF`
echo "******user details********* "
echo "-----------------------------"
aname=`echo $var | cut -d'|' -f1`
astatus=`echo $var | cut -d'|' -f2`
dbs=`echo $var | cut -d'|' -f3`
echo "user name : $aname "
echo "account status : $astatus"
echo "default tablespace : $dbs"
2)
#!/bin/bash
echo "Enter user name :"
read username
var=`sqlplus -s / as sysdba <<EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select username||'|'||account_status||'|'||default_tablespace from dba_users where username in($username);
exit
EOF`
echo "******user details********* "
echo "-----------------------------"
for dir in $var; do
aname=`echo $dir | cut -d'|' -f1`
astatus=`echo $dir | cut -d'|' -f2`
dbs=`echo $dir | cut -d'|' -f3`
echo "user name : $aname "
echo "account status : $astatus"
echo "default tablespace : $dbs"
echo "--------------------------"
echo " "
done
3)
#!/bin/bash
echo "Enter user name :"
read username
clear
var=`sqlplus -s / as sysdba <<EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select table_name||'|'||num_rows from dba_tables where owner=upper('$username');
exit
EOF`
echo "******$username schema details********* "
echo "-----------------------------"
echo " "
echo "table name no of rows"
echo "---------- ----------"
for dir in $var; do
tname=`echo $dir | cut -d'|' -f1`
nor=`echo $dir | cut -d'|' -f2`
echo " $tname $nor "
echo " "
done
4)
#!/bin/bash
echo "Enter user name :"
read username
clear
var=`sqlplus -s / as sysdba <<EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select table_name from dba_tables where owner=upper('$username');
exit
EOF`
echo "******$username schema details********* "
echo "----------------------------------------"
echo " "
echo "table name no of rows"
echo "---------- ----------"
for dir in $var; do
tname=$dir
nor=`sqlplus -s / as sysdba <<EOF
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select count(*) from $username.$dir;
exit
EOF`
echo " $tname $nor "
echo " "
done
No comments:
Post a Comment