Oracle PL/SQL/SQL Plus/Accept
Содержание
- 1 ACCEPT command creates a variable of datatype CHAR by default
- 2 Accept input and pass into a query
- 3 ACCEPT variable value from user input
- 4 Read user input and use if to determine which table to print
- 5 Read value from prompt and output with pl/sql
- 6 Use accept to get user input value and use them in a select statement
- 7 Using the ACCEPT command
ACCEPT command creates a variable of datatype CHAR by default
<source lang="sql">
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;
</source>
Accept input and pass into a query
<source lang="sql">
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>
</source>
ACCEPT variable value from user input
<source lang="sql">
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;
</source>
Read user input and use if to determine which table to print
<source lang="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.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.
</source>
Read value from prompt and output with pl/sql
<source lang="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("¬_can",can, 60); 5 DBMS_OUTPUT.PUT_LINE(can); 6 END; 7 /
SQL> SQL>
</source>
Use accept to get user input value and use them in a select statement
<source lang="sql">
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;
</source>
Using the ACCEPT command
<source lang="sql">
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;
</source>