SQL，PL/SQL之2

最新推荐文章于 2021-04-03 00:30:48 发布

weixin_33768481

最新推荐文章于 2021-04-03 00:30:48 发布

阅读量163

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/cswggod/1183204

版权

1.Alternative Quote (q) Operator 引用操作符q

需要在字符串中使用单引号时，可用[],<>,{}。

推荐使用[]，例如

q'['s Job]'

SQL>SELECT department_name ||
      q'[, it's assigned Manager Id: ]'
       || manager_id
       AS "Department and Manager"
FROM hr.departments;

2.转义

Oracle的转义字符需要用ESCAPE函数来定义

SQL>create table test.t_char(a varchar2(20));

SQL>insert all
into test.t_char values('a_b')
into test.t_char values('acb')
into test.t_char values('a%b')
into test.t_char values('a''b')
into test.t_char values('a/b')
into test.t_char values('a\b')
into test.t_char values('%')
into test.t_char values('_')
into test.t_char values('a')
select * from dual;

#-----------------其中into test.t_char values('a''b')中 a''b

#-----------------表明将引号用引号转义

SQL>commit;

SQL>select * from test.t_char;

SQL>select * from test.t_char where a like 'a_b';
########查询像a[]b

SQL>select * from test.t_char where a like 'a\_b' escape '\';
#########查询a_b，其中'_'是关键字，用 '\'转义

--但是字符'还是需要用两个''来代替字符串中的单引号
--可以用其他字符，甚至是“'”、“_”、“%”来用作转义字符，但是一般都使用\，以免发生逻辑错误
----要搜索"/"或者"\"则必须使用与之相同的符号作为转义字符，否则无效

SQL> select * from t_char where a like 'a\/b' escape '\';
select * from t_char where a like 'a\/b' escape '\'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character

SQL> select * from t_char where a like 'a_/b' escape '_';
select * from t_char where a like 'a_/b' escape '_'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character

SQL> select * from t_char where a like 'a//b' escape '/';

A
--------------------
a/b

SQL> select * from t_char where a like 'a/\b' escape '/';
select * from t_char where a like 'a/\b' escape '/'
*
ERROR at line 1:
ORA-01424: missing or illegal character following the escape character

SQL> select * from t_char where a like 'a\\b' escape '\';

A
--------------------

3.替代变量(Substitution variables)

使用&或&&来声明替换变量

使用范围：

Where条件

Order by子句

列表达式

表名

全部select语句

输入字符串时，需用单引号扩起。例如：WHERE JOBID = '&V_NAME'

使用&&号，简化多次输入。

使用定义指令。例如：DEFINE V_NAME = 200

Select 语句

UNDEFINE V_NAME

Verify开关：（显示变量替换过程）

Set Verify on

Set Verify off

3.1定义替代变量

3.1.1通过DEFINE设置精确值

define var1=’Tom’;

变量var1的值为Tom。

3.1.2通过ACCEPT定义一个变量，同时提示你输入它的值

accept var1 char prompt 'Enter a last name: '

变量var1的值需要用户输入

3.1.3.通过&&定义一个变量，同时要求你输入它的值

SQL> define var1='Pat';
SQL> accept var1 char prompt 'Enter a first name:'

SQL> select first_name from hr.employees where first_name ='&&var1';
FIRST_NAME
--------------------
Pat

3.1.4.通过COLUMN NEW_VALUE定义

new_value是将所获得的列值赋予到变量名，然后该变量名可以参与后续处理

col [列] new_value [变量名];

SQL> define len='15';
SQL> create table test.tb1(name varchar2(&&len));
Table created

SQL> define len='15';
SQL> create table test.tb2(new_name varchar2(&&len));
Table created

SQL> insert into test.tb1 values('Tom');
1 row inserted

SQL> commit;
Commit complete

SQL> col name new_value var_value; --定义列值保存到变量var_value
SQL> select name from test.tb1; --查看表tb1的列name，此时name的值将被赋予给变量var_value
NAME
---------------
Tom

SQL> insert into test.tb2 values('&var_value');
1 row inserted

SQL> commit;
Commit complete

SQL> select * from test.tb2;
NEW_NAME
---------------
Tom

SQL>

3.2使用替代变量

如果替代变量已经定义或者赋值，那么就可以在其之前加“&”来调用它

3.3查询已定义的变量

使用define命令，后面不需要添加任何参数，就可以查询到所有已经定义过的替代变量，以及它的值和类型。

SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2013/4/21" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "" (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SYSTEM" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST" (CHAR)
DEFINE var1             = "2" (CHAR)
DEFINE len              = "15" (CHAR)
DEFINE VAR_VALUE        = "Tom" (CHAR)

3.4在数据中插入字符“&”

有时候我们需要在数据中插入字符“&”，而不需要它作为替代变量的调用符号，我们可以通过以下方法来实现：

a.set define off关闭替代变量功能；

b.set escape \设置转义字符“\”，这样使用“\&”就表示字符“&”。

3.5在spool脚本中添加当前时间

关于Spool命令

在生产中常会遇到需要将数量比较大的表值导入到本地文本文件中. 方法有很多种,比较常用的就是spool命令: 要输出符合要求格式的数据文件只需在select时用字符连接来规范格式。比如有如下表 :

SQL>select username,user_id,
to_char(created,'yyyymmdd hh24:mi:ss') as created_date from all_users;

要输出符合TEST,91,20130330 18:51:59这样的数据格式就用以下SQL:

select username||','||user_id||','||
to_char(created,'yyyymmdd hh24:mi:ss') as created_date from all_users;

并将符合要求格式的数据输出到文件中，此文件不会含有其它不需要东西，只有数据部分。

SQL> set echo off            --是否显示执行的命令内容
SQL> set feedback off       --是否显示
SQL> set heading off        --是否显示字段的名称
SQL> set verify off         --是否显示替代变量被替代前后的语句
SQL> set trimspool off      --去字段空格
Cannot SET TRIMSPOOL-----PLSQL没有这个功能，使用Sqlplus可以使用

SQL> set pagesize 1000 --页面大小
SQL> set linesize 50//linesize设定尽量根据需要来设定，值越高则生成的文件也越大

--------------(1)将输入sql及其结果写入文件

SQL> define file='e:\expuser.txt'
SQL> prompt Spooling to &file
Spooling to e:\expuser.txt

SQL> spool &file
Started spooling to e:\expuser.txt

SQL>
SQL> select username||','||user_id||','||
to_char(created,'yyyymmdd hh24:mi:ss') as created_date from all_users;
SQL> spool off;
Stopped spooling to e:\expuser.txt

--------------(2)脚本文件名为expuser.sql，存数据的文件名为e:\expuser.txt

-------生成sql

SQL> set echo off -----------------关闭显示执行的命令内容
SQL> spool e:\expuser.sql
Started spooling to e:\expuser.sql

SQL> select q'[select username||','||user_id||','||to_char(created,'yyyymmdd hh24:mi:ss') as created_date from all_users;]' from dual;
select username||','||user_id||','||to_char(created,'yyyymmdd hh24:mi:ss') as created_date from all_users;

SQL> spool off
Stopped spooling to e:\expuser.sql

--------执行sql，生成文件

SQL>set echo off -------------------关闭显示执行的命令内容

SQL> Spool e:\expuser.txt
Started spooling to e:\expuser.txt

SQL> @e:\expuser.sql
TEST,91,20130330 18:51:59
BI,90,20130330 14:17:13
PM,89,20130330 14:17:13
SH,88,20130330 14:17:13
IX,87,20130330 14:17:13
OE,86,20130330 14:17:13
HR,85,20130330 14:17:13
SCOTT,84,20090813 23:35:44
OWBSYS_AUDIT,83,20090813 23:35:05
OWBSYS,79,20090813 23:35:03
APEX_030200,78,20090813 23:26:09
APEX_PUBLIC_USER,76,20090813 23:26:09
FLOWS_FILES,75,20090813 23:26:08
MGMT_VIEW,74,20090813 23:24:58
SYSMAN,72,20090813 23:22:32
SPATIAL_CSW_ADMIN_USR,70,20090813 23:22:20
SPATIAL_WFS_ADMIN_USR,67,20090813 23:22:15
MDDATA,65,20090813 23:19:11
MDSYS,57,20090813 23:12:05
SI_INFORMTN_SCHEMA,56,20090813 23:12:05
ORDPLUGINS,55,20090813 23:12:05
ORDDATA,54,20090813 23:12:05
ORDSYS,53,20090813 23:12:05
OLAPSYS,61,20090813 23:18:04
ANONYMOUS,46,20090813 23:10:12
XDB,45,20090813 23:10:12
CTXSYS,43,20090813 23:09:45
EXFSYS,42,20090813 23:09:35
XS$NULL,2147483638,20090813 23:11:44
WMSYS,32,20090813 23:07:30
APPQOSSYS,31,20090813 23:06:36
DBSNMP,30,20090813 23:06:35
ORACLE_OCM,21,20090813 23:02:20
DIP,14,20090813 23:01:49
OUTLN,9,20090813 23:01:00
SYSTEM,5,20090813 23:00:59
SYS,0,20090813 23:00:59

SQL> spool off
Stopped spooling to e:\expuser.txt

SQL>

可以通过为sysdate定义替代变量的方法在spool脚本中添加时间

SQL> column expdate new_value mydate noprint
SQL> select to_char(sysdate,'yyyymmdd') expdate from dual;

SQL> spool &mydate.report.txt
Started spooling to E:\Software\PLSQL Developer\20130331report.txt

SQL> spool off
Stopped spooling to E:\Software\PLSQL Developer\20130331report.txt

这样mydate变量就把系统时间传递到spool脚本里了，其中noprint保证select语句不会将expdate的值输出，

执行下面的select语句不会有任何结果返回的，

“&mydate.report.txt”中第一个“.”只是替代变量的结束符不能当做字符，

如果“mydate”的值为“20100124”的话“&mydate.report.txt”在spool脚本中输出地字符就为“20100124report.txt”。

3.6可以通过“.”使用已经定义的替代变量
define mycity = Melbourne
spool &mycity.Australia.txt
之后输出就变为“MelbourneAustralia.txt”。

3.7在替代变量的值后面添加字符“.”
define mycity = Melbourne
spool &mycity..log
之后输出为“Melbourne.log”。

3.8在SQL*Plus命令中使用绑定变量
因为像SPOOL, SET 和 TTITLE这些SQL*Plus命令，

是在SQL*Plus程序中执行，而不是传送到数据中再执行的，所以它们无法识别绑定变量。
    -- Set a bind variable to a text string
    variable mybindvar varchar2(20)
    begin
      :mybindvar := 'myspoolfilename';
    end;

-- Transfer the value from the bind variable to the substitution variable

column mc new_value mysubvar noprint
select :mybindvar mc from dual;

-- Use the substitution variable
    spool &mysubvar..txt
    select * from employees;
    spool off

3.9将参数值传递给SQL*Plus替代变量
可以将参数传递到SQL*Plus脚本中执行
sqlplus hr/my_password @myscript.sql employees "De Haan"
或者
SQL> @myscript.sql employees "De Haan"
可以在myscript.sql中用“&1”和“&2”传递参数“employee”和“"De Haan"”，如下所示

SQL> set verify off
SQL>select employee_id from &1 where last_name = '&2';

------------------“set verify off”可阻止SQL*Plus返回替代变量在执行SQL前后的状态。例如：
SQL> @xxx Hello!
old 1: select '&1' greeting from dual
new 1: select 'Hello!' greeting from dual

HI
------
Hello!

SQL> set verify off
SQL> @xxx Greetings!

HI
----------

Greetings!

4. DECODE函数

Oracle特定

decode(exp1,serch1 ,return1 ,

serch2 ,reruen2 ,

……

default

)

例如：

SQL>select sid,serial#,username,
DECODE(command,
0,'None',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
8,'Drop',
'Other') cmmand
from v$session where username is not null;

5.meger

该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据. ORACLE 9i 中，

使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动.

具体示例详见：http://wenku.baidu.com/view/ee388322ccbff121dd3683a0.html

6.insert all or insert frist

引用自http://zuoren110.blog.163.com/blog/static/617563201232202958587/

INSERT ALL是9i新增的语法，它扩充了原有的INSERT语句，使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。

下面看几个简单的例子：

SQL> CREATE TABLE TABLE_STORAGE

2 (

3 TABLE_NAME VARCHAR2(30),

4 TABLESPACE_NAME VARCHAR2(30),

5 PCT_FREE NUMBER,

6 PCT_USED NUMBER,

7 INI_TRANS NUMBER,

8 MAX_TRANS NUMBER,

9 INITIAL_EXTENT NUMBER,

10 NEXT_EXTENT NUMBER,

11 MIN_EXTENTS NUMBER,

12 MAX_EXTENTS NUMBER,

13 PCT_INCREASE NUMBER,

14 FREELISTS NUMBER,

15 FREELIST_GROUPS NUMBER

16 );

表已创建。

SQL> CREATE TABLE TABLE_STAT

2 (

3 TABLE_NAME VARCHAR2(30),

4 NUM_ROWS NUMBER,

5 BLOCKS NUMBER,

6 EMPTY_BLOCKS NUMBER,

7 AVG_SPACE NUMBER,

8 CHAIN_CNT NUMBER,

9 AVG_ROW_LEN NUMBER

10 );

表已创建。

SQL> INSERT ALL

2 INTO TABLE_STORAGE VALUES (TABLE_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED,

3 INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,

4 PCT_INCREASE, FREELISTS, FREELIST_GROUPS)

5 INTO TABLE_STAT VALUES (TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,

6 CHAIN_CNT, AVG_ROW_LEN)

7 SELECT * FROM USER_TABLES;

已创建54行。

---------------无条件 INSERT ALL

SQL> SELECT COUNT(*) FROM TABLE_STORAGE;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLE_STAT;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM USER_TABLES;

COUNT(*)

----------

SQL> DROP TABLE TABLE_STAT;

表已丢弃。

SQL> DROP TABLE TABLE_STORAGE;

表已丢弃。

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

上面是最简单的INSERT ALL语句的实现，下面看看带条件的INSERT ALL语句。

SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE INDEX_ALL (INDEX_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE OBJECT_OTHER (OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(30));

表已创建。

SQL> INSERT ALL

2 WHEN (OBJECT_TYPE = 'TABLE') THEN

3 INTO TABLE_ALL VALUES (OBJECT_NAME)

4 WHEN (OBJECT_TYPE = 'INDEX') THEN

5 INTO INDEX_ALL VALUES (OBJECT_NAME)

6 ELSE

7 INTO OBJECT_OTHER

8 SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS

9 ;

已创建91行。

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX';

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM USER_OBJECTS

2 WHERE OBJECT_TYPE NOT IN ('TABLE', 'INDEX');

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLE_ALL;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM INDEX_ALL;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM OBJECT_OTHER;

COUNT(*)

----------

SQL> DROP TABLE TABLE_ALL;

表已丢弃。

SQL> DROP TABLE INDEX_ALL;

表已丢弃。

SQL> DROP TABLE OBJECT_OTHER;

表已丢弃。

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

下面看一下INSERT ALL和INSERT FIRST的区别：

SQL> CREATE TABLE TABLESPACE_USERS (NAME VARCHAR2(30), TYPE VARCHAR2(30));

表已创建。

SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));

表已创建。

SQL> INSERT ALL

2 WHEN (SEGMENT_TYPE = 'TABLE') THEN

3 INTO TABLE_ALL VALUES (SEGMENT_NAME)

4 WHEN (TABLESPACE_NAME = 'USERS') THEN

5 INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)

6 SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME

7 FROM USER_SEGMENTS;

已创建69行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

COUNT(*)

----------

SQL> TRUNCATE TABLE TABLE_ALL;

表已截掉。

SQL> TRUNCATE TABLE TABLESPACE_USERS;

表已截掉。

SQL> INSERT FIRST

2 WHEN (SEGMENT_TYPE = 'TABLE') THEN

3 INTO TABLE_ALL VALUES (SEGMENT_NAME)

4 WHEN (TABLESPACE_NAME = 'USERS') THEN

5 INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)

6 SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME

7 FROM USER_SEGMENTS;

已创建51行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

COUNT(*)

----------

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

COUNT(*)

----------

最后看一下多表插入语句的限制条件：

只能对表执行多表插入语句，不能对视图或物化视图执行；

不能对远端表执行多表插入语句；

不能使用表集合表达式；

不能超过999个目标列；

在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；

多表插入语句不支持执行计划稳定性；

多表插入语句中的子查询不能使用序列。

其实你对比INSERT ALL和INSERT FIRST语句的例子仔细观察，就会明白这个例子的含义，两个SQL语句唯一的差别就是ALL和FIRST的差别，其余部分完成一样。
对于INSERT ALL，插入第一张表的数据，如果满足第二张表的条件，也会插入。
而INSERT FIRST不然，满足第一条插入条件的数据是不会进行随后的判断的，所以在INSERT FIRST执行后，在TABLESPACE_USERS表中看不到SEGMENT_TYPE为TABLE的记录

转载于:https://blog.51cto.com/cswggod/1183204