IMPLICIT CURSOR
DELETE
begin
delete from worker where deptno=20;
delete from worker where deptno=90;
delete from worker where deptno=10; // this is taken as SQL in the next line
if(sql%found) then
dbms_output.put_line('The deleted rows are'||SQL%ROWCOUNT);
else
dbms_output.put_line('There is no such department');
end if;
end;
/
output:
The deleted rows are 3 ( there are 3 rows that has deptno as 10)
INSERT
begin
insert into worker(empno,ename,sal) values(3489,'joe',2300);
if(sql%found) then
dbms_output.put_line('A new row has been inserted');
end if;
if(sql%notfound) then
dbms_output.put_line('Prob during insertion');
end if;
end;
/
A new row has been inserted
PL/SQL procedure successfully completed
Similarly we can use update…
Using loops
SQL> begin
2 for indx in 1..3
3 loop
4 delete from worker where sal>1000;
5 if(sql%found) then
6 dbms_output.put_line('The deleted rows are'||SQL%ROWCOUNT);
7 else
8 dbms_output.put_line('Already deleted');
9 end if;
10 end loop;
11 end;
12 /
Output:
The deleted rows are20
Already deleted
Already deleted
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> begin
2 insert into worker(empno,ename,sal) values(3489,'joe',2300);
3 insert into worker(empno,ename,sal) values(1234,'vithya',3400);
4 insert into worker(empno,ename,sal) values(4689,'kar',7890); //this is taken as SQL in the next line
if(sql%found) then
5 dbms_output.put_line(sql%rowcount);
6 else
7 dbms_output.put_line('Prob during insertion');
8 end if;
9 end;
10 /
1
PL/SQL procedure successfully completed.
SQL> declare
2 myemp worker%rowtype;
3 Begin
4 select *into myemp from worker where ename='smith';
5 if(sql%found) then
6 dbms_output.put_line(sql%rowcount);
7 elsif(SQL%notfound)
8 then
9 dbms_output.put_line('error'); /there can be if elsif with out else part
10 end if;
11 end;
12 /
/exception thrown... if no rows selected.... so ‘error’ not printed as in 9th statement /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
SQL> declare
2 myemp worker%rowtype;
3 Begin
4 select *into myemp from worker where ename='SMITH'; /use into clause for select statement here otherwise it will show error
5 if(sql%found) then
6 dbms_output.put_line(sql%rowcount);
7 end if;
8 end;
9 /
Output:
1
PL/SQL procedure successfully completed.
EXPLICIT CURSOR
SQL> declare
2 cursor c is select *from worker where deptno=10;
3 c1 c%rowtype;
4 begin
5 open c;
6 loop
7 fetch c into c1;
8 if(c%found) then
9 dbms_output.put_line(c%rowcount);
10 else
11 exit;
12 end if;
13 end loop;
14 close c;
15 end;
16 /
1
2
3
SQL> declare
2 cursor c is select *from worker where deptno=10;
3 c1 c%rowtype;
4 begin
5 open c;
6 loop
7 fetch c into c1;
8 if(c%found) then
9 dbms_output.put_line(c%rowcount);
10 dbms_output.put_line(c1.ename);
11 else
12 exit;
13 end if;
14 end loop;
15 close c;
16 end;
17 /
1
CLARK
2
KING
3
MILLER
PL/SQL procedure successfully completed.
1 declare
2 cursor c is select *from worker where deptno=10;
3 c1 c%rowtype;
4 begin
5 open c;
6 dbms_output.put_line(c%rowcount);
7 end;
Output:
0
SQL> declare
2 cursor c is select *from worker where deptno=10;
3 c1 c%rowtype;
4 begin
5 open c;
6 loop
7 fetch c into c1;
8 if(c%found) then
9 dbms_output.put_line(c%rowcount);dbms_output.put_line(c.ename);
10 else
11 exit;
12 end if;
13 end loop;
14 close c;
15 end;
16 /
dbms_output.put_line(c%rowcount);dbms_output.put_line(c.ename);
*
ERROR at line 9:
ORA-06550: line 9, column 58:
PLS-00225: subprogram or cursor 'C' reference is out of scope
ORA-06550: line 9, column 35:
PL/SQL: Statement ignored
C is the cursor pointing to the each fields in a row of a table and C1 is like temporary storage here C is fetched to C1 so C1.ename works not c.ename …
USING CURSOR TO FETCH EACH ROWS TO A RECORD
SQL> declare
2 type rec is record(n worker.empno%type,name worker.ename%type,sal worker.sal%type);
3 r rec;
4 cursor c is select empno,ename,sal from worker where ename='SMITH';
5 begin
6 open c;
7 fetch c into r;
8 if(c%found) then
9 dbms_output.put_line(c%rowcount);
10 dbms_output.put_line(r.n||' '||r.name||' '||r.sal);
11 end if;
12 end;
13 /
1
7369 SMITH 3500
PL/SQL procedure successfully completed.
1 declare
2 type rec is record(name emp.ename%type,salary emp.sal%type);
3 cursor c is select ename,sal from emp where deptno=10;
4 r rec;
5 begin
6 open c;
7 loop
8 fetch c into r;
9 if(c%found) then
10 dbms_output.put_line(c%rowcount);
11 dbms_output.put_line(r.name ||' '||r.salary);
12 else
13 exit;
14 end if;
15 end loop;
16 close c;
17* end;
SQL> /
1
CLARK 2450
2
KING 5000
3
karthik 1300
USING CURSOR FOR LOOP
1 DECLARE
2 CURSOR C IS SELECT ENAME,SAL,DEPTNO FROM EMP;
3 REC C%ROWTYPE; // R is of rowtype Cursor C
4 BEGIN
5 FOR REC IN C
6 LOOP
7 IF(REC.DEPTNO=10) THEN
8 DBMS_OUTPUT.PUT_LINE(REC.ENAME ||' '||REC.SAL);
9 END IF;
10 END LOOP;
11* END;
SQL> /
CLARK 2450
KING 5000
karthik 1300
open,fetch,close everything did implicitly here...
1 DECLARE
2 CURSOR C IS SELECT ENAME,SAL,DEPTNO FROM EMP;
3 BEGIN
4 FOR REC IN C
5 LOOP
6 IF(REC.DEPTNO=10) THEN
7 DBMS_OUTPUT.PUT_LINE(REC.ENAME ||' '||REC.SAL);
8 END IF;
9 END LOOP;
10* END;
SQL> /
CLARK 2450
KING 5000
karthik 1300
in the above case statement 3 REC EMP%ROWTYPE is missing but still it works fine as it invokes REC implicitly from previous execution
Further simplification...
1 BEGIN
2 FOR REC IN (SELECT ENAME,SAL,DEPTNO FROM EMP)
3 LOOP
4 IF(REC.DEPTNO=10) THEN
5 DBMS_OUTPUT.PUT_LINE(REC.ENAME ||' '||REC.SAL);
6 END IF;
7 END LOOP;
8* END;
SQL> /
CLARK 2450
KING 5000
karthik 1300
CURSOR WITH PARAMETERS
1 DECLARE
2 CURSOR C(INPUT NUMBER) IS SELECT COUNT(*) CNT FROM EMP WHERE DEPTNO=INPUT;
3 R C%ROWTYPE;
4 BEGIN
5 OPEN C(10);
6 FETCH C INTO R;
7 DBMS_OUTPUT.PUT_LINE('No of emp in deptno 10 is ' ||R.CNT);
8 CLOSE C;
9 OPEN C(20);
10 FETCH C INTO R;
11 DBMS_OUTPUT.PUT_LINE('No of emp in deptno 20 is ' ||R.CNT);
12 CLOSE C;
13* END;
SQL> /
No of emp in deptno 10 is 3
No of emp in deptno 20 is 5
LOCKING
1.ROW LEVEL
2.TABLE LEVEL
ROW LEVEL
FOR UPDATE [OF COLUMN] [NOWAIT]
refer pg no.49 in book
SESSION 1:
SELECT *FROM EMP WHERE DEPTNO=10 FOR UPDATE NOWAIT;
SESSION 2: it's not possi for us to update emp details of deptno=10 till session 1 releases lock
TABLE LEVEL
1.SHARE - This mode is for read only on entire table.we can not make changes to table.
2.SHARE UPDATE - This mode is used to lock the selected rows for update.This mode acquires lock on selected rows only,not entire table.Other user can have lock on other rows on the same table but not on the rows you have locked.
3.EXCLUSIVE - High priority mode if this lock is enabled then one cant do nothing on the table.
SQL> LOCK TABLE STUDENT IN SHARE MODE;
Table(s) Locked.
SQL> LOCK TABLE STUDENT IN SHARE UPDATE MODE;
Table(s) Locked.
SQL> LOCK TABLE STUDENT IN EXCLUSIVE MODE;
WHERE CURRENT OF
• should be used only with cursors and locked with for update option
is the latest craze. for more info explore the link above...
