oracle笔记

1.数据类型

1.1字符类型

char 固定长度的字符类型，最多存储2000个字节。

varchar2 可变长度的字符类型，最多存储4000个字节。

long 大文本类型，最大可存储2G。

1.2数值型

number：数值类型

number(5) 最大可以存的数字为99999

number(5,2) 最大可以存存的数字为999.99

1.3日期型

date:日期时间型，精确到秒

timestamp:精确到秒的小数点后9位。

1.4二进制型（大数据类型）

clob：存储字符，最大可存4G。

blob:存储图像，声音，视频等二进制数据，最多可以存4G。

2 修改表结构

2.1追加

alter table table_name add(
    id varchar2(20),//oracle varchar 是varchar2
    outdate date
)

2.2修改

alte table table_name modify(列名 类型 [default 默认值], ......)

2.3修改列名

alter table 表名字 rename column 原来的列名 to 新列名

2.4删除列

alter table 表名称 drop column 列名1 ,列名2

2.5 删除表

drop table 表名称

2 单行函数

2.1字符函数

2.1.1字符串长度

select length('ABCD') from dual //dual是伪表，一行一列，只有x

2.1.2字符串子串

select substr('abcd',2,2) from dual //bc

2.1.3字符串拼接

select concat('abc','d') from dual  //

select 'abc'||'d'||'ef' from dual

2.2数值函数

2.2.1四舍五入函数

select round(100.567,2) from dual //100.57

2.2.2数字截取

select trunc(100.567,2) from dual --100.56

2.2.3取模

select mod(10,2) from dual --1

2.3日期函数

2.3.1加月函数

select add_months(sysdate,2) from dual --当前时间往后数2个月

2.3.2求所在月的最后一天

select last_day(sysdate) from dual

2.3.3日期截取

select trunc(sysdate) from dual  --按日截取，把时间截掉
select trunc(sysdate,'mm')from dual --按月截取，把日截掉
select trunc(sysdate,'yyyy') from dual  --按年截取，把月截掉 当年1月1日
select trunc(sysdate,'hh') from dual  --按小时截取
select trunc(sysdate,'mi') from dual  --按分钟截取，

2.4转换函数

2.4.1数字转字符串

select to_char(100) from dual  

2.4.2日期转字符串

select to_char(sysdte,'yyyy-mm-dd hh:mi:ss') from dual  

2.4.3字符串转日期

select to_date('2022-10-03','yyyy-mm-dd') from dual  

2.4.4字符串转数字

select to_number('100')+10 from dual  

2.5其他寒素

2.5.1空值函数

select nvl(null,0) form dual  --0
select nvl(100,0) form dual  --100

select nvl2(参数,不为null,为null) form dual  

2.5.2条件判断

select decode(检测的东西,key1,value1,key2,value2,key3,......,最后的缺省值) form dual  --oracle的

select name,(case ownertypeid
             when 1 then '居民'
             when 2 then '行政事业单位'
             else '其他'
             end
            )from t_owners    --sql1999
            
select name,(case 
             when ownertypeid=1 then '居民'
             when ownertypeid=2 then '行政事业单位'
             else '其他'
             end
            )from t_owners    --sql1999 

2.5.3分析函数

select * from account order by usenum desc 
--值相同，排名相同，序号跳跃
select rank() over (order by usenum desc),t.* from account t
--值相同，排名相同，序号连续
select dense_rank() over (order by usenum desc),t.* from account t
--序号连续,不管值是否相同
select row_number() over (order by usenum desc),t.* from account t

3集合运算

3.1并集

select * from owners where id>5
union all --去掉all自动去重
select * from owners where id<8

3.2交集

select * from owners where id>5
intersect
select * from owners where id<8

3.3差集

select * from owners where id>5
minus
select * from owners where id<8

4视图

虚拟的表，封装sql语句

create [or replace] [force] view view_name
as subquery
[with check option]
[with read only]
--删除视图
drop view view_name

5序列

序列是oracle提供的用于产生一系列唯一数字的数据库对象。

6同义词

create [public] synonym 同义词名称 for object --object表示表，视图，序列等

7索引

8 PL/SQL

declare

begin

exception

end;
--声明变量
--变量名 类型（长度）
--变量名：=变量值
--一个=号是判断相等
--输出语句
dbms_output.put_line('金额'||v_money)

属性类型（引用型 表明.列名%type）

属性类型（记录型 表名%rowtype）

select into 变量名 from --要求必须得出一行记录

exception
	when 异常类型 then
		异常处理逻辑
		

if 条件 then
elseif  then
else
end if;

循环

无条件循环

while
loop 
	exit when ...
end loop

for i in 1...10
loop
end loop;

9游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。

cursor 游标名称 is SQL语句
 
 open 游标名称
 loop 
 	fetch 游标名称 into 变量
 	exit when 游标名称%notfound 
 end loop
close 游标名称

declare
	cursor 游标名称(变量名称) is select * from 表名 where 列名=变量名称
    begin
    	for i in 游标名称   --隐式打开关闭游标
    		loop
    			dbms_output.put_line('价格'||i.列);
    		end loop
    end;

10存储函数

create [or replace] function 函数名称（参数名称 参数类型，参数名称 参数类型,...)
return 结果变量数据类型
is
	变量声明部分;
begin
	逻辑部分;
	return 结果变量;
exception
	异常处理部分
end;

11存储过程

create [or replace] procedure 存储过程名称（参数名 参数类型，参数名 参数类型。。。）
is|as
	变量声明部分;
begin
	逻辑部分;
exception
end;

12触发器