oracle简单入门

oracle概念

oracle是一个关系型数据库，它有一个大的数据库，大的数据库下由一个或多个实例组成，每个实例由后台进程和逻辑数据组成，一个实例下可以创建多个表空间和多个用户，每个用户都有一个默认表空间，每个表空间都可以成为某个用户的默认表空间，同一个表空间下用户的数据不会互相干扰，他们的数据存在隔离。表空间由逻辑数据结构和物理文件组成，物理文件负责存储数据，逻辑数据结构负责操作数据。要删除物理文件需要先删除表空间。逻辑数据内含有表结构。表的具体定位是：用户名.表名。在oracle中表是隶属于用户之下的。

sql的简单介绍

sql的简单介绍：

sql的全称是结构化查询语言（Structured Query Language），于1970年最先由IBM公司提出，最先由oracle数据库使用。sql常用于存取数据以及查询、更新和管理关系数据库系统。

结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作，无需了解底层数据库的组成结构和逻辑。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

SQL的特点：

1. 数据描述、操纵、控制等功能一体化。

2. 两种使用方式，统一的语法结构。

3. 高度非过程化。

4. 语言简洁，易学易用。

sql的主要组成：

SQL从功能上可以分为3部分：数据定义、数据操纵和数据控制。

• DML【数据库操作语言】
• 其语句包括：CRUD，增加（create），读取（read），更改（update），删除（delete）【也称为动作查询语言】
• DDL【数据库定义语言】
• 是对表的定义，数据表，视图，索引，用户等。
• DCL【数据库控制语言】
• 是管理用户权限的，其在oracle的语句包括：grant，revoke。

oracle的连接

**driver：**oracle.jdbc.driver.oracledriver

**url：**jdbc:oracle:thin:@localhost:1521:oracl

**username：**C##yunyu

**password：**123456

oracle创建表的顺序

1. 有表空间。
2. 有用户。
3. 使用指定用户创建表，并指定表的默认表空间。
4. 表内的数据操作。

oracle学习中的易错点

1. 函数可以直接返回具体值

---

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

例子：

v_stuId username.tablename.field%type;

-- v_stuId 是自定义变量名
-- v_stuId后面句子的解读是，username用户下的tablename表的field字段的类型；

---

3. 变量也可以保存某一个表的一行数据，定义变量类型时使用，以下表达式。

例子：

v_rowData username.tablename%rowtype;

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

-- 保存数据后的调用
v_rowData.field; -- 显示保存行的某个字段数据

---

4. select … into 变量名 from … where … ;中select子句处理的数据对象有几个就可以给几个变量对象赋值，期间只需要一个into即可，赋值对象依次对应。

---

5. 可以直接返回具体值

---

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

---

7. 存储函数不可以无参

---

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

---

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

---

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

---

11. 可以直接返回函数的值

---

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

---

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

---

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

---

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

和 变量名:=值;

---

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

---

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

具体实现步骤

1. 创建表空间

   create tablespace myspace
   datafile 'c:\myspace.dbf'
   size 500m
   autoextend on
   next 20m;
   
   -- myspace 是表空间，名称自定义。
   -- datafile 是设置的物理存储的文件位置。
   -- size 用于设置表空间的初始大小。
   -- autoextend 是否在该dbf文件适用完后自动扩展。
   -- next 每次自动扩展的大小是多少。
   

2. 创建用户

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

3. oracle命名规范

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

4. 定义变量

   -- 语法
   v_name type [not null][default value];
   
   -- 例子
   flag boolean not null default false;
   
   -- 注意boolean类型需要有默认值
   -- v_name 自定义变量名
   -- 
   

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权限包括所有权限
   

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)
   )
   insert into studentinfo values(1,'云与','男',20,sysdate,1);
   insert into studentinfo values(2,'小暴','女',18,to_date('2021-8-12 15:52:00','YYYY-MM-DD HH24:MI:SS'),1);
   

insert into studentinfo values(3,‘小续’,‘女’,17,to_date(‘2018-7-28 10:30:00’,‘YYYY-MM-DD HH24:MI:SS’),1);
– [以上数据由脚本生成，实际由100+条]

4. #### 代码块结构

>三大组成：定义结构，执行结构， 异常结构
>
>**代码解析**：
>
>```sql
>declare
>	定义部分
>begin
>	执行部分
>	exception
>	异常部分
>end;   
>
>-- 像调用存储过程的时候就可以用到代码块进行测试
>-- 定义部分可以定义变量
>-- 执行部分就是执行具体逻辑
>-- 异常部分可有可无，就是用于处理异常的
>```

4. #### oracle中的常用语句

>**条件语句**
>
>```sql
>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;
>-- ===================
>```
>
>
>
>**循环语句**
>
>```sql
>loop
> 循环体;
>exit when 结束条件;  
>end loop; 
>-- ===================
>while 条件 loop
>	循环体；
>end loop; 
>```
>
>

4. #### oracle的数据类型转换

>**隐式类型转换**
>
>```sql
>select '2'+3 as result from dual;
>
>-- 输出结果：-->5
>```
>
>**显示强制转换**
>
>```sql
>-- 格式化字符串
>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类型数据保存】
>
>```
>
>

4. #### 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】

4. #### 序列

>解释：序列作用是用于实现Oracle的自增的，它本身是一个计数器，会生成一定的数字。
>
>**创建序列**
>
>```sql
>create sequence 序列名
>	[increment by 1]  -- 步长，如果省略默认为1，如果是递减操作则设置为负值
>	[start with 1]    -- 初始值，从1开始【递增/递减】
>	[maxvalue=?/minvalue=?/nomaxvalue] -- 可以由最大值，最小值，和没有最大值。
>	[cycle/nocycle]  -- cycle表示循环，循环到最大值后再从初始值开始递变。nocycle就是不循环，但是如果设置最值后，不循环，递变到最大值后会有错误。
>	[cache number];  -- CACHE(缓冲)定义存放序列的内存块的大小，默认为20。
>```
>
>**修改序列**
>
>```sql
>alter sequence 序列名
>　　[incrment by n]  -- 修改步长
>　　[maxvalue n/minvalue n/nomaxvalue]  -- 修改最值
>　　[cycle/nocycle]  -- 修改是否循环
>　　[cache n/nocache];  -- 修改序列缓存
>*不能修改序列的初始值
>```
>
>**删除序列**
>
>```sql
>drop sequence 序列名;
>```

7. #### 同义词

>**简介**：同义词是为了防止sql查询中直接暴露真实数据结构而有的一种伪装机制，通过对表名的同义词建立，能在操作表的时候避免真实表名的公开。
>
>同义词的类型有两种：
>
>​	一个是公有同义词，可被所有数据库用户访问。
>
>​	二个是私有同义词，其只能在模式内使用。
>
>
>
>==**注意**：==同义词的建立与删除都需要一定的权利。
>
>---
>
>**创建私有同义词**
>
>```sql
>create or replace synonym syn_name for username.tablename;
>
>-- syn_name是自定义的同义词名称
>-- username是用户名称
>-- tablename是要被定义同义词的对象
>```
>
>**创建公有同义词**
>
>```sql
>create or replace public synonym syn_name for username.tablename;
>
>-- syn_name是自定义的同义词名称
>-- username是用户名称
>-- tablename是要被定义同义词的对象
>```
>
>**同义词的使用**
>
>```sql
>select ... from syn_name where ...;
>
>-- syn_name 是自定义的同义词
>```

8. #### 触发器

>**简介**：
>
>---
>
>**语法**：
>
>```sql
>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之间，异常信息自定义。
>	
>*/
>
>
>```
>
>**举个小栗子**：
>
>```sql
>-- 创建触发器
>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: 您操作的是：云与，您无权操作此用户...
>```
>
>**删除触发器**
>
>```sql
>drop trigger tri_name;
>
>-- tri_name删除触发器的名称；
>```

8. #### 函数/存储函数

>**介绍**：存储过程才是最应该存在的，但是由于历史原因数据库设计都保留的存储函数，它和存储过程没有多大的区别。
>
>----
>
>**函数的定义语法**
>
>```sql
>create or replace function fun_name(变量名 [in/out/in out] 数据类型,...)
>return 数据类型
>is
>	变量定义区
>begin
>	逻辑实现区
>	return ...;
>end;
>```
>
>**自定义函数小栗子**
>
>```sql
>-- 自定义函数
>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 >> 下午好先生
>```
>
>**删除自定义函数**
>
>```sql
>drop function fun_name;
>
>-- fun_name 是你自定义的函数名称
>```

9. #### 存储过程

>**介绍：**和存储函数相差不大，以下的和存储函数的同异。
>
>​	相同点:
>
>​		1，定义方式和调用方式大体相同。
>
>​		2，结构相同，功能作用相同。
>
>​		3，存储过程和存储方法都可以通过out参数返回值。
>
>​	不同点：
>
>​		1，储存方法需要有返回值，存储过程不需要。
>
>​		2，储存方法不能无参，存储过程可以无参。
>
>​		3，存储过程有异常处理，储存方法没有。
>
>---
>
>**语法：**
>
>```sql
>CREATE OR REPLACE PROCEDURE pro_name(参数 [in/out/in out] 参数类型)
>as/is
>	-- 变量名定义区;
>BEGIN
>	-- 业务逻辑实现区;
>	EXCEPTION    -- 存储过程异常
>END 
>
>-- pro_name 是自定义的存储过程名称
>-- [in/out/in out]表示可选是否写，不写默认是in
>-- as/is 在这里没有区别，任选
>```
>
>**举个小栗子**【无参存储过程】
>
>```sql
>-- 创建存储过程【无参】
>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;
>```
>
>**调用存储过程**【无参】
>
>```sql
>-- 调用无参存储过程方法一
>call pro_sayhi();
>
>-- 调用存储过程方法二
>begin
>	pro_sayhi();
>end;
>
>-- 输出结果：2022-02-14 21:06:30 >> yunyu
>```
>
>**删除存储过程**【无参】
>
>```sql
>drop procedure pro_sayhi;
>
>-- pro_sayhi表示存储过程的自定义名称。
>```
>
>
>
>---
>
>
>
>**创建存储过程**【有参】
>
>```sql
>--创建有参存储过程
>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;
>```
>
>**调用存储过程**【有参】
>
>```sql
>-- 写代码块进行调用
>declare
>	-- 该区域可以定义变量
>	data varchar2(50);
>begin
>	-- 该区域调用函数
>	pro_sayhello('23',data);
>	-- 输出返回结果
>	dbms_output.put_line(data);
>end;
>```
>
>**删除存储过程**【有参】
>
>```sql
>drop procedure pro_sayhello;
>
>
>-- 和无参存储过程一样的删除
>```
>
>

16. #### 游标操作

 >**简介**：
 >
 >>1. 游标是什么？
 >>   用来存储多条查询数据的一种数据结构（'结果集'），
 >>   它有一个 '指针'，从上往下移动（'fetch'），从而能够 '遍历每条记录'
 >>2. 优缺点
 >>   (1) 提高 sql '执行效率'
 >>   (2) 牺牲 '内存'
 >>
 >>出自：https://blog.csdn.net/qq_34745941/article/details/81294166（讲的很好，推荐）
 >
 >**语法**
 >
 >```sql
 >declare
 >	-- 定义变量名称，用于存储查出的数据，便于后期调用遍历
 >	
 >	-- 声明游标
 >	cursor cur_name( 参数列表 ) 
 >	is
 >	select ... from tablename where ...;
 >begin
 >	-- 打开游标
 >  	open cur_name(传参);
 >  	-- 提取数据
 >  	fetch cur_name into 变量名;
 >  	-- 关闭游标
 >  	close cur_name;
 >end;
 >```
 >
 >**小栗子**
 >
 >```sql
 >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;
 >```
 >
 >