Oracle cursor variables

Question

I have this Oracle code that I need to convert in SQL Server but need help understanding what exactly it is doing . Since I have always avoided cursors it is still a mystery to me how they are used . please see the code below . This is placed in an insert trigger

CURSOR c1(table_name1 IN VARCHAR2)  
IS
    SELECT 
       a.begin, a.end, a.isnotactive, a.isactive, MIN(g.age) minage       
    FROM alltables a
    LEFT OUTER JOIN people g ON (g.ageid  = a.ageid)
       WHERE table_name = table_name1

    c1x_rec c1%ROWTYPE; 
    c1_rec c1%ROWTYPE; 

I am particularly unsure about the following 3 lines . What exactly is it doing ? Where does the table_name1 gets its value from ?

WHERE table_name = table_name1

    c1x_rec c1%ROWTYPE; 
    c1y_rec c1%ROWTYPE; 

Answer 1

• OPEN c1(table_name1) - this will open the cursor
• FETCH c1 INTO variable - this is will fetch data into variable

You need to create variable, that must match with your select statement in your cursor, to fetch the data. For this, you use %ROWTYPE - that attribute provides a record type that represents a row. For example, your variables c1x_rec, c1y_rec have begin, end, isnoactive, isactive, minage fields, with each field declared as equivalent column type in alltables or people table.