Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Variable

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

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

   <source lang="sql">

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></source>


Bind variables can even be referenced by SQL queries

   <source lang="sql">

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></source>


Check pre-defined variables

   <source lang="sql">

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></source>


Define variable and print its value out

   <source lang="sql">

SQL> VARIABLE x NUMBER SQL> BEGIN 

 2     :x := 1;
 3  END;
 4  /

PL/SQL procedure successfully completed. SQL> PRINT :x; 

        X

        1

SQL> SQL></source>


Define variable and set value

   <source lang="sql">

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></source>


Define variable and use it in sql statement

   <source lang="sql">

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></source>


Print variable out

   <source lang="sql">

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></source>


Undefine variable

   <source lang="sql">

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></source>


Use defined-variable in update set statement

   <source lang="sql">

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></source>


Use "variable command" to define variable to reference cursor

   <source lang="sql">

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.</source>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/SQL_PLUS_Session_Environment/Variable&oldid=2778»