Oracle-35-隐式游标&显式游标&【Oracle自学总结】

一、游标作用（或定义）

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;