CODEWITHSUNDEEP
sqloracle-databaseplsqlcursororacle-sqldeveloper
iamP
iamP

Reputation: 317

how to directly specify second parameter while opening a cursor when first parameter has a default value set

I came across this situation where I provide a default value for the first parameter of the cursor and simply declare the second parameter as shown in the code snippet. I want to skip inputting the first parameter value while opening the cursor and pass only the value for second parameter. Is there any way to do this ? I get error if I simply put a comma and second parameter value or just the second parameter value.

 SET SERVEROUTPUT ON;
    DECLARE 
        VEID TMP_EMPLOYEES.NAME%TYPE;
        CURSOR CURF(VID VARCHAR2:=5,vname varchar2) IS--default value set
        SELECT NAME FROM TMP_EMPLOYEES
        WHERE ID<VID and name like'%'||vname||'%';
    BEGIN 
        OPEN CURF('4','ra');-- I just want to pass 'ra'
        LOOP
        FETCH CURF INTO VEID;

        EXIT WHEN CURF%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VEID);
        END LOOP;
        CLOSE CURF;
        DBMS_OUTPUT.PUT_LINE('DONE');
    END;

Upvotes: 0

Views: 57

Answers (1)

Gasparen
Gasparen

Reputation: 81

Using named notation you can clarify which arguments you are passing to the cursor/procedure/function you are calling.

The following calls would generate the same result:

CURF('4', 'ra')
CURF(vid => '4', vname => 'ra')
CURF(vname => 'ra', vid => '4') -- Order of named arguments don't matter
CURF('4', vname => 'ra') -- Mixing ordered and named arguments

By using named notation you can omit arguments with default values i.e. CURF(vname => 'ra') will give the wanted result

Upvotes: 1

Related Questions