ORACLE数据库开发基础第八章高级用法.docx
《ORACLE数据库开发基础第八章高级用法.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库开发基础第八章高级用法.docx（20页珍藏版）》请在冰豆网上搜索。
ORACLE数据库开发基础第八章高级用法
ORACLE数据库管理员基础
第八章一些高级的用法
这里给出一点不作为一般要求的内容，它虽然不是必需的，但对于应用开发来说很重要。
希
望Oracle应用设计者把它应用到系统的设计中。
8.1关于DECODE
DECODE是Oracle公司独家提供的功能，它是一个功能很强的函数。
它虽然不是SQL
的标准，但对于性能非常有用。
到目前，其他的数据库供应商还不能提供类似DECODE的
功能，甚至有的数据库的供应商批评Oracle的SQL不标准。
实际上，这种批评有些片面或
不够水平。
就象有些马车制造商抱怨亨利。
福特的“马车”不标准一样。
8.1.1DECODE中的if-then-else逻辑
在逻辑编程中，经常用到If–Then–Else进行逻辑判断。
在DECODE的语法中，实际上
就是这样的逻辑处理过程。
它的语法如下：
DECODE（value,if1,then1,if2,then2,if3,then3,...else）
Value代表某个表的任何类型的任意列或一个通过计算所得的任何结果。
当每个value值被测
试，如果value的值为if1，Decode函数的结果是then1;如果value等于if2，Decode函数结
果是then2;等等。
事实上，可以给出多个if/then配对。
如果value结果不等于给出的任何配
对时，Decode结果就返回else。
需要注意的是，这里的if、then及else都可以是函数或计算表达式。
8.1.2DECODE的简单例子
Oracle系统中就有许多数据字典是使用decode思想设计的，比如记录会话信息的
V$SESSION数据字典视图就是这样。
我们从《Oracle8iReference》资料中了解到，当用户登
录成功后在V$SESSION中就有该用户的相应记录，但用户所进行的命令操作在该视图中只
记录命令的代码（0—没有任何操作，2—Insert…），而不是具体的命令关键字。
因此，我们
需要了解当前各个用户的名字及他们所进行的操作时，要用下面命令才能得到详细的结果：
selectsid,serial#,username,
DECODE（command,
0,’None’,
2,’Insert’,
181
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’）cmmand
fromv$sessionwhereusernameisnotnull;
8.1.3DECODE实现表的转置
数据库中的表是由列和行构成的一个二维表。
一般列在任何数据库中都是有限的数量，而行
的变化较大，如果表很大，行的数量可能大上千万行。
同一列的不同行可能有不同的值，而
且不是预先定义的。
除上面描述表具有的一些特点外，有一些表可以看成是不变的或者是较稳定的，比如住房公
积金系统是各个单位按照职工的工资数的比例交到本地的经办行中，它的处理流程如下：
例1.住房公积金报表置换实例：
1.各个单位在本地经办行进行开户，开户就是将单位的基本信息和职工信息的进行登记;
2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金，系统记录有每个职工
的交缴明细并在每条记录上记录有经办行的代码;
3.每月、季、半年及年终都要求将经办行变为“列”给出个月的明细报表：
经办行：
城西区城东区。
。
。
月份：
2001.01xxxx1.xxxxxxx2.xx
2001.02xxxx3.xxxxxxx4.xx
。
。
。
。
。
。
原来的数据顺序是：
城西区2001.01xxxxx1.xx
城东区2001.01xxxxx2.xx
城西区2001.02xxxxx3.xx
城东区2001.02xxxxx4.xx
住房公积金系统记录职工的每月交缴名细的pay_lst表结构是：
bank_codevarchar2（6）NOTNULL,--经办行代码
acc_novarchar2（15）notnull,--单位代码（单位帐号）
emp_acc_novarchar2（20）notnull,--职工帐号
tran_datedatenotnull,--交缴日期
tran_valNumber（7,2）notnull,--交缴额
sys_datedatedefaultsysdate,--系统日期
182
oper_idvarchar2（10）--操作员代码
这样的表结构，一般按照将经办行作为行进行统计是很容易的，但是如果希望将经办行变为
列这样的格式来输出就有困难。
如果用DECODE函数来处理则变得很简单：
我们创建一个视图来对目前的pay_lst表进行查询。
将经办行代码变为一些具体的经办行名
称即可：
CREATEORREPLACEVIEWbank_date_lstAS
Selectto_char（tran_date,’yyyy.mm’）,
SUM（DECODE（bank_code,’001’,tran_val,0））城西区，
SUM（DECODE（bank_code,’002’,tran_val,0））城南区，
SUM（DECODE（bank_code,’003’,tran_val,0））城东区
FROMpay_lst
GROUPBYto_char（tran_date,’yyyy.mm’）;
例2.希望将下面的列结果按照列的方式来显示JOB内容：
SQL>selectempno,ename,job,sal,deptnofromemp
2orderbydeptno,job;
EMPNOENAMEJOBSALDEPTNO
-------------------------------------------------
7934MILLERCLERK130010
7782CLARKMANAGER245010
7839KINGPRESIDENT500010
7788SCOTTANALYST300020
7369SMITHCLERK80020
7876ADAMSCLERK110020
7566JONESMANAGER297520
7938赵元杰软件1234520
7698BLAKEMANAGER285030
7499ALLENSALESMAN160030
7654MARTINSALESMAN125030
7844TURNERSALESMAN150030
7521WARDSALESMAN125030
18rowsselected.
183
再看下面的查询结果：
SQL>selectdeptno,job,sum（sal）fromempgroupbydeptno,job;
DEPTNOJOBSUM（SAL）
-----------------------------
10CLERK1300
10MANAGER2450
10PRESIDENT5000
20ANALYST3000
20CLERK1900
20MANAGER2975
20软件74070
30MANAGER2850
30SALESMAN5600
9rowsselected.
从上面的结果看，如果希望将JOB置换成列的方式，则只要用DECODE将JOB列进行描述即可。
创建的视图如下：
createorreplaceviewempvas
selectdeptno,
sum（decode（job,’ANALYST’,sal,0））ANALYST,
sum（decode（job,’CLERK’,sal,0））CLERK,
sum（decode（job,’MANAGER’,sal,0））MANAGER,
sum（decode（job,’PRESIDENT’,sal,0））PRESIDENT,
sum（decode（job,’SALESMAN’,sal,0））SALESMAN,
sum（decode（job,’软件’,sal,0））软件
fromempgroupbydeptno;
具体运行的显示样本如下：
SQL>createorreplaceviewempvas
2selectdeptno,
3sum（decode（job,'ANALYST',sal,0））ANALYST,
4sum（decode（job,'CLERK',sal,0））CLERK,
5sum（decode（job,'MANAGER',sal,0））MANAGER,
6sum（decode（job,'PRESIDENT',sal,0））PRESIDENT,
7sum（decode（job,'SALESMAN',sal,0））SALESMAN,
8sum（decode（job,'软件',sal,0））软件
9fromempgroupbydeptno;
184
Viewcreated.
SQL>select*fromempv;
DEPTNOANALYSTCLERKMANAGERPRESIDENTSALESMAN软件
----------------------------------------------------------------------
10013002450500000
203000190029750074070
30002850056000
8.2关于访问远程数据库
在许多环境中，都可能需要访问远程数据库。
现在的Oracle8i/9I的NET都支持远程访问技
术。
只要环境网络具备和在参数文件中进行相应的配置就能在SQL语句中进行访问。
关于环
境的安装和配置另见DBA资料。
8.2.1数据库链接
Oracle本地要与远程进行连接，要通过数据库链接。
1.使用数据库链接进行查询和更新：
SELECT*fromworker;
这样的语句表示在本地进行查询。
而对于远程，则需要在语句后加相应的数据库链接。
如：
SELECT*FROMworker@remote_connect;
如果想省去@号后面的字串，可以采用建立一个本地的同义词来实现。
如：
CREATESYNONYMWORKER_SYNforWORKER@remote_connect;
如果希望进行远程更新的话，类似可以在UPDATE语句中加上远程连接符。
如：
UPDATEworker@remote_connect
SETlodging=’CRANMER’wherelodging=’ROSEHILL’;
2.创建数据库链接的语法：
创建数据库链接的语法如下：
185
CREATE[PUBLIC]DATABASELINKremote_connect
CONNECTTOusernameidentifiedbypasswordusing‘connectstring’;
一般PUBLIC有DBA来创建。
个人用户可以不加PUBLIC就是私有的数据库链接。
Oracle系统可以创建链接，但是连接的数量有限制。
缺省的并发数是4（由init.ora文件中
的OP_LINKS来限制）。
为了建立数据库链接，需要有CREATEDATABASELINKS系统权限;要与远程的帐号进行连接
还需要有CREATESESSION普通权限。
CREATEdatabaselinkzhaoconnecttozhaoyuanjieidentifiedbyzhao_yuan_jie
Using‘sun450’;
这样创建后，可以用下面语句来使用：
SELECT*fromtst@sun450;
需要注意的是，在创建数据库链接时是根据Oracle的Tnsnames.ora参数文件中的连接字符
串来填写连接字串。
比如上面的连接字串是sun450。
则在tnsmaes.ora中的要有下面的数
据项：
SUN450=
（DESCRIPTION=
（ADDRESS_LIST=
（ADDRESS=（PROTOCOL=TCP）（HOST=dbsvr）（PORT=1521））
）
（CONNECT_DATA=
（SERVICE_NAME=s450）
）
）
8.2.2使用同义词获得本地透明
在应用系统的生命周期内，为了方便和维护简便，经常采用建立同义词来实现透明的访问。
例1.没有建立本地透明时的访问：
SELECT*FROMnorth.worker;
例2.建立一个同义词，然后进行访问：
CREATESYNONYMWORKERFORNORTH.WORKER;
186
SELECT*FROMWORKER;
在实际应用中，为了达到隐藏表的所有权，还可以通过使用数据库链接和同义词来隐藏数据
的物理位置。
通过使用对应远程端的本地表同义词，可以把另一个逻辑层从应用转移到数据
库中。
例3.建立一个本地同义词的远程表：
CREATESYNONYMWORKERSKILL
ForWORKERSKILL@remote_connect;
8.2.3在视图中使用user伪列
Oracle提供一个伪列user,它可以在创建视图中使用，从而实现对结果的返回的限制。
如：
CREATEORREPLACEVIEWemp_lst
ASselect*fromemp
Whereename=user;
这样的视图表示，只有使用者的用户名与ename中的名字一样，才能返回相关的记录。
看下
面的结果就会明白：
SQL>CREATEORREPLACEVIEWemp_lst
2ASselect*fromemp
3Whereename=user;
Viewcreated.
SQL>select*fromemp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTN
-----------------------------------------------------------------------------
7369SMITHCLERK790217-DEC-808002
7499ALLENSALESMAN769820-FEB-8116003003
7521WARDSALESMAN769822-FEB-8112505003
7566JONESMANAGER783902-APR-8129752
7654MARTINSALESMAN769828-SEP-81125014003
7698BLAKEMANAGER783901-MAY-8128503
7782CLARKMANAGER783909-JUN-8124501
7788SCOTTANALYST756619-APR-8730002
7839KINGPRESIDENT17-NOV-8150001
187
7844TURNERSALESMAN769808-SEP-81150003
7876ADAMSCLERK778823-MAY-8711002
7934MILLERCLERK778223-JAN-8213001
7938赵元杰软件23-SEP-0112345543212
7939赵元杰软件23-SEP-0112345543212
14rowsselected.
SQL>select*fromemp_lst;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTN
-----------------------------------------------------------------------------
7788SCOTTANALYST756619-APR-8730002
SQL>
上面结果显示，由于本用户的名字是scott，所以只显示ename=scott的记录。
8.2.4使用COPY功能
如果我们用CREATETABLExxxASselect*fromtablename@remote_connect;不能完成
带Long等列的操作时，请你使用Oracle提供的COPY命令。
COPY可以实现在CREATETABLE
复制表时所不能实现的功能。
COPY命令的语法如下：
COPY{FROMusername[/password]@database|TO
username[/password]@database|
FROMusername[/password]@databaseTO
username[/password]@database}
{APPEND|CREATE|INSERT|REPLACE}
destination_table[（col1,col2,...）]USINGquery
参数说明：
APPEND如果目标表已存在，将查询的记录插入该表中，
如果目标表不存在，则创建再插入所查询的记录。
CREATE先创建目标表再插入所查询的记录,
如果目标表不存在，则提示错误。
INSERT将查询（必须用usingquery查）的记录插入到目标表中，
如果目标表不存在，则提示错误。
REPLACE将查询的记录替换到目标表中的内容，
如果目标表存在，则先删旧表内容再用拷贝表替换，
188
如果目标表不存在，则创建表。
使用copy命令还需设置以下变量：
SQL>setlongn;/*数据长度*/
SQL>setcopycommitm;/*m行提交一次*/
SQL>setarraysizen;/*批操作的大小*/
例：
COPYfromzhao/zhaoyuan@sun450
CREATEWORKERusingselect*fromWORKER;
8.2.5管理Oracle名称服务器
要保证与远程进程连接，DBA必须确保名称服务器配置的正确。
另见DBA资料的“NET8名称
服务器”章节。
8.3关于上下文的使用
在Oracle系统中，可以支持对文本进行处理。
要想对文本进行搜索，则需要对数据库环境进
行设置。
8.3.1设置上下文选项
为了在数据库中使用上下文功能，先要设置上下文选项。
用以启动数据库上下文处理进
程。
可以用CTXCTL实用程序来启动上下文服务器：
$CTXCTL
……
>help
Thefollwingcommandsareavailable:
Help[command]--命令信息
Status--正在运行的服务器情况
Startn[line|query|ddl|dml]…--启动n服务进程
Stoppid…|all--停止ctxctl
189
Quit--结束ctxctl
Exit--结束ctxctl
为了支持文本搜索，用户至少启动一个query上下文服务器。
到底启动多少个服务器，要根
据用户的要求决定。
比如：
start1ddldmlquery
start1query
服务器的当前情况可以从CTS_ALL_SERVERS数据字典来查询，如：
SQL>colser_namefora30
Selectser_name,ser_status,set_started_atfromCTX-ALL_SERVERS;
从结果可看出已经启动的服务器，DBA可以在SQL>下用下面命令来停止服务的运行：
SQL>execCTX_ADM.SHUTDOWN;
1.在init.ora参数文件中设置启动参数：
要使上下文有效，必须在Init.ora文件中设置下面参数：
text_enable=TRUE
也可以在SQL>下进行设置：
ALTERSESSIONsettext_enable=TRUE;
2.必要的角色：
在Oracle数据库中，上下文的数据字典属于CTXSYS用户所有。
所以CTXSYS是唯一具有
CTXADMIN角色的用户。
此外，还有CTXAPP（应用程序所有）和CTXUSER（应用程序用户）
角色。
一般需要将CTXAPP授予开发上下文的用户。
如：
grantCTXAPPtoscott;
8.3.2为上下文查询设置表
为了实现上下文的查询，需要在基表的某列建立主关键字和为表中的文本列设置相应的查询
策略。
比如有下面的求职表：
1.基本的设置：
190
1）创建基本表：
CREATETABLEprospect（namevarchar2（20）,
Addressvarchar2（40）,
Resumelong）tablespaceuser_data
storage（initial5mnext1mpctincrease0）;
2）为prospect表建立
升级会员 