Oracle总结_kasq1314的博客-CSDN博客

一、数据类型

Oracle 9i有3种数据类型：基本数据类型、集合类型和关系类型。

1.1、基本数据类型：

Char，nchar,Varchar2,Nvarchar2,number,Date, float，Long Raw,Long Raw,Rowid,Blog,Clob,Nclob,Bfile,Urowid，

Boolean字符类型:true，fault

Char，Nchar, Varchar2,Nvarchar2，Long字段是固定长度的情况或字段经常改变的情况下使用Char，Nchar;如果储存的字段是变长的话使用Varchar2和Nvarchar2. 存储大量的变长字符类型使用Long，最大可达2GB;

带N的存储的Unicode字符，即汉字占一个字符，不带N汉字占两个字符;

日期类型：只有Date，使用的时候需要按照数据库的日期存储格式进行;

数值类型：Number，Float 。 Number存储整数或浮点型数据，比如Number（4，2）代表4位，小数点为2位。Float可以使用Number代替

非结构化的变长字符的数据类型

Raw（L）2KB和Long Raw（L）2GB存储二进制数据，不会在字符集间转换。

L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。

1.2、集合数据类型：

包括序列、数组类型和表类型3种。

序列可以实现sql server中自增长列

例子：

Create sequence 序列名 increment by 1 start with 1

解释：从1开始，每次增长1

使用的时候序列名.nextval

1.3、关系数据类型：

也称作引用数据类型(REF)，它以引用的方式定义了和其他对象的关系，存储的是指向不同对象数据表的数据的指针。

1.4、数据类型之间的转换

1、Raw和Varchar2之间的转换

1) utl_raw.cast_to_raw该函数按照缺省字符集（一般为GB2312），将VARCHAR2字符串转换为RAW。如: utl_raw.cast_to_raw(‘您好！’)

2) utl_raw.cast_to_varchar2该函数按照缺省字符集合（一般为GB2312），将RAW转换为VARCHAR2。如: utl_raw.cast_to_varchar2(字段名或十六进制或ASCII码)

2、to_number将给出的字符转换为数字

3、日期的插入和读取：to_char和to_date 的使用：

1) 当往数据库中数据类型为date的字段插入值的时候需要使用to_date()进行类型转换

2) 当从数据库中取出数据的时候需要使用to_char()来取出需要的部分

二、自带的函数

2.1、常用的函数

1.ASCII返回与指定的字符对应的十进制数;如ascii(’A’)

2.CHR给出整数,返回对应的字符; chr(54740)

3.CONCAT连接两个字符串;

4.INITCAP返回字符串并将字符串的第一个字母变为大写;

5.INSTR(C1,C2) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

6.LENGTH返回字符串的长度;需要注意汉字

7.LOWER返回字符串,并将所有的字符小写

8.UPPER返回字符串,并将所有的字符大写

9.RPAD和LPAD 语法：lpad（‘gray’，10，‘*’）结果：******gray

10. LTRIM删除左边出现的字符串,RTRIM删除右边出现的字符串

11.SUBSTR(string,start,count) ,取子字符串,从start开始,取count个

如果start位负数则从倒数开始取

12.REPLACE(’string’,’s1’,’s2’)，在string中使用s2代替s1

13.SOUNDEX返回一个与给定的字符串读音相同的字符串

14.TRIM(’s’ from ’string’)在string中将s去掉，默认位空字符

15.ABS返回指定值的绝对值

16.CEIL返回大于或等于给出数字的最小整数如3.1返回4，5就返回5

17.FLOOR对给定的数字取整数

18.MOD(n1,n2) 返回一个n1除以n2的余数

19.ROUND和TRUNC按照指定的精度进行舍入

Round进行四舍五入，Trunc不进行四舍五入

20.TO_CHAR(date,’format’)

21.TO_DATE(string,’format’)将字符串转化为ORACLE中的一个日期关于to_char和to_date的使用：当往数据库中数据类型为date的字段插入值的时候需要使用to_date()进行类型转换当从数据库中取出数据的时候需要使用to_char()来取出需要的部分

22. to_number将给出的字符转换为数字

23.STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
24. 处理字段名可能出现 null 情况的函数

NVL(expr1, expr2) 如果expr1为null则转换为expr2

NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型

NULLIF(expr1, expr2) ->相等返回NULL，不等返回expr1

25.COALESCE (expression_1, expression_2, ...,expression_n)返回的结果为第一个结果不为null的表达式值，如果都为null则返回null,可以替换简单的case语句

26.CONVERT(c,dset,sset)将源字符串sset从一个语言字符集转换到另一个目的dset字符集?

2.2、日期

1、时间的取法

取年：Select to_char(sysdate,’YYY’) from dual;

Y,YY,YYY,YYYY,YEAR取年的几位，依次为：8，08,008,2008, Two Thousand Eight取月：Select to_char(sysdate,’MM’) from dual;

MM,RM,Month依次为：10，X,10月

取季度：Q select to_char(sysdate ,’Q’) from dual;

如: 1～3月为第一季度，2表示第二季度。

取周Select to_char(sysdate,’WW’) from dual;

WW当前第几周，W本月第几周

取日Select to_char(sysdate,’DDD’) from dual;

DDD, 当年第几天, DD当月第几天, D周内第几天, DY,day中文的星期几取小时：Select to_char(sysdate,’HH’) from dual;

HH，hh12，12制度，HH24，24制度

取分秒:

Mi为分钟，ss为秒

读取整个日期的格式：

SQL> select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) from dual;

结果：2008-11-03 16:42:18

可以根据想要的结果进行组合

2、常用的日期函数

Add_months增加或减去月份add_months(sysdate,2) from dual;

months_between(date2,date1)两个日期之间的月数，前面的减去后面的

也可以为-2，整数表示现在时间往后退，负数表示时间往前推

last_day返回本月的最后一天select last_day(sysdate) from dual;

next_day给出当前日期date和星期x之后的日期

trunc(date,fmt)按照给出的要求将日期截断,如果fmt=’mi’表示保留分,截断秒

current_date()返回当前会话时区中的当前日期

extract()找出日期或间隔值的字段值

如：select extract(month from sysdate) “This Month” from dual;

三、建表和约束

3.1、建表

客户表(client)

create table client

(

c_id number(4) primary key,

);

创建客户自增长的序列

create sequence seq_c_id increment by 1 start with 1;

当刚创建序列的时候不能使用currval进行操作，

需要使用nextval进行访问序列中的下一个值

select seq_c_id.currval from dual;

使用创建的序列进行表的插入

insert into client values(seq_c_id.nextval);

订单表（c_order）

create table c_order

(

o_id number(4) primary key,

c_id number(4) references client(c_id) on delete cascade,

外键约束：删除主表的时候连通子表也删除

on delete set null删除主表的时候将子表中的字段设为null

默认是不能删除有子表相关联的主表中的数据

o_invoice varchar2(6) check (o_invoice= ‘需要’ or o_invoice= ‘不需要’), --check约束

unique (o_id,c_id) --唯一约束

);

3.2、约束：

分表级和列级，使存储的数据变得有意思，实现数据的完整性

约束可以在创建表的时候创建，也可以在创建表后创建

Not null，默认为null，

Unique唯一约束，有时候需要两个或两个以上的列来完成

primary key 主键约束，自动包含了unique和not null

foreign key 外键约束，有时也叫参照完整性约束

check 使数据库存储的字段按照要求进行存储

default 即默认的时候字段的值

创建表后添加约束语法：

不要写约束名（Oracle自动添加）

alter table OracleTypesTable

add unique (mynumber,MyVarchar2);

写名约束名：

alter table OracleTypesTable

add constraint de_OracleTypesTable_1

unique (mynumber,MyVarchar2);

删除约束

alter table OracleTypesTable

drop constraint de_OracleTypesTable_1;

约束的其他用法：

alter table authors

disable constraint pk_au --使约束失效

enable的使用 --使约束可用
cascade的用法：
删除列的时候将对应的约束也删除掉
alter table ss
drop column id cascade constraint

四、sql语句

1、Select username un,count(*)

From ss

Group by username

Having count(*)>1

Order by id desc;

在没有使用组函数之前order by后面可以跟select后面没有出现的字段，

使用了组函数以后，组函数之前的字段必须要在group by中出现，

使用多表操作的时候往往是先挑选后连接

2、取top n

工资最高的前5个员工

select * from

(select last_name,salary from s_emp

order by salary desc )

where rownum < 6;

3、连接：

自身连接：使用表别名连接

外连接：左外连接和右外连接

表1.id（+）=表2.id；

如果表1中的信息没有表2多，则表1的缺乏的值将用null填充

4、单行函数

Lower，upper，substr，instr，trunc

5、组函数

AVG(DISTINCT|ALL)

all表示对所有的值求平均值,distinct只对不同的值求平均值默认位all MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

使用count（）的时候有三种情况：

Count（*）最后的结果包括重复和null的列

Count（字段名）不包括null，但包括重复的列

Count（distinct字段名）不包括null和重复的列

转义的使用：在查询匹配的时候用

例如查询s开头的只带一个s的，数据库中字段有ss….，和s….

使用转义：like ‘s\_%’ escape ‘\’; 转义的\可以随便指定一个字符

定义格式：使查询的结果按照预定义的格式进行读取

例如:定义：column salary format ‘$999,999.00’

删除：column salary clear

五、存储过程

5.1、基本语法：

Create or replace procedure存储过程名

(I in number, ii out number,iii in out number ) (sql server中要不是输入要不是输出)

As|is

变量的声明

Begin

End存储过程名；

------------操作单个记录------------------
5.2、不带参数的存储过程
   create or replace procedure p_loop
as
   number1 integer:=80;    --声明变量
   i integer:=0;
begin
   for i in 1..10 loop --循环的次数
     number1:=number1+1;
   end loop;
   dbms_output.put_line(‘number1”s value:’||to_char(number1));
end;
--修改表的不带参数的存储过程
create or replace procedure pp
as
   num integer:=10;
   --i integer :=0;
begin
     --for i in i..10 loop
     update ss set id =num;
     --end loop;
end;
5.3、存储过程的创建用于将id=acc_no的id加1
--只有输入参数的存储过程
       CREATE PROCEDURE credit
       (acc_no IN NUMBER)
        AS
       BEGIN
          UPDATE ss
          SET id = id+1
          WHERE id =acc_no;
       END;
--执行的时候需要在命令窗口中执行
       exec credit(2);
   5.4、有输入和有输出的存储过程
   create or replace procedure first_pro
   (i in number,ii out number)
   as
   begin
       ii:=i+1;
   end;
   --执行，同样也是在命令窗口中进行
   SQL> var ii number;                      --定义输出的变量
   SQL> exec first_pro(2,:ii);              --执行存储过程
   5.5、只有输出的存储过程
   create or replace procedure Out_only
   (i out number)
   as
   ii number:=100;
   begin
        i:=ii+1;
   end;

六、事物的使用

6.1、Oracle数据库中的事物

insert into ss values(1,’gray’); --第一条插入语句

savepoint insert1; --设置保存点，也就是还原点

insert into ss values(2,’liulangren’); --第二条插入语句

rollback to insert1; --回滚到insert1还原点，这是第二条插入语句就无效了

commit; --提交事物

rollback to insert1; --错误在commit了以后，事物的还原点就清空了，

rollback; --只写一个rollback还原最近一个commit；

6.2、Asp.net（c#）中使用Oracle事物

使用OracleTransaction类的一个对象来表示一个事务。 OracleTransaction类包含许多操控事务的方法。使用Commit()方法永久提交SQL语句，并可以使用Rollback()撤销这些语句。您还可以使用Save() 在事务中设置一个保存点。

导入命名空间

using System.Data.OracleClient;

第1步

创建一个OracleConnection对象连接到Oracle数据库，然后打开该连接。在C#中：

OracleConnection myOracleConnection =new OracleConnection(

“User Id=store;Password=store;Data Source=ORCL”);

myOracleConnection.Open();

第2步

创建一个OracleTransaction对象，然后调用OracleConnection对象的BeginTransaction()方法启动事务。 OracleTransaction myOracleTransaction =myOracleConnection.BeginTransaction();第3步创建一个OracleCommand对象，用于存储SQL语句。 OracleCommand myOracleCommand = myOracleConnection.CreateCommand();

因为OracleCommand对象使用OracleConnection对象的CreateCommand()方法创建的，所以它自动使用在第2步中为OracleConnection对象设置的事务。

第4步

将OracleCommand对象的CommandText属性设为向表ss中添加一行的第一条INSERT语句。在C#中： myOracleCommand.CommandText =”insert into ss values ( 3, ‘M’)”;

//设置事物的保存点SaveName

myOracleTransaction.Save(“SaveName”);

第5步

使用OracleCommand对象的ExecuteNonQuery()方法运行INSERT语句。

myOracleCommand.ExecuteNonQuery();

第6和第7步

将OracleCommand对象的CommandText属性设为向表ss中添加一行的第二条INSERT语句，并运行它。 myOracleCommand.CommandText =”insert into ss values ( 4, ‘N’)”; myOracleCommand.ExecuteNonQuery();

第8步使用OracleTransaction对象的Commit()方法提交数据库中的事务。 myOracleTransaction.Commit();在完成Commit()方法之后，由INSERT语句添加的两行将在数据库中永久记录。 //可以使用事物的回滚，是插入的第二条语句失效，回滚到SaveName myOracleTransaction.Rollback(“SaveName”);

第9步

使用Close()方法关闭OracleConnection对象。

myOracleConnection.Close();

在 .NET程序中设置事务保存点

使用OracleTransaction类的Save()方法在事务中设置保存点。

七、存在的问题

游标的使用，数据集的读取

使用group by的时侯select语句后面的字段必须都要在group by后面出现，否则会出现没有关联的错误

select ssfj,dss,qybm,qymc,sum (rs),sum (sre),sum (msxmhj),

sum (ynse),sum (jmse),sum(sjynse)

from bims_sbqktjb200712

group by ssfj,dss,qybm,qymc