Oracle学习小结

秃比秃

已于 2022-02-15 18:56:00 修改

阅读量535

点赞数 1

分类专栏： Oracle 文章标签： oracle 数据库 database

于 2022-02-15 14:14:09 首次发布

本文链接：https://blog.youkuaiyun.com/OfSand/article/details/122943485

版权

Oracle 专栏收录该内容

1 篇文章

订阅专栏

Oracle学习小结

1、易错点

函数可以直接返回具体值

存储过程或者自定义函数中获取某表某字段的数据类型可以使用

例子：
v_stuId username.tablename.field%type;

-- v_stuId 是自定义变量名
-- v_stuId后面句子的解读是，username用户下的tablename表的field字段的类型；
变量也可以保存某一个表的一行数据，定义变量类型时使用，以下表达式。

例子：
v_rowData username.tablename%rowtype;

-- v_rowData 表示保存某行表数据的变量名。
-- v_rowData后面句子的解读是，username用户下的tablename表的行的类型；

-- 保存数据后的调用
v_rowData.field; -- 显示保存行的某个字段数据
select … into 变量名 from … where … ;中select子句处理的数据对象有几个就可以给几个变量对象赋值，期间只需要一个into即可，赋值对象依次对应。

函数可以直接return也可以out

可以在as/is下定义变量时进行赋值

函数不可以无参

形参默认是in，可以省略in不写

字符串拼接的双竖线可以赋值，concat()的返回结果也可以赋值或直接返回。

可以使用concat()的返回结果赋值

if中可以自接使用between等内部函数和保留字

between 的取值范围是包含最大值和最小值

concat()函数的最大形参只有两个，无法使用它来拼接超过两个以上的字符。

赋值方式有两种，select … into … from dual;

和变量名:=值;

return 和 out形参可以同时存在。

无法直接使用%作为取余符号，用到取余的需要使用mod(m,n)计算

2、基本操作

2.1、创建表空间

create tablespace myspace
datafile 'c:\myspace.dbf'
size 500m
autoextend on
next 20m;

-- myspace 是表空间，名称自定义。
-- datafile 是设置的物理存储的文件位置。
-- size 用于设置表空间的初始大小。
-- autoextend 是否在该dbf文件适用完后自动扩展。
-- next 每次自动扩展的大小是多少。

2.2、创建用户

create user C##yunyu
identified by 123456
default tablespace myspace;

-- C##yunyu是用户名，由于未知原因需要使用C##才能执行用户名。
-- identified by后跟着该账户的密码。
-- default tablespace后是该用户默认的表空间。【创建用户必要的指定】
-- 【一个表空间可以创建很多个用户】

2.3、oracle命名规范

变量：v_变量名
常量：c_常量名
过程：pro_过程名
函数：fun_函数名
视图：view_视图名
索引：i_索引名
序列：seq_表名

2.4、定义变量

-- 语法
v_name type [not null][default value];

-- 例子
flag boolean not null default false;

-- 注意boolean类型需要有默认值
-- v_name 自定义变量名
--

2.5、分配权限【分为系统权限和实体权限】

-- 【系统权限】
grant dba[,resouce,connect] [on tablename] to C##yunyu;

-- dba是系统权限的最高权限。
-- resource表示只可以创建实体，不可以创建数据库结构。
-- connect权限表示可以连接但是不能创建实体和数据库结构。

revoke dba[,resouce,connect] [on tablename] to C##yunyu;

-- revoke 后跟着要回收的系统权限。

-- 【实体权限】
grant select[,execute,update,insert,alter,index,delete,all] [on tablename] to C##yunyu;

-- execute执行存储过程的权限
-- all权限包括所有权限

2.6、创建表

create table student(
	id number(5) primary key,
    name varchar(20) not null,
    sex varchar(4) not null,
    age number(3),
    birthday date,
    pet varchar(30)
)

2.7、代码块结构

三大组成：定义结构，执行结构，异常结构

代码解析：

declare
	定义部分
begin
	执行部分
	exception
	异常部分
end;   

-- 像调用存储过程的时候就可以用到代码块进行测试
-- 定义部分可以定义变量
-- 执行部分就是执行具体逻辑
-- 异常部分可有可无，就是用于处理异常的

2.8、oracle中的常用语句

条件语句

if 条件 then
	逻辑实现区
end if;
-- ===================
if 条件 then
	逻辑实现区
else
	逻辑实现区
end if;
-- ===================
if 条件 then
	逻辑实现区
elsif 条件 then
	逻辑实现区
...
else 
	逻辑实现区
end if;
-- ===================
case 变量名
	when 具体值1 then
		逻辑实现区
	when 具体值2 then
		逻辑实现区
	when 具体值3 then
		逻辑实现区
	else
   	逻辑实现区
   end case;
-- ===================

循环语句

loop
循环体;
exit when 结束条件;  
end loop; 
-- ===================
while 条件 loop
	循环体；
end loop;

2.9、oracle的数据类型转换

隐式类型转换

select '2'+3 as result from dual;

-- 输出结果：-->5

显示强制转换

-- 格式化字符串
select to_char(sysdate,'yyyy"年"MM"日"dd"日"') from dual;

-- 输出：2022年02日15日
-- =========================================

-- 字符串/时间搓转日期格式
select to_date('2022年02月15日','yyyy"年"MM"月"dd"日"') from dual;
select to_date('2022-02-15','yyyy"-"MM"-"dd') from dual;
select to_date('2022@02@15','yyyy"@"MM"@"dd') from dual;

-- 以上都输出：2022-02-15 00:00:00【日期格式，可以存储在date中】
-- =========================================

-- 字符串转数字
select to_number('231')+324 as result from dual;
-- 输出结果是：555【可以将字符串类型数据转为number类型数据保存】

2.10、Oracle的常用数据类型

字符型
char【固定长度，最大存2000字节】
varchar2【可变长度，最大存4000字节，一个英文和一个汉字所占大小相同】（oracle推荐使用）
varchar【可变长度，最大存4000字节，一个英文1字节，一个汉字二字节】
long【大文本类型，和mysql的text差不多，最大存2G】
数值型
number(5)【可存最大值，99999】
number(5,2)【可存最大值，999,99】
【第一个值表示数字个数，第二个值表示小数位数】
日期型
date【日期时间型，可保存年-月-日时:分:秒】
timestamp【日期时间型，在date的基础上可保存到秒的后9位】
二进制类型（大数据类型）
clob【存储字符串，最大存储4G】
blob【存储相片，视频，音频等二进制文件，最大存4G】

2.11、序列

解释：序列作用是用于实现Oracle的自增的，它本身是一个计数器，会生成一定的数字。

创建序列

create sequence 序列名
	[increment by 1]  -- 步长，如果省略默认为1，如果是递减操作则设置为负值
	[start with 1]    -- 初始值，从1开始【递增/递减】
	[maxvalue=?/minvalue=?/nomaxvalue] -- 可以由最大值，最小值，和没有最大值。
	[cycle/nocycle]  -- cycle表示循环，循环到最大值后再从初始值开始递变。nocycle就是不循环，但是如果设置最值后，不循环，递变到最大值后会有错误。
	[cache number];  -- CACHE(缓冲)定义存放序列的内存块的大小，默认为20。

修改序列

alter sequence 序列名
　　[incrment by n]  -- 修改步长
　　[maxvalue n/minvalue n/nomaxvalue]  -- 修改最值
　　[cycle/nocycle]  -- 修改是否循环
　　[cache n/nocache];  -- 修改序列缓存
*不能修改序列的初始值

删除序列

drop sequence 序列名;

2.12、同义词

简介：同义词是为了防止sql查询中直接暴露真实数据结构而有的一种伪装机制，通过对表名的同义词建立，能在操作表的时候避免真实表名的公开。

同义词的类型有两种：

一个是公有同义词，可被所有数据库用户访问。

二个是私有同义词，其只能在模式内使用。

==注意：==同义词的建立与删除都需要一定的权利。

创建私有同义词
create or replace synonym syn_name for username.tablename;

-- syn_name是自定义的同义词名称
-- username是用户名称
-- tablename是要被定义同义词的对象
创建公有同义词
create or replace public synonym syn_name for username.tablename;

-- syn_name是自定义的同义词名称
-- username是用户名称
-- tablename是要被定义同义词的对象
同义词的使用
select ... from syn_name where ...;

-- syn_name 是自定义的同义词

2.13、触发器

简介：

语法：

create [or replace] trigger tri_name
before/after insert/delete/update of tablename/[field on tablename]
[for each row]
[dclare]
	-- 可定义变量 
begin
	-- 触发器逻辑实现区
	-- 取新数据<--> :new.field;
	-- 取旧数据<--> :old.field;
	-- 拦截不让执行语句被执行，抛出异常，使用raise_application_error(异常代码,异常信息);
	-- 放行无需操作，不抛异常就可以正常执行之前的语句。
end;


-- 参数介绍：
/*
	tri_name：自定义的触发器名称
	
	before：表示在语句执行之前【语句指你要执行的增删改操作】
	
	after：表示在语句执行之后。
	
	insert/pudate/delete表示任选一个触发场景，当然还可以使用or连接不同场景
	
	of 后面可以跟着要触发的表对象或者表内的某个字段。
	
	无for each row的是语句级触发器：当对表操作一次就触发一次，无论这一次操作了多少条数据。
	
	有for each row的行级触发器：对表操作一次，但是影响多行数据，则每被影响一行都会有与之对应的触发器被触发。
	
	raise_application_error（异常编号，异常信息）：错误编号信息范围在-20001到-20999之间，异常信息自定义。
	
*/

举个小栗子：

-- 创建触发器
create or replace trigger tri_studentinfo
before delete or update on C##yunyu.studentinfo 
for each row
declare
	-- 变量名定义区
		v_ercode number:=to_number((to_char(sysdate,'YYYY')||'5'));
begin
	if :old.sid=1 then
		raise_application_error(-v_ercode,'您操作的是：'||:old.sname||'，您无权操作此用户...');
	end if;
end tri_studentinfo;

-- 当执行 
delete from studentinfo where sid=1;
-- 或者执行
update studentinfo set sname='lyl' where sid=1;
-- 输出结果：> ORA-20225: 您操作的是：云与，您无权操作此用户...

删除触发器

drop trigger tri_name;

-- tri_name删除触发器的名称；

2.4、函数/存储函数

介绍：存储过程才是最应该存在的，但是由于历史原因数据库设计都保留的存储函数，它和存储过程没有多大的区别。

函数的定义语法

create or replace function fun_name(变量名 [in/out/in out] 数据类型,...)
return 数据类型
is
	变量定义区
begin
	逻辑实现区
	return ...;
end;

自定义函数小栗子

-- 自定义函数
create or replace function fun_sayhi(v_num1 in number) 
return varchar2
as
	v_date varchar2(20);
	v_say varchar2(20);
	v_name varchar2(10);
	v_end varchar2(40);
	v_time number;
begin
	-- 获取当前时间（24小时格式）
	v_time:=to_number(to_char(sysdate,'HH24'));
	-- 判断时间范围
	if v_time between 5 and 11 then
		v_say:='中午好';
	elsif v_time between 12 and 13 then
		v_say:='中午好';
	elsif v_time between 14 and 17 then
		v_say:='下午好';
	else 
		v_say:='晚上好';
	end if;
	-- 判断输入值
	if mod(v_num1,2)=0 then 
		v_name:='先生';
	else
		v_name:='女士';
	end if;
	-- 获取当前时间
	select to_char(sysdate,'yyyy-MM-dd HH24:mi') into v_date from dual;
	-- 选择赋值到一个变量后返回，也可以直接返回。
	v_end := v_date||' >> '||v_say||v_name;
	return v_end;
end fun_sayhi;

-- 调用
select fun_sayhi(20) as sddsdsa from dual;
-- 执行结果
-- 2022-02-14 15:15 >> 下午好先生

删除自定义函数

drop function fun_name;

-- fun_name 是你自定义的函数名称

2.15、存储过程

**介绍：**和存储函数相差不大，以下的和存储函数的同异。

相同点:

1，定义方式和调用方式大体相同。

2，结构相同，功能作用相同。

3，存储过程和存储方法都可以通过out参数返回值。

不同点：

1，储存方法需要有返回值，存储过程不需要。

2，储存方法不能无参，存储过程可以无参。

3，存储过程有异常处理，储存方法没有。

语法：

CREATE OR REPLACE PROCEDURE pro_name(参数 [in/out/in out] 参数类型)
as/is
	-- 变量名定义区;
BEGIN
	-- 业务逻辑实现区;
	EXCEPTION    -- 存储过程异常
END 

-- pro_name 是自定义的存储过程名称
-- [in/out/in out]表示可选是否写，不写默认是in
-- as/is 在这里没有区别，任选

举个小栗子【无参存储过程】

-- 创建存储过程【无参】
create or replace procedure pro_sayhi
as
	v_name varchar2(30):='yunyu';
	v_date varchar2(40):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
begin
	dbms_output.put_line((v_date||' >> '||v_name));
end;

调用存储过程【无参】

-- 调用无参存储过程方法一
call pro_sayhi();

-- 调用存储过程方法二
begin
	pro_sayhi();
end;

-- 输出结果：2022-02-14 21:06:30 >> yunyu

删除存储过程【无参】

drop procedure pro_sayhi;

-- pro_sayhi表示存储过程的自定义名称。

创建存储过程【有参】

--创建有参存储过程
create or replace procedure pro_sayhello
(v_name in varchar2,v_redata out varchar2)
is
	v_date varchar2(30):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
begin
	v_redata:='hello world ->'||v_date||' ->'||v_name;
end;

调用存储过程【有参】

-- 写代码块进行调用
declare
	-- 该区域可以定义变量
	data varchar2(50);
begin
	-- 该区域调用函数
	pro_sayhello('23',data);
	-- 输出返回结果
	dbms_output.put_line(data);
end;

删除存储过程【有参】

drop procedure pro_sayhello;


-- 和无参存储过程一样的删除

2.16、游标操作

简介：

游标是什么？
用来存储多条查询数据的一种数据结构（‘结果集’），
它有一个 ‘指针’，从上往下移动（‘fetch’），从而能够 ‘遍历每条记录’
优缺点
(1) 提高 sql ‘执行效率’
(2) 牺牲 ‘内存’

出自：https://blog.youkuaiyun.com/qq_34745941/article/details/81294166（讲的很好，推荐）

语法

declare
	-- 定义变量名称，用于存储查出的数据，便于后期调用遍历
	
	-- 声明游标
	cursor cur_name( 参数列表 ) 
	is
	select ... from tablename where ...;
begin
	-- 打开游标
 	open cur_name(传参);
 	-- 提取数据
 	fetch cur_name into 变量名;
 	-- 关闭游标
 	close cur_name;
end;

测试

declare
-- 定义存储数据的变量
 v_stu_info C##yunyu.studentinfo%rowtype;
 -- 步骤1: 声明游标【带参】
 cursor cur_stu_info(v_id C##yunyu.studentinfo.sid%type) is
   select * from C##yunyu.studentinfo t where t.sid = v_id;
begin
 -- 步骤2: 打开游标
 open cur_stu_info(1);
 -- 步骤3: 提取数据
 fetch cur_stu_info
   into v_stu_info;
 dbms_output.put_line(v_stu_info.sid || ' : ' || v_stu_info.sname);
 -- 步骤4: 关闭游标
 close cur_stu_info;
end;

2.17、包

简介

包（Package）用于组合逻辑相关的PL/SQL类型、PL/SQL项和PL/SQL子程序
通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏、子程序重载等功能
包由包规范和包体两部分组成
当创建包时，需要首先创建包规范，然后再创建包体

注意：

包规范下你跟对接口
包体相当于接口的实现

创建包规范

包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数
在包规范中所定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用
创建包规范时需要注意的是：为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，而应该只定义公用组件

语法：

create or replace package dbutil_package is
pi constant number(10,7):=3.1415926; -- 定义常量
function getarea(radius number) return  number; -- 定义函数及返回值
procedure print_area;
end dbutil_package;

创建包体

为了实现包规范中所定义的公用过程和函数，必须创建包体
包体用于实现包规范所定义的过程和函数
在创建包时，为了实现信息隐藏，应该在包体内定义私有组件

语法：

CREATE [OR REPLACE] PACKAGE  BODY package_name
IS | AS
private type and item declarations
subprogram bodies
END package_name;

使用包体

create or replace package  body dbutil_package as
    area number(10);
    function getarea(radius number) return number is
    begin
          area:=pi*radius*radius;
          return area;
    end;
    procedure print_area is
    begin
          dbms_output.put_line('圆的面积是：'||area);
    end;
end dbutil_package;

调用包的组件

对于包的私有组件，只能在包内调用，并且可以直接调用
对于包的公用组件，既可以在包内调用，又可以在其他应用中调用
在调用同一包内其他组件，可直接调用，不需要加包名作为前缀

调用包的公用变量、过程、函数

当在其他应用中调用包的公用变量时，必须在公用变量、过程、函数名前添加包名作为前缀

调用包

declare
    area number(10,7);
begin
      area:=dbutil_package.getarea(3);
    dbms_output.put_line('由function返回的面积：'||area);
      dbutil_package.print_area;
end;

出自：https://blog.youkuaiyun.com/weixin_50799927/article/details/122941564