PL/SQL Procedure simple practise

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Locked
username12245
Member
Posts: 25
Joined: Mon Apr 21, 2014 2:09 pm
Location: Germany

PL/SQL Procedure simple practise

Post by username12245 » Sun Sep 21, 2014 10:36 pm

Hi Tim.
I'm practising/relearning PL/SQL Programming. Trying to retrieve some columns using %ROWTYPE.
Using %TYPE I could select the desired columns but using %ROWTYPE I'm getting error
I think it is crazy to write proc for select * from emp. But, I thought of to test it with a procedure.

Getting this error
>> ORA-01422: exact fetch returns more than requested number of rows

Could you please correct me where I'm doing wrong.

Code: Select all


create or replace   procedure   tim_empinfo   as 

l_obj   emp%ROWTYPE;
CURSOR c_obj IS  SELECT * FROM  emp;

rec_cur_row   c_obj%rowtype;

begin
select * into  l_obj  from emp;

open c_obj;
      loop 
       fetch c_obj  into  rec_cur_row;
   exit  when  c_obj%notfound;
       end loop;
close c_obj;

for cur_rec in (select * from emp) 
      loop
       null;

dbms_output.put_line('employee no '  ||    l_obj.empno);
dbms_output.put_line('the name of employee is:'  ||  l_obj.ename);
dbms_output.put_line('job of the employee is:'  ||   l_obj.job);
dbms_output.put_line('salary of employee is:'   ||     l_obj.sal);

end loop;
end tim_empinfo;
/
And, Thank you to move the "Introduction to PL/SQL" link to the top in " PL/SQL" !

Thanks,
PB

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: PL/SQL Procedure simple practise

Post by Tim... » Sun Sep 21, 2014 11:16 pm

Hi.

See this:

Code: Select all

select * into  l_obj  from emp;
"l_obj" is a ROWTYPE. It holds a single row of data. In the above query, you have done a select of a whole table, not just one row! That is what is causing the error. The solution is to pull back a single row:

Code: Select all

select * into  l_obj  from emp WHERE empno = ?????;

OR

select * into  l_obj  from emp WHERE rownum = 1
Or to use BULK COLLECT INTO and return the multiple rows into a collection.

https://oracle-base.com/articles/9i/bul ... lk_collect

Cheers

Tim...
Tim...
About Me

username12245
Member
Posts: 25
Joined: Mon Apr 21, 2014 2:09 pm
Location: Germany

Re: PL/SQL Procedure simple practise

Post by username12245 » Mon Sep 22, 2014 5:28 pm

Hi Tim.
Thank you for the link !!! That helps me learn more.

I'm still unable to retrieve complete rows from the table.
Giving a try I could either get only 1 row from the table or no data.
i understood the rows are not getting incremented. Something wrong I'm doing by not incrementing the for loop.
Could you please advice me where I'm doing wrong.

Code: Select all

create or replace procedure tim_empinfo as 

l_obj emp%ROWTYPE;
CURSOR c_obj IS  SELECT * FROM emp;
rec_cur_row c_obj%rowtype;

begin
open c_obj;
    loop 
    fetch c_obj into rec_cur_row;
exit when c_obj%notfound;
end loop;

for cur_rec in (select * into l_obj from emp where rownum = 1) loop

dbms_output.put_line('employee no '|| l_obj.empno);
dbms_output.put_line('the name of employee is:'|| l_obj.ename);
dbms_output.put_line('job of the employee is:'|| l_obj.job);
dbms_output.put_line('salary of employee is:'|| l_obj.sal);

--l_obj:=l_obj+1; tried to increment rows this way but got error.

end loop;
end tim_empinfo;
/
Thanks :)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: PL/SQL Procedure simple practise

Post by Tim... » Mon Sep 22, 2014 6:20 pm

Hi.

You are using the cursor for loop incorrectly. The index variable (cur_rec) is the rowtype in this case.

for cur_rec in (select * from emp) loop
dbms_output.put_line('employee no '|| cur_rec.empno);
dbms_output.put_line('the name of employee is:'|| cur_rec.ename);
dbms_output.put_line('job of the employee is:'|| cur_rec.job);
dbms_output.put_line('salary of employee is:'|| cur_rec.sal);
end loop;

You are kind-of mixing several different methods together, which is why you are having problems. Check out the examples here:

https://oracle-base.com/articles/misc/i ... l-in-plsql

There is not such thing as incrementing the rows. In a cursor-for-loop, each row is processed in turn. If you manually code it with a FETCH, each FETCH returns the next row.

Cheers

Tim...
Tim...
About Me

username12245
Member
Posts: 25
Joined: Mon Apr 21, 2014 2:09 pm
Location: Germany

Re: PL/SQL Procedure simple practise

Post by username12245 » Mon Sep 22, 2014 7:47 pm

Thank you very much to correct to me Tim ! Now, i have the output :)

Yes, i went through this link
https://oracle-base.com/articles/misc/i ... l-in-plsql
and the below one
https://oracle-base.com/articles/misc/i ... -plsql.php

I go through them once again.

Could you please confirm me the below 2 Procedures are of Implicit Cursors

1) When a user enters department number the output should be employee name and salary.

Code: Select all

create or replace procedure getemp (pdeptno emp.deptno%type ) as

pename emp.ename%type;
psal emp.sal%type;

cursor  c1 is  select ename, sal from emp where deptno=pdeptno;

 begin
    open c1;
    loop 
    fetch c1 into pename,psal;
     exit when c1%notfound;
 
  DBMS_OUTPUT.PUT_LINE ('employee name is:' ||pename);
  DBMS_OUTPUT.PUT_LINE ('employee sal is:' ||psal);
  end loop;
  close c1;

end getemp;
/
***********************************
executing the above proc by giving some dept no

execute getemp (10);
2) You corrected my mistake here. Is the Cursor in this Procedure is called an Implicit Cursor?

Code: Select all

create or replace procedure tim_empinfo as 

l_obj emp%ROWTYPE;
CURSOR c_obj IS  SELECT * FROM emp;
rec_cur_row c_obj%rowtype;

begin
open c_obj;
    loop 
    fetch c_obj into rec_cur_row;
exit when c_obj%notfound;
end loop;

for cur_rec in (select * from emp) loop

dbms_output.put_line('employee no '|| cur_rec.empno);
dbms_output.put_line('the name of employee is:'|| cur_rec.ename);
dbms_output.put_line('job of the employee is:'|| cur_rec.job);
dbms_output.put_line('salary of employee is:'|| cur_rec.sal);

--l_obj:=l_obj+1; tried to increment rows this way but got error.

end loop;

end tim_empinfo;

username12982
Member
Posts: 10
Joined: Sun Sep 21, 2014 3:31 pm

Re: PL/SQL Procedure simple practise

Post by username12982 » Mon Sep 22, 2014 8:46 pm

Reckon you would be better off with a simple FOR loop.

:)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: PL/SQL Procedure simple practise

Post by Tim... » Mon Sep 22, 2014 8:55 pm

Hi.

1) I agree with SuperMack. This would be much simpler to read and work faster.

Code: Select all

create or replace procedure getemp (pdeptno emp.deptno%type ) as
begin
  for cur_rec in (select ename, sal from emp where deptno=pdeptno) loop
    DBMS_OUTPUT.PUT_LINE ('employee name is:' ||cur_rec.ename);
    DBMS_OUTPUT.PUT_LINE ('employee sal is:' ||cur_rec.sal);
  end loop;
end getemp;
/
2) An explicit cursor defines the cursor in the declaration section using the CURSOR keyword. That is an explicit cursor. You will probably also combine this with an explicit OPEN, FETCH and CLOSE.

If you don't do all that and instead use a SELECT INTO or a cursor FOR LOOP, like I showed you above, it is known as an implicit cursor, which is almost always faster.

You will see a lot of "old timers" program using OPEN, FETCH, CLOSE, but in the vast majority of cases it is the wrong thing to do as it is slower, uses more code and IMHO looks ugly. :)

Cheers

Tim...
Tim...
About Me

username12982
Member
Posts: 10
Joined: Sun Sep 21, 2014 3:31 pm

Re: PL/SQL Procedure simple practise

Post by username12982 » Tue Sep 23, 2014 7:51 pm

Haha Tim. I once worked at a place where the explicit cursors were everywhere. This was only a few years back, and nobody seemed to think it was odd. It was painful just reading through the code. :)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: PL/SQL Procedure simple practise

Post by Tim... » Tue Sep 23, 2014 8:33 pm

Hi.

In my current company the coding standard states that you *must* use explicit cursors. I do not follow that coding standard! :)

Cheers

Tim...
Tim...
About Me

Locked