Cursors with parameters (Oracle PL/SQL)

What are cursors?
Cursor is a SELECT statement that is defined within the declaration section of your PLSQL code.
A cursor contains information on a select statement and the rows of data accessed by it.
A cursor can hold more than one row, but can process only one row at a time.

Let’s look at an example about cursors:

Users&Profession - Tables

Cursor with parameters example (with useful comments, explaining code):

declare
  --provides a record type that represents a row in the "profession" table
  job profession%rowtype;
  --provides a record type that represents a row in the "users" table
  user users%rowtype;

  --cursor with parameters:
  cursor u(v_job profession.profession_id%type) is
    select u.* from users u, profession p
    where u.profession_id = p.profession_id
    and v_job=p.profession_id;
  --simple cursor:
  cursor p is
    select * from profession;

begin
  open p; --opening cursor p
  dbms_output.put_line('=> Jobs with salaries greater or equal to 1000:');
  dbms_output.put_line('');
  loop --starting outter loop
 --fetching cursor results into "job" variable which is declared in declare block
    fetch p into job;
    --setting loop exit statement:
    exit when p%notfound;
    if job.salary >= 1000 then
      --fetching u(param) cursor results...
      --doing this way, it implicitly open and close cursor:
      for user in u(job.profession_id) loop --starting inner loop
        --output of "username" attribute from "users" table
        dbms_output.put_line('::Username: ' || user.username);
        --output of "name" attribute from "profession" table
        dbms_output.put_line('::Profession: ' || job.name);
        --output of "salary" attribute from "profession" table
        dbms_output.put_line('::Salary: ' || job.salary);
        dbms_output.put_line('');
      end loop; --ending inner loop
    end if;
  end loop; --ending outter loop
  close p; --closing cursor p
end;

Results:

=> Jobs with salaries greater or equal to 1000:

::Username: Tintin
::Profession: Aplicational Support Technician
::Salary: 1000

Advertisement