Implicit and Explicit Cursors : Explicit Cursor « Cursor « Oracle PL / SQL

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

Implicit and Explicit Cursors

  

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> CREATE TABLE JOBS (
  2    JOB_ID      VARCHAR2 (10)  NOT NULL,
  3    JOB_TITLE   VARCHAR2 (35)  CONSTRAINT JOB_TITLE_NN   NOT NULL,
  4    MIN_SALARY  NUMBER (6),
  5    MAX_SALARY  NUMBER (6),
  6    CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ;

Table created.

SQL>
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'DBA', 'President', 20000, 40000);

1 row created.

SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'PROG', 'Administration Vice President', 15000, 30000);

1 row created.

SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'MGR', 'Administration Assistant', 3000, 6000);

1 row created.

SQL>
SQL>
SQL>
SQL> declare
  2    cursor cursorValue
  3    is select e.last_name name, e.salary
  4         from employees e, jobs j
  5         order by salary;
  6
  7    name       varchar2(200);
  8    salary     number(9,2);
  9  begin
 10    for c1 in (select j.job_title, j.min_salary, j.max_salary,
 11                      avg(e.salary) avg_salary
 12                 from employees e, jobs j
 13                where e.job_id = j.job_id
 14                group by j.job_title, j.min_salary, j.max_salary
 15                order by j.job_title) loop
 16      dbms_output.put_line(c1.job_title||'s, average $'||c1.avg_salary);
 17    end loop;
 18
 19    open cursorValue;
 20    fetch cursorValue into name, salary;
 21    dbms_output.put_line(chr(13) || chr(13));
 22    dbms_output.put_line('cursorValue');
 23    while cursorValue%FOUND loop
 24      dbms_output.put_line(name || ' makes $' || salary);
 25      fetch cursorValue into name, salary;
 26    end loop;
 27    close cursorValue;
 28  end;
 29  /
Administration Assistants, average $27500
Administration Vice Presidents, average $21000
Presidents, average $20000

cursorValue
Wells makes $20000
Java makes $20000
Beck makes $20000
Viper makes $20000
Wells makes $20000
Oracle makes $20000
Java makes $20000
Beck makes $20000
Viper makes $20000
Oracle makes $20000
Viper makes $20000
Beck makes $20000
Java makes $20000
Oracle makes $20000
Wells makes $20000
Bliss makes $24000
Bliss makes $24000
Bliss makes $24000
Kyte makes $25000
Kyte makes $25000
Kyte makes $25000
Lawson makes $30000
Lawson makes $30000
Lawson makes $30000

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employees;

Table dropped.

SQL> drop table jobs;

Table dropped.

SQL>
SQL> --

   
  

Related examples in the same category