Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Variable — различия между версиями

Текущая версия на 10:03, 26 мая 2010

Содержание

1 Bind variables can be declared in your SQL*Plus script,Preface a bind variable with a colon to reference it in a PL/SQL block
2 Bind variables can even be referenced by SQL queries
3 Check pre-defined variables
4 Define variable and print its value out
5 Define variable and set value
6 Define variable and use it in sql statement
7 Print variable out
8 Undefine variable
9 Use defined-variable in update set statement
10 Use "variable command" to define variable to reference cursor

Bind variables can be declared in your SQL*Plus script,Preface a bind variable with a colon to reference it in a PL/SQL block

SQL>
SQL> SET ECHO ON
SQL> 
SQL> VARIABLE  s_table_name  varchar2(30)
SQL>
SQL> 
SQL> BEGIN
  2    :s_table_name := "emp";
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>

Bind variables can even be referenced by SQL queries

SQL>
SQL> SET ECHO ON
SQL> 
SQL> VARIABLE  s_table_name  varchar2(30)
SQL>
SQL> 
SQL> BEGIN
  2    :s_table_name := "emp";
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> 
SQL> SELECT index_name
  2    FROM user_indexes
  3   WHERE table_name = :s_table_name;
no rows selected
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(:s_table_name);
  3  END;
  4  /
emp
PL/SQL procedure successfully completed.
SQL>

Check pre-defined variables

SQL>
SQL> def x
DEFINE X               = "7500" (CHAR)
SQL> def
DEFINE _DATE           = "" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR)
DEFINE _USER           = "sqle" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production" (CHAR)
DEFINE _O_RELEASE      = "1002000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
DEFINE X               = "7500" (CHAR)
SQL> undefine x
SQL>

Define variable and print its value out

SQL> VARIABLE x NUMBER
SQL> BEGIN
  2     :x := 1;
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL> PRINT :x;
         X
----------
         1
SQL>
SQL>

Define variable and set value

SQL>
SQL> SET ECHO ON
SQL>
SQL> VARIABLE ename VARCHAR2(30)
SQL> EXECUTE :ename := "Mike";
PL/SQL procedure successfully completed.
SQL> SELECT :ename FROM dual;
Mike
1 row selected.
SQL>
SQL>

Define variable and use it in sql statement

SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      startDate DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> select empno, ename, sal from emp where deptno = 10;
     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300
SQL> variable empno number
SQL> variable ename varchar2(20)
SQL> variable sal number
SQL>
SQL> exec :empno := 7934; :ename := "MILLER"; :sal := 1300;
PL/SQL procedure successfully completed.
SQL>
SQL> select empno, ename, sal
  2  from emp
  3  where empno = :empno
  4    and ename = :ename
  5    and sal = :sal
  6    for update nowait
  7  /
     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL>

Print variable out

SQL> SET ECHO ON
SQL>
SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := "Brighten the corner where you are";
PL/SQL procedure successfully completed.
SQL> PRINT my_bind_var
Brighten the corner where you are
SQL>
SQL>

Undefine variable

SQL>
SQL> UNDEFINE user_name
SQL> VARIABLE l_user VARCHAR2(30)
SQL> EXECUTE :l_user := "&user_name";
Enter value for user_name:
PL/SQL procedure successfully completed.
SQL> SELECT "User " || :l_user || " has " || TO_CHAR(COUNT(*)) || " tables."
  2  FROM all_tables
  3  WHERE owner = UPPER(:l_user);
User  has 0 tables.
1 row selected.
SQL>

Use defined-variable in update set statement

SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      startDate DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> select empno, ename, sal from emp where deptno = 10;
     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300
SQL> variable empno number
SQL> variable ename varchar2(20)
SQL> variable sal number
SQL>
SQL> exec :empno := 7934; :ename := "MILLER"; :sal := 1300;
PL/SQL procedure successfully completed.
SQL> update emp
  2  set ename = :ename, sal = :sal
  3  where empno = :empno;
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL>

Use "variable command" to define variable to reference cursor

SQL>
SQL> set echo on
SQL>
SQL> create table t as select * from all_users;
Table created.
SQL>
SQL> variable x refcursor
SQL>
SQL> begin
  2      open :x for select * from t;
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
15 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> print x
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
PLSQL                                  73 24-JUL-08
sqle                                 36 28-JUN-08
FLOWS_020100                           35 07-FEB-06
FLOWS_FILES                            34 07-FEB-06
HR                                     33 07-FEB-06
MDSYS                                  32 07-FEB-06
ANONYMOUS                              28 07-FEB-06
XDB                                    27 07-FEB-06
CTXSYS                                 25 07-FEB-06
DBSNMP                                 23 07-FEB-06
TSMSYS                                 20 07-FEB-06
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DIP                                    18 07-FEB-06
OUTLN                                  11 07-FEB-06
SYSTEM                                  5 07-FEB-06
SYS                                     0 07-FEB-06
15 rows selected.
SQL>
SQL> drop table t;
Table dropped.

Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Variable — различия между версиями

Текущая версия на 10:03, 26 мая 2010

Содержание

Bind variables can be declared in your SQL*Plus script,Preface a bind variable with a colon to reference it in a PL/SQL block

Bind variables can even be referenced by SQL queries

Check pre-defined variables

Define variable and print its value out

Define variable and set value

Define variable and use it in sql statement

Print variable out

Undefine variable

Use defined-variable in update set statement

Use "variable command" to define variable to reference cursor

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 10:03, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 10:03, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)