Oracle---基础知识篇

一、基本数据类型

查看自己的oracle支持的数据类型命令

select distinct data_type from user_tab_cols;

1、字符型

Char：定长最大2000字符。如char(10),当填入’abc’不足10个字符时，自动用空格填充剩余部分。较浪费空间，但查询速度快。

Varchar2：可变长最大4000字符。如varchar(20)，当填入’abc’时，占用空间3个字符，剩余的部分不占用。节省空间，但查询会比较慢。

Clob(character large objiect)：用于存储字符型大对象(如一篇文章)，最大4G。

NCHAR：即国家字符集，使用方法和CHAR相同。如果开发的项目需要国际化，那么数据类型选择NCHAR数据类型。NCHAR数据类型和CHAR数据类型最大的区别在于NCHAR用来存储Unicode字符集类型，即双字节数据。

2、 数字型

Number 范围-10ˆ38 -10ˆ38。可以是表示整数，也可以表示小数

Number(5,2)表示一个小数有5位有效数字，2为小数

Number(5)表示一个5位整数

Long表示长整型

Float表示浮点型

NCLOB 数据类型用于存储大的NCHAR字符数据。NCLOB数据类型同时支持固定宽度字符和可变宽度字符（Unicode字符数据）。大字符对象的大小不大于4GB。

3、日起类型

Date ：包含年月日时分秒

Timestamp：是对date的扩展，精确到小数秒(毫秒级),用的较少

4、文件存储类型

Blob：二进制数据 可用于存储图片、声音、视频等 最大4G。

注：一般文件很少会往数据库里放，一般会放到文件夹内，数据库里放文件路径。但对于一些安全性要求非常的文件往往会用到。

二、相关命令

创建用户

--创建用户
create user shall IDENTIFIED by 123456;

--赋予权限给指定用户
GRANT CONNECT,RESOURCE,dba to shall;

创建命名空间

--创建表空间
CREATE tablespace ts1 datafile '/home/oracle/tablespace/ts1.dbf'size 50M;

--修改默认表空间
ALTER database DEFAULT tablespace ts1;

创建表

--创建表
create table tb_stu(
id number,
name VARCHAR(32)
primary key (id)
);

三、自增长

由于Oracle不像MySQL一样拥有auto increment 可以实现某一列自增长，在Oracle中实现起来就比较麻烦一点，下面就来讲解一下，Oracle如何实现自增长

1、创建表

create table tb_stu(id number,name VARCHAR(32));

2、创建一个序列

【其实下面很多参数可以不用写，只是为了展示序列的所有参数】

--创建序列完整版
create sequence kt minvalue 1 nomaxvalue INCREMENT by 1 start with 1 nocache;

下面是序列的引伸知识，这里千万别执行，因为执行了第一个，最后你会发现结果并不是从 1 开始，执行第二个会报错，只有先执行第一个，再执行第二个才会正常显示

select user_seq2.nextval from dual;

select user_seq2.currval from dual;

3、创建触发器

--创建触发器
create or replace trigger tb_stu_trigger_insertId
before insert on tb_stu for each row
begin
select kt.nextval into:new.id from dual;
end;

4、插入数据

insert into tb_stu (name) values ('Jerry');

可以自己select 一下，检验一下结果

四、触发器

和MySQL几乎一样

1、创建表
【书籍表的typeId 参考 书籍类型表的id】

--创建书籍表
create table t_book(
id NUMBER,
bookName varchar(32),
typeId number,
primary key(id)
);

--创建书籍类型表 t_book_type
create table t_book_type(
id NUMBER,
typeName varchar(32),
primary key (id)
);

--创建历史表
create table t_book_oplog(
actionuser VARCHAR2(32),
actionname VARCHAR2(32),
actiontime DATE
);

2、创建触发器
【对 t_book 表进行update、insert、delete操作时，会将数据写入t_book_oplog中】

create or replace TRIGGER tr_t_book_oplog
after insert or update or delete on t_book
begin
	if updating then
		insert into t_book_oplog values(user,'update',SYSDATE);
	else if inserting then
		insert into t_book_oplog values(user,'insert',SYSDATE);
	else if deleting then
		insert into t_book_oplog values(user,'delete',SYSDATE);
	end if;
	end if;
	end if;
end;

五、自定义函数

1、统计表中数据行数

--创建函数--统计行数
create or replace FUNCTION GETOPCOUNT return number as
begin
	declare op_count number;
	begin
		select count(1) into op_count from t_book_oplog;
		return op_count;
	end;
end GETOPCOUNT;

--调用自定义函数
select getopcount() from dual;

2、第一种执行sql

--创建带参数函数
create or replace FUNCTION getopcountBy(opName VARCHAR2) return number as
begin
	declare op_count number;
	begin
	--第一种字符串拼接：=+ ,不用考虑字段加 '' 
		select count(1) into op_count from t_book_oplog where actionName =+ opName;
		return op_count;
	end;
end getopcountBy;

select * from t_book_oplog;

select getopcountBy('insert') from dual;
select getopcountBy('update') from dual;

3、第二种执行sql

--自定义函数，里面执行sql
create or replace FUNCTION getopcountBy2(opName VARCHAR2) return number as
begin
	declare op_count number;
	query_sql varchar2(300);
	begin
	--第二种字符串拼接 a || b , 想要加 ' 请使用 ''''
		query_sql:='select count(1) from t_book_oplog where actionName ='||''''|| opName||'''';
		execute immediate query_sql into op_count;
		return op_count;
	end;
end getopcountBy2;

select getopcountBy2('insert') from dual;