Cursor with parameter : Cursor parameters « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor parameters

Cursor with parameter

  
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL> declare
  2    cursor cursorValue (p_deptid in number)
  3    is select *
  4         from employees
  5        where department_id = p_deptid;
  6
  7    l_emp employees%rowtype;
  8  begin
  9    open cursorValue(30);
 10    loop
 11      fetch cursorValue into l_emp;
 12      exit when cursorValue%notfound;
 13      dbms_output.put('Employee id ' || l_emp.employee_id || ' is ');
 14      dbms_output.put_line(l_emp.last_name);
 15    end loop;
 16    close cursorValue;
 17
 18    dbms_output.put_line('Getting employees for department 90');
 19    open cursorValue(90);
 20    loop
 21      fetch cursorValue into l_emp;
 22      exit when cursorValue%notfound;
 23      dbms_output.put('Employee id ' || l_emp.employee_id || ' is ');
 24      dbms_output.put_line(l_emp.last_name);
 25    end loop;
 26    close cursorValue;
 27  end;
 28  /
Getting employees for department 90

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employees;

Table dropped.

SQL> --

   
  

Related examples in the same category