替代变量与SQL*Plus环境设置

--============================

-- 替代变量与SQL*Plus环境设置

--============================

 

 

1.SQL与PL/SQL代码终止符

    SQL代码以";"来表示代码的终止

    PL/SQL代码以"."来表示代码的终止，以"/"表示代码的执行

        scott@ORCL> select * from emp where empno=7788;

 

             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

              7788 SCOTT      ANALYST         7566 19-APR-87       3100                    20

 

        scott@ORCL> declare v_ename varchar2(10);

          2  begin

          3  select ename into v_ename from emp where empno=7788;

          4  dbms_output.put_line('Employee Name: '||v_ename);

          5  exception

          6      when no_data_found then

          7      dbms_output.put_line('There is no employee');

          8  end;

          9  .

        scott@ORCL> /

        Employee Name: SCOTT

 

        PL/SQL procedure successfully completed.

     

2.替代变量

   

    &变量名、&&变量名

    执行时，如果&变量名，没有赋值的话，会提示输入变量值

 

        scott@ORCL> select * from emp where empno=&no;

        Enter value for no: 7788

        old   1: select * from emp where empno=&no

        new   1: select * from emp where empno=7788

 

             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

              7788 SCOTT      ANALYST         7566 19-APR-87       3100                    20

 

        scott@ORCL> save /u01/bk/scripts/select_empno.sql

        Created file /u01/bk/scripts/select_empno.sql

        scott@ORCL> @/u01/bk/scripts/select_empno.sql

        Enter value for no: 7788

        old   1: select * from emp where empno=&no

        new   1: select * from emp where empno=7788

 

             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

              7788 SCOTT      ANALYST         7566 19-APR-87       3100                    20

 

    set verify 的使用        

        scott@ORCL> set verify off   --使用set verify off来关闭替代变量使用时的提示，即执行SQL语句前不显示使用的变量值

        scott@ORCL> @/u01/bk/scripts/select_empno.sql

        Enter value for no: 7788

 

             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

              7788 SCOTT      ANALYST         7566 19-APR-87       3100                    20    

 

    对于字符型和日期型数据，替代变量要加单引号( ' ' )，将变量括起来

        select * from emp where job='&job'

 

    &&  对输入变量的再次引用，同时会保存变量的值

        scott@ORCL> set serveroutput on;

        scott@ORCL> begin

          2  dbms_output.put_line('The num is '||&&num);   --使用双&&保存了变量num的值

          3  dbms_output.put_line('The second num is '||&num);  --因此第二次输出变量num也是

          4  end;

          5  /

        Enter value for num: 10

        The num is 10

        The second num is 10

   

    替代变量的设置

        set define character    --修改缺省的替代变量符号为其他符号，但不能使用数字和空格

        set define on           --启用替代变量

        set define off          --关闭替代变量

       

        scott@ORCL> set define off

        scott@ORCL> @/u01/bk/scripts/select_empno.sql

        SP2-0552: Bind variable "NO" not declared.

   

 

3.  DEFINE  定义变量

        define job=SALESMAN

        select * from emp where job='&job'

 

    DEFINE 查看变量

        define

        define varname

 

    ACCEPT  定义替代变量

        格式：accept 变量名［prompt 正文］[HIDE]

 

 

4.sql plus 环境设置

    set 设置环境变量

        如set linesize 200

 

    show 显示环境变量

        如show all

        show linesize

 

    sql plus 环境配置文件

        配置文件：

            $ORACLE_HOME/sqlplus/admin/glogin.sql

            安装sql plus时创建的，卸载sql plus时会删除

 

    用户配置文件：

        文件名为login.sql，位置可任意放置，启动sql plus 时所在目录下有login.sql

        就会按设置进行环境配置，否则其它目录下的login.sql不理采。

        为了使用的方便一般会放到oracle用户的家目录

 

    set echo    @|start 运行脚本时，是否显示脚本内容

        set echo on/off

       

        scott@ORCL> set echo on;

        scott@ORCL> @/u01/bk/scripts/select_empno.sql

        scott@ORCL> select * from emp where empno=&no

          2  /

        Enter value for no: 7788

 

             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

              7788 SCOTT      ANALYST         7566 19-APR-87       3100                    20

 

        scott@ORCL> set echo off;      

 

    set feedback    设置脚注

            查询时返回多少行，会有提示，feedback控制提示显示

            可以是ON、OFF、n自然数，默认为

 

            set feedback on/off

            set feedback 10

 

    set heading {on/off}    控制是否显示列名

 

    set linesize    设置每行字符数

 

    set autocommit {on / off }  设置是否自动提交

 

    set long {80 ｜n}   设置查看长字符字段时的显示宽度

 

    column  设置列格式

        col ename format a15

 

    通用的SQL*Plus环境变量设置

        源自Tom大师：Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions   

 

        define _editor=vi

        set serveroutput on size 1000000

        set trimspool on

        set long 5000

        set linesize 180

        set pagesize 9999

        column plan_plus_exp format a80

        column global_name new_value gname

        set termout off

        define gname=idle

        column global_name new_value gname

        select lower(user) || '@' || substr( global_name, 1,

        decode( dot, 0, length(global_name), dot-1) ) global_name

        from (select global_name, instr(global_name,'.') dot from global_name );

        set sqlprompt '&gname> '

        set termout on

   

5.更多参考

有关SQL请参考

        SQLPlus常用命令

        SQL 基础--> 子查询

        SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

 

    有关PL/SQL请参考

        PL/SQL --> 语言基础

        PL/SQL --> 函数

        PL/SQL --> 异常处理

        PL/SQL --> 存储过程

        PL/SQL --> 流程控制