в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:39Z

1 версия

Новая страница

==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("&not_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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/SQL Plus/Accept - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия