Oracletool_MyOracleTool

------------------------------------Oracle 常用命令------------------------------------

-------------一般命令----------------

1、清屏

clear screen ;

2、导入sql脚本

@d:/sqlCreate.sql

3、设置回滚点:

savepoint aa;

4、回滚数据:

rollback to aa;

5、提交事务：

commit;

6、设置只读事务： --设置只读后，之后其他用户加入的数据不会影响到本用户的查询结果。

set transaction read only;

6、修改数据库的默认时间类型(默认为："dd-mm-yyyy")：

alter session set nls_date_format='yyyy-mm-dd';

7、打开操作时间开关：

set timing on ;

8、控制台中输入语句：

dbms_output.put_line('雇员名：'||v_ename);  --dbms_output ：是包名

9、声明一个变量

declare v_ename varchar2 ;

10、给变量赋值

v_ename:='赋值' ;

-----------用户管理------------

1、创建一个用户：

create user zou identified by mima;

2、修改用户的密码：

password newMima;

3、连接一个新用户：

conn sys/accpsys as sysdba;

4、删除用户:

drop user zou;

5、查询所有用户：

select * from dba_users;   desc dba_users;

6、给用户赋角色权限：

grant connect to zou ;

7、给某个用户授予某张表的访问权限：

grant select on emp to zou ;

或者

grant add on emp to zou;

8、同时允许该用户继续授权给其他用户方式：

grant add on emp to zou with grant option;

9、回收权限：

revoke select on emp from zou;

10、查询用户具有权限：

select * from dba_tab_privs ;

11、查询用户具有的角色:

select * from dba_role_privs ;

12、查询Oracle中所有角色：

select * from dba_roles ;

-----------权限表------------

connect：一般开发人员就足够了

alter session

create cluster

create database link

create sequence

create session

create table

create view

resource ：具有应用开发人员所需要的其他权限，比如存储过程、触发器等。

create cluster

create indextype

create table

create sequence

create type

create procedure

create trigger。

dba角色：具有所有的系统权限，不具有启动和关闭数据库的权限。

-------------表的基本操作----------------

1、新建一张表：

create table users

(

userId int not null primary key,

userName varchar2(20),

birthday date

);

----添

2、添加一条数据方法：

insert into users(1,'aaa',to_date('2009-10-13','yyyy-mm-dd'));

3、一次性插入多条数据：

insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10

----修

3、修改一条数据：

update users set userName='ccc' where userId=1;

4、一次性修改多条数据：

update emp set(job,sal,comm)==(select job,sal,comm from emp where ename='smtth') where ename='scott' ;

----查

4、查询一条数据：

select * from users;

5、查询某列是空值的方式：

select * from student where birthday is null;

6、查询表结构：

desc users;

7、多表连接查询：

select a1.ename,a1.sal,s2.dname from emp a1,poed a2 where a1.ename=a2.pname ;

8、用查询结果建立一张新表：

create table mytable(id,name,sal,job,deptno) as select empno,ename,sal from emp ;

9、查询当前用户可以访问的所有表:

select * from all_tables;  --all_tables :能访问到的表 。user_tables ：查询该用户的所有表。

--dba_tables :查询所有解决方案的表。

9、表的联合查询:

select ~~~~~ union

select ~~~~~

--关键字：union ：两张表中的数据，并保证无重复数据。

--        union all ：不保证无重复数据。

--        intersect ：取出两张表中公共部分。

--        minus a表中已涵盖了b表，取出来的就是a表减b表的数据。

8、子查询语句：

select * from emp where sal in(select sal from emp where sal=30) ;  --in 是指所有的意思，=指任何一个

或者

select * from emp where (deptno,job)=(select deptno,job from emp where lie=30) ;

9、Oracle的分页写法：

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6 ;

----删

7、删除表中的数据：

delete from users;

8、删除表结构，即，表。

drop table users;

或者

truncate table student;  --此语句更快

----修改列、及按照固定的格式显示数据。

9、按照固定的时间格式显示时间：

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

10、薪水显示方式：

to_char(sal,'L99.999.99')

11、添加一个字段：

alter table student add(classId number(2));

12、删除字段(慎用)

alter table student drop column sal;

13、修改表字段的长度、类型：

alter table student modify(xm varchar2(30));

14、给列起别名：

select sal*12 '年薪' from emp ;

-------------Oracle存储过程、视图----------------

1、创建存储过程

create or replace procedure sp_proc1

is

begin

insert into my tes values('zou','aaa') ;

end ;

/

-----显示错误信息

show error ;

2、调用存储过程：

exec 过程名(参数值1，参数值2...)

或者

call 过程名(参数值1,参数值2...)

3、带输出、输入参数的存储过程：

create or replace procedure sp_pro(spNo in number,spName out varchar2,spSal out varchar2)

is

begin

select ename,sal into spName,spSal from emp where empno=spno;

end;

/

在Java中调用：

CallableStatement cs=ct.prepareCall("{call sp_pro(?,?,?)}") ;

cs.setInt(1,7788) ;

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);

cs.execute();

String name=cs.getString(2);

String sal=cs.getString(3);

4、返回集合列表：

1)、创建一个包

create or replace package testpackage as

type test_cursor is ref cursor ;

end testpackage ;

2)、创建存储过程：

create or replace procedure sp_pro(spNo in numbar,p_cursor out tespackage.test_cursor) is

begin

open p_cursor for select * from emp where deptno=spNo ;

end ;

/

3)、Java中读取集合：

......

cs.execute();

ResultSet rs=(ResultSet)cs.getObject(2);

While(rs.next()){

System.out.println(rs.getInt(1)+" "+rs.getString(2));

}

--关闭连接

....

5、分页存储过程：

--开发一个包

create or replace package testpackage AS

type test_cursor is ref cursor;

end testpackage;

--存储过程

create or replace procedure fenye

(tableName in varchar2,

Pagesize in number,      --一页显示数量

pageNo in number,      --页码

myrows out number,     --总记录数

myPageCount out number,--总页数

P_cursor out tespackage.test_cursor  --返回的记录集

) is

v_sql varchar2(1000) ；

v_begin number:=(pageNow-1)*Pagesize+1;

v_end  number:=pageNow*Pagesize;

begin

--执行部分

v_sql:= ' select * from (select t1.* rownum rn from (select * from '|| tableName | |') t1 where rownum<='|| v_end ||') where rn>='|| begin ；

--把游标和sql语句关联起来

open p_cursor for v_sql;

--要计算myrows和myPageCount

--组织一个sql语句

v_sql:=select count(*) from '|| tableName;

--执行sql，并把返回的值，赋给myrows

execute immediate v_sql into myrows;

--计算my

if mod(myrows,Pagesize)=0 then

myPageCount:=myrows/Pagesize;

else

myPageCount:=myrows/Pagesize+1;

end if ;

--关闭游标

close p_cursor;

end ;

/

6、创建Oracle视图

create or replace view myView as select * from emp where no<1000

-------------Oracle的异常处理----------------

1、异常的定义：

1)、case_not_found       ：

2)、cursor_already_open  ：游标已经打开，再次打开时则会抛出异常。

3)、dup_val_on_index     ：在唯一索引添加重复值时，抛出的异常。

4)、invaild_cursor       ：当试图在不合法的游标上执行操作时，会触发该例外。

5)、invalid_number       ：比如应该输入数字输入的是字符串。

6)、too_many_rows        ：返回多行时，用一个接收就会抛出该异常。

7)、zero_divide          ：当执行2/0语句时，则会出触发异常。

8)、value_error          ：当得到的数据比变量大，则会抛出该异常。

9)、login_denide         ：用户登陆异常则是该异常。

10)、not_logged_on       ：如果用户没登陆就执行dml操作，就会触发。

11)、storage_error       ：如果超出了内存空间或是内存被损坏，就触发该例外。

12)、timeout_on_resource ：如果oracle在等待资源时，出现了超时就触发该例外。

create or replace procedure sp_pro6(spno number) is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=spno;

case

when v_sal<1000 then

update emp set sal=sal+100 where empno=spno;

when v_sal<2000 then

update emp set sal=sal+200 where empno=spno

end case;

exception

when case_not_found then

dbms_output.put_line('case语句没有与'|| v_sal ||'相匹配的条件');

end ;

/

-------------数据备份操作操作----------------

----前提条件在Oracle的主目录下找到/bin/exp.exe，并运行

1、导出自己的表：

exp userid=system/accpsystem@accp tables=(users,table2) file=d:/users.dmp

2、导出其他用户的表：

exp userid=system/accpsystem@accp tables=(scott.emp) file=d:/emp.dmp

3、导出表结构：

exp userid=system/accpsystem@accp tables=(users) file=d:/users.dmp rows=n

4、直接导出方式：

exp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp direct=y

5、导出自己的方案：

exp scott/accpscott@accp owner=scott file=d:/scott.dmp

6、导出数据库：

exp userid=system/accpsystem@accp full=y inctype=complete file=d:/accp.dmp

7、导入自己的表：

imp userid=scott/accpscott@accp tables=(users,emp) file=d:/users.dmp

----导入时该表不能有主外键关系。

8、导入表到其他用户内：

imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp touser=system

9、导入表结构(导入表结构而不导入数据)：

imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp rows=n

10、导入表结构(导入数据而不导入表结构)：

imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp ignore=y

11、导入方案：

imp userid=scott/accpscott file=d:/scoot.dmp

12、导入其他解决方案(必须具有dba的权限)：

imp userid=system/accpsystem file=d:/system.dmp fromuser=system touser=scott

13、导入数据库：

imp userid=system/accpsystem full=y file=d:/accp.dmp

-----------------表空间的管理-----------------

1、创建表空间：

create tablespace spoo1 datafile 'd:/test/data01.dbf' size 20m uniform size 128k;

2、使用表空间：

create table myPart(deptno number(4),dname varchar2(10),loc varchar2(13)) tablespace sp001 ;

3、使表空间脱机：

alter tablespace 表空间名 offline;

4、使表空间联机：

alter tablespace 表空间名 online ;

5、只读表空间：

alter tablespace 表空间名 read only ;

6、可读写表空间：

alter tablespace 表空间名 read write;

7、查询表空间中的表：

select * from all_tables where tablespace_name='表空间名' ;

8、知道表名，查询所属表空间：

select tablespace_name,table_name from user_tables where table_name='emp' ;

9、删除表空间：

drop tablespace '表空间' including contents and datafiles ;

10、增加数据文件：

alter tablespace sp001 add datafile 'd:/test/sp001.dbf' size 20m

11、增加数据文件的大小：

alter tablespace sp001 'd:/test/sp001.dbf' resize 200m ;

12、设置文件的自动增长：

alter tablespace sp001 'd:/test/sp001.dbf' autoextend on next 10m maxsize 500m ;

----故障处理，将表移动到其他表空间：

1)、确定数据文件所在的表空间：

select tablespace_name from dba_data_files where file_name='d:/sp001.dbf' ;

2)、使表空间脱机：

alter tablespace sp001 offline ;

3)、使用命令移动数据文件到指定的目标位置：

host move d:/sp001.dbf c:/sp001.dbf ;

4)、执行alter tablespace 命令：

alter tablespace sp001 rename datafile 'd:/sp001.dbf' to 'c:/sp001.dbf' ;

5)、使表空间联机：

alter tablespace sp001 online ;

6)、显示表空间信息：

select tablespace_name from dba_tablespaces ;

7、显示表空间所有包含的数据文件：

select file_name,bytes from dba_data_files where tablespace_name='表空间名' ;

-------------Oracle基本类型----------------

number(5,2)  ：表示一个小数有5位有效数，2位小数范围：-999,99-999,99.

number(5)      ：表示一个5位的整数 -99999-99999

number范围 -10的38次方-10的38次方可以表示整数，也可以表示小数。

char(10)       ：字符型，使用它比varchar的读取效益要强很多，常用属性要是用char类型，最大2000.

varchar        ：最大4000

clob           ： 字符型的大对象4G。

date           ：精确到秒钟。比较常用。

timestamp      ：精度很高，精确到毫秒。

blob           ：二进制数据，可以存放图片/声音 4G。(很少使用，如果安全性很高的话可以使用)。

-------------Oracle约束、角色----------------

1、添加约束：

alter table customer add constraint card_uniquer unique(cardId) ;

2、删除约束：

alter table customer drop constraint 约束名 ;

3、删除主键：

alter table 表名 drop primary key cascade ;

4、查询约束信息：

select * from user_constraints where table_name='约束名' ;

5、创建索引：

create index on '索引名' on '表名'(列名,列名....) ;

-------------Oracle的内置函数----------------

lower()          ：将字符串转换成小写。

upper()          ：转换大写。

length(char)     ：返回字符的长达。

substr(char,m,n) ：截取字符串。

--第一个参数列名，第二参数是从几个开始取，第三个参数是取几个。

--示例：select upper(substr(ename,1,1)) || lower(substr(ename,2, length(ename)-1)) from emp;

replace(char1,search_string,replace_string) ：替换方法。

instr(char1,char2,[,n[,m]]) ：查找位置。

round(n,[m]) ：该函数用于执行四舍五入，如果省掉m，则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后，

如果m是负数，则四舍五入到小数点的m位前。

trunc(n,[m]) ：该函数用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截取

到小数点的m位后，如果m是负数，则截取到小数点的前m位。

mod(m,n)     ： 取余数。例：select mod(elieming,2) from emp;

floor(n)     ：返回小于或是等于n的最大整数。

ceil(n)      ：返回大于或是等于n的最小整数。

abs(n)       ：返回数字n的绝对值。

acos(n)      ：返回数字的反余弦值。

asin(n)      ：返回数字的反正旋值。

atan(n)      ：返回数字的反正切。

cos(n)       ：

exp(n)       ：返回e的n次幂。

log(m,n)   ：返回对数值。

power(m,n)   ：返回m的n次幂。

----日期函数：

sysdate      ：获取当前时间。

ADD_MONTHS(日期,月数) 返回日期

MONTHS_BETWEEN(日期，日期) 返回月数

LAST_DAY(日期) 当前日期月份最后一天 如倒数第几天，可以使用减。

ROUND(日期，模式) 当前日期舍入日期 如round(sysdate,’year’)

NEXT_DAY(日期，星期) 如next_day(sysdate,'星期二’)

TRUNC(日期,模式) 截断日期

EXTRACT(模式 from 日期) 提取时间部分

Oracle可以进行隐形转换数据类型。例：

create table t1(id int)；

insert into t1 values('10') —>这样oracle会自动的将’10’转换成10。

日期显示时/分/秒。例：

yy          ：两位数字的年份2004—>04。

yyyy        ：四位数字的年份 2004年。

mm          ：两位数字的月份8月—>08

dd          ：2位数字的天30号—>30

hh24        ：8点—>20

hh12        ：8点—>08

mi、ss      —>显示分钟/秒

select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;