CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
abdul Hafeez
abdul Hafeez

Reputation: 23

How to execute and get input of cursor in popup?

I want create a cusrsor in oracle sql developer in which it shows employee report according to input popup salary amount.

I have created cursor but i don't know how to run this cursor and how to get my input using popup from user?

declare
cursor c_emp is
select FIRST_NAME,SALARY,DEPARTMENT_ID from EMPLOYEES where 
SALARY>=10000;
v_name EMPLOYEES.FIRST_NAME%type;
v_sal EMPLOYEES.SALARY%type;
v_deptno EMPLOYEES.DEPARTMENT_ID%type;
 begin
 open c_emp;
    loop    
        fetch c_emp into v_name, v_sal, v_deptno;
        exit when c_emp%NOTFOUND;
        dbms_output.put_line(v_name||' '||v_sal||' '||v_deptno);
    end loop;
  close c_emp;
  end;

How to execute cursor and get salary input from user in popup?it will show each employee report.

Upvotes: 1

Views: 612

Answers (1)

APC
APC

Reputation: 146239

SQL Developer supports many (but not all) of the venerable SQL*Plus command set. The supported set includes the & syntax for identifying substitution variables. A substitution variable is a placeholder which prompts the user for input when they run the code.

So what you need to do is edit your code to use this:

declare
  cursor c_emp is
    select FIRST_NAME,SALARY,DEPARTMENT_ID 
    from EMPLOYEES
    where SALARY >= &min_salary; -- substitution variable

  v_name EMPLOYEES.FIRST_NAME%type;
  v_sal EMPLOYEES.SALARY%type;
  v_deptno EMPLOYEES.DEPARTMENT_ID%type;
begin
 open c_emp;
    loop    
        fetch c_emp into v_name, v_sal, v_deptno;
        exit when c_emp%NOTFOUND;
        dbms_output.put_line(v_name||' '||v_sal||' '||v_deptno);
    end loop;
  close c_emp;
end;

This will prompt the user to enter min_salary when they run this program. In Oracle SQL Developer you run this code like any other statement ctrl+enter or clicking the green arrow on the menu bar.

Substitution variables are placeholders: they are not stored in an addressable space. If you want to do that for any reason, you need to explicitly defined variable and assign the substitution variable to it:

l_min_salary number := &min_salary;

Then you would need to change the rest of the code to use that variable. Maybe like this:

declare
  cursor c_emp (p_min_sal number) is
    select FIRST_NAME,SALARY,DEPARTMENT_ID 
    from EMPLOYEES
    where SALARY >= p_min_sal; 

  l_min_salary number := &min_salary;-- substitution variable

  v_name EMPLOYEES.FIRST_NAME%type;
  v_sal EMPLOYEES.SALARY%type;
  v_deptno EMPLOYEES.DEPARTMENT_ID%type;
begin
 open c_emp (l_min_salary);
    loop    
        fetch c_emp into v_name, v_sal, v_deptno;
        exit when c_emp%NOTFOUND;
        dbms_output.put_line(v_name||' '||v_sal||' '||v_deptno);
    end loop;
  close c_emp;
end;

Upvotes: 1

Related Questions