Oracle
Oracle
Oracle的基本信息详情见
http://baike.sogou.com/v449760.htm?fromTitle=Oracle数据库
Oracle简介
Oracle Database[1]，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。劳伦斯·埃里森和他的朋友，之前的同事Bob Miner和Ed Oates在1977年建立了软件开发实验室咨询公司（SDL，Software Development Laboratories）。作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。
服务器
Oracle Server是一个对象一关系数据库管理系统。它提供开放的、全面的、和集成的信息管理方法。每个Server由一个 Oracle DB和一个 Oracle Server实例组成。它具有场地自治性（Site Autonomy）和提供数据存储透明机制，以此可实现数据存储透明性。每个 Oracle数据库对应唯一的一个实例名SID，Oracle数据库服务器启动后，一般至少有以下几个用户：Internal，它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA用户使用来完成数据库的管理任务，包括启动和关闭数据库；Sys，它是一个 DBA用户名，具有最大的数据库操作权限；System，它也是一个 DBA用户名，权限仅次于 Sys用户。
客户端
为数据库用户操作端，由应用、工具、SQL* NET组成，用户操作数据库时，必须连接到一服务器，该数据库称为本地数据库（Local DB）。在网络环境下其它服务器上的 DB称为远程数据库（Remote DB）。用户要存取远程 DB上的数据时，必须建立数据库链。
Oracle数据库的体系结构包括物理存储结构和逻辑存储结构。由于它们是相分离的，所以在管理数据的物理存储结构时并不会影响对逻辑存储结构的存取。
主要特点
完整的数据管理功能：
1）数据的大量性
2）数据的保存的持久性
3）数据的共享性
4）数据的可靠性
2、完备关系的产品：
1）信息准则---关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示；
2）保证访问的准则
3）视图更新准则---只要形成视图的表中的数据变化了，相应的视图中的数据同时变化
4）数据物理性和逻辑性独立准则
3、分布式处理功能：
ORACLE数据库自第5版起就提供了分布式处理能力，到第7版就有比较完善的分布式数据库功能了，一个ORACLE分布式数据库由oraclerdbms、sql*Net、SQL*CONNECT和其他非ORACLE的关系型产品构成。
4、用ORACLE能轻松的实现数据仓库的操作。
这是一个技术发展的趋势，不在这里讨论。
优点
可用性强
可扩展性强
数据安全性强
稳定性强
Oracle的基本操作
表空间
创建表空间： create tablespace 表空间名
datafile ‘表空间的地址.DBF’ size ?M
autoextend on(是否自动扩展（OFF不）);
删除表空间： drop tablespace 表空间名;
查看表空间: select * from dba_data_files where tablespace_name=’?’
创建用户并授权
创建新用户步骤
a) .创建表的临时表空间
b).创建数据的表空间
c).创建用户
d).分配权限
a). 创建表的临时表空间
--删除表空间
drop tablespace user_temp;
--创建表空间
create temporary tablespace user_temp
tempfile 'F:\Oracle\temp\user_temp.dbf'
size 10M maxsize 50M
autoextend on
next 10M maxsize 50M
extend management local;
b). 创建数据库表空间 **/
--删除表空间
drop tablespace user_data;
--创建表空间
create tablespace user_data
logging
datafile 'F:\Oracle\log_user_data.dbf'
size 10M
autoextend on
next 10M maxsize 50M
extend management local;
c). 创建用户
--删除用户
drop users martin casade;
--创建用户
create user users
identified user
default tablespace user_data
temporary tablespace user_temp;
--修改密码
alter users martin identity by abc123;
d). 分配权限
grant connect,resource,bda users;
--移除处权限
revoke connect from users
序列
创建序列
create sequence seq_id
start with 1
increment by 1--增长间隔
maxvalue 100
cycle --达到最大值从头生成
cache 30;--缓存
访问：
nextval下一个
currval 当前值
删除
drop sequence seq_id;
生成32位编码
select sys_guid() from dual;
PL/SQL
declare
声明变量（变量名 类型 [:=初始值]）
begin
sql语句
exception
异常处理
end;
游标
2种方法:LOOP和for
① LOOP
declare
--定义变量存放内容
names students.sname%type;
gnames grade.gname%type;
--定义游标
Cursor cur_stu IS
select s.sname,g.gname from students s inner
join grade g on s.gid=g.gid;
Begin
Open cur_stu; --打开游标
Loop --循环
fetch cur_stu into names,gnames;
exit
when cur_stu%Notfound;
Dbms_Output.put_line ('第'||cur_stu%Rowcount||'个学员的名字为：'||names||'，所在的班级为：'||gnames);
end Loop;
end;
② For方法
declare
--定义变量存放内容
names students.sname%type;
gnames grade.gname%type;
--定义游标
Cursor cur_stu IS
select s.sname,g.gname from students s inner join grade g on s.gid=g.gid;
Begin
For cur_record in cur_stu
Loop
Dbms_Output.put_line('第'||cur_stu%Rowcount||'个学员的名字为：'||names||'，所在的班级为：'||gnames);
end Loop;
end;
带参显示游标
DECLARE
CURSOR emp_cursor(no NUMBER) IS
SELECT ename,sal FROM employee WHERE deptno=no;
emp_record emp_cursor%ROWTYPE;
v_sal employee.sal %TYPE; (游标变量)
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor(10);
END IF;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
隐式游标
--%NOTFOUND属性举例
DECLARE
v_name employee.ename%TYPE;
BEGIN
SELECT ename INTO v_name FROM employee
WHERE empno=45;
IF SQL%NOTFOUND THEN
dbms_output.put_line('NOTFOUND-不存在该员工');
ELSE
dbms_output.put_line('存在该员工');
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found-不存在该员工');
WHEN OTHERS THEN
dbms_output.put_line('其他错误');
END;
存储过程
Oracle存储过程
1.单独赋予权限:
grant execute on 存储名称 to 用户名
2.调用存储名称
execute 存储名称(参数)
3.存储过程(Procedure)和函数(function)的区别
不同点:
① function 有返回值,可以在Query中引用function
或使用function返回值
② 最根本: 存储过程是命令,而函数是表达式的一部分
相同点:
① 都是PL/SQL程序
Oracle存储过程的基本语法
1.基本结构
Create Or replace procedure 存储名称
(
参数1 in number,
参数2 in number
)As
变量名1 integer :=0;
变量2 Date;
Begin
End 存储过程名称
存储过程调用
1. Pl/Sql中执行存储过程在sql*plus中：
declare
--必要的变量声明，视你的过程而定begin
execute yourprocudure(parameter1,parameter2,...);
end
2. .Oracle调用Oracle存储过程
execute 存储名称(参数)
3. Hibernate调用Oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
newHibernateCallback()...{
public Object doInHibernate(Sessionsession)
throws HibernateException,SQLException...{
CallableStatement cs=session.connection()
.prepareCall("{call modifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();returnnull;
}
}
)
关于Oracle存储过程的若干问题备忘
1. 在Oracle中,数据表的别名不能加as
分析: select a.appname from appinfo a;--正确
select a.appname from appinfo as a;--错误
也许，是怕和Oracle中的存储过程中的关键字as冲突的问题吧
2. 在存储过程中，select某一字段时，后面必须紧跟into，
如果select整个记录，利用游标的话就另当别论了。
分析: select af.keynode into kn from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;--有into，正确编译
----------------------------------------------------------------
select af.keynode from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;--没有into, 编译报错，
提示：Compilation Error:PLS-00428:an INTO clause is expected in this SELECT statemen
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"nodatafound"异常。
可以在该语法之前，先利用selectcount(*)from查看数据库中是否存在该记录，
如果存在，再利用select...into.
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
分析: select keynode into kn from APPFOUNDATION
where appid=aid and foundationid=fid;--正确运行
---------------------------------------------------
select af.keynode into kn from APPFOUNDATION af
where af.appid=appid and af.foundationid=foundationid; --运行阶段报错，
提示：ORA-01422:exact fetch returns more than requested number of rows
5. 在存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table A
(
id varchar2(50) primary key not null,
vcount number(8) not null,
bidvar char2(50) not null--外键
);
如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录，则fcount=null
(即使fcount定义时设置了默认值，如：fcountnumber(8):=0依然无效，fcount还是会变成null)，
这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if
fcount is null
then
fcount:=0;
end if;
这样就一切ok了。
用Java调用Oracle存储过程总结
一.无返回值的存储过程
测试表:
--Create table
create table TESTTB
(
ID VARCHAR2(30),
NAME VARCHAR2(30)
)
tablespace BOM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
分页sql语句
select * from (
select A.* , RN from C
select * from student ) A where RN bettwen (页数-1)*记录数/页 and (页数*记录数/页)
删除重复记录（仅保留一页）
SQL语句：delete from stuINfo where rowId not in(
Select Max(RowId) from stuInfo
group by stuname,stuAge
having (count(stuAge||stuAge)>1)
union
select Max(rowId) from stuInfo
Group by stuName,stuAge
Having (count(stuAge||stuName)==1)
)
Oracle操作的实用语句
-- 查看ORACLE 数据库中本用户下的所有表
SELECT table_name FROM user_tables;
-- 查看ORACLE 数据库中所有用户下的所有表
select user,table_name from all_tables;
-- 查看ORACLE 数据库中本用户下的所有列
select table_name,column_name from user_tab_columns;
-- 查看ORACLE 数据库中本用户下的所有列
select user,table_name,column_name from all_tab_columns;
-- 查看ORACLE 数据库中的序列号
select * from user_sequences;
-- 上面的所有对象，都可以通过下面的SQL语句查询得到
-- 查询所有的用户生成的ORACLE对象
SELECT * FROM user_objects;
-- 查看ORACLE 数据库中所有表的注释
select table_name,comments from user_tab_comments;
-- 查看ORACLE 数据库中所有列的注释
select table_name,column_name,comments from user_col_comments;
-- 给表加ORACLE的注释
COMMENT ON TABLE aa10 IS '系统参数表';
-- 给列加ORACLE的注释
COMMENT ON COLUMN aa10.aaa100 IS '参数类别';
-- 通过约束查看表名
select TABLE_NAME from all_constraints where CONSTRAINT_NAME='约束名';
或
select constraint_name,constraint_type,table_name from all_constraints where CONSTRAINT_NAME='约束名';
注意：
1.表应为：all_constraints
2.主键约束“PK_ID”必须大写
-- 通过表名去查列名的长度
select * from user_tables where table_name = upper('表名')—
--查询出用户所有表的索引
select * from user_indexes
--查询用户表的索引(非聚集索引):
select * from user_indexes where uniqueness='NONUNIQUE'
--查询用户表的主键(聚集索引):
select * from user_indexes where uniqueness='UNIQUE'
--查询表的索引
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name='NODE'
--查询表的主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' AND cu.table_name = 'NODE'
--查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and cu.table_name='NODE'
--查找表的外键
select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION'
--查询外键约束的列名：
select * from user_cons_columns cl
where cl.constraint_name = 外键名称
--查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
--查看当前线程数
select count(*) from v$session;
--查看最大的线程数
select value from v$parameter where name = 'sessions'
--修改最大线程数(重启Oracle服务器才会生效)
Alter system set session=300 scope = spfile
--查看最大进程数
select value from v$parameter where name = 'processes'
--修改最大线程数(重启Oracle服务器才会生效)
alter system set sessions=335 scope=spfile;
-- 通过系统表，查看表中列的属性，包括 数据类型，是否非空等
SELECT
table_name,COLUMN_ID,column_name,data_type,data_length,DATA_PRECISION,NULLABLE
FROM user_tab_columns
ORDER BY table_name,COLUMN_ID;
--查看所有表空间
selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name
--查看未使用表空间大小
selecttablespace_name,sum(bytes)/1024/1024 from dba_free_space group bytablespace_name;
-- 查看数据库中表、索引占用的数据库空间大小
SELECT * FROM user_segments;
-- 查看所有表的记录数
CREATE TABLE table_count(table_name VARCHAR2(50),columns NUMBER(20));
-- 通过PB运行下面的语句，得到结果集，将结果集在PB下执行，最后提交
select 'insert into table_count values('''||table_name||''', (select
count(1)from '||table_name||'));//'||comments from user_tab_comments;
-- 所有表的记录都在table_count了
SELECT * FROM table_count;
// 将ORACLE数据库的注释同步到PB中 代码开始
DELETE FROM PBCATCOL WHERE PBC_TNAM LIKE '%';
DELETE FROM PBCATTBL WHERE PBT_TNAM LIKE '%';
INSERT INTO PBCATTBL
( PBT_TNAM,
PBT_OWNR ,
PBT_CMNT)
SELECT ALL_TAB_COMMENTS.TABLE_NAME,
ALL_TAB_COMMENTS.OWNER,
ALL_TAB_COMMENTS.COMMENTS
FROM ALL_TAB_COMMENTS
WHERE ALL_TAB_COMMENTS.OWNER = 'LH'
AND TABLE_NAME LIKE '%';
// 同步字段名
INSERT INTO PBCATCOL
( PBC_TNAM,
PBC_OWNR,
PBC_CNAM,
PBC_LABL,
PBC_CMNT,
PBC_HDR)
SELECT ALL_COL_COMMENTS.TABLE_NAME,
ALL_COL_COMMENTS.OWNER,
ALL_COL_COMMENTS.COLUMN_NAME,
ALL_COL_COMMENTS.COMMENTS ,
ALL_COL_COMMENTS.COMMENTS ,
ALL_COL_COMMENTS.COMMENTS
FROM ALL_COL_COMMENTS
WHERE ALL_COL_COMMENTS.OWNER = 'LH'
AND TABLE_NAME LIKE '%';
COMMIT;
-- 将ORACLE数据库的注释同步到PB中 代码结束
--将PB注释同步到ORACLE中
select 'comment on table '||pbt_tnam||' is '''||pbt_cmnt||''';' from
pbcattblwhere pbt_tnam not like 'PB%'
UNION
select 'comment on column '||pbc_tnam||'.'||pbc_cnam||' is
'''||pbc_cmnt||''';'from pbcatcol where pbC_tnam not like 'PB%';
--查进程
select object_id,session_id,locked_mode from v$locked_object;
selectt2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
--杀进程
alter system kill session '3,6666';
oracle操作语句：
1.创建表
create table 表名(
列名1 类型，
列名2 类型
);
2.修改类属性
alter table 表名 modify(列名 类型);
3.添加列
alter table 表名 add(列名 类型);
4.添加主键约束和非空约束
alter table 表名 add constraint pk_表名 primary key(列名);
alter table 表名 modify(列名 not null);
5.删除主键约束
alter table 表名 drop primary key;
alter table 表名 drop constraint pk_表名;
6.失效约束
alter table 表名 disable primary key;
alter table 表名 disable constraint pk_表名;
7.有效约束
alter table 表名 enable primary key;
alter table 表名 enable constraint pk_表名;
8.删除列
alter table 表名 drop column 列名;
9.设置某列不可用，然后删除
alter table 表名 set unused(列名);
alter table 表名 drop unused columns;
10.修改表名
rename 表名1 to 表名2
alter 表名1 rename to 表名2;
11.截断表
truncate table 表名;
12.截断表保留行空间
truncate table 表名 resue storage;
13.查看表结构
desc table 表名;
14.删除表
drop table 表名;
15.插入记录
例：insert into 表名 values(内容1,内容2,内容3,内容4);
16.带参数对话方式插入行
例:insert into 表名 values(&列名1,&列名2);
insert into 表名 values(内容1,内容2);
17.插入某几列记录
insert into 表名(列名1,列名2) values(内容1,内容2);
18.为列插入空值（其列不能为not null）
insert into 表名 values(内容1,null,null);
19.创建表（包括主键及外键设置）方法一
create table 表名(
列名1 类型
constraint pk_表名 primary key,
列名2 类型 not null,
列名3 类型
constraint fk_表名 reference 表名(列名),
列名3 类型
constraint ck_表名 check(列名3 in(''内容1'',''内容2'',''内容3''))
);
20.查询所有行
select * from 表名;
21.查询某几列
select 列名1,列名2 from 表名;
22.重复行消除
select distict 列名 from 表名;
23.where语句查询
select * from 表名 where 条件 order by 列名;
(注：如number类型查出自动按升序排列，如要按降序排列，则select * from 表名 where 条件 order by 列名desc;)
24.创建表,方法二
create table 表名(
列名1 类型 primary key,
列名2 类型 not null,
列名3 类型check(列名3 in('''','''','''')),
列名4 类型 refernce 表名(列名)
);
25.修改 列=‘？’的数据
update 表名 set (列=？) where 列=‘？’;
26.删除行
delete from 表名 where 条件;
27.事务处理
--事务处理
update 表名
set 列名（日期） = ''30-5月-98''
where 条件;
savepoint mark1;
delete from 表名 where 条件;
savepoint mark2;
rollback to savepoint mark1;
rollback;
28.建立用户user1，密码为password
授予用户connect,resource的权限
connect角色用于登录
resource角色用于建表等.
connect system/manager
create user user1 identified bypassword;
grant connect,resource to password;
29.数据控制语言
connect scott/tiger
30.把对表1查询和修改的权限授予user1
grant select,update on 表1 to user1;
31.把对表表1中列1和列2修改的权限授予user1
grant update(列1,列2) on 表1 to user1;
32.把对表表1查询的权限授予用户user1
并且user1用户还可以把这个权限授予别的用户(with grant option)
grant select on 表1 to user1 with grant option;
33.从用户user1撤销对表1查询和修改的权限
revoke select,update on 表1 from user1;
select COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'TB_CHSS_JWSSS';
