SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3 000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
declare
sal number:=1000;
flag varchar2(20):= 'Greater then value';
begin
if (sal > 500) then
dbms_output.put_line(flag);
end if;
end;
/
Greater then value
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
sal number:=20;
flag varchar2(100);
begin
if (sal > 15) then
select ename into flag from emp where empno = 7902;
dbms_output.put_line(flag);
end if;
end;
/
FORD
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
avg1 float ;
emp_sal number ;
flag varchar2(100) := 'Employee salary is greater then avg sal';
begin
select avg(sal) into avg1 from emp ;
select sal into emp_sal from emp where empno = 7369;
if( emp_sal >= avg1 ) then
dbms_output.put_line(flag);
end if ;
end;
/
declare
avg1 number ;
emp_sal number ;
begin
select avg(sal) into avg1 from emp ;
select sal into emp_sal from emp where empno = 7839;
if( emp_sal >= avg1 ) then
dbms_output.put_line('Employee salary is greater then avg sal');
end if ;
end;
/
Employee salary is greater then avg sal
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
declare
emp_hiredate date ;
min_hiredate date := '23-JAN-82';
emp_name varchar2(10);
begin
select hiredate,ename into emp_hiredate,emp_name from emp where empno = 7788;
if (emp_hiredate > min_hiredate) then
dbms_output.put_line(emp_name ||' '|| 'Hired After ' ||min_hiredate ) ;
end if ;
end;
/
SCOTT Hired After 23-JAN-82
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
IF THEN ELSE
declare
emp_ename varchar2(20);
emp_sal number;
avg_sal integer ;
begin
select ename , sal into emp_ename,emp_sal from emp where empno = 7566;
select avg(sal) into avg_sal from emp;
if (emp_sal > avg_sal ) then
dbms_output.put_line('Employee' || ' '|| emp_ename || ' is getting more than avg sal.' || 'Avg sal is '||avg_sal || ' and employee ' ||emp_ename || ' sal is ' || emp_sal );
ELSE
dbms_output.put_line('Employee' || ' '|| emp_ename || ' is getting less than avg sal.' || 'Avg sal is '||avg_sal || ' and employee ' ||emp_ename || ' sal is ' || emp_sal );
end if ;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
id number := 10;
begin
if (id = 10) then
dbms_output.put_line('statement 1 id is equal to id');
elsif (id = 10) then
dbms_output.put_line('statement 2 id is equal to id1');
else
dbms_output.put_line('no value matched');
end if ;
end;
/
this logic check condition is true or if whenever condition found true the statement will be executed .
and counter will be outside of 'IF then else' .
if no condition found then
ELSE statement will be executed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
id number := 10;
id_t number := 10;
id_t3 number := 20;
name varchar2(20) := 'A';
begin
case
when id > id_t then dbms_output.put_line('id less then id_t ');
when id <= id_t then dbms_output.put_line('id less then id_t3');
else dbms_output.put_line('no value match');
End case;
end ;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
A number:= 100;
B number:= 200;
begin
if ( A = 54) then
if (B =200 ) then
dbms_output.put_line('value of A is and B is');
end if ;
dbms_output.put_line( A || ' ' || B);
end if ;
end ;
/
declare
emp_name varchar2(20);
emp_sal number ;
a number ;
x number := 0;
begin
select ename ,sal into emp_name,emp_sal from emp where empno = 7566;
select count(*) into a from emp;
loop
dbms_output.put_line(emp_name || ' ' || emp_sal ) ;
x := x+1;
if (x > a ) then
exit;
end if;
end loop;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> declare
2 emp_name varchar2(20);
3 emp_sal number ;
4 a number ;
5 x number := 0;
6 begin
7 select ename ,sal into emp_name,emp_sal from emp where empno = 7566;
8 select count(*) into a from emp;
9 loop
10 dbms_output.put_line(emp_name || ' ' || emp_sal ) ;
11 x := x+1;
12 if (x > a ) then
13 exit;
14 end if;
15 end loop;
16 end;
17 /
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
PL/SQL procedure successfully completed.
declare
emp_name varchar2(20);
emp_sal number ;
a number ;
x number := 0;
begin
select ename ,sal into emp_name,emp_sal from emp where empno = 7566;
select count(*) into a from emp;
loop
dbms_output.put_line(emp_name || ' ' || emp_sal ) ;
x := x+1;
exit when x > a ;
end loop;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> declare
2 emp_name varchar2(20);
3 emp_sal number ;
4 a number ;
5 x number := 0;
6 begin
7 select ename ,sal into emp_name,emp_sal from emp where empno = 7566;
8 select count(*) into a from emp;
9 loop
10 dbms_output.put_line(emp_name || ' ' || emp_sal ) ;
11 x := x+1;
12 exit when x > a ;
13
14 end loop;
15 end;
16 /
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
simple FOR LOOP
SQL>declare
emp_name varchar2(20);
emp_sal number ;
a number ;
x number := 0;
begin
select ename ,sal into emp_name,emp_sal from emp where empno = 7566;
select count(*) into a from emp;
for x in 1..a loop
dbms_output.put_line(emp_name || ' ' || emp_sal ) ;
end loop;
end;
/
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
JONES 2975
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare
emp_deptno number ;
emp_count number;
x number :=0;
count1 number ;
begin
select count(distinct deptno) into count1 from emp;
for i in 1..count1 loop
x := x+10;
select deptno, count(*) into emp_deptno,emp_count from emp where deptno = x group by deptno;
dbms_output.put_line(emp_deptno || ' ' || emp_count);
end loop;
end;
/
10 3
20 5
30 6
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Reverse FOR LOOP Statement
DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
while loop
declare
emp_deptno number ;
emp_count number;
count1 number ;
x number :=0;
A number := 0;
begin
select count(distinct deptno) into count1 from emp;
while A <= count1-1 loop
x := x+10;
A := A+1;
select deptno, count(*) into emp_deptno,emp_count from emp where deptno = x group by deptno;
dbms_output.put_line(emp_deptno || ' ' || emp_count);
end loop;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
SQL> declare
2 emp_deptno number ;
3 emp_count number;
4 count1 number ;
5 x number :=0;
6 A number := 0;
7 begin
8 select count(distinct deptno) into count1 from emp;
9 while A <= count1 loop
10 x := x+10;
11 A := A+1;
12 select deptno, count(*) into emp_deptno,emp_count from emp where deptno = x group by deptno;
13 dbms_output.put_line(emp_deptno || ' ' || emp_count);
14 end loop;
15 end;
16 /
10 3
20 5
30 6
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
same code
SQL> declare
2 emp_deptno number ;
3 emp_count number;
4 count1 number ;
5 x number :=0;
6 A number := 0;
7 begin
8 select count(distinct deptno) into count1 from emp;
9 while A <= count1-1 loop
10 x := x+10;
11 A := A+1;
12 select deptno, count(*) into emp_deptno,emp_count from emp where deptno = x group by deptno;
13 dbms_output.put_line(emp_deptno || ' ' || emp_count);
14 end loop;
15 end;
16 /
10 3
20 5
30 6
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>..
nested looping
SQL> declare
2 emp_no number ;
3 empno number;
4 emp_ename varchar2(20);
5 i number;
6 g number :=0;
7 f number :=0;
8 no1 number;
9 begin
10 for f in 1..3 loop
11 for i in 1..2 loop
12 g := g+1;
13 select empno,ename,no into emp_no,emp_ename,no1 from( select empno,ename,rownum no from emp)
14 where no = g;
15 dbms_output.put_line(emp_no ||' '|| no1);
16 end loop;
17 end loop;
18 end;
19 /
7839 1
7698 2
7782 3
7566 4
7788 5
7902 6
7369 7
7499 8
7521 9
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
declare
id number := 10;
no number :=30;
begin
if (id = 10) then
dbms_output.put_line('statement1');
elsif (id = 10) then
dbms_output.put_line('statement2');
elsif (no =30) then
dbms_output.put_line('statement3');
else
dbms_output.put_line('no value matched');
end if ;
end;
/
declare
id number := 10;
id_t number := 5;
id_t3 number := 5;
begin
case
when id > id_t then dbms_output.put_line('statement1 ');
when id_t = id_t3 then dbms_output.put_line('statement2');
else dbms_output.put_line('no condition true');
End case;
end ;
/
DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 700) THEN
-- if condition is true then check the following
IF( b = 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
else
dbms_output.put_line('else');
END IF;
END;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure one
as
begin
declare
id number := 10;
id_t number := 5;
id_t3 number := 5;
begin
case
when id > id_t then dbms_output.put_line('statement1 ');
when id_t = id_t3 then dbms_output.put_line('statement2');
else dbms_output.put_line('no condition true');
End case;
end ;
end one;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
A subprogram can be created:
At schema level
Inside a package
Inside a PL/SQL block
A schema level subprogram is
create or replace procedure one
as
begin
declare
id number := 10;
id_t number := 5;
id_t3 number := 5;
begin
case
when id > id_t then dbms_output.put_line('statement1 ');
when id_t = id_t3 then dbms_output.put_line('statement2');
else dbms_output.put_line('no condition true');
End case;
end ;
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
end one;
/
SQL> exec one;
statement1
Square of (23): 529
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE OR REPLACE FUNCTION countemp
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM emp;
RETURN total;
END;
/
calling function from plsql block
SQL> declare
2 c number ;
3 begin
4 c:= countemp();
5 dbms_output.put_line(c);
6 end;
7 /
14
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
declare
empno number;
begin
update dept10
set job ='MANAGER';
dbms_output.put_line(sql%rowcount || ' Rows updated ') ;
end;
/
3 Rows updated
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>.
%Found attribute(as %found return boolean values TRUE or FALSE so it return a value not string . so we not it as string)
declare
emp_name varchar2(10);
status1 varchar2(5) ;
begin
select ename into emp_name from emp where ename = 'SMITH1';
if (sql%found) then
status1 := 'TRUE';
end if;
dbms_output.put_line(status1);
end;
/
>>>>>>>>>>>>>>>>>>>
declare
empno number;
begin
update dept10
set job ='MANAGER';
if (sql%found) then
dbms_output.put_line(sql%rowcount || ' Rows updated ') ;
end if ;
end;
/
3 Rows updated
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
OPEN
FETCH
CLOSE
declare
name emp.ename%type;
emp_no emp.empno%type;
emp_deptno emp.deptno%type;
cursor emp_tab is select empno,ename,deptno from emp;
begin
open emp_tab;
loop
fetch emp_tab into emp_no,name,emp_deptno ;
exit when emp_tab%notfound;
dbms_output.put_line( name || ' '|| emp_no || ' '|| emp_deptno );
end loop;
dbms_output.put_line('no of returned Rows from cursor(emp_tab) is '||emp_tab%rowcount);
close emp_tab;
end;
/
KING 7839 10
BLAKE 7698 30
CLARK 7782 10
JONES 7566 20
SCOTT 7788 20
FORD 7902 20
SMITH 7369 20
ALLEN 7499 30
WARD 7521 30
MARTIN 7654 30
TURNER 7844 30
ADAMS 7876 20
JAMES 7900 30
MILLER 7934 10
no of returned from cursor(emp_tab) is 14
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
data type RECORD
Table-based
Cursor-based records
User-defined records
>>>>>>>
here simple example of Table base RECORD
Table based
extracting ENAME ,EMPNO from Record named emp_rec.
declare
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno = 7934;
dbms_output.put_line(emp_rec.ename ||' '|| emp_rec.empno );
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
cURSOR based Record (recored type is based on cursor)
declare
cursor emp_cur is select empno,ename from emp ;
emp_rec emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec ;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.empno || ' '||emp_rec.ename);
end loop;
end;
/
7839 KING
7698 BLAKE
7782 CLARK
7566 JONES
7788 SCOTT
7902 FORD
7369 SMITH
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7876 ADAMS
7900 JAMES
7934 MILLER
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
user defined Record
(here record 'emp_rec' is user defined type that is 'emp_tab')
declare
cursor emp_cur is select empno,ename,hiredate ,deptno from emp where deptno in (10,30);
type emp_tab is RECORD
(
emp_no number,
e_name varchar2(10),
hiredate date,
dept_no number
);
emp_rec emp_tab;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec ;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.emp_no || ' ' || emp_rec.e_name ||' '|| emp_rec.hiredate ||' '|| emp_rec.dept_no);
end loop;
close emp_cur;
end;
/
7839 KING 17-NOV-81 10
7698 BLAKE 01-MAY-81 30
7782 CLARK 09-JUN-81 10
7499 ALLEN 20-FEB-81 30
7521 WARD 22-FEB-81 30
7654 MARTIN 28-SEP-81 30
7844 TURNER 08-SEP-81 30
7900 JAMES 03-DEC-81 30
7934 MILLER 23-JAN-82 10
PL/SQL procedure successfully completed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Tigger
>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE OR REPLACE TRIGGER update_trigger
after update
of ename
on t_emp
begin
dbms_output.put_line(' msg from Trigger: Row updated');
end;
/
>>>>>>
SQL> update t_emp
2 set ename = 'SMITH' WHERE ENAME = 'ROBIN';
msg from Trigger: Row updated
1 row updated.
>>>>>>>>>>>>>>>>>>>>
No comments:
Post a Comment