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) ) -
代码块结构
三大组成：定义结构，执行结构， 异常结构
代码解析：
declare 定义部分 begin 执行部分 exception 异常部分 end; -- 像调用存储过程的时候就可以用到代码块进行测试 -- 定义部分可以定义变量 -- 执行部分就是执行具体逻辑 -- 异常部分可有可无，就是用于处理异常的 -
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; -
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类型数据保存】 -
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】
-
序列
解释：序列作用是用于实现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 序列名; -
同义词
简介：同义词是为了防止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 是自定义的同义词 -
触发器
简介：
语法：
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删除触发器的名称； -
函数/存储函数
介绍：存储过程才是最应该存在的，但是由于历史原因数据库设计都保留的存储函数，它和存储过程没有多大的区别。
函数的定义语法
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 是你自定义的函数名称 -
存储过程
**介绍：**和存储函数相差不大，以下的和存储函数的同异。
相同点:
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; -- 和无参存储过程一样的删除 -
游标操作
简介：
- 游标是什么？
用来存储多条查询数据的一种数据结构（‘结果集’），
它有一个 ‘指针’，从上往下移动（‘fetch’），从而能够 ‘遍历每条记录’ - 优缺点
(1) 提高 sql ‘执行效率’
(2) 牺牲 ‘内存’
出自：https://blog.csdn.net/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; - 游标是什么？
1354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
