oracle简单入门
oracle概念
oracle是一个关系型数据库,它有一个大的数据库,大的数据库下由一个或多个实例组成,每个实例由后台进程和逻辑数据组成,一个实例下可以创建多个表空间和多个用户,每个用户都有一个默认表空间,每个表空间都可以成为某个用户的默认表空间,同一个表空间下用户的数据不会互相干扰,他们的数据存在隔离。表空间由逻辑数据结构和物理文件组成,物理文件负责存储数据,逻辑数据结构负责操作数据。要删除物理文件需要先删除表空间。逻辑数据内含有表结构。表的具体定位是:用户名.表名。在oracle中表是隶属于用户之下的。
sql的简单介绍
sql的简单介绍:
sql的全称是结构化查询语言(Structured Query Language),于1970年最先由IBM公司提出,最先由oracle数据库使用。sql常用于存取数据以及查询、更新和管理关系数据库系统。
结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作,无需了解底层数据库的组成结构和逻辑。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。
SQL的特点:
数据描述、操纵、控制等功能一体化。
两种使用方式,统一的语法结构。
高度非过程化。
语言简洁,易学易用。
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创建表的顺序
- 有表空间。
- 有用户。
- 使用指定用户创建表,并指定表的默认表空间。
- 表内的数据操作。
oracle学习中的易错点
- 函数可以直接返回具体值
- 存储过程或者自定义函数中获取某表某字段的数据类型可以使用
例子:
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即可,赋值对象依次对应。
- 可以直接返回具体值
- 可以在as/is下定义变量时进行赋值
- 存储函数不可以无参
- 形参默认是in,可以省略in不写
- 字符串拼接的双竖线可以赋值,concat()的返回结果也可以赋值或直接返回。
- 可以使用concat()的返回结果赋值
- 可以直接返回函数的值
- if中可以自接使用between等内部函数和保留字
- between 的取值范围是包含最大值和最小值
- concat()函数的最大形参只有两个,无法使用它来拼接超过两个以上的字符。
- 赋值方式有两种,select … into … from dual;
和 变量名:=值;
- return 和 out形参 可以同时存在。
- 无法直接使用%作为取余符号,用到取余的需要使用mod(m,n)计算
具体实现步骤
-
创建表空间
create tablespace myspace datafile 'c:\myspace.dbf' size 500m autoextend on next 20m; -- myspace 是表空间,名称自定义。 -- datafile 是设置的物理存储的文件位置。 -- size 用于设置表空间的初始大小。 -- autoextend 是否在该dbf文件适用完后自动扩展。 -- next 每次自动扩展的大小是多少。
-
创建用户
create user C##yunyu identified by 123456 default tablespace myspace; -- C##yunyu是用户名,由于未知原因需要使用C##才能执行用户名。 -- identified by后跟着该账户的密码。 -- default tablespace后是该用户默认的表空间。【创建用户必要的指定】 -- 【一个表空间可以创建很多个用户】
-
oracle命名规范
变量:v_变量名
常量:c_常量名
过程:pro_过程名
函数:fun_函数名
视图:view_视图名
索引:i_索引名
序列:seq_表名 -
定义变量
-- 语法 v_name type [not null][default value]; -- 例子 flag boolean not null default false; -- 注意boolean类型需要有默认值 -- v_name 自定义变量名 --
-
分配权限【分为系统权限和实体权限】
-- 【系统权限】 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权限包括所有权限
-
创建表
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;
>```
>
>