Read user input and use if to determine which table to print : Accept « SQL Plus « Oracle PL / SQL

1. Oracle PL / SQL
2. SQL Plus
3. Accept

Read user input and use if to determine which table to print

   
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', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL> CREATE TABLE departments
  2  (department_id           number(10)            not null,
  3   department_name      varchar2(50)      not null,
  4   CONSTRAINT departments_pk PRIMARY KEY (department_id)
  5  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 1,             'Data Group' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 2,             'Purchasing' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 3,             'Call Center' );

1 row created.

SQL>
SQL> insert into departments ( department_id, department_name )
  2                    values( 4,             'Communication' );

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> set echo off
SQL> set serveroutput on
SQL> set verify off
SQL> set define '&'
SQL>
SQL> prompt

SQL> prompt 'What table would you like to see?'
'What table would you like to see?'
SQL> accept tab prompt '(L)ocations, (D)epartments, or (E)mployees : '
(L)ocations, (D)epartments, or (E)mployees : prompt
SQL>
SQL> declare
  2    type refcur_t is ref cursor;
  3    refcur refcur_t;
  4
  5    type sample_rec_type is record (
  6      id          number,
  7      description varchar2(200));
  8    sample sample_rec_type;
  9
 10    selection varchar2(1) := upper(substr('&tab',1,1));
 11  begin
 12    if selection = 'D' then
 13      open refcur for
 14        select department_id, department_name
 15          from departments
 16         where rownum < 11
 17         order by 1;
 18      dbms_output.put_line('Sample DEPARTMENT data:');
 19
 20    elsif selection = 'E' then
 21      open refcur for
 22        select employee_id, last_name
 23          from employees
 24         where rownum < 11
 25          order by 1;
 26      dbms_output.put_line('Sample EMPLOYEE data:');
 27
 28    else
 29      dbms_output.put_line('Please enter ''D'', or ''E''.');
 30      return;
 31    end if;
 32
 33    fetch refcur into sample;
 34
 35    while refcur%FOUND loop
 36      dbms_output.put_line('#' || sample.id || ' is ' || sample.description);
 37      fetch refcur into sample;
 38    end loop;
 39    close refcur;
 40  end;
 41  /
Please enter 'D', or 'E'.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table departments cascade constraints;

Table dropped.

SQL> drop table employees cascade constraints;

Table dropped.

   
    
  

Related examples in the same category