oracle 游标非空,Oracle数据库学习笔记

一、DDL数据库定义语言

1、表空间

创建表空间：

create tablespace bkjname --表空间名称

datafile 'E:\bkjname.dbf' --文件存放的路径

size 100m --大小

autoextend on --自动扩展

next 10m --每次扩展的大小

表空间是一个逻辑单位。

删除表空间：

droptablespace bkjname;　　　　--只删除逻辑关系，本地的表空间文件需要手动删除

2、用户

创建用户：

create user yh --用户名

identified by yh --密码

default tablespace bjkname; --表空间

给用户授权：

--grant 角色 | 权限 to yh

grant connect to yh; --赋予连接登录的权限

grant dba to yh; --最高权限dba

grant resource to yh; --通常给开发人员授权resource

3、表

创建表

create table表名(

列名 列的类型(列的约束),

列名 列的类型(列的约束)

);列的类型varchar：在Oracle中目前支持，但不保证以后还支持

varchar2(长度)：可变字符长度

char(长度)：固定字符长度

number(总长度,小数长度)：数字类型，注意小数长度要小于总长度

date：年月日时分秒

timestamp：时间戳，比date更加精准

long/clob：存放一本小说

blob：存放电影

给表添加字段

alter table stu add(

phonevarchar2(11),

gendervarchar2(2)

);

修改列类型

alter table stu modify gender varchar2(4)

修改列名

alter table stu rename column phone to telephone;

删除列

alter table stu drop column gender;

修改表名

rename stu to student;

删除表

drop table student;

4、约束

(1)主键约束：primary key，不能为空，必须唯一

(2)非空约束：not null，不能为空

(3)唯一约束：unique

(4)检查约束：check(条件)在MySQL中可以写，但是MySQL直接忽略了检查约束

(5)外键约束：foreign key主要用来约束从表A的数据，必须是存在于主表中

添加外键约束alter table student add foreign key(sno) references people(pid);

首先主表必须存在11号pid，从表才能插入11号学生

删除主表(不推荐)drop table people; --表中记录被关联无法删除

drop table people cascade constraint; --强制删除外键约束和表

级联删除(常用)添加级联约束：alter table student add foreign key(sno) references people(pid) on delete cascade;

删除：delete from people where pid=2

首先在从表中找有没有关联的数据，若有则删除从表中sno=2的数据然后删除主表pid=2的数据

二、DML数据操纵语言

1、增删改

1.插入数据：

insert into 表名 values(所有列的值)insert into 表名(列1,列2) values(值1,值2)使用子查询插入数据

insert into 表名 查询语句

2.更新数据

update 表名 set 列名=值 (where条件)

3.删除数据

delete from 表名 (where条件)delete和truncate 区别delete：DML语言，逐条删除，支持事务操作

truncate：DDL语言，先删除表再创建表(效率高)，不支持事务操作

2、事务事务就是一系列的操作，要么都成功，要么都失败

四大特性：原子性、隔离性、持久性、一致性

如果不考虑隔离级别：脏读、虚读、不可重复读MySQL隔离级别：read uncommitted(读未提交数据), read committed(读已提交数据), repeatable read(可重复读), serializable(串行化)

Oracle隔离级别：read committed, serializable, read only

保存点savepoint当事务报错时可rollback to 事务点

3、视图是对查询结果的一个封装能够封装复杂的查询结果(视图里面的所有数据都来自查询的表，视图本身不存储任何数据)

屏蔽一些东西不给看

语法：

create [or replace] view 视图的名称 as 查询语句 [with read only]通过视图修改数据(一般视图都会加只读权限)

update 视图名称 set 字段=值 where(条件)

4、同义词

create synonym 同义词名称 for 视图名称

5、序列sequence生成类似MySQL中的 auto_incrementID自增长

语法：

createsequence 序列名称

startwith从几开始

incrementby每次增长多少

maxvalue 最大值/nomaxvalue

minvalue 最小值/nominvalue

cycle/nocycle 是否循环

cache 缓存数量3/nocache从序列中取值：currval：当前值

select seq1.currval from dual;nextval：下一个值

select seq1.nextval from dual;

6、索引相当于是一本书的目录

如果某一列经常被用作查询条件，且数据量大的情况下，有必要创建索引

创建索引：

create index 索引名称 on 表名(列)注解约束自带主键索引，唯一约束自带唯一索引

创建复合索引：

create index 索引名称 on 表名(列,列)索引的原理：btree balance tree平衡二叉树

如果某列作为查询条件的时候可以提高查询效率，但是修改时会变慢

7、SQL优化F5查看执行计划

SQL优化：主要是减少CPU调用次数和影响行数。所以SQL优化的方式是增加索引

三、PLSQL语言

1、procedure language 过程语言

基本语法：

declare

--声明变量

变量名 变量类型;

变量名 变量类型:=初始值;

vsal emp.sal%type; --引用型的变量

vrow emo%rowtype; --声明记录型变量

age number := &aaa --弹出输入框(类似scanner)

begin

--业务逻辑

select sal into vsal from emp where empno=7369 --将查询的值赋给vsal

dbms_output.put_line(sal); --输出

dbms_output.put_line(1); --输出1

dbms_output.put_line('*'); --输出*

end;

PL条件语句：

--放在begin下

if thenelsifthen

else

end if;

PLSQL循环：

--for循环

for 变量 in(reverse倒序) 起始值..结束值 loop

...endloop;--while循环

while条件 loop

...endloop;--loop循环

loopexit when条件end loop;

2、游标cursor

是用来操作查询结果集，相当于是JDBC中的ResultSet

普通游标

cursor 游标名 is 查询结果集

开发步骤：声明游标

打开游标： open

从游标中取数据： fetch 游标名 into 变量游标名%found：找到数据

游标名%notfound：没找到数据

关闭游标：close

示例：输出员工表中所有的员工姓名和工资(不带参数游标)

declare

--游标：所有员工

cursor vrows is select * fromemp;--声明变量，记录一行数据

vrow emp%rowtype;begin

--1.打开游标

openvrows;--2.从游标中取数据

loop

fecth vrowsintovrow;exit when vrows%notfound;

dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);endloop;--3.关闭游标

closevrows;end;

指定游标(带参数)

cursor 游标名[(游标名,游标类型)] is 查询结果集示例：输出指定部门下的员工姓名和工资游标：指定部门的所有员工

declare

--声明游标

cursor vrows(dno number) is select * from emp where deptno=dno;--声明变量

vrow emp%rowtype;begin

--1.打开游标，指定10号部门

open vrows(10);--2.循环遍历取数据

loopfetch vrows intovrow;exit when vrows%notfound;

dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);endloop;--3.关闭游标

closevrows;end;

系统引用游标步骤：声明游标：游标名 sys_refcusor

打开游标：open 游标名 for结果集

从 游标中取数据

关闭游标

示例：

declare

--声明系统引用游标

vrows sys_refcusor;--声明一个变量

vrow emp%rowtype;begin

--1.打开游标

open vrows for select * fromemp;--2.取数据

loop

fecth vrowsintovrow;exit when vrows%notfound;

dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);endloop;--3.关闭游标

closevrows;end;扩展：使用for循环遍历游标

declare

--声明一个游标

cursor vrows is select * fromemp;begin

for vrow invrows loop

dbms_output.put_line('姓名'||vrow.ename||'工资'||vrow.sal);endloop;end;for循环遍历游标：不需要声明额外变量：游标里是什么类型，变量就是什么类型

不需要打开游标

不需要关闭游标

3、例外(意外/异常)

1.系统例外

程序运行的过程发生异常

declare

--声明变量

begin

--业务逻辑

exception--异常处理

when 异常1 then...when 异常2 then...when others then...处理其他异常

dbms_output.put_line('发生了其他异常'||sqlerrm);end;异常定义zero_divide：除零异常

value_error：类型转换异常

too_many_rows：查询出多行数据，但是赋值给了rowtype记录一行数据变量

no_data_found：没有找到数据

2.自定义异常示例：查询指定编号的员工，如果没有则抛出自定义异常，有则保存

错误示例：

declare

--1.声明一个变量 %rowtype

vrow emp%rowtype;--2.声明一个自定义的异常

no_emp exception;begin

--查询员工信息，保存

select * into vrow from emp where empno=8888; --这句就抛出no_data_found异常

if vrow.sal is null thenraise no_emp;--抛出自定义异常

end if;

exceptionwhen no_emp thendbms_output.put_line('发生了自定义异常');when others thendbms_output.put_line('发生了其他异常'||sqlerrm);end;

正确示例：(用游标来判断)

declare

--1.声明游标

cursor vrows is select * from emp where empno=8888;--2.声明一个变量 %rowtype

vrow emp%rowtype;--3.声明一个自定义异常

no_emp exception;begin

--a.打开游标

openvrows;--b.取数据

fecth vrows intovrow;--c.判断游标是否有数据

if vrows%notfound thenraise no_emp;end if;--d.关闭游标

closevrows;

exceptionwhen no_emp thendbms_output.put_line('发生了自定义异常');when others thendbms_output.put_line('发生了其他异常'||sqlerrm);end;

4、存储过程实际上是封装在服务器上的一段PLSQL代码片段，已经编译好了的代码

客户调取存储过程，执行效率高

create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型)is|as

--声明部分

begin

--业务逻辑

end;调用：

call 存储过程名称(参数,参数);

5、存储函数与存储过程的区别函数存在的意义是给过程调用：存储过程里面调用存储函数

存储函数有返回值

create [or replace] function 存储函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return参数类型is|as

--声明部分

begin

--业务逻辑

end;

6、触发器trigger当用户执行了Insert、update、delete等操作后，可以触发一系列其他的动作

常在动作执行之前或之后，触发业务处理逻辑(如插入数据前做一些校验)

create [or replace] trigger触发器的名称

before|afterinsert|update|delete

on表名[for each row]

declare...begin...end;

以上为初学者个人总结，欢迎大家指出不足QAQ