/*
@author:luowen
@time:2013-12-04
@desc:oracle base operations
Oracle notes
1 创建用户
create user username identified by password; //创建用户名和密码
grant create session to username; //给创建的用户授权
2 给用户赋权限
grant privilege/role to username;
eg:
grant connect to username; //是username拥有登入权限
grant resource to username; //给username普通的权限
3 回收用户权限
revoke privilege/role from username;
eg:
revoke connect from username; //回收username登入权限
revoke resource from username; //回收普通股的权限
4 删除用户
drop username [cascade]; //删除用户[cascade]级联的东西都删除掉
5 创建表
create table user (id number); //创建一个user表,字段只有一个id;
6 用户a查询用户b创建的表(默认是a用户查询不到用户b创建的表的) --> (Oracle 方案概念)
b用户:
grant select[update,insert,delete]/[all] on tableName to a //用户a可以查询用户b创建的表.
如果来了个c用户,要从a用户拿到b用户的访问b表的权限时候,b用户授权的时候需要加上"with grant option"-> grant [select,update,delete,insert[all]] on tableName to a;
"with grant option" -> 对象权限
"with admin option" -> 系统权限
7 Oracle 启动流程
1.windows
a.cd Orcale/bin/lnsrtlt.exe start; //启动监听
b. //启动数据库实例
8 Oracle用户管理
1.用户登入限制
create profile profileName limit failed_login_attempts 3 password_lock_time 2; //当用户输入密码错误超过3次的是,用户将被搜定2天
alter user userName profile profileName; //将配置的文件分配给指定用户userName;
alter user userName account unlock; //给锁定用户解锁
2.oracle终止口令
create profile profileName limit password_life_time 10 password_grace_time 2; //限制用户的密码生存时间为10天,宽限是时间为2天,到期自动要求用户更改密码;
alter user userName profile profileName; //将配置的文件分配给用户
9 Oracle用户验证
1.wondows
当登入windows的用户在orl_dba用户组中时候,该用户可以跳过验证
conn xxx/xxx as sysdba //可以跳过验证,直接登入到oracle //解决方案 -> (1) 可以到orl_dba用户组中将该用户删除;(2) 可以直接到oracle安装目录中搜索 sqlnet.ora,将用户验证方式NTS修改为NONE,可以,不适用oracle的系统验证方式。
当登入windows的用户不在orl_dba用户组中时候,则选择数据验证方式。
2. sys在oracle拥有最高权限,system适用系统验证登入时候,也会自动切换到sys用户。
10 当管理密码丢失时候(解决方案)
1.在Oracle安装目录中搜索pwd[数据对象实例].ora(二进制形式)
2.拷贝该文件,以备后用,删除该文件.
3.在dos命令行创建一个新的文件
orapwd file=原来密码文件的全路径\密码文件.ora password=新密码
11 Oracle 建表
create table table_name (
列1 Oracle数据类型,
列2 Oracle数据类型,
列3 Oracle数据类型,
列4 Oracle数据类型,
);
Oracle 数据类型
char(size) -> 2k个字符(定长);
varchar2(size) -> 4k个字符(不定长)
nchar(size) -> 2k个字符(Unicode编码)(定长)[一个汉字占用一个字符]
nvarchar(size) ->4k个字符(Unicode编码)(不定长)[一个汉字占用一个字符]
clob(character large object) -> 字符型大对象,最大8tb(不定长)[只能存字符类型]
blob(binary large object) -> 二禁止类型大对象,最大8tb(比定长)[可以存二进制](很少用,占用网络带快,速度慢,实际记录文件存放路径)
number -> 可以放整数,可以放小数,number(p,s) p为整数,s为小数 1 <= p <= 38,-84 <= s <= 127(不定长)
number(n,m) -> 有效位为n,小数点为m位!
date(日期类型) -> 用于表示时间(年/月/日/时/分/秒)Oracle默认格式为(dd-mm-yy)希望添加自己的喜欢的格式添加时,我们需要添加Oracle的函数
timestamp(n) -> 时间戳,当表中的字段更改的时候,timestamp会跟着联动(不推荐);
12 建表实例
1.students 表
create table students(
id number,
name varchar2(64),
sex char(2),
birthday date,
fellowship number(10,2),
resume clob
);
2 class 表
create table class_(
id number,
name varchar2()
)
3.修改表的操作
alter table table_name drop column;
alter table table_name add .......etc.
13 Oracle 的crud操作
1.SELECT
select *[column] from table_name where [conditions] order by [column];
2.INSERT
insert into table_name (column) values (column);
3.DELETE
delete from table_name where [conditions];
4.UPDATE
update table_name set [column] = [values] where [condition];
注意:Oracle中 null = ' '、Oracle中字符串需要用 ''包裹起来,查询时,如需要去重复,可以添加关键字 eg: select distinct * from tab_name;
13 Oracle的一些函数
1.abs() //求平均值
2.count() //求总数
3.substr() //截取
4.replace() //替换
5.nvl() //判断为空
6.concat() //连接字符
7.ltrim() //剪切左边部分
8.rtrim() //剪切右边部分
9.max(); //求最大值
10.min() //求最小值
etc.....
plugin tips -> 我们希望删除用户,同事保存该用户的数据对象
方案: 我们可以将该用户锁定 alter user username account lock;
解锁 alter user username account unlock;
14 Oracle的分页操作
MySQL:select * from table_name limit start ,count;
SqlServer: select top 10 * from table_name where id not in(select top 4 id from table_name where conditon ); //排除前四条,去除了第5条到14条
Oracle: select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum <= 3) t2 where t2.rn >= 1;
第一层:select * from table_name where condition;
第二层:select t1.*,rownum from (select * from table_name where condition) t1 where rownum <= 3;
第三层:select t2.*,rownum from (select t1.*,rownum from (select * from table_name where condition) t1 where t1.rownum <=3 ) t2 where t2.rownum >=9; //这个查询中,记住第二个条件需要判断的是最小值,然后第三层是限定最大值 (先小后大)
复制表
create table table1 like table2; -----------|
|---->将table2 replication to table1;
create tabl table1 select * from table2 ----|
复制表内容
insert into table1 select * from table2;
select * into table1 from table2;
15 Oracle
union 简单的去两个查询语句的并集
union all 简答的和两个查询结果进行并集,并不排序,不去重复
minus 查询两个查询语句的差集(去掉两个集合中公共有的部分,如果没有,就去第一个集合的结果)
cube select avg(sal),deptno,job from emp group by cube(deptno,job); //查询每个部门,部门的每个职员的平均工资
内连接
select emp.job,dept.dname from emp,dept where emp.deptno = dept.deptno;
select emp.job,dept.dname from emp inner join dept on emp.deptno = dept.deptno;
特点:只有两张表,两个结果集里面同时满足的时候,才会被选中。
外连接
左外联:select emp.job,dept.dname from emp left join dept on emp.deptno = dept.deptno; //emp表的内容与dept表中没有匹配,也要全部被显示
右外联:select emp.job,dept.dname from emp right join dept on emp.deptno = dept.deptno; //emp表的内容与dept表中没有匹配,也要全部被显示
select emp.job,dept.dname from emp ,dept where emp.deptno = dept.deptno(+); //同上也是有外链,只是写法的不同
完全外联
select emp.job,dept.dname from emp full outer join dept on emp.deptno = dept.deptno; //当不匹配的,也全部显示出来
16.java链接Oracle
1.jdbc
package com.luowen.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Statement;
public final class JdbcUtils {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/";
private static String database = "test";
private static String user = "root";
private static String password = "luowen";
private JdbcUtils(){
}
static{
try {
//加载驱动
Class.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url+database,user,password);
}
public static void close(ResultSet rs,Statement sm,Connection conn){
try {
if(rs != null)
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
try {
if( sm != null)
sm.close();
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}finally{
try {
if(conn != null)
conn.close();
} catch (Exception e3) {
// TODO: handle exception
e3.printStackTrace();
}
}
}
}
}
2 桥链接(odbc)
在window里面设置数据源(odbc) java代码里只要更改driver 和 url 就可以了
17 适用子查询实现Oracle的行迁移
create table temp# as select * from table_name where id < 10000; //将table_name中的id小于10000的数据迁移到temp#表中
update emp set (job,sal,comm) = (select job,sal,comm from emp where ename= "SMISTH"); //支持将表的多个字段更新
18 Oracle 函数
1.to_date();
to_date("1909-11-12",'yyyy-mm-dd'); //把字符串转化成制定格式的日期
to_char();
to_char(); //将日期格式转化成字符串格式
2.replace(field,'old','new'); //将查询的结果,替换显示,但数据库中的数据没有更改!
3.instr(c1,c2,I,J) //c1->被搜索的字符,c2->希望搜索的字符,I->从第几个开始查,J->从哪里结束。 返回出现的位置
4.ascii('a') -> 97 //返回给定字符的asciii值
5.chr(97) -> 'a' //给定一个数字,返回一个字符
6.concat(); //链接两个字符函数
select concat(ename,'是好人') from emp; //将两个字符连接起来
select ename || '是好人' from emp; //同上
7.initcap('adfsdf') -> Adfsdf; lcase('asdfsf') --> 转大写; ucase("SDFSDF") -> 转小写; //返回字符串大写
8. trim(" ","asdfsdf ") // 清除函数
9.ceil(); //向上取整 ->(天花板)
10.floor(); //向下取整 ->(地板)
11.mod(); //取模函数 select mod(3,2) from dual; 3除以2返回1
12.round() //用户四舍五入 round(3.444) -> 3;
13.trunc() //直接去掉小数点后面数 trunc(1.333); -> 1;
14.add_mouths(); //去除三个月入职的员工 select * from emp where add_mouths(hiredate,3) >= sysdate;
15.last_day(); //返回这个月的最后一天
19 Oracle事务处理机制 --> transaction
| savepoint aa; //保存点aa
| savepoint bb; //保存点bb
| rollback bb; //回馈到aa
| rollback aa; //回馈到bb
\/ //回馈到aa,就不能回馈到bb了
一旦commit后,所有的东西都rollback 不了了
20 Oracle 数据完整性
1. not null //非空
2. unique //限制数据为唯一,Oracle可以存入多个空
3. primary key //不能重复,不能为空
4. foreign key(外键);//两张表通过外键联系 create table stu(id number primary key,name varchar2(36) not null,classid number references class(id)); create table class (id number unique,name varchar2(32)); //学生表和班级表通过classid来联系,foreign key 必须是主键,或是唯一,且数据类型是一致的.
5.cheack(); //create table user (id number primary key,sal number cheace(sal >=1000 and sal <=50000)); //添加的数据sal必须在cheack范围内
21 Oracle 的权限和角色
系统权限
授权:
select * from system_privilege_map order by name;
grant create session;
grant create table;
grant create view;
grant create trigger
grant create procedure
etc...
选项:with admin option;
回收:
revoke create session from user_name //系统权限不能联机回收
对象权限
授权:
grant select,update on Obj.table to user_name;
etc ...
选项: with grant option
回收:
revoke select , update on table.colum from user_name; //回收具有联机效应
角色(connect,dba,resource)
select * from dba_roles; //查看系统的角色信息
select * from dba_sys_privs where grantee = "DBA"; //查看DBA角色的权限
select * from dba_role_privs where grantee="user_name"; //查看用户是什么角色
自定义角色:
create role role_name not indentified; //创建一个role_name的角色
grant create session to role_name;
grant select on scott.emp to role_name;
grant delete on scott.emp to role_name;
grant update on scott.emp to role_name;
etc .. //给role_name 赋权限
给角色给用户户
grant 角色名 to user;
grant role_name to user_name; //给用户user_name赋上了role_name 的角色!
删除角色:
drop role role_name;
添加约束
alter table table_name add constraint constraint_name [unique,primary key,cheack(),foreign key] (field);
alter table table_name modify field [not null]
删除约束
alter table table_name drop constraint constriant_name; //指定约束名
alter table table_name drop primary key; //一张表中只有一个主键
序列(sequence)[可以为表中自动添加值,可以为多个用户共享]
create sequence my_sequence //my_sequence 序列名
start with 1 //从1开始
increment by 1 //增长1
minvalue 1 //最小值1
maxvalue 30000 //最大值3w
cycle //当数据达到3w时候,循环
nocache //不保留缓存
使用
insert into table_name values(my_sequence.nextval,'insert_name');
select my_sequence.currval from dual; //产看当前sequence到哪里了
删除序列(delete sequence)
drop sequence sequence_name; //删除序列名为sequence_name的序列
索引
create index index_name on table_name(column);
创建过程并可以实现输入参数
create procedure pro_demo (in_id in number,in_name in varchar2)
is
begin
update emp set ename = in_name where empno = in_id;
end;
/ //->结束
exec pro_demo(12,'smith'); //调用过程
call pro_demo(12,'smith'); //调用过程
创建Oracle 函数
created function func1(in_name varchar2)
return number is
declare
out_sal number;
begin
select (sal+nvl(comm,0))*13 into out_sal from emp where ename = in_name;
return out_sal;
end;
/ --//结束
创建Oracle包 (包是问了更好的管理自的写的函数,过程.类似java超类)
create [ or replace ] package package_name is
--声明过程
procedure 过程名(变量 变量类型);
--0声明函数
function 函数名(参数 变量) return 反悔变量名;
end;
包体(实列化包)
create or replace package body 包名 is
---实现过程
create or replace procedure pro_name(参数 参数类型) --//对应包中的过程名
is
begin
insert into emp (...) values (.....); //对应执行语句
end;
---实现函数
create function funtion_name(参数 参数类型)
return 返回参数类型
is
begin
select ..............; --//对应执行语句
end;
end;
22 PL/SQL编程
PL/SQL语法定义
1.标量类型(scalar);
2.复合类型(composite);
3.参照类型(reference);
4.lob(large object);
pl/sql 是一种基于sql的扩张编程语言,可以定义变量,可以实现条件判断,循环等。
pl/sql 可以开发,存储过程,函数,包(包体),触发器 //一块为单位的
create procedure pro1 is
begin
insert into emp values("11",'sdfdf');
end;
/ //一个最简单的存储过程.
带参数的存储过程
create procedure pro1(id number) is begin delete from emp where empno = id; end; / //删除出入的id对应的数据
块实例
declare
/------------注释部分---------------/
begin
/------------注释部分---------------/
exception
/------------注释部分---------------/
end;
eg. 打印hello word
begin
dbms_output.put_line('hello word');
end;
/
//开启自动打印功能
set serveroutput on;
eg2.
declare
--------定义一个变量
v_ename varchar2(32);
begin
---------//开始执行
select ename into v_ename from emp where ename = &ename; --------//从表中查询出来ename给v_ename &ename会弹出一个对话框,输入值
dbms_output.put_line('雇员名是:' || v_ename);
end;
----------//定义结束
/ -----------//结束标记符号
Oracle复合数据
格式:
create or replace procedure pro_demo1(v_in_empno in number)
is
----//定义了一个变量,该变量就是记录类型(复合类型)注:类似定义了一个对象,对象里面存放了成员变量
type luowen_emp_record is record(
v_ename emp.enam%type,
v_sal emp.sal%type,
v_job emp.job%type
);
v_emp_record luowen_emp_record; ---//变量 v_emp_record 的类型是luowen_emp_record
begin
select ename,sal,job into v_emp_record from emp where empno = v_in_empno;
dbms_output.put_line('名字:'||v_emp_record.v_ename || '工资:'||v_emp_record.v_sal );
end;
类似数组类型
declare
type sp_table_type is table of emp.ename%type ---//声明sp_table_type 是 table 类型
index by binary_integer;
sp_table sp_table_type; --/定义变量sp_table 的类型为sp_table_type
begin
select into sp_table(-1) from emp where empno = 7788; -----//将查出来的值复给sp_table(-1)中
dbms_output.put_line('xxxx');
end;
/
参照变量(reference)
游标变量cursor
1.定义游标
type 自定义游标名字 is ref cursor;
变量名 自定义有标名;
2.打开游标
open 游标变量 for select 语句 ------;
3.取出当前游标
fetch 游标变量 into 其他变量;
4.判断游标是否是最后的记录
游标变量%nofound
eg:
create or replace procedure pro_demo2(v_in_deptno number) is
type luowen_emp_cursor is ref cursor; ----//定义一个游标变量
v_emp_cursor luowen_emp_cursor; -----//定义标量 v_emp_cursor 类型是luowen_emp_cursor;
v_ename emp.ename%type; ---//定义变量保存查出的数据
v_sal emp.sal%type; ------//定义一个变量保存出查出的薪水
begin
open v_emp_cursor for select ename,sal from emp; ---------//将游标适用到select 语句中
loop
fetch v_emp_cursor into v_ename,v_sal; ---//适用循环
exit when v_emp_cursor%notfound; ---//判断是否达到了末尾,达到则对出
--dbms_output.put_line('用户名:' || v_ename || '薪水:' || v_sal); --//打印查出的数据
if v_sal < 200 then
update emp set sal = sal + 100 where ename = v_ename; ------//查询出结果为小于200的员工自动加上100元的薪水
end if;
end loop; ---//结束循环
close v_emp_cursor; ---//关闭游标
end;
Oracle 分支语句 (emp表中如果job是president的人工资加上1000 为manager的加上500 其他的加上100)
create or replace procedure pro_demo2(v_in_empno in number) is
v_job emp.job%type;
begin
select job into v_job from emp where enpno = v_in_empno;
if v_job = 'PRESIDENT' then
update emp set sal = sal + 1000 where empno = v_in_empno
elseif v_job = 'MANAGER' then
update emp set sal = sal+ + 500 where empno = v_in_empno;
else
update emp set sal = sal + 100 where empno = v_in_empno;
end if;
end;
/
Oracle循环(给一张表中添加100条数据)
create table user(id number,username varchar2(32));
create or replace procedure insert_pro(in_username in varchar2,in_num in number)
is
v_start number := 0;
begin
loop
insert into user values(v_start,in_username);
exit when v_start = in_num;
v_start := v_start + 1;
end loop;
end;
oracle while循环
create or replace pro_demo3(v_in_name varchar2,v_in_num number) is
v_empno number := 200;
begin
while v_empno <= 200 + v_in_num loop
insert into users values(v_empno,v_in_ename);
end loop;
end;
oracle 带有返回结果的procedure
返回普通的字串
create or replace procedure pro_demo(in_empno in number,out_ename out varchar2)
is
begin
select ename into out_ename from emp where empno = in_empno;
end;
----//返回结果
-------//在Java中调用
Class.forName("oracle.jdbc.driver.OracleDriver"); //加载驱动
Connection ct = DiverManage.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger"); //得到链接
String sql = "{call pro_demo(?,?)}"; //定义sql语句
CallableStatement cs = ct.prepareCall(sql); //构建预定statement
cs.setInt(1,7839); //定义?的值
cs.registerParameter(2,oracle.jdbc.OracleType.VARCHAR); //注册过程pro_demo返回值得容器
cs.execute(); //执行过程
String res = cs.getString(2); //取回过程返回的值.
oracle 分页过程 (in_table_name[需要查询的表],in_pagesize[当前页显示多少条数据],in_pagenow[显示当前为第几页],out_cursor[定义返回的游标,用于返回的数据],out_cnt[数据的总数],out_pagecnt[数据的分页数])
create or replace procedure pagePro(in_table_name varchar2,in_pagesize in number,in_pagenow in number,out_cursor pack1.demo_cursor,out_cnt out number,out_pagecnt number)
is
v_start number;
v_end number;
v_sql varchar2(2000);
begin
v_start := in_pagesize * (in_pagenow - 1); ---//定义当前页显示多少条数据
v_end := in_pagesize * pagenow; ----//定义当前页
v_sql := 'select t2.* from (select t1.*,rownum cnt from (select * from '|| in_table_name ||') t1 where rownum <= '|| v_end ||') where t1.cnt >=' || v_start; ---//分页查询语句
select count(*) into out_cnt from emp; //查询共有多少条数据
if mod(out_cnt,in_pagesize) = 0 then //判断是否能整出
out_pagecnt := out_cnt/in_pagesize; //整出的页数
else
out_pagecnt := out_cnt/in_pagesize + 1; //不能整出的页数
end if;
open out_cursor for v_sql; //打开pack1定义的demo_cursor的游标
end; //over
----------java调用部分--------------------
package com.luowen.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Test {
public static void main(String[] args) {
getPage("emp",5,1);
}
private static void getPage(String tabName,int start,int end) {
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql = "{call pro_demo(?,?,?,?,?,?)}";
CallableStatement cs = ct.prepareCall(sql);
cs.setString(1, tabName);
cs.setInt(2,start);
cs.setInt(3,end);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.NUMBER);
cs.execute();
rs = (ResultSet) cs.getObject(4);
int cnt = cs.getInt(5);
int page_cnt = cs.getInt(6);
while(rs.next()){
System.out.println(rs.getObject(3) + "---" + rs.getObject(4) + "---" + rs.getObject(9));
}
System.out.println("总的数据:"+cnt+"共有页数:"+page_cnt);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
if(rs != null){
try {
rs.close();
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
}
}
}
Oracle视图
create view view_name as select 语句 [with read only] --//带上with read only 表示只能读,不能删除,添加
drop view view_name; //删除视图
Oracle 触发器
1.dml触发器(数据操作语言,insert,delete,update);
2.ddl触发器(数据定义语言,create table,create view ,create tigger,drop)
3.系统触发器 (与系统相关的触发器,比如用户登入,退出,启动数据库,关闭数据库)
create trigger my_trigger
after insert on scott.emp
begin
dbms_output.put_line('添加了一条数据')
end;
create or replace my_trigger
after update on scott.emp
for each row ------//每行变化都监听,打印下面的数据
begin
dbms_output.put_line('更新了一条数'');
end;
create or replace my_trigger
after delete on scott.emp for each row
begin
if to_char(sysdate,'day') in ('星期六','星期天') then
dbms_output.put_line('周末不能删除数据');
RAISE_APPLICATION_ERROR(-20001,'周末不能删除数据'); ---//抛出一个异常,用于阻止用户删除数据
end if;
end;
---//注:RAISE_APPLICATION_ERROR 填入两个参数 -20000 ~ -20999,自定义错误语句
create or replace tigger trig2
before
insert or update or delete on ---//监听多个事件,insert,update,delete
scott.emp
begin
case
when inserting then
dbms_output.put_line('insertint');
when udating then
dbms_output.put_line('updating');
when deleteing then
dbms_output.put_line('delete');
end case;
end;
create or replace trigger trig3
after update on scott.emp for each row
begin
if :new.sal < :old.sal then
dbms_output.put_line('工资不能减少');
end if;
end;
Oracle系统触发器
create or replace trigger 触发器名字
after [before] logon [logoff] on databases
begin
---//执行语句
end;
eg.
//用户登入记录数据
create or replace trigger tri1
after logon or logoff on database
begin
insert into log_table (username,login_time,ip) values(ora_login_user,sysdate,ora_client_ip_address);
end;
//用户退出记录信息
create or replace trigger tri2
before logoff on database
begin
insert into login_table (username,logout_time,ip) values (ora_login_user,sysdate,ora_client_ip_address);
end;
*/
