1.Oracle修改默认date格式‘dd-MON-yy’到 ‘yyyy-mm-dd’:
alter sessionset nls_date_format='yyyy-mm-dd';
2. 设置保存点:
savapoint aa;
回滚保存点:
rollback aa;
Oracle一些其它常用实用的命令:
(1)连接命令 conn[ect] 用法:conn 用户名/密码@网络服务名 [assysdba/sysoper] 当用特权用户身份连接时,必须带上as sysdba 或是 as sysoper.
(2)显示当前登陆用户名: show user;
(3)用其它用户重新登录:直接使用(1)中的命令 conn 用户名/密码即可。
(4)断开与当前数据库的连接: disc[onnect]
(5)修改密码,passw[ord] 如果要修改别的用户的密码,必须使用sys/system登陆。
3.Oracle处理null值的函数:NVL(string1, replace_with)
功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值。
引申一下,此NVL的作用与SQLserver 中的 ISNULL( string1, replace_with) 一样。
注意事项:string1和replace_with必须为同一数据类型,除非显示的使用TO_CHAR函数。
4.设置只读事务:
Set transaction read only
设置了只读事务后,可能查询设置只读事务时间点及以前的数据,而不受之后其它用户对表的增删改的影响。
5.Oracle字段字符串的合并用“||”实现
例:select fitstname || secondnamefrom employee where userid=’007
’
6.Oracle内置的SQL函数:
lower(char), 将char转换成全部为小写格式
upper(char) 将char转换成全部为大写格式
length(char) 获取char的长度
substr(char,m,n) 获取子字符串,m:表示源字符串char的起始点位置(m从1开始,表示char中的第一个字符),n代表长度。
replace(char,search_str,replace_str) 查找并将字符串char中的子字符串search_str替换为replace_str表示的字符串
instr(source_str,des_str [,m[,n]]) 获取source_str中从m位置起始的第n次出现des_str的位置。 m可选,省略则默认为1,为正则从左向右检索,为负则从右向左检索。n不写默认为1.不可为负,否则报错。
常用数学函数:
round(n [,m]) 四舍五入函数,n为被操作的数,m不写则四舍五入到整数位,为正数则四舍五入到小数点后m位,为负数则四舍五入到小数点前m位。
trunc(n [,m]) 截断取整函数,n为被操作的数,m不写则截断取到整数位,为正数则截断取到小数点后m位,为负数则截断取到小数点前m位。
mod(n,m) 获取n除以m的余数
floor(n) 返回小于或是等于n的所有整数中最大的整数
ceil(n) 返回大于或是等于n中所有整数中最小的整数
常用日期函数:
sysdate 返回系统时间,Oracle默认日期格式为:dd-mon-yy 即 12-8月-12
last_day(d) 获取d表示的日期所在月份的最后一天。 d为日期类型数据,或能转换成日期格式的字符串。
add_months(d,n) 在d表示的日期上加上n指定的月数,d为日期类型数据,或能转换成日期格式的字符串。 n表示月数,不为整数则取整数位的值,为正则为在d上加n个月,为负,则在d上减n(绝对值)个月
7.Oracle导出自己的表:
1:进入bin目录下,使用exp.exe程序:
2:格式为
exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,...) file=导出文件目录\导
出文件名.dmp
8.导出其它用户(即其它方案)的表:
1:登录的用户需要有dba权限或是exp_full_database的权限:
2;格式为:
exp userid=登录的用户名/密码@数据库实例名 tables=(其它用户名.表名1,其它用户名.表名2,...) file=导出文件目录\导出文件名.dmp
9.只导出表结构,不包含数据:
在之前的格式后面加上rows=n即可
如:
exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,...) file=导出文件目录\导
出文件名.dmprows=n
10.直接导出方式:(导出表结构和表内容,不过要求数据库字符集和客户端字符集完全一致,否则会报错,优点:速度快)
exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,...) file=导出文件目录\导
出文件名.dmpdirect=y
exp userid=登录的用户名/密码@数据库实例名 tables=(其它用户名.表名1,其它用户名.表名2,...) file=导出文件目录\导出文件名.dmp direct=y
11.导出方案(即导出指定数据库中的指定方案下的所有表,存储过程,视图等等……)
exp userid=登录的用户名/密码@数据库实例名 owner=登录的方案名 file=导出文件目录\导出文件名.dmp (登录的用户名即方案名)
导出其它方案:
exp userid=登录的用户名/密码@数据库实例名 owner=(登录的方案名,其它方案名1,其它方案名2,....) file=导出文件目录\导出文件名.dmp
注:如果要一起导出其它方案的话,登录的方案名和密码要具有dba权限或是exp_full_database权限。
12.导出整个数据库:
exp userid=登录用户名/密码@数据库实例名 full=y inctype=complete file=导出文件目录\导出文件名.dmp
13.导入自己的表:
(1)进入oracle的bin目录,以使用bin目录下的imp.exe导入工具软件。
imp userid=登录的用户名/密码@数据库实例名 tables=(表名1,表名2,表名3,…) file=备份文件所在目录\备份文件名.dmp
14.导入表到其它用户:
要求登录的用户具有dba的权限,或者是imp_full_database权限
imp userid=登录的用户名/密码@数据库实例名 tables=(表名1,表名2,表名3,….) file=备份文件所在目录\备份文件名.dmp touser=其它用户名
15.只导入表的结构,不导入表的数据
imp userid=登录的用户名/密码@数据库实例名 tables=(表名1,表名2,表名3,….) file=备份文件所在目录\备份文件名.dmp rows=n
16.只导入表数据,不导入表的结构(如果对象(比如表)已存在)
imp userid=登录的用户名/密码@数据库实例名 tables=(表名1,表名2,表名3,….) file=备份文件所在目录\备份文件名.dmp ignore=y
17.导入方案,和导入表类似
(1) 导入自身方案
imp userid=登录的用户名/密码 file=备份文件所在目录\备份文件名.dmp
(2)导入其它方案(登录用户名须具有dba或者imp_full_database权限)
Imp userid=登录的用户名/密码 file=备份文件所在目录\备份文件名.dmp fromuser=登录的用户名 touser=其它方案名
18.导入数据库
Imp userid=登录的用户名/密码 full=yfile=登录的用户名 touser=其它方案名
19.数据字典和动态性能视图:
user_tables;
用于显示当前用户所拥有的所有表,它只返回用户所对应方案的所有表:
比如:selecttable_name from user_tables;
all_users
用于显示当前用户可以访问的所有表,它不仅会返回当前用户方案的所有表,还会返回当前用户可以访问的其它方案的表(比如别的用户可以授权该用户拥有对自己的表的各种权限)
比如:selecttable_name from all_tables;
dba_tables
它会显示所有方案拥有的数据库表,但是查询这种数据库字典视图,要求用户必须是dba角色或是有selectany table系统权限。
dba_users;
查询dba_users可以显示所有数据库用户的详细信息
dba_sys_privs
通过查询数据字典视图dba_sys_privs,可以显示用户具有的系统权限
或者通过role_sys_privs 查询用户具有的系统权限
比如:select *from role_sys_privs where role=’CONNECT’
dba_tab_privs
通过查询数据字典视图dba_tab_privs,可以显示用户具有的对象权限
dba_col_privs
通过查询数据字典dba_col_privs可以显示用户具有的列权限
dba_role_privs
通过查询数据字典视图dba_role_privs可以显示用户所具有的角色
dba_roles
通过查询dba_roles,可以显示Oracle中所有角色,一般是dba
system_privilege_map
查询Oracle中所有的系统权限,一般是dba
比如:
Select * form system_privilege_map order byname;
dba_tab_privs
查询Oracle中所有对象权限,一般是dba
比如:
Select distinct privilege fromdba_tab_privs;
dict
显示当前用户可以访问的所有数据字典视图
比如:select *from dict where comments like ’%grant%’
global_name
显示当前数据库的全称
比如:select *from global_name;
20.PL/SQL编程
(1)创建过程
过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out).通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境。
create or replace procedure 过程名(参数名1 参数1的类型,参数2 参数2的类型,….) is
(2)创建函数
函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。
create function 函数名 (参数名1 参数1的类型,参数2 参数2的类型,….) return 返回值类型 is 返回值名 具体的返回类型;
比如 createfunction myfunction ( mynum number,myhaha varchar2) return number is myresultnumber(7,2);
begin
--代码执行体
end;
-- set serveroutput on/off---打开/关闭输出选项
(3)PL/SQL中的异常,例外
在PL/SQL中的begin和end部分中加入形如以下的代码:
exception 自定义或Oracle预定义的异常类型名 then
--处理代码
Oracle中常见的例外:
http://blog.163.com/zhm_spongcer/blog/static/174805577201121852821869/
no_data_found
(4)在sqlplus中调用函数:
Sql> var income number --定义一个变量income,类型为number
Sql> call 函数名(参数1,参数2,…) into:income –将调用函数产生的返回值通过 into:(包含英文冒号)存入income中
Sql> print income –打印得到的值
在应用程序中调用函数:
select 函数名(参数1,参数2….) from dual;
可以用resultSet类中的rs.getString(“”),rs,getInt(“”)得到返回值
(5)创建包
包用于在逻辑上组合过程和函数,它由规范和包体两部分组成。包的规范只包含了过程和函数的说明,但是没有过程和函数实现代码。如要实现过程和函数,可以使用“包体”。
create or replace package 包名 is
procedure 过程名(参数1 参数1的类型 , 参数2 参数2的类型…);
function 函数名(参数1 参数1的类型 , 参数2 参数2的类型…);
end;
(6)创建包体(本质上是将包中的过程和函数按照它们自己的格式实现执行部分,成为包体)
create or replace package body 包体名 is
procedure 过程名(参数1 参数1类型,参数2 参数2类型..) is
begin
--执行部分内容
end;
function 函数名(参数1 参数1的类型 , 参数2 参数2的类型…)
return 返回类型 is 返回值名 具体的返回类型
begin
--select sal from 表名。。。。。into 返回值名 where 。。。
return 返回值名
end;
end;
(7)调用包中的过程或者函数
Sql> exec/call 包名.过程名(参数1,。。。)/ 包名.函数名(参数1。。。)
(8)PL/SQL中标量
定义的变量一般有如下格式:
常量:以c_ 开头 例:c_myname varchar2 :=’超人’
变量:以 v_ 开头 例:v_myvar varchar2
游标:以 _cursor作后缀 mycursor_cursor
使用 %type类型:
标识符名 表名.列名%type –这样可以获得表的相应字段的类型,赋给变量名,从而解决自定义的变量的类型的溢出等问题
Oracle中四种数据类型:http://blog.csdn.net/jojo52013145/article/details/6827795
(9) Oracle中的复合(composite)
【1】记录类型
(record类型于Java中的结构体,一个复合类型相当于一个或多个变量的集合)
格式:
declare
type 自定义复合记录类型名 is record (参数1 参数1的类型, 参数2 参数2的类型….);
参数1,2的类型可为:表名.列名%type
declare
type my_Record_typeis record(…..)
使用方法:
复合记录变量名 自定义复合记录类型名; ----例:my_record variable my_Record_type
begin
select字段1,字段2,字段3。。into复合变量名 from 表名1 where(….) (select的几个字段要和复合变量成员的数目,类型,顺序要一致)
dbms_output.put_line(复合记录变量名.参数1);--使用点号操作符引出复合变量的成员
end;
【2】表类型 PL/SQL表
相当于高级语言中的数组,但是需要注意的是,在高能语言中的下标不能为负,而PL/SQL中是可以为负的,并且表元素的下标没有限制
格式:
declare
type 自定义表类型名 is table of 变量类型 --(变量类型可为:表名.列名%type)定义一个PL/SQL表类型
index by binary_integer; -- (表示表类型的下标是整数类型)
例:
declare
type my_Table_type is table of Employer,salary%type index by binary.integer;
使用方法:
复合表变量名 自定表类型名; ---例: my_Table_variable my_Table_type;定义一个表类型的变量
begin
select 字段1 into my_Table_variable(0) from 表名 where….;
dbms_output.putline(复合表变量名(0)) --复合表变量名(0) 表示下标为0的元素
end;
(10)Oracle中的参照变量 1.游标变量 ref cursor 2.参照类型变量 ref obj_type
【1】游标变量 refcursor
格式:
declare
type 自定义游标类型名 is ref cursor;
标量变量1 类型;……
自定义游标类型变量名 自定义游标类型名;
begin
open自定义游标类型变量名 for 一条SQL语句–-(将游标和select结合一般为select语句,得到多行结果集)
loop
fetch 自定义游标类型变量名 into 标量变量1,标量变量2…..; --取出光标所在行的单行结果,通过 into 赋值给对应结果,通过loop等循环语句使光标下移到新行
end loop;
end;
例:
declare
type sp_cursor_type is ref cursor;
my_cursor sp_cursor_type;
my_var1 表名.字段1%type;
my_var2 表名.字段2%type;
….
begin
open my_cursor for select * from 某表名 where ….;
loop
fetch my_cursor into my_var1,my_var2;
exit when my_cursor%notfound; --通过 exit when……退出,避免死循环
dbms_output.put_line(my_var1 || my_var2);
end loop;
end;
(11)Oracle中的PL/SQL的控制语句
【1】单重条件分支
格式:
if 条件语句 then
--执行语句1
end if; --PL/SQL中if分支的then,end if 不可少,这是语法规定的
【2】二重条件分支
格式:
if 条件语句 then
--执行语句1
else
--执行语句2
end if;
【3】多重条件分支
格式
If 条件语句 then
--执行语句1
elsif 条件语句 then --此处为elsif不是else if /elseif
--执行语句2
[else/elsif…….] –多重嵌套多个条件分支,可选
end if;
【4】loop循环语句(相当于Java中和do…while();循环,至少执行一次)
格式:
loop
--执行体语句
exit when 退出循环的条件语句;
end loop;
【5】while循环语句
格式:
while 判断条件 loop
--执行体语句
end loop;
【6】for循环语句
格式:
for i in reverse 1..10 loop 1和10之间是两个点号,步长为1.
--执行代码
end loop;
【7】goto语句
格式:
goto 标签名
<<s标签名>>
【8】null语句
相当于Java里只有一对大括号的空语句,没有任何操作,只为增强可读性
if 条件语句 then
--执行体语句
else
null;
end if;
(12)Oracle中的带有返回值的存储过程(单行结果) (存储过程没有返回值,实质以out参数返回结果)
Create or replace procedure 存储过程名 (参数1 in 参数1的类型, 参数2 in 参数2的类型, …. 参数3 out参数3的类型, 参数4 out 参数4的类型….) is --in表示为输入参数,out表示输出参数,即返回值。不写in或out则默认为in。
begin
select Xx1,Xx2..into 参数3,4….. from XX where …..;
end;
Java程序中调用带返回值的存储过程:(存储过程没有返回值,实质以out参数返回结果)
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connectionconn=DriverMnaager.getConnnection(“jdbc:oracle:thin:@IP:端口号:数据库实例名”,”用户名”,”密码”);
CallableStatement cs=conn,prepareCall(“{call存储过程名(?,?)}”);
cs.setXxx(1,”Xxx”);….--- setXxx()方法是给in 类别参数赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.Xxx);----该重载方法是给out类别参数注册一个输出值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.Xxx);…….
cs.execute();
String name=cs.getString(2);…. --注意,cs.getXxx(n)中的n等参数要和registerOutParameter(n,XX)中的n要一致。
(13)Oracle中处理返回结果集型(多行结果)返回值的存储过程
步骤1:建一个包,如下:
Create or replace package 包名1 as
Type 自定义游标类型名 is ref cursor; --一般游标以_cursor结尾
end 包名1;
步骤2:创建存储过程
Create or replace procedure 存储过程名(参数1 in类型1,参数2 in类型2,…参数3 out 包名.自定义游标类型名) is
begin
--执行体代码
open 参数3 for select……; --参数3为类型为自定义游标类型名的一个游标变量名
end;
Java中处理调用存储过程返回的结果集:
Cs.registerOutParameter(n,oracle.jdbc.OralceTypes.CURSOR);
Cs.execute();
ResultSet rs=(ResultSet)cs.getObject(2);
While(rs.next()){……}
(14)Oracle中处理分页的技术:
核心SQL语句:
Select * from ( (select t1.*,rownum rn from ( (select * from 表名1) t1 ) where rownum<=自己想要的最大行号) tb2) where tb2.rn>=自己想要的最小行号); --核心思想是将子查询的结果当作一个表,并取别名,再次进行查询,rownum为Oracle内置的表示行数的函数
(15)例外exception和自定义例外
Oracle中提供预定义例外达20多种
如何触发自定义异常:
1在存储过程中定义一个自定义异常名,类型为exception
2使用 sql%notfound 等语句作为判断条件
3使用raise 自定义异常名 –触发自定义异常
例
Create or replace procedure my_pro1(my_numnumber) is
myex exception; --自定义一个类型为exception的异常变量
begin
update table1 set XX=xxx where yyy=my_num;
if sql%notfound then --判断是否触发以上异常,判断条件可以自定义
raise myex; --触发自定义异常
end if;
exception --以下3句为异常处理块,和处理Oracle预定义异常没什么不同的格式
when myex then
--执行自定义异常处理语句
end;
(16)创建与使用视图
格式:
Create or replace view 自定义视图名 as
一条select 语句 [with read only] ;
使用view:
select * from 自定义视图名where…..和查询表没有什么不同
删除视图
drop 自定义视图名; ….和删除表没什么不同
视图本身也可以在语句中使用其它视图