oracle 二进制字符串转十六进制 存储过程,oracle基础知识点小结

当前位置:我的异常网» 数据库 » oracle基础知识点小结

oracle基础知识点小结

www.myexceptions.net  网友分享于：2013-08-25  浏览：9次

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 

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语句的关键字书写是有顺序的

文章评论