PL/SQL7——变量与SQL*Plus环境设置

转自：http://blog.csdn.net/robinson_0612/article/details/6084376

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

 

SQL DEFINE variable = value

说明： variable 指变量名 value 指变量值

定义好了变良值后, 执行绑定变量的SQL语句时不再提示输入变量

使用DEFINE的例子：

SQL DEFINE dname = sales

SQL DEFINE dname

DEFINE dname = “sales” (CHAR)

SQL select name from dept where lower(name)= dname;

NAME

sales

sales

SQL UNDEFINE dname

SQL DEFINE dname

Symbol dname is UNDEFINED

    ACCEPT  定义替代变量

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

 接收和定义变量的SQL*PLUS命令

ACCEPT

DEFINE UNDEFINE

ACCEPT的语法和例子

SQL ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]

说明： variable 指变量名 datatype 指变量类型，如number,char等 format 指变量显示格

式 prompt text 可自定义弹出提示符的内容text hide 隐藏用户的输入符号

使用ACCEPT的例子：

ACCEPT p_dname PROMPT Provide the department name:

ACCEPT p_salary NUMBER PROMPT Salary amount:

ACCEPT pswd CHAR PROMPT Password: HIDE

ACCEPT low_date date format YYYY-MM-DD PROMPT“Enter the low date range(YYYY-MM-DD):”

sqlplus定义变量区别

oracle定义变量（常量）常用：declare、define、variable

1）define、variable用于sqlplus中，在整个sqlplus连接中都生效，而declare用于pl/sql中。

SQL show user

USER is "TEST"

SQL var

SP2-0568: No bind variables declared.

SQL define

DEFINE _DATE           = "20-9月 -10" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER           = "TEST" (CHAR)

DEFINE _PRIVILEGE      = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _EDITOR         = "ed" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE      = "1002000400" (CHAR)

SQL var x number;          ——定义变量x，类型是number

SQL exec :x:=1;                 ——给变量赋值1

PL/SQL procedure successfully completed.

SQL print x

         X

----------

         1

SQL define y=10;

SQL define

DEFINE _DATE           = "20-9月 -10" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER           = "TEST" (CHAR)

DEFINE _PRIVILEGE      = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _EDITOR         = "ed" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE      = "1002000400" (CHAR)

DEFINE Y               = "10" (CHAR)

SQL disconn

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL print x

SP2-0640: Not connected

SP2-0641: "PRINT" requires connection to server

SQL var x

variable   x

datatype   NUMBER

x变量值没了，但是变量定义让然存在

SQL define

DEFINE _DATE           = "20-9月 -10" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "" (CHAR)

DEFINE _USER           = "" (CHAR)

DEFINE _PRIVILEGE      = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _EDITOR         = "ed" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE      = "1002000400" (CHAR)

DEFINE Y               = "10" (CHAR)

define定义的y值仍然存在

SQL show serveroutput

serveroutput OFF

SQL set serveroutput on

SQL declare

2 m number :=1;

3 n number;

4 begin

5 select n1 into n from test where rownum=m;

6 dbms_output.put_line(n);

7 end;

8 /

1

PL/SQL procedure successfully completed.

2）variable（var）和define区别在于，前者用于绑定变量，后者是用于 或 进行变量替换。

var

SP2-0568: No bind variables declared.

var x number;

exec :x:=1;

PL/SQL procedure successfully completed.

SQL select * from test where rownum=:x;

        N1        N2

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

1                    2

SQL define rn=1;

define

DEFINE _DATE           = "20-9月 -10" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER           = "TEST" (CHAR)

DEFINE _PRIVILEGE      = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _EDITOR         = "ed" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE      = "1002000400" (CHAR)

DEFINE RN              = "1" (CHAR)

SQL select * from test where rownum=

old   1: select * from test where rownum= rn

new   1: select * from test where rownum=1

        N1         N2

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

         1          2

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

数据库小技能：PL/SQL中书写定义sql 触发器由数据库管理系统负责调用和执行，通过触发触发器所监听的事物来实现触发器的调用。 表级别的触发器（对于整个数据库表做监听） 行级别的触发器（对于表中的每一行做监听）
数据库小技能：PL/SQL（Procedure Language/SQL,过程化SQL语言）预备知识 过程化SQL语言（Procedural Language/SQL）是Oracle数据库对SQL语句的扩展：在普通SQL语句的使用上增加了编程语言的特点，把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。