oracle基础知识点总结

                                  目录
                                                 
Oracle总结 2
一、数据类型 2
1.1、基本数据类型： 2
1.2、集合数据类型： 2
1.3、关系数据类型： 3
1.4、数据类型之间的转换 3
二、Oracle自带的函数 3
2.1、常用的函数 3
2.2、日期 4
三、建表和约束 5
3.1、建表 5
3.2、约束： 6
四、sql语句 6
五、存储过程: 7
六、事物的使用： 9
6.1、Oracle数据库中的事物 9
6.2、Asp.net（c#）中使用Oracle事物 9
七、存在的问题 10








Oracle总结
一、数据类型
Oracle 9i有3种数据类型：基本数据类型、集合类型和关系类型。
1.1、基本数据类型：
Char，nchar,Varchar2,Nvarchar2,number,Date, float，Long Raw,Long Raw,Rowid,Blog,Clob,Nclob,Bfile,Urowid，Boolean字符类型：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(‘您好！’)
utl_raw.cast_to_varchar2该函数按照缺省字符集合（一般为GB2312），将RAW转换为VARCHAR2。如: utl_raw.cast_to_varchar2(字段名或十六进制或ASCII码)
2、to_number将给出的字符转换为数字
3、日期的插入和读取：to_char和to_date的使用：
当往数据库中数据类型为date的字段插入值的时候需要使用to_date()进行类型转换
当从数据库中取出数据的时候需要使用to_char()来取出需要的部分
二、Oracle自带的函数
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 ssdrop 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 
where qybm=220102794440621
错误出现在sql语句的关键字书写是有顺序的