What Does This Mean?

[arpan]

In the following procedure, what do the 2 lines (in blue color) do or what do they mean?

CREATE OR REPLACE PROCEDURE TryParamSP4(
iID IN Try.ID%TYPE)
IS
CURSOR CursorTry (curID int)
IS
SELECT * FROM Try WHERE ID=iID;
curTry CursorTry%ROWTYPE;
BEGIN
...............
...............
...............
END TryParamSP4;

Also can anyone suggest at least one website, if not a few, which provides tutorials in using Cursors in Oracle?

Thanks,

Arpan

 

[Abhivyakti]

hi arpan

try visiting sqlcourse.com or sqlcourse2.com for tutorials

abhivyakti

 

[carp]

CURSOR CursorTry (curID int)
IS
SELECT * FROM Try WHERE ID=iID;

is defining an explicit cursor that accepts an argument (although I think it's a little honked up; I believe it SHOULD be
CURSOR CursorTry (curID int)
IS
SELECT * FROM Try WHERE ID=curID

curTry CursorTry%ROWTYPE;
is defining a record variable. In this case it will have a structure identical to a row returned by the cursor CursorTry (and will, since that cursor is doing a select * from Try, be identical to the column structure of the Try table).

 

[arpan]

Hi Carp,

Thanks for your help. One very important point you have put forward is that the actual SELECT statement should be

CURSOR CursorTry (curID int)
IS
SELECT * FROM Try WHERE ID=curID;

& not

CURSOR CursorTry (curID int)
IS
SELECT * FROM Try WHERE ID=iID;

This is one point I am not understanding. Why are you saying that in the WHERE clause, it should be curID & not iID? Actually this is the entire procedure (as such, the procedure is working fine):

CREATE OR REPLACE PROCEDURE TryParamSP1(
iID IN Try.ID%TYPE)
IS
v_FName Try.FName%TYPE;
v_LName Try.LName%TYPE;
CURSOR CursorTry (curID int)
IS
SELECT FName,LName INTO v_FName,v_LName FROM Try WHERE ID=curID;
BEGIN
FOR curTry IN CursorTry(iID) LOOP
DBMS_OUTPUT.PUT_LINE('FName : ' || curTry.FName);
DBMS_OUTPUT.PUT_LINE('LName : ' || curTry.LName);
END LOOP;
END TryParamSP1;

As you can see in the SELECT statement, I have used curID in the WHERE clause but I don't understand how curID gets assigned the value of the parameter that is passed to the procedure? For e.g. if the above procedure is executed as follows (I am using an Oracle tool named TOAD to view the output):

BEGIN
TryParamSP1(4);
END;

then how come curID becomes equal to 4 (which is passed as the parameter) & retieves the correct record? 4 is being passed to the variable iID & not curID, isn't it? Am I wrong? If curID in the WHERE clause is substituted with iID, then it is OK since 4 is being passed to the variable iID & thus iID now equals to 4 & thus the correct record gets retrieved. But how the correct record gets retrieved when curID is used in the WHERE clause (as shown in the above procedure & also as you have suggested)?
Please just clarify this doubt.

Thanks once again,

Regards,

Arpan

 

[carp]

The reason I said that was because you were not providing us with the rest of the code. Since your original post did not show an IID, there was no way to tell what IID was - and given the frequency of typos in posted code, I assumed that was what I was looking at.