Oracle学习笔记

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的类型, 参数2in 参数2的类型, …. 参数3 out参数3的类型, 参数4out 参数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，参数2in类型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 自定义视图名;  ….和删除表没什么不同

 

视图本身也可以在语句中使用其它视图