面试２
来源：互联网 发布：好享购 知肤泉水光面膜 编辑：程序博客网 时间：2024/04/25 01:14
OracleMTS的相关问题
一、什么是MTS
MTS =Multi-Threaded Server
MTS是ORACLE SERVER的一个可选的配置选择，是相对DEDICATE方式而言，它最大的优点是在以不用增加物理资源（内存）的前提下支持更多的并发的连接。
Joseph C.Johnson以餐馆给出一个MTS的形象的比喻
假设ORACLE是一家餐馆，当你走进一家餐馆时你感觉最 舒服的服务方式就是有一个专门的waiter来为你服务，而不管餐馆中来了多少人，她只对你请求应答，这是DEDICTE的处理方式，也就是说每一个 ORACLE客户端的连接都有一个专门的服务进程来为它服务。而大部的餐馆的服方式都不是一对一的，当你走进的时侯，你就被指定了一个waiter，她也 可能为其它桌服着务，这对于餐馆来说是最有利的，因为他们可以服务更多的客人而不需要增加他们的员工。这样对你来说也可能是不错的，如果餐馆不是太忙，她 服务的客人的请求都很简短且容易完成，你的感觉也好像自己拥有一个专门的waiter，waiter把你的ORDER转给厨师，然后把做好的菜拿给你，这 就是MTS的处理方式，这些共享的waiters我们叫她们为Dispatchers，厨师我们则叫他们为Shared Server Processes。
二、MTS架构
[metalink-Note:29038.1]
1. Oracle Multi-ThreadedServer Architecture.
----------------------------------------------
+-----------+ user
+-----------+| processes
| ||
|APPLICATION||
| CODE ||
| |+
+-----------+
/|\
| CLIENT
- - - - - - - - - - - -- - - - - - - - - - - - - - - - -
| SERVER
\|/
+----------------------------------+
+----------------------------------+|
| ||
| DISPATCHERPROCESSES ||
| |+
+----------------------------------+
| /|\
| |
| +-----------+ |
| +-----------+| shared |
| +-----------+|| server |
| | ORACLE ||+ processes |
| |SERVER CODE|+ |
| +-----------+ |
| /|\ /|\ | |
| | | | |
| | | | |
| | \|/ | |
+---------------|-----|-------|----------------|-------+
| \|/ | \|/ | |
| +-------------------+ +----------------------+ |
| | REQUEST | | RESPONSE QUEUES | |
| | QUEUES | +----------------------+ |
| +-------------------+ |
| SYSTEM GLOBALAREA |
| |
+------------------------------------------------------+
Client Connections to MTS
~~~~~~~~~~~~~~~~~~~~~~~~~
A client process wanting to connect to an Oracle instance using MTS
should go through the following steps:
a) Call the listener
b) The listener tells it to call back on the address where
the dispatcher is listening (a REDIRECT)
c) The client calls the dispatcher and establishes a connection.
d) The dispatcher now has a CLIENT connection ESTABLISHED and
will also continue to listen for any new connections.
e) The client sends a SQL message to the dispatcher.
f) The dispatcher unwraps this message packet and places the
request onto a queue in the SGA (System Global Area).
This queue has NOTHING to do with SQL*Net. The SQL*Net
layer ends in the dispatcher.
g) A shared server will pick up the request from the SGA queue
and process it. When there is any result this isplaced
on a separate queue in the SGA ready for thedispatcher.
h) The dispatcher picks up the response message from the SGA
and wraps this into a SQL*Net message. This is passed
back to the client.
三、确定你的DB是否在使用MTS
[metalink-Note:1071305.6]
Solution Description:
=====================
Look at the "init.ora" parameter MTS_DISPATCHERS.
MTS_DISPATCHERS defines the number of dispatchers created when the instance
starts up. If this is set to zero, or is null (default value), then you are NOT
using MTS.
If MTS_DISPATCHERS is greater thanzero, then check these other "init.ora"
parameters for valid values:
MTS_MAX_DISPATCHERS Maximum number of dispatchers
MTS_SERVERS Number of server processes created at startup
MTS_MAX_SERVERS Maximum number of shared server processes
MTS_SERVICE SID
MTS_LISTENER_ADDRESS Configuration of the listener
Solution Explanation:
=====================
These other "init.ora" parameters may have valid values, but ifMTS_DISPATCHERS
is zero or null, MTS is not being used.
四、MTS相关参数的含义
[metalink-Note:29038.1]
The main parameters required tostart MTS are:
o MTS_SERVICE - Thisparameter establishes the name of the MTS service
that clients connect to, in order for the dispatchers tohandle requests.
o MTS_DISPATCHERS - Dispatchers are detached processes that handleclient
process requests and communicate them to server processesfor execution.
This parameterdefines the number of dispatchers to startup for
each protocol that is configured for MTS. For example,
mts_dispatchers = "ipc,2"
o MTS_SERVERS - This is thenumber of shared server processes that
start at instance startup time. Shared servers servicethe
client requests passed on to them by the dispatchers.
o MTS_LISTENER_ADDRESS - Thisis the full address for dispatchers to listen
on, and is the same as the address configured in the TNSlistener. The
address specification is protocol-specific. Forexample:
mts_listener_address = "(address=(protocol=ipc)(key=sw))"
五、使用MTS连接DB
先在主机命令行 $ lsnrctl services;
"D000" established:0refused:0 current:1 max:972 state:ready
DISPATCHER <machine: db1,pid: 3950>
(ADDRESS=(PROTOCOL=tcp)(HOST=eagle1)(PORT=33166))；
查看dispather的监听端口号——33166。(dispather进程名称一般是 ora_dNNN_SID,NNN in (000-999))
在client端的tnsname.ora中，注意：
TODB_MTS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT= 33166))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = dbname)
)
)
这样可以解决ora-12520问题(当然这只是解决方法之一)
六、一问一答
Q：MTS一定比 Delicated模式好吗？
A：不是。如果硬件好点，建议使用Delicated模式。biti语录：“我是能不用MTS就不用”
Q：MTS与Delicated模式可以相互转换吗？
A：可以。
MTS => Delicated
Reset the MTS Parameters ininit.ora and reopen your DB.
Delicated => MTS
Added the MTS Parameters in init.oraand reopen your DB.
Q：MTS模式下可以用 Delicated连进来吗？
A：可以。(S
sqlplus常用命令
**如何在XP的cmd命令行状态下输入中文？
1.开始－》运行中输入regedit
2.HKEY_CURRENT_USER\Console\%SystemRoot%_system32_cmd.exe下的项CodePage 项值改为十进制"936"值或
十六进制"000003a8"值。说明一下： 十六进制"000003a8"或十进制"936"，表示"936 (ANSI/OEM - 简体中文GBK)"。
**启动oracle服务：net startOracleServicesid 其中sid是服务器的名
**查看ORACLE的实例: select *from v$instance
**查看ORACLE的数据库名:select *from v$database;
** startup，等于以下三个命令
startup nomount
alter database mount
alter database open
**<打开控制台输出>1、showall serveroutput OFF 2、set serveroutput on 启动及关闭模式
**关闭oracle数据库
shut down
**查看数据库名
实例名：
select * from v$instance;
数据库名：
select * from v$database;
**查看oracle中的所有表：
SELECT * FROM dba_tables
**oracle忘记sys密码解决
这个其实很简单，不过自己经常忘记，所以记下来
sqlplus /nolog;
connect / as sysdba
alteruser sys identified by ;
alter user system identified by ;
**创建用户：
create user username(用户名)
identified by userpassword(用户密码)
default tablespace system
quota 5M on system -----供用户使用的最大空间限制
** 查看用户及其密码：
select username,password from dba_users;
**修改用户system密码 为manager
alter user system identified by manager;
**删除用户及其所建的实体：
DROP USER jxzy CASCADE;
**授予用户的系统特权：
grant create session,create table to username;
GRANT CREATE USER,ALTER USER,DROP USER
TO jxzy_new --用户名
WITH ADMIN OPTION;
**回收系统特 权：(注意没有级联回收的功能)
REVOKE CREATE USER,ALTER USER,DROP USER
FROM jxzy_new
**显示某个用户在oracle中所授的系统特权
SELECT * FROM sys.dba_sys_privs
**oracle 对象的管理和控制：
oracle对象特权：用户在指定的表上进行特殊操作的权利。这些特殊操作包括增、删、改、查看、执行（存储过程）、
引 用（其它表字段作为外键）、索引等。
授权：
GRANT SELECT,INSERT(office_num,office_name), --针对某个表某个列进行授权
UPDATE(desc)ON office_organization,
TO new_adminidtrator(用户名),
WITH GRANT OPTION;
级联授权：
grant all on office_organization
to new_administrator
回收：
revoke update on office_organization
from new_administrator;
显示：
select * from sys.dba_tab_privs;
**oracle 中的角色管理：
分类：(5种)
CONNECT：具有创建表、视图、序列等特权；
RESOURCE：具有创建过程、触发 器、表、序列等特权
DBA：DBA具有全部系统特权；
EXP_FULL_DATABASE：具有卸出数据库的特权
IMP_FULL_DATABASE： 装入数据库的特权
查看每种角色所拥有的权利：
select * from sys.dba_sys_privs;
授予用 户角色：
grant dba(角色类) to new_administrator(用户名)
with grant option;
**oracle的用户安全：
1、由于Oracle有几个系统预建的用户，所以最好在安装完成以后马上改变这些用户的密 码。
系统默认得密码分别为：internal/oracle, sys/change_on_install, system/manager.
2、当选择密码文件认证方式时，可以再向系统中加入其他超级用户。
比如用以下语句把用户SCOTT加入超级用户之中：(由具有sysdba权限的人执行)
GRANT SYSDBA TO SCOTT;这样SCOTT用户就具有了sysdba权限。
注意：此时SCOTT用户可以以两种身份登录：SCOTT, SYS.当SCOTT在登录时没有输入ASSYSDBA时，
SCOTT是作为普通用户登录的。而当登录时输入了AS SYSDBA时，此时SCOTT登录进去的用户实际上为sys。
3、当前系统中的具有sysdba权限的用户名可以从数据字典视图v$pwfile_user中查询得到：
SELECT * FROM V$PWFILE_USERS;
4、系统中最大的具有sysdba权限的用户数由创建密码文件时的ENTRIES参数决定。
当需要创建更多的具有sysdba权限的用户时，就需要删除原有的密码 文件，重新创建一个。
这需要关闭数据库，删除密码文件，重新创建一个新的密码文件，在entries中输入足够大的数目。
再启动Oracle。这时，所有原来北授权的超级用户都不再存在，需要重新授权。
所以在重新创建密码文件前，先要查询该视图，记下用户名，再在创建 完密码文件后重新授权。
5、Internal用户密码忘记的处理方法：
有两种办法：
1)、ALTERUSER SYS IDENTIFIED BY 新密码；//这同时也改变了Internal的密码,在Oracle8I中通过
2)、重新创建一个新 的密码文件，指定一个新的密码。
**查看某个用户中的表：
SELECT * FROM USER_TABLES
**在Oracle中查看所有的表:
select * from tab/dba_tables/dba_objects/cat;
看用户建立的表 :
select table_name from user_tables; //当前用户的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表 show user;//当前用户
**乱码：
字符集不一致，保证服务器端的字符集和client的 字符集一致即可，服务器端的字符集一旦建库就基本上不能修改了，
可以用select userenv('language') from dual查看，client的字符集只需要在bash_profile变量里面export
LANG和NLS_LANG即可。当然shell 的字符集最好也保持一致，编辑文件/etc/sysconfig/i18n。如果远程登陆的话，
登陆软件如scurityCRT或者PUTTY 的字符集设定也应该一致。
1.sqlplus : 登陆SQL
清屏：HOSTCLS
2.exit: 退出SQL
3.clear buffer : 清空缓冲区内容。
4.describe：描述表结构 ，缩写为desc。
5.list：用来显示缓冲区的内容，缓冲区 只保存最近一条SQL或PL SQL命令，缩写为l。
6./ ：执行缓冲区的SQL语句 /=run
7.change /old /new ：将旧串的内容改变为新串的内容。缩写为c。/可以换成其它的特殊符号，只有前后一致就 OK了。
如：change？old ？new
8.append new ：追加，将新串追加到行尾。 缩写为a
9.save filename [replace]: 将缓冲区的SQL语句保存成文件。使用可选参数replace可以覆盖原文件的内容。
10.get filename: 将SQL文件取到缓冲区
11.start filename: 执行SQL文件 ，相当于get filename +/
12.@ filename: 执行SQL文件，等价于start filename
13.edit filename: 编辑缓冲区的SQL语句。
14.spool filename: 用来保存在SQL*Plus工具中操作过的内容，记得之后要使用spool off。要不它只会停留在使
用spoolfilename命令的点上，不会保存到内容
15.help index: 显示所有SQL*Plus命令。Help 命令名，显示这条命令的信息
16.column : 预定义列格式，后面可以放名列或列的别名。
17.修改缓冲区内容的三种方法：
（1）a. 输入要修改的行数，回车，b.使用命令change/old /new
（2）输入要修改的行数，然后直接重写整一句的内容。
（3） 用edit命令，进入vi编辑。
18.定义变量
用&来定义变量。可以放在任何地方，执行时，用输入的值替换变量的值
如：select&name from emp;
JDBC实例Statement,PreparedStatement,CallableStatement三个方法的实例
public void ListStudents() throws SQLException{
int i, NoofColumns;
String StNo, StFName, StLName;
//初始化并加载JDBC-ODBC驱 动程序
Class.forName("jdbc.odbc.JdbcOdbcDriver");
//创建连接对象
Connection Ex1Con =DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");
// 创建一个简单的Statement对象
Statement Ex1Stmt = Ex1Con.createStatement();
// 创建SQL串，传送到DBMS并执行SQL语句
ResultSet Ex1rs = Ex1Stmt.executeQuery("SELECT StudentID,FirstName, LastName FROM Students");
//处理每一个数据行，直到不再有数据行
System.out.println("Student Number First Name Last Name");
while(Ex1rs.next()){
// 将列值保存到java变量中
StNo = Ex1rs.getString(1);
StFName = Ex1rs.getString(2);
StLName = Ex1rs.getString(3);
System.out.println(StNo, StFName, StLName);
}
}
public void UpdateStudentName(String StFName, String StLName, StringStNo) throws SQLException, ClassNotFoundException
{
int RetValue;
//初始化 并加载JDBC-ODBC驱动程序
Class.forName("jdbc.odbc.JdbcOdbcDriver");
// 创建连接对象
Connection Ex1Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");
// 创建一个简单的Statement对象
Statement Ex1Stmt = Ex1Con.createStatement();
// 创建SQL串，传送到DBMS并执行该SQL语句
String SQLBuffer = "UPDATE Students SET FirstName = " +
StFName + ",LastName = " + StLName +
"WHERE StudentNumber = " + StNo;
RetValue = Ex1Stmt.executeUpdate(SQLBuffer);
System.out.println("Updated" + RetValue + "rows inthe Database.");
}
//使用PreparedStatement改进实例
//Declare class variables
Connection Con;
PreparedStatement PrepStmt;
boolean Initialized = false;
public void InitConnection() throws SQLException, ClassNotFoundException{
//Initialize and load the JDBC-ODBC driver.
Class.forName("jdbc.odbc.JdbcOdbcDriver");
//Make the connection object.
Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");
//Create a prepared Statement object.
PrepStmt = Con.prepareStatement("SELECT ClassName, Location,DaysAndTimes FROM Classes WHERE ClassName = ?");
Initialized = true;
}
public void ListOneClass(String ListClassName) throws SQLException,ClassNotFoundException{
int i, NoOfColumns;
String ClassName, ClassLocation, ClassSchedule;
if(!Initialized){
InitConnection();
}
//Set the SQL parameter to the one passed into this method
PrepStmt.setString(1, ListClassName);
ResultSet Ex1rs = PrepStmt.executeQuery();
//Process each row until there are no more rows and display theresults on the console.
System.out.println("Class Location Schedule");
while(Ex1rs.next()){
ClassName = Ex1rs.getString(1);
ClassLocation = Ex1rs.getString(2);
ClassSchedule = Ex1rs.getString(3);
System.out.println(ClassName,ClassLocation,ClassSchedule);
}
}
// 使用CallableStatement显示成绩
//预先定义好的存储过程的调用形式为：studentGrade= getStudentGrade(StudentID, ClassID)
public void DisplayGrade(String StudentID, String ClassID) throwsSQLException
{
int Grade;
//Initialize and load the JDBC-ODBC dirver.
Class.forName("jdbc.odbc.JdbcOdbcDriver");
//Make the connection object;
Connection Con =DriverManager.getConnection("jdbc:odbc:studentDB";uid="admin";pw="sa");
//Create a Callable Statement object;
CallableStatement CStmt = Con.prepareCall({? = callgetStudentGrade[?,?]});
//Now tie the placeholders with actual parameters.
//Register the return value from the stored procedure
//as an integer type so that the driver knows how to handle it.
//Note the type is defined in the java.sql.Types.
CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
//Set the In parameters (which are inherited from thePreparedStatement class)
CStmt.setString(1,StudentID);
CStmt.setString(2,ClassID);
//Now we are ready to call the stored procedure
int RetVal = CStmt.excuteUpdate();
//Get the OUT Parameter from the registered parameter
//Note that we get the result from the CallableStatement object
Grade = CStmt.getInt(1);
//And display the results on the console;
System.out.println("The Grade is:" + Grade);
}
大家都知道Statement、PrepareStatement和CallableStatement对象，其实它们是interface，为什么JDBC2.0中要提供这三个对象呢？对于Statement就是为了实现简单的SQL语句，但是PrepareStatement和CallableStatement是为了：
1)Prevent SQL inject attack
2)Catch of DB overflow
3)Readable and maintained of code
4)Efficiency
当然对于效率而言环境的模拟是不适合的， 所以我们只能依据编译的过程来看，只是适合某些情况PrepareStatement可以在create时就compiled，然后invokesetXXX(num,value);
传递参数，当然预编译的SQL要使用"?"来做占位符；
Batch我 想是PrepareStatement的又一大特点，如果你要batch update some data，so you should choosePreparesStatement;
ForExample:
PreparedStatement st=null;
Stringsql = "update BOOKS " +"set bookid = ? where authorlike ?";
st =con.prepareStatement(sql);
int []salesForWeek = {11, 24, 43, 67, 85};
String[] author= {"Colom", "French", "Blom","Decaf", "Qop"};
intlen = author.length;
for(inti = 0; i < len; i++) {
st.setInt(1,salesForWeek[i]);
st.setString(2, authoe[i]);
st.executeUpdate();
}
PreparedStatement stmt = conn.prepareStatement(
"insertinto client values(?,?, ?)");
User[]user = new User();
for(int i=0; i<user.length; i++) {
stmt.setInt(1,user[i].getID());
stmt.setString(2,user[i].getName());
stmt.setString(3, user[i].getPassword());
stmt.addBatch();
}
stmt.executeBatch();
Statement是最基础的JDBC对象，由Connection的createStatement()生成，主要作用于数据的CRUD；缺点在于参数的拼接可能导致SQLinject；
CallableStatement是继承Statement和PreparedStatement的，所以可以处理参数和有预编译的功能；参数的处理 有三种：in、out、inout；初级的鸟可能用的比较少，它主要用于调
用storedprocedure：
DatabaseMetaData dbmd=conn.getMetadata();
if(dbmd.supportsNamedParameters()==true){
CallableStatementst= con.prepareCall("{call procedure_name(?, ?)}");
st.registerOutParameter(1, java.sql.Types.TINYINT);
st.registerOutParameter(2, java.sql.Types.DECIMAL,3);
cstmt.executeQuery();
byte x= cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);
}
else{ ...... }
PrepareStatement和CallableStatement是实际开发中用的最多的JDBC对象，无论从安全性和效率上讲都是不错的.
Statement对象用于将 SQL 语句发送到数据库中，执行对数据库的数据的检索或者更新。它有2个子类，CallableStatement,PreparedStatement（确切的说是接口。）现在就大体说说他们的用法。
Statement对象用于执行不带参数的简单SQL 语句；PreparedStatement对象用于执行带或不带IN 参数的预编译SQL 语句；CallableStatement对象用于执行对数据库已存储过程的调用。
1.数据库的连接
和其 他的语言一样，java的数据库连接需要如下参数。
数据库的驱动程序，数据库的host,端口和数据库名(URL)，数据库登陆用 户名，数据库登陆password。
以oracle数据库为例：
一个数据库连接如下建立
Class.forName("oracle.jdbc.driver.OracleDriver");/ /取得Oracle的数据驱动，应用不同的数据驱动可以用不同的class路径。
connection= DriverManager.getConnection(url,username,password) ;//取得数据库连接
取得Statement对象：
statement= connection.createStatement();
connection取得statement还有别的方法，比如
createStatement(intresultSetType, int resultSetConcurrency)
createStatement(intresultSetType, int resultSetConcurrency, int resultSetHoldability)
可以根据不同的需要传参数取得相应特征的statement
用法：（关于statement的用法只是按照个人使用时的感觉来描 述的，实际的用法肯定没有这么狭隘）
对于where条件不确定的sql查询或者登陆语句可以用这个statement对象。
比如，对数据库只访问一次，实现对于一个用户信息进行一次的检索操作。用户信息包括如下字段：用户名，性别，住址，用户类别等等
业务逻辑在进行检索的时候，要根据用户名，性别，住址，用户类别等进行检索，但是用户的检索条件不确定。可能只根据其中的几项进行检索。
这个时候sql无法确定，需要根据某个字段的值是否入力来确定是否要这个检索条件。用statement来执行相应的sql就可以了。
当然，用Statement执行其他类别的检索也可以。
取得PreparedStatement对象：
preparedStatement = connection.prepareStatement(String sql);
PreparedStatement继承了Statement类，所以取得statement也有别的方法
用法：
对于数据库的检索或更新1条记录，并有批量数据（针对单条记录）要进行操作。
比如，对数据库只访问一次，要插入一条用户信息。用 户信息包括用户名，性别，住址，用户类别等等。
如果用statement类，需要频繁的拼写sql文，而且容易出错。
如果用PreparedStatement，可以如下操作。
预先取得要插入数据库的各个变量。
sql ="INSERT INTO TABLENAME (A,B,C,D,E,F,G...)VALUES(?,?,?,?,?,?,?,...)" 调用preparedStatement.setString(intparameterIndex, String x)方法，依次设定要插入的参数。
PreparedStatement还包括针对数据库字段各种类型的set方法。当然也可以利用 sql函数来实现对于数据类型的转换。
比如对于Date型的字段，可以直接用PreparedStatement的 setDate(intparameterIndex, Date x, Calendar cal) 方法来设定字段值，也可以在sql文中写入TO_DATE(?,’yyyy/mm/dd’)，直接调用setString方法传入一个是yyyy/mm/dd格式的字符串就可以了。
用这个类的好处就是将变量的取得和sql文的拼写区分开来。比较清晰。
取得CallableStatement对象：
callableStatement = connection.prepareCall(String sql);
CallableStatement继承了Statement类，所以取得statement也有别的方法
用法：
CallableStatement对象可以返回参数，所以可以返回某次数据库操作的结果。这个我一般用于对数据库需要根据数据的不同和存在与否进行多 次数据库访问的操作。并将自定义的操作结果返回给java程序。一般会写一个存储过程，将数据库的复杂操作封装在这个存储过程中。
比如，简单的说，在插入一条用户数据的时候，首先要判断用户是否存在，存在的话只做更新，不存在的话插入。而更新操作后还要更新数据库里别的对应的表。
这个时候就把这些操作都写在存储过程中，比如PLSQL。然后通过
CallableStatement执行这个调用存储过程的SQL文。比如，PLSQL的package名为userManager,调用的过程为update_user_info()，其中这个过程的参数有in 参数和out参数。
sql=”calluserManager.update_user_info(…)”
取得CallableStatement对象后，要设定IN参数（同PreparedStatement）。同时要注册输出参数。
用如下方法注册输出参数CallableStatement.registerOutParameter(intparameterIndex, int sqlType)
然后执行这次数据库操作后，就完成了。
以上就是个人对java中 Statement的一点看法，如有错误，请指正讨论。
以上没有考虑对SqlException异常的捕捉。
Statement ─ 由方法 createStatement 所创建。createStatement不会初始化，没有预处理，没次都是从0开始执行SQLStatement对象用于发送简单的SQL 语句。
PreparedStatement ─ 由方法 prepareStatement 所创建。会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率.PreparedStatement对象用于发送带有一个或多个输入参数（IN 参数）的SQL 语句。PreparedStatement拥有一组方法，用于设置IN 参数的值。执行语句时，这些IN 参数将被送到数据库中。PreparedStatement的实例扩展了Statement ，因此它们都包括了Statement 的方法。PreparedStatement对象有可能比Statement 对象的效率更高，因为它已被预编译过并存放在那以供将来使用.prepareStatement解决有关特殊字符插入到数据库的问题。如(',",),?)
CallableStatement─ 由方法prepareCall 所创建。CallableStatement对象用于执行SQL 储存程序─ 一组可通过名称来调用（就象函数的调用那样）的SQL 语句。CallableStatement对象从PreparedStatement 中继承了用于处理 IN 参数的方法，而且还增加了用于处理 OUT 参数和 INOUT 参数的方法。
Statement接口提供了执行语句和获取结果的基本方法。PreparedStatement接口添加了处理IN 参数的方法；而CallableStatement 添加了处理 OUT 参数的方法。
PreparedStatement:对于同一条语句的多 次执行,Statement每次都要把SQL语句发送给数据
库,这样做效率明显不高,而如果数据库支持预编 译,PreparedStatement可以先把要执行的语句一次发给它,然后每次执行而不必发送相同的语句,效率当然提高,当然如果数据库不支持预编 译,
PreparedStatement会象Statement一样工作,只是效率不高而不需要用户工手干预.另外PreparedStatement还支持接收参数.在预编译后只要传输不同的参数就可以执行,大大提高了性能.
CallableStatement:是PreparedStatement的子类,它只是用来执行存储过程的.
prepareCall()方法错误地放到某个循环体中
Connectionconn = null;
PreparedStatement stmt = null;
CallableStatementcs = null;
String sql = null;
try {
InitialContextic = new InitialContext();
DataSourceds = (DataSource)ic.lookup("ECSN/DS/SMSCenter");
conn =ds.getConnection();
for(inti = 0; i < 1000; i++) {
String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?)}";
cs = conn.prepareCall(sql); // 一般prepareCall()方法不应该放到循环体中
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setString(2, "13701163936");
cs.setString(3,"短信内容");
cs.execute();
intoks = cs.getInt(1);
// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中
// 执行到第299次时，抛异常ORA-01000:maximum open cursors exceeded
// 注：连接的数据库为oracle10g，open_cursors= 300
Statement、PreparedStatement、CallableStatement(2)
}
}catch(Exceptione){
System.out.println(e.getMessage());
}finally{
// 把ResultSet，Statement，Connection的close()调用，放在finally块中
// 即使try块中程序抛了未catch的异常（如RuntimeException或Error），也能确保被执行
// 从而避免了连接长时间不关闭，导致连接池泄漏（leakage）
if (cs!= null) try { cs.close(); } catch (Exception e) { }
if(conn != null) try { conn.close(); } catch (Exception e) { }
}
Connectionconn = null;
PreparedStatementstmt = null;
CallableStatement cs = null;
Stringsql = null;
try {
InitialContextic = new InitialContext();
DataSourceds = (DataSource)ic.lookup("ECSN/DS/SMSCenter");
conn= ds.getConnection();
for(inti = 0; i < 1000; i++) {
String sql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?)}";
cs = conn.prepareCall(sql); // 一般prepareCall()方法不应该放到循环体中
cs.registerOutParameter(1, OracleTypes.NUMBER);
cs.setString(2, "13701163936");
cs.setString(3,"短信内容");
cs.execute();
intoks = cs.getInt(1);
// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中
// 执行到第299次时，抛异常ORA-01000:maximum open cursors exceeded
// 注：连接的数据库为oracle10g，open_cursors= 300
}
}catch(Exception e){
System.out.println(e.getMessage());
}finally{
// 把ResultSet，Statement，Connection的close()调用，放在finally块中
// 即使try块中程序抛了未catch的异常（如RuntimeException或Error），也能确保被执行
// 从而避免了连接长时间不关闭，导致连接池泄漏（leakage）
if (cs!= null) try { cs.close(); } catch (Exception e) { }
if(conn != null) try { conn.close(); } catch (Exception e) { }
}
原因：上述代码将prepareCall()函数放在了循环体中，而且语句执行完后又不关闭，导致oralce会话打开游标数超过最大上限open_cursors。一般 来说，preparedStatement()和prepareCall()函数每调用一次，即打开一个新的游标。
解决方法：将prepareCall()挪到循环体外，即可解决此问题。当然，也可以循环体内每次执行完后，调用cs.close()。但这样的话jvm负担加重，因 为cs对象不断被创建抛弃。
Stringsql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";
cs =conn.prepareCall(sql); // 挪到循环体外，避免游标打开过多
cs.registerOutParameter(1, OracleTypes.NUMBER);
for(inti = 0; i < 1000; i++) {
cs.setString(2,"13701163936");
cs.setString(3, "短信内容");
cs.execute();
intoks = cs.getInt(1);
// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中
// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded
// 注：连接的数据库为oracle10g，open_cursors= 300
}
Stringsql = "{call ? := VM.Pckg_Srv_Sms.FUNC_SEND_SMS(?,?) }";
cs =conn.prepareCall(sql); // 挪到循环体外，避免游标打开过多
cs.registerOutParameter(1, OracleTypes.NUMBER);
for(inti = 0; i < 1000; i++) {
cs.setString(2,"13701163936");
cs.setString(3, "短信内容");
cs.execute();
intoks = cs.getInt(1);
// 语句执行完成后cs没有被close()，而直接放到jvm的垃圾收集器中
// 执行到第299次时，抛异常ORA-01000: maximum open cursors exceeded
// 注：连接的数据库为oracle10g，open_cursors= 300
}
CallableStatement的批处理
PreparedStatement的批处理非常简单，网上范例也很多，此处不必赘 言。而CallableStatement的批处理很少使用，可能是这样做的性能改善并不像PreparedStatement那么明显。因为CallableStatement是PreparedStatement的子类，所以也继承了executeBatch()方法，具有批次执行的功能。 但限制条件也很苛刻：存储过程必须全是in参数，而不能有out或inout参数。即只能调用空参，或参数全为IN的procedure，而不能是 function。如果具有out参数，则一旦调用cs.registerOutParameter()方法即报错。
在[1]的 7.1.3的范例中，列举了CallableStatement的批处理写法：
viewplaincopy to clipboardprint?
CallableStatementcstmt = con.prepareCall("{call updatePrices(?, ?)}");
cstmt.setString(1, "Colombian");
cstmt.setFloat(2,8.49f);
cstmt.addBatch();
cstmt.setString(1,"Colombian_Decaf");
cstmt.setFloat(2, 9.49f);
cstmt.addBatch();
int []updateCounts = cstmt.executeBatch();
CallableStatementcstmt = con.prepareCall("{call updatePrices(?, ?)}");
cstmt.setString(1, "Colombian");
cstmt.setFloat(2,8.49f);
cstmt.addBatch();
cstmt.setString(1,"Colombian_Decaf");
cstmt.setFloat(2, 9.49f);
cstmt.addBatch();
int []updateCounts = cstmt.executeBatch();
疑问：updateCount到底是通过什么方式返回 的呢？在文档中，也只是泛泛地提到："... Further, the stored procedure must returnan update count..."，因为批处理不能调用function，所以肯定不是通过函数返回值传递，那又通过什么方式呢？是不是通过SQL%ROWCOUNT来 传递，试了好像不起作用。
通过setExecuteBatch()方法和标准的executeUpdate()方法实现批量处理更 新和插入。
如果成批地处理插入和更新操作，就能够显著地减少它们所需要的时间。Oracle提供的Statement和CallableStatement并不真正地支持批处理，只有PreparedStatement对象才真正地支持批处理。我们可以使用 addBatch()和executeBatch()方法选择标准的JDBC批处理，或者通过利用PreparedStatement对象的setExecuteBatch()方法和标准的executeUpdate()方法选择速度更快的Oracle专有的方法。要使用Oracle专有的批 处理机制，可以以如下所示的方式调用setExecuteBatch()：
PreparedStatementpstmt3D null;
try {
((OraclePreparedStatement)
pstmt).setExecuteBatch(30);
...
pstmt.executeUpdate();
}
Jdbc 和hibernate
一、Jdbc是java连接数据库的基础，无论那种框架,只要用到数据库都需要jdbc，所以掌握了jdbc的细节了才能用好更高级的框架，如 hibernate。
以下总结了jdbc的性能和效率（主要从处理时间和内存消耗上做出比较），希望能给大家带了益处。
1．Statement和PreparedStatement
2. 插入数据（一次1万条）。
3. 读数据。
4. 脏数据的问题。
5. 锁表。
6. 索引
7. 索引和select
8. 索引和insert,update
1.Statement和PreparedStatement（后续有例子）
1)Statement
特 点：statement不会初始化，没有预处理，每次都是从0开始执行SQL。
优点：sql语句直观；对于只执行一次的sql（就 是sql语句执行一次后就基本不再用了）存取的时侯， Statement比PreparedStatement 性能高。因为PreparedStatement需要预编译。
缺点：对于需要反复执行的sql语句，则效率远远低于PreparedStatement。
对于oracle，会缓存sql语句，如果以Statement方式多次提交的相同语句sql，其中仅参数不同，oracle也会作为不同的语句缓存起来，可以查看v$sqlarea.
这就是为什么叶飞要发邮件强调的“绑定变量问题”，垃圾sql多了。 呵呵，我们新业务部在这个问题上受到了表扬。
2）PreparedStatement
特点：prepareStatement会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。
优 点：当sql重复多次执行时，PreparedStatement将会大大降低运行时间，加快访问数据库的速度；参数可以有?表示，方便和易读; 安全和可靠:保证数据的合法性和有效性。较少的占用Oracle的缓存空间。
对于大数据量的操作可以用批处理的方式进行，大大提高 了性能。
2. 插入数据：1）Statement插入2）PreparedStatement插入3）PreparedStatement.executeBatch()插入。4）CallableStatement。
注：1) 用CallableStatement存储过程插入数据比较少见，我这个存储过程仅仅传来一个数字参数，来告诉存储过程要插入多少数据，仅仅想看看在写入 相同的数据时消耗在网络上时间有多少。
2）对于oracle, 一张表如果并发的insert和update比较频繁时可以设置in..参数。
有一个表t2(id,name)，分别用各种方式单独写入1万条数据。
测试时间：晚12：30。环境 我自己的机器连接19的oracle服务器。
每种情况单独运行3次的平均值。
方式
记录数insertinto t2(id,name)values(?,?)
处理时间(s)
内存消耗（M）
写入oracle最大数据量/秒
数据多时：
语句insertinto t2(id,name)values(?,?)
Statement插入
写入1万条
(42.819+43.738+43.233)/3=43.26S
(0.046+0.046+0.036)/3=0.042M
231条/每秒
PreparedStatement插入
写入1万条
(8.178+8.364+8.38)/3=8.30S
0.096+0.096+0.090=0.094M
1204条/每秒
PreparedStatement.executeBatch()
写入1万条
(0.328+0.313+0.312)/3=0.32S
(0.164+0.165+0.167)/3=0.165M
31250条/每秒（经验证8万条数据才用时1S）
CallableStatement
写入1万条
0.812+1.078+0.797=0.90S
忽略（和我写的存储过程有关）
11111条/每秒
数据少时：
Statement插入
写入1条
0.078S
PreparedStatement插入
写入1条
0.172s
PreparedStatement. executeBatch()
写入1条
0.156s
CallableStatement
写入1条
0.203s
我的存储过程有返回参 数，因此网络占据了一部分时间
可以看出在数据多时：PreparedStatement.executeBatch()的效率最高，Statement的效率最低。
在数据少时：Statement的效率最高，存储过程的 效率最低。说明下，我的存储过程有返回参数，因此网络占据了一部分时间。
在使用PreparedStatement.executeBatch()插入数据时，8万条才用时1S，可见数据量越大性能越高。
3. 读数据
关于读 数据可以说是
1）ResultSet.TYPE_FORWARD_ONLY：默认的游标方式Statement、PreparedStatement读和 CallableStatement读（取出游标）
2）ResultSet.TYPE_SCROLL_INSENSITIVE方式读
3)ResultSet.TYPE_SCROLL_SENSITIVE 方式读
方式
记录数
第一次处理时间(s)
第一次内存消耗（M）
第二次处理时间（S）
第二次内存消耗
写入oracle最大数据量/秒
TYPE_FORWARD_ONLY
Statemen读
读100万条
103.213+118.941 +111.929
11.97+11.97+11.97
106.384+ 118.255+101.745
10.70+10.70+10.70
TYPE_FORWARD_ONLY
PreparedStatement读
读100万条
90.343+83.767+83.611
oralce flashback 使用方法总结：
一，关键知识点
在oracle 10g中,如果是使用drop把表对象删除了，会把这个对象放在回收站里
查看回收站：select * from recyclebin;
然后恢复对象：flashback table TABLENAME to beforedrop;
删除不放回 回收站：drop table TABLENAME purge;
如果不小心误删除了某个表中的部分数据，需要恢复回来，可以利用oracleflashback闪回误删除的表数
据。但要注意：flashback table 需要下面几个条件：
1. 需要有flashback any table的系统权限或者是flashback这个表的对象权限;
2. 需要有对这个表的基本的dml，alter操作权限;
3. 必须保证该表row movement(这主要是让flashback记住表的rowid)
扩展：oracle flashback闪回不仅可以误删除的表数据，还可以错误的修改也可以恢复过来；
注意：
ORA-08189: cannot flashback the table because row movement is not enabled
但是抛出了8189错误，原因就是因为表没有row movement,改变下表的属性：
SQL> alter table test_tablename enable row movement;
再执行闪回操作；
二，实例
1.创建示例表：
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
2.插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID)VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
3.恢复误删除的表数据
例如：
FlashBack table TBL_TEST to timestamp to_timestamp('2009-8-116:59:36','yyyy-mm-dd
hh24:mi:ss');
报错：ORA-08189: cannot flashback thetable because row movement is not enabled
错误解决，要执行：alter table TBL_TEST enable rowmovement;
FlashBack table TBL_TEST to timestamp to_timestamp('2009-8-116:59:36','yyyy-mm-dd
hh24:mi:ss');
4.恢复误表数据错误的修改:
例如：
update TBL_TEST set pid=1 where id =5;
FlashBack table TBL_TEST to timestamp to_timestamp('2009-8-116:59:36','yyyy-mm-dd
hh24:mi:ss');
5.恢复被删除表的步骤:
(1)显示回收站信息
select * from RECYCLEBIN;
(2)确定被删除 表存在后，可以恢复诶删除表，此时可以指定表明或者回收站对象名
flashback table TBL_TEST to before drop;
或 flashbacktable "BIN$zgwx7wCsQ92JRcrAVpABQg==$0" to before drop
6.恢复被删除表的同时可以改变被删除表的名称
flashback table TBL_TEST to before drop rename to test_TBL_TEST
或 flashback table"BIN$zgwx7wCsQ92JRcrAVpABQg==$0" to before drop rename totest_TBL_TEST
7. 删除不放回回收站：
drop table TBL_TEST purge;
优化SQL语句的若干方法
1、操作符号： NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。
推荐方案：用NOTEXISTS 或（外连接+判断为空）方案代替 "IS NULL", "<>", "!=","!>", "!<", "NOT", "NOT IN","NOT LIKE", "LIKE '%500'"，
因为他们不走索引全是表扫描。
NOT IN会多次 扫描表，
使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,
而Exists比IN更快，最慢的是NOT操作。
使用in时，在IN后面值的列表中，将出现最频 繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
2、注意union和unionall的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
3、查询时尽量不要 返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
4、尽量少用视图， 它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资 料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要 使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开 销,查询受到干扰.
5、创建合理的索 引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段 或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根 据索引找到范围。select* from chineseresume where title in ('男','女')
Select * from chineseresume wherebetween '男' and '女'是一样的。
由于in会在比较多次，所以有时会慢些。
8、ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，最好选择少乘多
例如：表TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
selectcount(*) from tab1,tab2
执行时间0.96秒
选择TAB1作为基础表 (不佳的方法)
selectcount(*) from tab2,tab1
执行时间26.09秒
9、ORACLE采用自下而上的顺序解析WHERE子句,
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
低效,执行时间156.3秒
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;
高效,执行时间10.6秒
10、没有必要时不要用DISTINCT和ORDER BY，它们增加了额外的开销。这些动作可以改在客户端执行。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。
他们的执行顺序应该如下最优：select的Where字句选择所有合适的行，Group By用 来分组个统计行，Having字句用来剔除多余的分组。
这样Group By和Having的开销小，查询快.对于大的数据行进行分 组和Having十分消耗资源。
15、 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
DECODE的语 法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如 果不等于任何一个if值，则返回else。
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以 用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
16、计算记录条数
和一般的观点相反, count(*)比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
17、减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
例如:
低效
SELECT TAB_NAME
FROMTABLES
WHERETAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROMTABLES
WHERE (TAB_NAME, DB_VER)
= ( SELECT TAB_NAME, DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update多个Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
HERE EMP_DEPT = 0020;
18、避免在索引 列上使用NOT
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的
影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0
Oracle学习笔记
一，定义抽象数据类型
/*
create or replace type animal_ty as object
(breed varchar2(25), --动物种类
name varchar2(25), --名字
birthdate date, --出生日期
member function AGE(birthdate in date) return number --根据出生日期计算年龄
); --带有方法的抽象数据类型
*/
create or replace type animal_ty as object
(Bread varchar2(25), --动物种类
name varchar2(25), -- 名字
hobby varchar2(10) --爱好
);
desc animal_ty;
查看 user_types;
user_type_attrs;
二，抽象数据类型的使用
1)创建关系表
drop table animal;
create table animal
(id number primary key ,
anima animal_ty
);
set desc depth 3
desc animal;
2) 插入数据(使用构造方法)
insert into animal values(1,animal_ty('MULE','FRANCES','PLAY'));
insert into animal values(2,animal_ty('DOG','BENJI','EAT'));
insert into animal values(3,animal_ty('CROCODILE','LYLE','SWIM'));
3)操作
查看:select f.anima.name from animal f;
更新: update animal f set f.anima.hobby='PLAY1' where id=1;
删除：delete from animal awhere a.anima.hobby='PLAY1';
2,删除对象类型
drop type animal_ty force;
3,查询相关性
select name,type from user_dependencies wherereferenced_name='ANIMAL_TY';
4,在抽象数据类型上创建索引
create index Idx on animal(anima.name);
5,final 和 not final 修饰类型
instantiable 和not instantiable 可修饰类型和方法
默认情况下，抽象数据类型是不能被继承的，同时类型提供构造函数，类型中的方法可以被实
现
声明类型加 not final该类型可被继承
修饰方法: not instantiable类型不提供方法实现
修饰类型: not instantiable类型没有构造函数.不能实例化
二，可变数组
1）创建VARRAY类型
create or replace type tools_va as varray(5) ofvarchar2(25);
2）创建关系表，该表的字段类型是varray类型
create table borrower(name varchar2(25) primary key,
tools tools_va);
字典
select typecode,attributes from user_types wheretype_name='TOOLS_VA';
select coll_type,elem_type_owner,elem_type_name,upper_bound,
lengthfrom user_coll_types;
3）插入数据
insert into borrower values('JED HOPKINS',
Tools_va('HAMMER','SLEDGE','AX'));
insert into borrowervalues('PK',
Tools_va('PEN1','PEN2','PEN3'));
4）查看
select * from table(select t.toolsfrom borrower t where t.name='PK');
三，嵌套表
drop type emp_tyforce;
1,
create or replace type emp_ty as object
( empno number,
ename char(10),
sal number);
2,create or replace type emps_nt as table of emp_ty;
3,
create table ntdept
(
deptno number,
dname varchar2(20),
loc varchar2(20),
dno_det emps_nt)
nested table dno_det store as emp_nt_tab;
--插入数据
insert into ntdeptvalues
(10,'市场部','海珠北路',emps_nt(emp_ty(100,'MARRY',1000),
emp_ty(101,'Lili',1500),
emp_ty(102,'KHK',3000))
);
insert into ntdeptvalues(20,'教务部','海珠南路',emps_nt(emp_ty(103,'JAKE',2200),
emp_ty(104,'ROSE',1000),
emp_ty(105,'LUSU',2000)
));
--查询
可以使用the或table
select deptno,dname,loc,dno_det from ntdept;
select dno_det from ntdept where deptno=10;
select nt.* from table(select dno_det from ntdept where deptno=10)nt;
select nt.* from table(select dno_det from ntdept where deptno=20)nt where
nt.empno=103 ;
select nt.empno,nt.ename from table(select deptno from ntdeptwhere deptno=10)
nt where nt.empno=101;
--插入
insert into table(select dno_det from ntdept wheredeptno=10)
values(106,'HANG',3000);
或从嵌套表中选择数据插入
insert into ntdept values(40,'NEWd','NEWloc',
cast(multiset(select * from table(select dno_det from ntdeptwhere deptno=10)
nt where nt.empno=100) as emps_nt))
cast:将查询的结果转换成嵌套表的形式
multiset:允许cast查询中包含多条记录
--更新
update table(select dno_det from ntdept where deptno=10) nt setnt.ename='LLLL'
where nt.empno=101;
--删除
delete from the(select dno_det from ntdept where deptno=10) ntwhere
nt.empno=101
四，对象表
1,建立对象类型
drop type animal_ty force;
create type animal_ty as object
(name varchar2(15),
hobby varchar2(20)
)
/
2,建立对象表
create table animal of animal_ty(name constraintpk primary key);
3,插入数据
insert into animal values(animal_ty('SNAKE','PLAY'));
insert into animal values(animal_ty('DOG','SWIM'));
insert into animal values(animal_ty('CAT','SLEEP'));
4,查看oid
select ref(f) from animal f;
5,查看对象
select value(r) from animal r;
使用deref
droptable keeper;
create table keeper
(
keepername varchar2(10),
animalkept ref animal_ty
)
插入数据
insertinto keeper select 'JEK',ref(f) from animal f where name='DOG';
insert into keeper select 'BLK',ref(f) fromanimal f where name='CAT';
查看数据
在关系表中看对象
select deref(animalkept) from keeper;
假设删掉一个对象
delete from animal where name='DOG';
select* from keeper; 还在引用该对象
update keeper set animalkept=null where animalkept isdangling;--去掉引用
五，对象视图
将dept关系表做为对象表访问
1，建立对象类型
createor replace type dept_type as object
(dno number(2),
dname varchar2(15),
local varchar2(18)
);
2，建立对象视图
create view dept_view ofdept_type with object oid
(dno) as
select * from dept ;
3,通过对象视图操纵关系表数据
insert intodept_view values(dept_type(60,'aaa','bbb'));
delete from dept_view where deptno=60;
结果会反映到关系表中,当改变关系表数据时，也会反映到对象视图中
对象视图每行都有一个oid
4，当关系表之间存在引用关系时，也可以用对象视图实现
dept 和 emp是主、外键表，emp表的deptno引用了dept表的deptno
并且已为关系表dept生成了对象视图，此时关系表的每一个行都具有oid
dept表的行可以作为行对象通过dept_view视图进行访问，只要为关系表创建
了对象视图，即可将任何关系表作为行对象进行访问
--make_ref 只对所提供的参数应用一个Oracle内部的算法，即可得到引用，它并没有真
正读取视图。
select make_ref(dept_view,deptno) fromdept;
select make_ref(dept_view,deptno) fromemp;
5,通过对象视图建立引用关系
create view emp_ov as selectmake_ref(dept_view,deptno) dep,empno,ename,sal
from emp;
该视图第一列指向了对象视图dept_view的行
selectderef(a.dep) from emp_ov a;
在SQLPLUS下，实现中-英字符集转换
alter session set nls_language='AMERICAN';
alter session set nls_language='SIMPLIFIED CHINESE';
主要知识点：
一、有关表的操作
1)建表
create table test as select * from dept; --从已知表复制数据和结构
create table test as select * from dept where1=2; --从已知表复制结构但不包括数据
2)插入数据：
insert into test select * from dept;
二、运算符
算术运算符：+ - * /可以在select 语句中使用
连接运算符：|| select deptno|| dname fromdept;
比较运算符：> >= = != < <= like between isnull in
逻辑运算符：not and or
集合运算符： intersect ，union， union all， minus
要求：对应集合的列数和数据类型相同
查询中不能包含long列
列的标签是第一个集合的标签
使用order by时，必须使用位置序号，不能使用列名
例：集合运算符的使用：
intersect ，union， union all， minus
select * from emp intersect select * from emp where deptno=10 ;
select * from emp minus select * from emp where deptno=10;
select * from emp where deptno=10 union select *from emp where deptno in (10,20); --不包括重复行
select * from emp where deptno=10 union allselect * from emp where deptno in (10,20); --包括重复行
三，常用 ORACLE 函数
sysdate为系统日期 dual为虚表
一）日期函数[重点掌握前四个日期函数]
1,add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate S1,add_months(sysdate,10) S2,
add_months(sysdate,5) S3 from dual;
2,last_day [返回该月最后一天的日期]
select last_day(sysdate) from dual;
3,months_between[返回日期之间的月份数]
select sysdate S1, months_between('1-4月-04',sysdate) S2,
months_between('1-4月-04','1-2月-04') S3 from dual
4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
select sysdate S1,next_day(sysdate,1) S2,
next_day(sysdate,'星期日') S3 FROM DUAL
5,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
6,trunc[截断到最接近的日期]
select sysdate S1,
trunc(sysdate) S2,
trunc(sysdate,'year') YEAR,
trunc(sysdate,'month') MONTH ,
trunc(sysdate,'day') DAY from dual
7,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
二）字符函数(可用于字面字符或数据库列)
1，字符串截取
select substr('abcdef',1,3) from dual
2,查找子串位置
select instr('abcfdgfdhd','fd') from dual
3,字符串连接
select 'HELLO'||'hello world' from dual;
4, 1)去掉字符串中的空格
select ltrim(' abc') s1,
rtrim('zhang ') s2,
trim(' zhang ') s3 from dual
2)去掉前导和后缀
select trim(leading 9from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3 from dual;
5,返回字符串首字母的Ascii值
select ascii('a') from dual
6,返回ascii值对应的字母
select chr(97) from dual
7,计算字符串长度
select length('abcdef') from dual
8,initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3 from dual;
9,Replace
select replace('abc','b','xy') from dual;
10,translate
select translate('abc','b','xx') fromdual; -- x是1位
11,lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1,rpad('func',15,'-') s2 from dual;
select lpad(dname,14,'=') from dept;
12, decode[实现if ..then逻辑]
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
三）数字函数
1，取整函数（ceil 向上取整,floor 向下取整）
select ceil(66.6) N1,floor(66.6) N2 from dual;
2, 取幂(power)和求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;
3,求余
select mod(9,5) from dual;
4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
5,返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;
四）转换函数
1，to_char()[将日期和数字类型转换成字符类型]
1) select to_char(sysdate) s1,
to_char(sysdate,'yyyy-mm-dd') s2,
to_char(sysdate,'yyyy') s3,
to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
to_char(sysdate, 'hh24:mi:ss') s5,
to_char(sysdate,'DAY') s6 from dual;
2) select sal,to_char(sal,'＄99999')n1,to_char(sal,'＄99,999') n2 from emp
2, to_date()[将字符类型转换为日期类型]
insert into emp(empno,hiredate)values(8000,to_date('2004-10-10','yyyy-mm-dd'));
3, to_number() 转换为数字类型
selectto_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
五）其他函数
user:
返回登录的用户名称
select user from dual;
vsize:
返回表达式所需的字节数
select vsize('HELLO') from dual;
nvl(ex1,ex2):
ex1值为空则返回ex2,否则返回该值本身ex1（常用）
例：如果雇员没有佣金，将显示0，否则显示佣金
selectcomm,nvl(comm,0) from emp;
nullif(ex1,ex2):
值相等返空，否则返回第一个值
例：如果工资和佣金相等，则显示空，否则显示工资
selectnullif(sal,comm),sal,comm from emp;
coalesce:
返回列表中第一个非空表达式
selectcomm,sal,coalesce(comm,sal,sal*10) from emp;
nvl2(ex1,ex2,ex3) :
如果ex1不为空，显示ex2,否则显示ex3
如：查看有佣金的雇员姓名以及他们的佣金
selectnvl2(comm,ename,') as HaveCommName,comm from emp;
六）分组函数
max min avg count sum
1，整个结果集是一个组
1) 求部门30的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和
select max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp where deptno=30;
2, 带group by和 having 的分组
1)按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno;
2)部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno having deptno=30;
3, stddev 返回一组值的标准偏差
select deptno,stddev(sal) from emp group by deptno;
variance 返回一组值的方差差
select deptno,variance(sal) from emp group by deptno;
4, 带有rollup和cube操作符的Group By
rollup 按分组的第一个列进行统计和最后的小计
cube 按分组的所有列的进行统计和最后的小计
select deptno,job ,sum(sal)from emp group by deptno,job;
select deptno,job ,sum(sal)from emp group by rollup(deptno,job);
cube 产生组内所有列的统计和最后的小计
select deptno,job ,sum(sal)from emp group by cube(deptno,job);
四、临时表
只在会话期间或在事务处理期间存在的表.
临时表在插入数据时，动态分配空间
create global temporary table temp_dept
(dno number,
dname varchar2(10))
on commit delete rows;
insert into temp_dept values(10,'ABC');
commit;
select * from temp_dept; --无数据显示,数据自动清除
on commit preserve rows:在会话期间表一直可以存在（保留数据）
on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据）
oracle命令
<FONT face=宋体 size=3>1.启动数据库
cd @ORACLE_HOME/bin
lsnrctl
start
2. @ORACLE_HOME/dbs目录下存放实例配置文件
3. 在java中以odbc方式连接oracle
1.配置sqlnet easy configration
2.在windows中配置 odbc,添加 system DSN,其中data source name可任意指定,
sol*net connect string为在sol*net中指定的Database alias
3.确保path指向orawin95\bin
4.执行 oracle中的32-bit odbc test测试连接
5. java中参数指定如下:
driver: "sun.jdbc.odbc.JdbcOdbcDriver",
dbURL: "jdbc:ODBC:odbc DSN 名",
logid: "",
logpass: ""
4. 在java中使用oracle的 driver连接oracle
driver: "oracle.jdbc.driver.OracleDriver",
dbURL: "jdbc:oracle:thin:@机器名:1521:数据库",
logid: "",
logpass: "");
5. 设置日期格式
服务器端: exportNLS_DATE_FORMAT='DD-MON_RR'
客户端: 进入win95注册表,选KEY_LOCAL_MACHINE->SOFTWARE->ORACLE,然后
从菜单选择编辑-> 新建->字符串值:输入NLS_DATE_FORMAT,然后编辑这个
新串,在值域输入DD_MON_RR(或RRRR)
6. 处理用户被锁住的方法
首先执行下列语句,确定用户是否真的被锁住:
select a.username,a.sid,a.serial#,b.id1,c.sql_text
from v$session a,v$lock b,v$sqltext c
where a.lockwait = b.kaddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value;
USERNAME SID SERIAL# ID1 SQL_TEXT
--------- --- ----------- ---------
SCOTT 11 9 131080 update
plsql_user.s_employee
set salary=5000
结果SCOTT在做 update时被锁住了
下一步确定是谁锁住了该用户,这个用户在做何种操作:
select a.username,a.sid,a.serial#,b.id1,c.sql_text
from v$session a,v$lock b,v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d,v$lock e
where d.lockwait=e.kaddr)
and a.sid = b.sid
and c.hashvalue=a.sql_hash_value
and b.request=0;
USERNAME SID SERIAL# ID1 SQL_TEXT
--------- --- ----------- ---------
PLSQL_USER 10 26 131080 update s_employee
set salary=10000
结果显示是由于PLSQL_USER和SCOTT对同一个表做update时将SCOTT锁住了
可以通过kill session释放锁,例如可以kill掉PLSQL_USER的session:
alter system kill session '10,26'
7. 使用查询优化器
Oracle Server提供了基于成本(Cost Based)和基于规则(Rule Based)
两种优化器,简称为CBO和RBO,用于查询操作的执行计划.可以使用下列
方法使用CB
方法1:在 INIT<sid>.ORA文件中设置参数
OPTIMIZER_MODE=choose
方法2:在session级设置
OPTIMIZER_GOAL=FIRST_ROWS 或ALL_ROWS
例如:
alter session set optimitzer_goal=first_rows
方法3: 在查询中使用hint,包括:
CHOOSE,ALL_ROWS,FIRST_ROWS等
8. 确定某个查询使用了那一种优化器
使用EXPLAIN PLAN命令生成该查询语句的执行计划,然后检查PLAN_TABLE
表中POSITION字段,如果值为NULL,说明使用了Rule based优化器,否则表明
使用了Cost Based优化器:
select decode(nvl(position,-1),-1,'RBO','CBO')
from plan_table
where id=0;
9. 使用estimate方式对表进行分析
10. 建立一个与现存数据库相同,但不包含数据的空库
对全库作俄 Export或Import时,使用参数ROWS=N
例:
exp system/manager full=Y rows=N file = full.dmp
imp system/manager full=Y rows=N file=full.dmp
11. 使某些host不能通过SQL*NETV2访问Oracle server
可以通过编辑$ORACLE_HOME/network/admin/protocol.ora文件完成
例如: protocol.ora
tcp.validnode_checking=yes
tcp.invited_nodes=(138.3.39.150)
这样只有138.3.39.150才能通过sol*netv2访问Oracle Server
又例如: protocol.ora
tcp.validnode_checking=yes
tcp.exclued_nodes=(138.3.39.150)
这样138.3.39.150被拒绝
12.在sol*plus中,向表中插入"'"
可以用chr(39)来标示"'"
例如:
insert into test values('i'||chr(39)||'m');
select * from test;
coll
---
i'm
13. 在win95或winNT上运行oracle Net8 Assistant或 Net8 EasyConfguration
时报错"unhandledexception erro in java.exe"
oracle Net8 Assistant 或 Net8 EasyConfguration调用了java,而java
在win95或winNT上对系统的分辨率和颜色都有要求,应把系统的分辨率
调到大于640X480,颜色大于256但不能设置成true color
14. 安装oracle 8 for windowsNT需要NT的service pack 3
15. 在oracle7和oracle8之间export/import数据
必须先在 oracle8数据库上以internal或sys用户运行catexp7.sql
svrmgr>connect internal
svrmgr>@?/rdbms/admin/catexp7.sql
16. 为远程用户设置"internal"口令
先在数据库端的init<sid>.ora文件中设置
remote_login_passwordfile=execlusive
并且为oracle owner设置环境变量
ORA_<SID>_PWFILE=orapw<sid>.pwd
然后以oracle owner运行以下命令:
$cd $ORACLE_HOME/dbs
$orapwd file=orapw<sid>.pwd password=<passwd> entries=5
17. 手工安装sqlplus的help facility
以oracle用户运行以下命令
$cd $ORACLE_HOME/bin
$SYSTE system/manager;export SYSTEM_PASS
$helpins
18. 查出前台正在发出的sql语句
先查出正在运行的前台程序的sid:
sql>select sid,serial#,username,program
from v$session
where status='ACTIVE';
然后根据上面得到的sid,可查出正在运行的前台程序发出的sql语句:
sql>select user_name,sql_text
from v$open_cursor
where sid=xx;
19. 在数据库一级设置sqltrace
在init.ora中加入参数sql_trace=true,然后重新启动
20. 在sql*plus中对某一session设置sql trace
sql*plus>alter session set sql_trace=true;
可以使用下列命令关闭trace
sql*plus>alter session set sql_trace=false;
21. 移动数据文件的位置
1. 正常关闭数据库
2. 复制文件到新位置
$cp $ORACLE_HOME/dbs/dbsNEW.DBF /usr3/oracle/db2NEW.dbf
3.svrmgrl
connect internal;
startup mount;
alter database rename file'<$ORACLE_HOME>/dbs/db2NEW.dbf'
to 'usr3/oracle/db2NEW.dbf';
alter database open
22. 移动LOG文件的位置
可以先添加新的LOG文件,再删除原来的LOG文件
svrmgrl>connect internal;
alter database add logfile
'<$ORACLE_HOME>/usr3/oracle/logNEW.dba';
alter database drop logfile
'<$ORACLE_HOME>/dbs/logNEW.dbf';
23. 确定安装了何种NETWORK DRIVER
使用'nm'命令,例如tcp/ip driver的符号为'nttini',要检查oracle是否
链接了该符号,可以利用下列命令:
nm oracle|grep nttini
如果nttini存在,表示rdbms已经链接了tcp/ip driver
24. 检查已安装的sql*net版本
可以在?/orainst目录下使用'inspdver'命令检查
$inspdver|grep SQL*NET
2.3.4.0.0 SQL*NEW(V2)
25. 备份数据库
把orant\database目录备份下来即可
26. 在unix下察看实例名
.pfofile 文件 </FONT></P> <P ><FONT size=3><FONTface="Times New Roman"><p></p></FONT></FONT></P>
Oracle学习笔记---(四)
在system用户中创建用户和授权：
create userusera identified by usera defalut tablespace test;
grantconnect,resource tousera;
一，同义词
分私有和公共
私有:普通用户创建的，只有创建该同义词的用户才可以使用
前提：普通用户具有对scott用户的表emp具有访问权利
connect scott/tiger;
grant all on empto usera; --all 包括：select ,update ,insert ,delete
connect usera/usera;
create synonym emp forscott.emp;
select * from emp;
公共:公共一般由dba创建，需要具有create public synonym系统权限,如普通用户要创建需要
connect system/manager;
grant create publicsynonym to usera;
conect usera/usera;
create public synonym emp for scott.emp;
其他用户也可以使用公共同义词 emp
查看用户自己创建的同义词:user_synonyms
select synonym_name fromuser_synonyms;
二，序列
通常和表一起使用，用来生成唯一主键值，在插入数据时使用，但不属于任何表，独立于表存在
createsequence deptseq
increment by 10
startwith 50
insert into dept values(deptseq.nextval,'Sale_dept','HaizhuStreet');
使用伪列nextval 和 currval
nextval:创建序列后第一次使用返回序列初始值，既start with指定的值，后续使用增量后的值
currval:返回序列的当前值
数据词典: user_sequences
三、视图
在创建视图时可以使用groupby ,order by子句,函数等,使用函数时需要指定列别名
1, 简单视图：使用单表查询创建的视图
可以对视图进行增删改操作,除了一些限定外，如：创建只读视图，或带有with chek option选项的视图，或视图中不包含
基表中不允许为空的列(不允许向视图中插入数据)等
1）使用with checkoption 可以限定对单表视图的修改, 不能更新无法通过该视图查看的行
create or replace view emp_view as select * from emp where job='SALESMAN' with check optionconstraint empv;
SQL>select * from emp_view;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- --------------------
7499 ALLEN SALESMAN 7698 20-2月-81 1600 300 30
7521 WARD SALESMAN 7698 22-2月-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月-81 1500 0 30
update emp_view set job='CLERK' where empno=7499;将无法更新
如创建视图时没有with check option，修改视图后将在视图查不到该行数据
2）使用with read only 创建只读视图
create or replace view emp_view as select * from emp where job='SALESMAN' with read only;
3) 创建视图时使用force,强制创建视图
如果视图定义中引用了不存在的表或表中有不存在的列，或创建视图的所有者没有访问表的权限，都可以创建，
但创建的视图有错误，在稍后创建表或得到访问权限后，ORACLE自动重新编译使视图有效，可以手动编译
假设dept表也不存在a1列，但还可以创建视图，但会提示错误，在表dept添加列a1后,视图自动编译为有效
create or replace force viewemp_view as select a1,dname from dept;
可以使用 alterview emp_view compile ; 手动编译
2, 复杂视图：
1)使用多表联接创建视图,
2)创建视图时包括函数或group by等
复杂视图通常不能直接进行增删改，但对于使用多表联接创建的视图，可以使用INSTEAD OF触发器可以修改(后面课程)
create or replace view emp_dept_view asselect empno,ename,dname,emp.deptno
from dept,emp wheredept.deptno=emp.deptno;
3, 键保留表
键保留表:复杂视图中出现的表。
如果满足条件:1)主键列全部显示在视图中，并且它们的值在视图中都是唯一且非空的.
如: createor replace view emp_dept_view as
select empno,ename,dname,dept.deptno
from dept,emp where dept.deptno=emp.deptno;
empn是emp表的主键,并且它的值在emp_dept_view中是唯一且非空.所以emp表是键保留表
而deptno的值在视图中不唯一,所以dept不是键保留表
可以修改视图中键保留表中的数据,主键值除外
update emp_dept_view set ename='JOE' where empno=7566; //可以
update emp_dept_view set dname='abc' where deptno=30; //不行 dname是dept表中的列,dept不是键保留表
4,分区视图
create or replace view v_emp as select *from emp1 partition(p1) union all select * from emp1 partition(p2);
数据字典:user_views
使用方法：在SQL*PLUS下
colview_name for a10 --定制显示的列宽
coltext for for a50
setlinesize 200 --设置每行显示的字符数
selectview_name,text from user_views; --查看用户创建的视图情况
四，索引
使用索引的目的：
1）强制唯一 ：在primary key列或UNIQUE 列上自动创建唯一索引
2）提高查询速度:当查询使用索引时，速度会有戏剧性的提高
只有在查询中使用where或 order by 时，才使用索引
1，唯一索引
create unique index ind2 on dept(deptno);
定义索引的列中，表的任意两行的值都不相同
2, 组合索引
在表的多列上创建的索引
create index comind1 on emp(deptno,empno)
3, 反向键索引
该索引反转索引列中的每一个字节
使插入操作分布在整个索引上，避免插入数据时索引性能的降低。但会使查询变慢
create index revind1 on salgrade(grade) reverse;
可将反向键索引改为标准索引
alter index revind1 rebuild noreverse;
4,位图索引(可在单个或多个连接表上创建)
如果列重复的次数超过了100次，则可以考虑在该列上建立位图索引。既不同值的数目比行数少的情况
表有1000000行，某列有10000个不同值,可在该列上建立位图索引
在位图索引中，使用每个键值的位图，而不是使用rowid。位图中的每一位对应一个可能的rowid
映射函数
每位位置---------rowid(确定表的一行)
当where子句中有and 或 or 条件时，可直接在位图上进行布尔运算
create bitmap index bitemp on emp(deptno);
5,索引组织表
对表是基于主键的访问，而不是基于rowid的访问
表中的数据象索引一样被存储，表中就没有rowid,因此用户不能从索引组织表中选取rowid伪列的值
对于要求精确匹配的查询和基于范围的搜索，它提供了一种更快的访问数据的方法
表的数据存储在与其关联的索引中，索引中不存储rowid ,而是存储表的实际数据
create table indorg (
a1 number(2) primary key,
a2 varchar2(10)
)
organization index;
用户不能在indorg表的其它列上创建索引，当数据很少变化时，使用索引组织表
6,基于函数的索引
如果在where中某列上使用了函数，即使在列上创建了索引，索引也不会被使用。所以可以创建函数索引
create index exind1 onemp(length(ename)); --基于函数创建索引
create index exind2 onemp(sal+nvl(comm,0)); --基于表达式创建索引
7,键压缩索引
为了节省索引块的空间,相同的列值，作为前缀存储一次,后缀是不同的rowid
create index exind1 onemp(length(ename)) compress 1; --作为前缀处理的键列的数目为1
create index exind2 onemp(deptno) compress 1;
8,索引分区
当用户建立了分区表后，也可以在该表上创建索引,这个索引可以按照用于表分区同样值范围进行划分
create table emp1(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
partition by range(empno)
(
partition p1 values less than (7566),
partition p2 values less than (7900),
partition p3 values less than (maxvalue) )
1) 使用local关键字，可以为每一个分区建立一个独立的索引,
这个索引命令将建立３个独立的索引，每个索引对应一个分区
数据字典：
selectindex_name,partition_name ,tablespace_name from user_ind_partitions
i) 本地无前缀索引
create index emp_ind1 on emp1(ename) local
ii) 本地前缀索引(索引的第一列与分区的第一列相同)
createindex emp_ind1 on emp1(empno) local;
或指定不同的索引表空间
create index emp_ind1 on emp1(empno) local
( partition p1 tablespace test1_idx,
partition p2 tablespace test2_idx,
partition p3 tablespace test3_idx)
如果分区上执行了修改，会自动重建索引
SQL> col segment_name for a20
SQL> select segment_name,partition_name,segment_type,tablespace_namefrom user_segments where segment_name='EMP_IND1';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------------------------------------ ------------------------------
EMP_IND1 P1 INDEX PARTITION SYSTEM
EMP_IND1 P2 INDEX PARTITION SYSTEM
EMP_IND1 P3 INDEX PARTITION SYSTEM
2) 使用global关键字,创建全局索引
i) 全局无前缀索引
create index emp_ind2 on emp1(ename)global;
ii) 全局前缀索引
建立全局前缀索引(empno为前缀，不能使用ename做索引列)
create index emp_ind2 on emp1(empno,ename)
global
partition by range(empno)
(
partition p1 values less than (6000) ,
partition p2 values less than (7500) ,
partition p3 values less than (maxvalue)
)
对分区的任何操作都会使全局索引无效，需要重建索引
注释：不能手动删除索引分区，当所引用的数据从表分区中删除时，索引分区自动删除
五，簇
簇是一组表，由两个或多个拥有公共列的表组成
簇是用来存储表的方法，这些表相互联系密切并通常相连接在磁盘的相同区域上。
如：emp 表存储在磁盘的一个区域(section)内,而dept表存储在另一区域内，它们的行可以被连接到同一区域上，该区域
称为簇（cluster）,簇关键字通常是在查询中把表连接起来的一列或几列(如dept,emp中的deptno),要聚簇这些表，用户必须
是表的所有者
1,创建了空簇
create cluster emp_dept(deptno_key number(2));
创建了空簇（如同建表一样，也设置了空间）
2,下面创建包含在簇中的表
create table dept
(
deptno number(2) primary key,
dname char(14),
local char(13)
)
cluster emp_dept(deptno);
--deptno为表中将要存储在簇关键字deptno_key中的列名
create table emp
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)references dept
)
cluster emp_dept(deptno);
3, 建立簇索引(在向簇表插入数据之前建立簇索引)
createindex emp_dept_NDX on cluster emp_dept;
4, 插入数据
insert into emp select * from scott.emp;
insert into dept select* from scott.dept;
两个表的数据实际存储在同一位置，簇好象是包含两个表数据的一个大表
///////////////
Oracle学习笔记---(三)
三
一，锁
为了防止用户在同一时间并发地访问和修改资源，ORACLE使用不同类型的锁控制对数据的并发访问，以防止用户之间出现破坏性的交互操作
，oracle 为处理事务自动锁定资源。
锁在SQL语句开始它们与数据的相互作用时获得，并在事务的整个过程中有效
oracle9i使用两种锁模式：
.独占模式（排他）：不允许其他任何并发会话以任何方式共享锁定的资源，修改数据时需要这种锁。
.共享模式：允许对同一块数据的并发读访问。在更改数据时，上升为独占模式
一)行级锁
insert update delete 隐式加行锁(排他)
select ... for update 显示加行锁(共享)
select ...for update 用于显示锁定将要更新的数据行，防止其他用户在更新之前操作此行
如：select * from empwhere deptno=30 for update
update emp set ename='Joke' where empno=7499;
在锁释放之前，其他用户不可以对锁定的数据行进行(修改，删除)操作，查询可以
假如有其他用户要锁定同一资源：可以使用wait子句对锁的等待时间控制
如： 在另一用户中：select * from emp where deptno=30 for update wait 2 (等待2秒 )
如2秒钟还未释放资源，系统将会给出提示信息
二)表级锁
共享模式（in share mode)
共享更新模式（in share update mode)
排他锁模式
锁定表的通用语法：
lock table 表名 in <share or share update or exclusive mode>;
1) 共享模式
不允许其他用户插入，更新和删除行,多个用户可以同时在同一表上设置共享锁，这样设置锁的多个用户都只能执行查询
lock tableemp in share mode;
2）共享更新模式（in share update mode)
允许多个用户同时锁定表的不同行,允许其他用户进行DML(insert update delete select)操作 ,除了已锁定的行
如： lock table emp in shareupdate mode;
select * from emp where deptno=30 forupdate //锁定的行
其他用户不能delete ,update部门30的雇员信息
其他用户可以查看锁定的行: select * from emp where deptno=30
3）排他锁模式(限制性强)
不允许其他用户插入，更新和删除行,允许查看数据，但只有一个用户可以在表中放置排他锁
lock table emp in exclusive mode;
三) 死锁
如：USERA: lock tablescott.emp in share mode;
USERB: lock table scott.emp in share mode ;
USERA: update scott.emp setename='Smith' where empno=7369;
USERB: update scott.emp setjob='CLERK' where empno=7521;
发生死锁
二，表分区
分区的类型：
1)，范围分区:分区基于某一特定列或一组列的值的范围
2)，散列分区：数据基于hash函数进行分区
3)，复合分区：首先基于范围分区，然后使用HASH函数进一步划分为子分区
4), 列表分区：通过在每个分区的描述中指定分区键的离散值列表,
允许按自然方式对无序和不相关的数据集进行分组和组织
一)范围分区
1) 创建分区表（一列）
1,创建分区表(一列分区键empno)
create table emp1(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
partition by range(empno)
(partition p1 values less than (7566),
partition p2 values less than (7900),
partition p3 values less than (9999))
2,插入数据
insert into emp1 select * fromscott.emp;
3,显示分区数据
select * from emp1partition(p1); --分区键empno,小于7566的empno插入到p1分区
select * from emp1partition(p2);
select * from emp1partition(p3);
4,数据字典
col table_name for a15;
col partition_name for a15;
select table_name,partition_name fromuser_tab_partitions;
2）创建分区表（两列）
1, 创建分区表（两列deptno,empno）
create table emp2(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
partition by range(deptno,empno)
(partition om1 values less than (20,7566), --分区边界的左部分优先,当empno<7566时,deptno=20的记录也将被插入
partition om2 values less than (30,7900),
partition om3 values less than (maxvalue,maxvalue))
2, 插入数据
insert into emp2 select * fromemp;
3, 显示分区数据
select * from emp2partition(om1);
select * from emp2partition(om2);
select * from emp2partition(om3);
3) 按日期类型分区
create table emp3(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
partition by range(hiredate)
(partition om1 values less than (to_date('1980-12-31','yyyy-mm-dd')),
partition om2 values less than (to_date('1981-12-31','yyyy-mm-dd')),
partition om3 values less than (to_date('1982-12-31','yyyy-mm-dd')))
二)散列分区
使用hash函数将数据划分到分区中
create table dept1(deptno number(2),
dname varchar2(14),
loc varchar2(13))
partition by hash(deptno)
(partition p1,partition p2);
insert into dept1 select* from scott.dept;
三）复合分区
create table salgrade1(gradenumber,losal number,hisal number)
partition by range(grade)
subpartition byhash(losal,hisal)
(
partition p1 values less than (3)
(subpartition sp1,subpartition sp2),
partition p2 values less than (6)
(subpartition sp3,subpartition sp4)
)
insert into salgrade1 select * from scott.salgrade;
select *from salgrade1 partition(p1);
select *from salgrade1 subpartition(sp1);
select *from salgrade1 subpartition(sp2);
四)列表分区
create table emp4
(empno number,
ename varchar2(10),
deptno number)
partition by list(deptno)
(
partition p1 values(10,20),
partition p2 values(30)
)
insert into emp4 select empno,ename,deptno from scott.emp;
select * from emp4 partition(p1);
三，维护分区
--表emp1在其用户所在的表空间中,创建用户时指定，没指定默认为系统表空间
1)在system用户中授权：
grant create tablespaceto scott;
2)登录到scott用户
connect scott/tiger; --假设在服务器端，客户端要加连接字符串
3)创建表空间test,该表空间包含一个数据文件,大小是5M
create tablespace test datafile
'd:\ora1.dbf' size 5m;
1,移动分区
alter table emp1
move partition p1 tablespace test;--将分区移动到表空间test
2,添加分区
alter table emp3
add partition om4 values lessthan(to_date('1984-12-31','yyyy-mm-dd'));
3,删除分区
alter table emp3
drop partition om4;
4,结合分区--用于散列分区
使用散列方法分区的表中，可以将某个分区的内容分发到由hash函数确定的一个或多个分区中，然后清除选定的分区
alter tabledept1 coalesce partition;
5，截断分区
删除分区的数据（不能回滚）
alter table emp3 truncate partition om3;
delete from emp3partition(om1) //可以回滚
6, 拆分分区
原来的分区p2不存在了
--p21将包括:7566到7700的记录
--p22将包括:7701到7900的记录
alter table emp1 split partition p2 at(7700)
into(partition p21,partition p22);
7,合并分区
合并范围分区表中的相邻两个分区的内容，产生新的分区p2，原来的分区p21,p22不存在了
alter table emp1 mergepartitions p21,p22 into partitionp2;
8,交换表分区
非分区表的数据和已分区表的某个分区数据进行交换
要求：非分区表的结构要和已分区表相同
如：
1，建立分区表
createtable Pdept(deptno number(2),
dname varchar2(14),
loc varchar2(13))
partition by range(deptno)
(partition p1 values less than (20) tablespacetest, --指定分区所在的表空间
partition p2 values less than (maxvalue) tablespace test);
2,插入（分区表）数据
insertinto Pdept select * from dept;
3,建立非分区表
create tableSdept as select * from dept where 1=2;
4,插入（非分区表）数据
insertinto Sdept values(60,'MyDept','MyLoc');
5,交换分区
alter table Pdeptexchange partition p2 with table Sdept;
- 面试2
- 面试2
- 面试2
- 面试２
- 面试2
- 面试 2
- 面试2
- 面试2
- 面试2
- 面试2
- 面试技巧（2） 个人面试注意事项
- 面试总结2--美团测试面试
- 【前端面试】HTML5+CSS3初级面试2
- Java 面试2
- Java面试题目(2)
- java面试2
- 面试题目2
- 2B面试问题
- jcifs实现域用户自动登录碰到的问题和解决方案
- 工作流管理系统
- 工作流管理系统架构
- 并发 生产者/消费者 读者/写者 哲学家进餐的理解
- 工作流的目前现状
- 面试２
- C style字符串
- php函数调用笔记
- 【新手购机必读】写给每一个想购买苹果手机的朋友
- 第7周任务1-求两数正差值 两种方法
- 悟透JavaScript
- 我编译的程序
- magento custom product api (3) --- getupdatedproducts
- Android布局单位及分辨率(dip,dp,sp,px)
