一、游标作用(或定义)
1.PL/SQL提供游标机制处理多行记录结果集;
2.游标类似于指针,使应用程序一次可以处理其中的一行记录,比如将游标放入一个for循环中,每循环一次就处理一行记录,那么循环n次就可以处理n行记录;
3.Oracle中,可以分为显式游标和隐式游标两种,比如select*from student就是用隐式游标进行遍历student表,然后将查询结果展示;
4.在平常在进行SELECT查询、DML操作Oracle都会自动创建声明“隐式游标”来处理结果数据;
6.如果需要完成特定功能(SELECT、DML操作),则可以由PL/SQL程序来自定义一个“显式游标”。
二、隐式游标
在执行一个SQL语句时,Oracle服务器将自动创建一个隐式游标。这个游标存储执行SQL语句的结果。
1.游标的主要属性(隐式和显式皆可):
(1)%FOUND:布尔型属性,如果SQL语句至少影响一行操作,则返回TRUE,否则为FALSE;
比如如下程序:
declare
a emp%rowtype;——a变量的数据类型是emp表中的行,便于后面将emp表一行数据存入a中
begin
select* into a from emp where empno = 7935;
if sql%found then——用隐式游标判断上一行的数据是否存在
dbms_output.put_line(‘员工编号为7935的员工存在,其名字为‘ ||a.ename);
end if;
end;
例1:练习游标%found属性
解:注意下图中的程序:
如果在where子句中输入一个不存在的学号’h001’,其余代码同上,会报错,提示数据找不到:
上述报错可用exception解决,注意下图中的程序:
(2)%NOTFOUND:布尔型属性,与%FOUND的功能相反;
(3)%ISOPEN:布尔型属性,当游标已打开时返回TRUE,游标关闭时为FALSE;
(4)%ROWCOUNT:数字型属性,返回受SQL语句影响的行数。
例2:练习游标%rowcount属性。
解:当前student表中数据,注意’s015’行其余数据为null:
现在用游标%rowcount属性更新数据:
然后我们查询当前student表,发现’s015’行数据已经更新:
所以说通过游标的%rowcount属性可以查看update数据之后,原student表中受其影响的行数,其余增删改查同理可用%rowcount查询。
2.隐式游标需要注意的地方
(1)PL/SQL中使用select语句,必须和into关键字一起使用;
(2)PL/SQL中select只返回一行数据,如果超过一行数据,那就要使用显式游标;
(3)对于select into语句,如果执行成功,sql%rowcount的值为1,如果没有成功,sql%rowcount的值为0且产生一个异常NO_DATA_FOUND;
3.【特别注意】
无论用游标哪种属性,记得格式:游标名%属性,比如隐式游标sql%found,再比如显式游标cursor_name%found。
三、显式游标
当查询结果返回多于一行时,必须使用显式游标。使用显式游标
的4个步骤:
Step1:声明显式游标;
Step2:打开显式游标;
Step3:检索显式数据;
Step4:关闭显式游标。
1.声明显式游标的语法格式
cursor 显式游标名 [(parameter[,parameter…])] [return return_type] is查询语句
其中parameter为显式游标的输入参数,它可以让用户在打开显式游标时,向显式游标传递值,parameter格式:
参数名 [in]参数数据类型 [{:= | default} expresson]
2.打开显式游标格式
open 显式游标名 (‘张三’);
3.关闭游标格式
close 显式游标名
注意在使用完游标后切记关闭游标。
4.利用显式游标检索数据,在检索数据时使用fetch语句找出结果集中的单行,并从中提取单个值传递给变量。做法是用一个loop循环,把fetch丢进去,比如:
loop
fetch emp_cursor into a;——利用fetch语句将游标emp_cursor的值传递给变量a
end loop;
例3:练习显式游标
解:注意下图中的程序:
程序分段解析:
declare
cursor v_cur(m in varchar2)——声明显式游标,注意不要指定参数m的长度
is select sno,sname from student where sno=m;——将参数m值给sno,之后select语句据此筛选出符合条件的数据
type t_record is record(——定义记录,便于之后用fetch语句将游标内数据传入变量中
id varchar2(20),
name varchar2(20)
);
t t_record;——定义相对于记录t_record的变量t,
begin
open v_cur(‘s001’);——开启显式游标
loop
fetch v_cur into t;——用fetch语句将游标内数据传入变量中
exit when v_cur%notfound;——定义跳出循环条件,即游标内没
数据时
dbms_output.put_line(t.id || ‘ ‘ || t.name);
end loop;
close v_cur;——关闭显式游标
end;
【总结】
(1)无论是隐式还是显式游标,都有属性:
%found, %notfound, %isopen,%rowcount;
(2)隐式游标只能操作单行数据,多行数据必须用显式游标;
(3)隐式游标名称固定sql,显式游标名称要自定义;
(4)显式游标要通过fetch语句将游标中的值传递给变量,通常把该fetch语句放入loop循环,记得循环中要定义跳出循环条件;
(5)在使用显式游标时,cursor游标名(参数名 in参数数据类型),这种方式定义时,参数数据类型中不要指定长度,否则报错,比如cursor v_cur (m in varchar2(20))是错的,必须不指定长度,即cursor v_cur (m in varchar2)。
=================================================================
Oracle自学总结(博客Oracle前35篇)
【常用命令】
(1)sqlplus sys/ as sysdba;(博客Oracle-03)
用sys用户身份登录数据库(无需输入密码)
(2)select*from all_users;(博客Oracle-04)
查询当前数据库中所有用户的公开信息
(3)create user 用户名 identified by 密码; (博客Oracle-04)
为该数据库创建新用户并指定其登录密码(注意:必须是具有创建用户权限的用户,如sys,才可以使用这条命令)
如:create user lisi identified by lisi
(4)connect 用户名/对应密码;(博客Oracle-04)
将当前登录数据库的用户切换至别的用户
如:当前登录数据库的是sys,那么用connect lisi/lisi切换成lisi用户
(5)grant connect, resource to用户;(博客Oracle-04)
为指定用户授予connect权限和resource权限,前者是让该用户能连接数据库,后者是让该用户能使用数据库资源(注意:必须具有授权权限的用户,如sys,才可以使用该命令)
如:当前登录数据库的是sys,那么用grant connect, resource to lisi为lisi用户授权
(6)drop user lisi;(博客Oracle-04)
删除自己创建的用户(注意:必须具有删除用户权限,如sys,才可以使用该命令)
(7)desc表名(博客Oracle-05)
查询表结构
(8)select * from表名(博客Oracle-05)
查询表内容
(9)commit(博客Oracle-05)
将修改的数据提交保存(不提交保存重启后会丢失)
(10)l或list(博客Oracle-05)
查看缓存中的SQL语句
(11)/或r(博客Oracle-05)
将缓存中的SQL语句重新运行一次
(12)n(博客Oracle-05)
在用list列出缓存中的SQL语句之后,可以使用n命令将光标定位到指定行
(13)del n(博客Oracle-05)
删除第n行SQL语句
(14)a添加内容(博客Oracle-05)
添加SQL语句
如:先list出缓存中的SQL语句,然后a where sno = 's001',则在之后添加where sno = 's001'语句
(15)c /被修改内容/新内容(博客Oracle-05)
修改缓存中的SQL语句
(16)select sysdate from dual(博客Oracle-07)
查询当前系统时间
(17)
create table [用户名.]表名
(列名数据类型 [default默认值] [,...])(博客Oracle-07)
创建表
如:
create table student(
id varchar2(10),
name varchar2(10)
);
(18)create table 表名 [列名,列名...] as 子查询 (博客Oracle-08)
用子查询创建表(复制操作)
如:create table worker as select * from emp;
将emp表中数据复制到worker表中,两张表的列名一样
(19)alter table表名 add
(列名数据类型 [default默认值] [,列名...])(博客Oracle-08)
为已存在的表添加新的一列
如:alter table A add age number(2) default 23;
为表A添加新的一列,该列名age,该列数据类型number(2),该列默认值23
(20)alter table 表名 modify
(列名数据类型 [default默认值] [,列名...])(博客Oracle-08)
修改一个表中已存在的列
(【注意】该命令使用有限制条件:
①被修改的列只能增加宽度不可以减少宽度,比如该列现在宽度是varchar(2),那么可以用该命令将其宽度改为varchar2(10),但是不可以用该命令将其宽度改为varchar2(1)
②如果想修改某列的数据类型,那么该列必须没有存储任何数据,比如number类型修改成varchar2类型,那么要求被修改的列必须为空)
如:alter table A modify age varchar2(2)
将A表中的age列数据类型修改为varchar2(2)
(21)alter table表名 drop column 列名(博客Oracle-08)
从一个表中删除指定列
如:alter table A drop column age
将A表中的age列删除
(22)drop table表名 [cascade constraints] [purge](博客Oracle-09)
删除表,其中cascade constraints是级联约束(删表之前必须先删约束),purge是彻底删除(不走回收站)
(23)flashback table Ato before drop(博客Oracle-09)
如果之前删除A表时候没加purge,那么可用该命令将A表从回收站中恢复到原味
(24)select * from cat(博客Oracle-09)
查看当前登录数据库的用户下所有表
(25)show recyclebin(博客Oracle-09)
查看回收站内容
(26)purge recyclebin(博客Oracle-09)
清空回收站(慎用)
(27)truncate table 表名 (博客Oracle-09)
截断表(清空表中所有数据,但是保留表结构,而drop是直接删除表)
(28)insert into 表名 [(列名,列名...)] values (数值,数值...)(博客Oracle-10)
往表中插入数据
如:insert into A (id,name) values (1003,'王麻子')
(29)update 表名 set 列名=数值 [,列名=数值] [where 条件](博客Oracle-10)
更新表中的某一行或多行数据,如果不加where条件,那么是将该表制定列的所有数据更新成同一个数值。
如:update A set name = ‘张三’, age = ‘23’ where rownum=1
更新A表第一行数据
(30)delete from 表名 [where 条件](博客Oracle-10)
删除表中的数据(记得删除完要commit,否则数据没有被真正删除)
如:delete from A where rownum=1
删除A表第一行数据
(31)drop table A cascade constraint purge;
删除A表所有约束(此法较暴力)
(32)alter table A drop constraint fk_1;
删除A表名为fk_1的约束(删表之前先删约束)
(33)
(34)select * from表名 [where条件] [group by分组列名] [having 聚合函数] [order by 排序列名 asc|desc](博客Oracle-18)
查询指定表的所有列
(35)select {[distinct]列名,列名,…} from 表名 [where条件] [group by分组列名] [having 聚合函数] [order by 排序列名asc|desc](博客Oracle-18)
查询指定表的部分列
(36)
(37)select * from emp a;(博客Oracle-18)
为emp表添加别名a
(38)select distinct 列名 from 表名; (博客Oracle-18)
将查询结果用distinct剔除重复的值
(39)select * from student where sname like ‘%\_%’ escape ‘\’ (博客Oracle-19)
查找student表中姓名带有_的学生,%是通配符,表示零个或多个字符,_也是通配符,表示有且只有一个字符,\是转义,\_表示_
(40)select 列名1 from 表名 order by 列名2 asc;(博客Oracle-20)
升序排序
(41)select 列名1 from 表名 order by 列名2 desc;(博客Oracle-20)
降序排序。
(42)group by(博客Oracle-24)
分组
如:select ssex, count(sage) from student group by ssex;先按照ssex相同的分为一组,然后用count(sage)计算每组中sage的记录数;
(43)select 表1.列名,表2.列名 from 表1,表2 where 表1.列名1 join_operator 表2.列名2;(博客Oracle-25)
两表联查;
如:select a.sno, sname, cname from student a,course b, sc c
where a.sno =c.sno and b.cno = c.cno;三表联查,其中student表和sc表通过sno连接,sc表和course表通过cno连接,a,b,c分别是三张表的别名。
(44)left/right/full outer join(博客Oracle-26)
左/右/全外连接
(45)select 表1的列1,表1的列2 from 表1 union select 表2的列1,表2的列2 from 表2; (博客Oracle-27)
并集运算;
(46)select empno, ename from emp intersect select empno, ename from emp where sal > 1500;(博客Oracle-27)
交集运算;
(47)select empno, ename from emp minus select empno, ename from emp where sal > 1500;(博客Oracle-27)
差集运算;
(48)create [or replace] [force |noforce] view视图名 [(别名[,别名]…)] as子查询[with {check option | read only} [constraint约束名]];(博客Oracle-29)
创建视图;
如:create or replace noforce view v_emp (“empno”,“ename”, “sal”, “hiredate”, “deptno”) as select empno, ename, sal, hiredate,deptno from emp where deptno = 10;创建一个简单的视图
【常用数据类型】 (博客Oracle-06)
(1)CHAR(length):存储固定长度字符串。length参数指定了字符串的长度。如果存储的字符串长度较小,就在字符串末尾以空格补全。
(2)VARCHAR2(length):存储可变长度字符串。length参数指定了字符串的长度。即使存储字符串长度比指定长度小,也不用在字符串末尾以空格补全。
(3)DATE:存储日期和时间类型。
(4)INTEGER:存储整数。整数不包括浮点数。
(5)NUMBER(m,n):存储浮点数,但也可以存储整数。m代表总共位数,n代表小数点右边的最大位数。如果没有指定m,也没有指定n,那么默认存储38位精度的数字。
(6)BINARY_FLOAT:Oracle 10g提供一种新数据类型,用于存储一个单精度的32位浮点数。
(7)BINARY_DOUBLE:Oracle 10g提供的一种新数据类型,用于存储一个双精度64位浮点数。
【约束】
(1)非空(NOT NULL)约束:所定义的列绝不能为空;(博客Oracle-14)
如:alter table A modify id notnull;
为表A的id列添加非空约束
(2)唯一(UNIQUE)约束:在表中每一行中所定义的列,其列值不能相同;(博客Oracle-15)
如:alter table A add constraint uk_a unique (id);
为表A的id列添加唯一约束。
(3)主键(PARIMARY KEY)约束:唯一标识表中的一行;(博客Oracle-11)
如:alter table A add constraint PK_A_ID primary key (id);
为A表的id列添加主键约束(必须确保A表的id列没有重复数据才可用此方法添加主键约束)
(4)外键(FOREIGN KEY)约束:用来维护子表(Child Table)和父表(ParentTable)之间的引用完成行;(博客Oracle-12)
如:constraint 自定义的外键名 foreign key (外键列名) references父表 (列名)
注意:该命令必须放在子表中,所谓子表和父表怎么区分,就是子和父对应关系n:1,即一人只有一个爹,而一个爹可有多个儿。外键允许空。
(5)条件(CHECK)约束:表中每行都要满足该约束条件。(博客Oracle-16)
【函数总结】
一、单行函数:一次只操作一行并且针对每个出书行返回一行。
1.字符函数:主要是操作字符串。常用的字符函数:(博客Oracle-21)
(1) concat(m,n)——将m和n连接起来,并返回连接后的字符串;
(2)initcap(n)——将n中的第一个字母转换成大写;
(3)instr(x,y,m,n)——在x中查找字符串y出现的位置。其中m,n是可选项,m代表从字符串x的第m个位置开始查找,n代表字符串y出现次数;
(4)length(n)——求字符串n的长度;
(5)lower(n)——将字符串n中各个字符转换成小写;
(6)lpad(x,n,y)——在字符串x的左边补齐空格,得到总长度为n个字符的字符串。其中y是可选项,这个参数用于指定左边补齐的字符串;
(7)rpad(x,n,y)——类似于lpad,只是在字符串x的右边补齐;
(8)trim函数:
trim(leading指定字符 from字符串)是去掉字符串左边的指定字符;
trim(trailing指定字符 from字符串)是去掉字符串右边的指定字符;
trim(both指定字符 from字符串)是去掉字符串左右两边的指定字符。
(9)ltrim(x,y)——在x左边截去指定的字符。指定字符可以通过参数y来表示。如果没有写参数y,则默认截去空格。
(10) rtrim(x,y)——类似于ltrim,只是在字符串x的右边截去指定字符。
(11) nvl(x,y)——如果x为null,则结果返回y,否则结果返回x。
(12) nvl2(x,y,z)——如果x不为null,则结果返回y,否则返回z。
(13) nanvl(x,y)——如果x的值是NAN(非数字),则结果返回y;否则结果返回x。
(14) replace(x,y,z)——在x中将y替换为z。
(15) upper(x)——将字符串n中各个字符转换成大写。
(16) substr(x,m,n)——从x中的m位置截取长度为n的子串,如果n省略不写,那么子串就是从x中的m位置一直取到结尾
2.数字函数:主要是操作数字类型的数值(博客Oracle-22)
(1) abs(x)——取x的绝对值;
(2) asin(x)——取x的反正弦;
(3) acos(x)——取x的反余弦;
(4) atin(x)——取x的反正切;
(5) cos(x)——取x的余弦;
(6) sin(x)——取x的正弦;
(7) ceil(x)——取大于或等于x的最小整数(向上取整);
(8) floor(x)——取小于或等于x的最大整数(向下取整);
(9) round(x,y)——对x进行取整,y是可选参数,表示对第几位小数进行取整。如果y为正数,则从x的小数点右边第y位开始取整;如果y为负数,则从x的小数点左边第y位开始取整;
(10) trunk(x,y)——对x进行截断,y是可选参数,表示对第几位小数进行阶段。如果y为正数,则从x的小数点右边第y位开始截断;如果y为负数,则从x的小数点左边第y位开始截断;
(11) mod(x,y)——计算x除以y的余数;
3.转换函数:这些函数将数据从一种数据类型转换为另一种数据类型(博客Oracle-23)
(1)bin_to_num(x)——将x转换为一个二进制数;
(2)chartorowid(x)——将x转换为rowid类型,rowid长度为18位;
(3)rowidtochar(x)——将rowid x转换为一个varchar2类型的字符;
(4)rowidtonchar(x)——将rowid x转换为一个nvarchar2类型的字符;
(5)to_char(x[, format])——将x转换为一个varchar2字符串,format为可选参数,用来指定x的格式;
(6)to_date(x[, format])——将x转换为一个DATE。format为可选参数,用来指定x的格式;
(7)to_number(x[,format])——将x转换为一个number。format为可选参数,用来指定x的格式。
4.日期函数:处理日期和时间(博客Oracle-23)
(1)trunk(x[,y])——对x进行截断;
(2)round(x[,y])——对x进行取整;
(3)next_day(x,day)——返回从x开始,一个day的日期;
(4)months_between(x,y)——返回x和Y之间的差值;
(5)last_day(x)——返回包含该月的最后一天;
(6)add_months(x,y)——返回x加上y个月后的结果。
5.正则表达式函数:这些函数使用正则表达式搜索数据
二、聚合函数:同时对多个行进行操作并返回一行输出结果。(博客Oracle-24)
1.count——返回找到的记录数,count()中如果传入*就是计算所有记录总数,如果传入列名就是计算该列所有非空数据总数;
2.min——返回一个数字列或计算列的最小值;
3.max——返回一个数字列或计算列的最大值;
4.sum——返回一个数字列或计算列的综合,只能用于数字类型数据,如果某列含有字符串数据,则用sum会报错;
5.avg——对一个数字列或计算列求平均值,只能用于数字类型数据,如果某列含有字符串数据,则用avg会报错;
【PL/SQL编程初步】
(1)%type变量——当用户事先不知道检索的数据列的数据类型,可用该变量定义; (博客Oracle-33)
%rowtype变量——一次可以存储一行数据(博客Oracle-33);
(2)
type 变量名 is record(
………………………………
………………………………
………………………………
);
记录类型,存储单行数据,其中省略号部分是需要存储的数据;(博客Oracle-33)
(3)
type 记录表名 is table of 数据类型 [not null] index by binary_integer;
记录表类型,存储多行数据,其中index by binary_integer是该记录表的下标数据类型或称为记录表的长度;(博客Oracle-33)
(4)条件语句 (博客Oracle-34)
if 条件1 then
语句1
elsif 条件2 then
语句2
else
语句3
end if;
(5)case语句 (博客Oracle-34)
case变量
when值1 then语句1;
when值2 then语句2;
when值3 then语句3;
……
when值n then语句n;
[else语句]
end case;
(6)loop循环 (博客Oracle-34)
loop
循环语句
end loop;
(7)while循环 (博客Oracle-34)
while 条件 loop
语句
end loop;
(8)for循环 (博客Oracle-34)
for 变量 in [revers] 变量初值..变量循环结束值 loop
语句
end loop;