Oracle PL/SQL/SQL Plus/Accept

Материал из SQL эксперт
Перейти к: навигация, поиск

ACCEPT command creates a variable of datatype CHAR by default

  

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
--ACCEPT command creates a variable of datatype CHAR by default, 
--but datatypes of NUMBER or DATE can also be specified. 
--If you create a variable of datatype NUMBER or DATE, 
--you can also specify the FORMAT in which they must be entered:
ACCEPT p_date DATE FORMAT dd/mm/yy 
PROMPT "Enter date "
Enter date 01/01/01
SELECT *
    FROM emp
    WHERE hiredate >"&p_date";
 
 
 
drop table emp;



Accept input and pass into a query

   

SQL> set termout on
SQL>
SQL> ACCEPT continue_flag CHAR PROMPT "Do you wish to DROP the tables first (Y/N)?"
Do you wish to DROP the tables first (Y/N)?
SQL> define flag1 = "n"
SQL> SET TERMOUT OFF
SQL> COLUMN continue_flag NEW_VALUE continue_flag
SQL> SELECT LOWER("&continue_flag") continue_flag FROM dual;
old   1: SELECT LOWER("&continue_flag") continue_flag FROM dual
new   1: SELECT LOWER("") continue_flag FROM dual

C
-

1 row selected.
SQL> SET TERMOUT ON
SQL>
SQL> SET SERVEROUTPUT ON
SQL> PROMPT
SQL> BEGIN
  2    IF "&flag1" = "n" THEN
  3      DBMS_OUTPUT.PUT_LINE("You must answer either Y or N.");
  4      DBMS_OUTPUT.PUT_LINE("Please rerun the command file and answer correctly.");
  5    END IF;
  6  END;
  7  /
old   2:   IF "&flag1" = "n" THEN
new   2:   IF "n" = "n" THEN
You must answer either Y or N.
Please rerun the command file and answer correctly.
PL/SQL procedure successfully completed.
SQL>
SQL> --Let the calling script know that we were successful.
SQL> define flag2 = "Y"
SQL>
SQL>



ACCEPT variable value from user input

   

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
prompt C R E A T E   N E W   E M P L O Y E E   R E C O R D
prompt
prompt Enter the employee"s information:
prompt
accept l_ename char format a10 prompt "Last name: "
accept l_empno number format "9999" prompt "Employee #: "
accept l_sal number format "99999.99" prompt "Salary [1000]: " default "1000.00"
accept l_comm number format "99999.99" prompt "Commission % [0]: " default "0"
accept l_hired date format "mm/dd/yyyy" prompt "Hire date (mm/dd/yyyy): "
prompt List of available jobs:
select distinct job
  from emp
 order by job
/
accept l_job char format a9 prompt "Job: "
prompt List of managers and employee numbers:
select empno, ename
  from emp
 order by ename
/
accept l_mgr number format "9999" prompt "Manager""s Employee #: "
prompt List of department numbers and names:
select deptno, dname
  from dept
 order by deptno
/
accept l_dept number format "99" prompt "Department #: "
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (&l_empno, "&l_ename", "&l_job", &l_mgr,
        to_date("&l_hired","mm/dd/yyyy"), &l_sal, &l_comm, &l_dept)
/
drop table emp;



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.ru", "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.ru", "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.ru", "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.ru", 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 .ru", 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.ru", 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.ru", 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.ru", 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.



Read value from prompt and output with pl/sql

    
SQL> SET ECHO OFF
SQL> ACCEPT not_can CHAR PROMPT "Enter a table reference >"
SQL> DECLARE
  2     can VARCHAR2(60);
  3  BEGIN
  4     DBMS_UTILITY.CANONICALIZE("&not_can",can, 60);
  5     DBMS_OUTPUT.PUT_LINE(can);
  6  END;
  7  /

SQL>
SQL>



Use accept to get user input value and use them in a select statement

   

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
prompt Enter the employee"s information:
accept l_ename char format a10 prompt "Last name: "
accept l_empno number format "9999" prompt "Employee #: "
accept l_sal number format "99999.99" prompt "Salary [1000]: " default "1000.00"
accept l_comm number format "99999.99" prompt "Commission % [0]: " default "0"
accept l_hired date format "mm/dd/yyyy" prompt "Hire date (mm/dd/yyyy): "
accept l_job char format a9 prompt "Job: "
accept l_mgr number format "9999" prompt "Manager""s Employee #: "
accept l_dept number format "99" prompt "Department #: "
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (&l_empno, "&l_ename", "&l_job", &l_mgr,to_date("&l_hired","mm/dd/yyyy"), &l_sal, &l_comm, &l_dept);

drop table emp;



Using the ACCEPT command

  

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  MGR NUMBER(4),
                  HIREDATE DATE,
                  SAL NUMBER(7, 2),
                  COMM NUMBER(7, 2),
                  DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
-- Using the ACCEPT command
ACCEPT Colx PROMPT"Please enter column name:"
ACCEPT cond PROMPT"Please enter lowest salary value:"
SELECT empno, eName, &Colx
     FROM Emp
     WHERE &Colx > &cond
     ORDER BY &Colx;

drop table dept;
drop table emp;
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/SQL_Plus/Accept&oldid=1702»