Day10-变量定义和循环控制

Day10-pl/sql变量和控制语句

pl/sql基础知识--定义并使用变量

介绍

在编写pl/sql程序时，可以定义变量和常量；

在pl/sql程序中包括有：

1、标量类型(scalar)(重点掌握)

2、复合类型(composite)（了解，不讲，看了不懂可以问，类似数组）

3、参照类型(reference)（重点掌握游标）

4、lob(large object)（存放大数据类型）

标量(scalar)--常用类型

在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。

pl/sql中定义变量和常量的语法如下：

identifier [constant] datatype [not null] [:=value]

名称 [指定常量] 数据类型 [不为null] [:=(赋初值) | default(默认值) expr(指定初始值)]

说明：

identifier:变量名称

constant:指定常量。需要指定它的初始值，且其值是不能改变的。datatype:数据类型

not null:指定变量值不能为null

:=给变量或是常量指定初始值

default:用于指定初始值

expr:指定初始值的pl/sql表达式，可是文本值、其它变量、函数等。

标量定义的案例：

1、定义一个变长字符串

v_ename varchar2(10);

2、定义一个小数范围-9999.99～9999.99

v_sal number(6,2);

3、定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号

v_sal2 number(6,2):=5.4

4、定义一个日期类型的数据

v_hiredate date;

5、定义一个布尔变量，不能为空，初始值为false

v_valid boolean not null default false;

特别说明：

pl/sql在定义一个变量的时候，

如果要赋初值，则需要使用:=，

如果只是= 则是用于判断两个值是否相等。

标量(scalar)使用标量

在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号(:=)

案例：以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。

说明变量的使用，看看如何编写？

标量(scalar)--使用%type类型

对于上面的pl/sql块有一个问题：

就是如果员工的姓名超过了5字符的话，就会有错误，

为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

我们看看这个怎么使用：

%type类型使用的基本语法：

标识符名表名.列名%type;

复合变量(composite)--介绍（希望掌握）

用于存放多个值的变量。常用的包括：1、pl/sql记录；2、pl/sql表

复合类型--pl/sql记录

类似与高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

复合变量定义基本语法：

type 自定义的pl/sql记录名 is record(

变量名变量类型,

变量名变量类型

);

//使用自定义的pl/sql记录

复合变量基本使用语法：

变量名自定义的pl/sql记录名;

请编写一个过程，该过程可以接收一个用户编号，并显示该用户的名字，薪水，工作岗位(注意：要求用pl/sql记录实现)

create or replace procedure inEmpno(in_empno number) is

--定义一个记录数据类型

type my_emp_record is record(

v_ename emp.ename%type,

v_sal emp.sal%type,

v_job emp.job%type

);

--定义一个变量，该变量的类型是my_emp_record

v_emp_record my_emp_record;

begin

select ename,sal,job into v_emp_record from emp where empno=in_empno; dbms_output.put_line('名字：'||v_emp_record.v_ename||' 工资：

'||v_emp_record.v_sal||' 职位：'||v_emp_record.v_job);

exception

when no_data_found then

dbms_output.put_line('你的输入有误!');

end;

/

复合类型--pl/sql表(了解即可)

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。(可以理解为是oracle下的数组)实例如下:

复合类型pl/sql表的基本语法：

type 自定义的pl/sql表名 is table of 对应表.列名%type

index by binary_integer;

//使用自定义的pl/sql表

变量名自定义的pl/sql表名;

declare

type sp_table_type is table of emp.ename%type

index by binary_integer;

sp_table sp_table_type;--定义一个变量：sp_table类型

begin

select ename into sp_table(-1) from emp where empno=7788;

dbms_output.put_line('员工名：'||sp_table(-1));

end;

说明：

sp_table_type 是pl/sql表类型

emp.ename%type 指定了表的元素的类型和长度

sp_table 为pl/sql表变量

sp_table(0) 则表示下标为0的元素

参照变量--介绍(重点，必须掌握)

参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。

游标变量

通过游标可以取得返回结果集(这个结果集，往往是select语句的结果)的任何一行数据，从而提供共享的效率。

参照变量--游标(ref cursor)使用

定义游标基本语法：

type 自定义游标名 is ref cursor;

变量名自定义游标名;

打开游标基本语法：

open 游标变量 for select 语句;

取出当前游标指向的行基本语法：

fetch 游标变量 into 其它变量;

判断游标是否指向记录最后基本语法：

游标变量%notfound

参照变量--游标(ref cursor)变量

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时，(open时)需要指定select语句，这样一个游标就写一个select语句结合了。

实例如下：

1、请使用pl/sql编写一个过程，可以输入部门号，并显示该部门所有员工姓名和他的工资。

create or replace procedure test_cursor(in_deptno in number) is --定义一个记录数据类型

type my_emp_record is record(v_ename emp.ename%type,v_sal emp.sal%type);

--定义一个变量，该变量的类型是my_emp_record

v_emp_record my_emp_record;

--先定义一个游标变量类型

type my_emp_cursor is ref cursor;

--定义一个游标变量

v_emp_cursor my_emp_cursor;

begin

--打开游标，执行语句

open v_emp_cursor for select ename,sal from emp where

deptno=in_deptno;

--取出游标指向的每行数据，使用循环语句取出

loop

fetch v_emp_cursor into v_emp_record;--会引起游标向下走

--判断当前游标是否到达最后

exit when v_emp_cursor%notfound;--判断游标是否为空，为空时退出循环

dbms_output.put_line('姓名：'||v_emp_record.v_ename||' 工资：'||v_emp_record.v_sal);

end loop;

--关闭游标[游标使用完后，一定要关闭，避免资源浪费]

close v_emp_cursor;

end;

/

2、在1基础上，如果某个员工的工资低于200元，就增加100元。

create or replace procedure test_cursor(in_deptno in number) is --创建一个记录数据类型

type my_emp_record is record(v_ename emp.ename%type,

v_sal emp.sal%type,

v_empno emp.empno%type

);

v_emp_record my_emp_record;

--创建游标

type my_emp_cursor is ref cursor;

v_emp_cursor my_emp_cursor;

begin

open v_emp_cursor for select ename,sal,empno from emp where deptno=in_deptno;

loop

fetch v_emp_cursor into v_emp_record;

exit when v_emp_cursor%notfound;

--判断工资低于200的人，加100块

if v_emp_record.v_sal<200 then

v_emp_record.v_sal:=v_emp_record.v_sal+100;

update emp set sal=v_emp_record.v_sal where

empno=v_emp_record.v_empno;

end if;

dbms_output.put_line('姓名：'||v_emp_record.v_ename||' 工

资：'||v_emp_record.v_sal);

end loop;

close v_emp_cursor; end;

/

pl/sql的循环控制语句

pl/sql进阶--控制结构

在任何计算机语言(c,java,c#,c++)都有各种控制语句(条件语句，循环语句，顺序控制结构..)在pl/sql中也存在这样的控制结构。

条件分支语句

pl/sql中提供了三种条件分支语句

if--then,

if--then--else,

if--then--elsif--elsif—else

这里我们可以和java语句进行一个比较。

简单的条件判断if--then

基本语法：

if 条件表达式 then

执行语句...;

end if;

编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就

二重条件分支if--then--else

基本语法：

if 条件表达式 then

执行语句;

else

执行语句;

end if;

?编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；

多重条件分支if--then--elsif--else

基本语法：

if 条件表达式 then

执行语句;

elsif 条件表达式 then

执行语句;

else

执行语句;

end if;

?编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT 就给他的工资增加1000，如果该雇员职位是MANAGER就给他的工资增加

循环结构--loop

loop是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop 结尾，这种循环至少会被执行一次。

基本语法：

loop

执行语句;

exit when 条件表达式;

end loop;

请编写一个过程，可以输入用户名和添加用户的个数n;循环添加n个用户到users

表中，用户编号从1开始增加，直到n

循环语句--while循环

基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true

时，才会执行循环体语句，while循环以while..loop开始，以end loop结

束。

基本语法：

while 条件表达式 loop

执行语句;

end loop;

10个用户到users表中，用户编号从11开始增加。

/

看下面题判断是否正确

下面的过程是否正确，如果不正确，应该怎么改？

create or replace procedure sp_pro6(spName varchar2) is

v_test varchar2(40);

v_test:='aaa';--赋初值需在定义时就直接赋值，或在定义之后在begin中进

行赋值。

begin

dbms_output.put_line(v_test);

end;

create or replace procedure sp_pro6(spName varchar2) is

v_test varchar2(40):='aaa';

begin

spName:='你好';--spName为传入变量，不能重复赋值。

dbms_output.put_line(v_test||spName);

end;

说明：

1、在is--begin之间只能定义变量类型同时初始化赋值，或定义变量类型后在begin内进行赋值，不能在定义变量类型之后再对变量赋值。

2、传入的参数变量不能在存储过程中再次赋值。

循环语句--for循环

基本for循环的基本结构如下：

begin

for i in reverse 1..10 loop

insert into users values(i,'htf');

end loop;

end;

基本语法：

for 变量 in reverse 开始值..结束值 loop

执行语句;

end loop;

我们可以看到控制变量i,在隐含中就在不停的增加

注意：推荐使用loop循环结构，不推荐使用for循环。

顺序控制语句--goto,null

1、goto语句

goto语句用于跳转到特定标号去执行语句，注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。基本语法如下：goto lable，其中lable是已定义好的标号名。

基本语法：goto 标号;

标号定义：<<标号>>

例：

declare

i number:=1;

begin

<>

loop

dbms_output.put_line('输出i='||i);

if i=12 then

goto end_loop;

end if;

i:=i+1;

if i=10 then

goto start_loop;

end if;

end loop;

<>

dbms_output.put_line('循环结束');

end;

--输出1至12 循环结束。

2、null

null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null 语句的主要好处是可以提高pl/sql的可读性。

例：

declare

v_sal emp.sal%type;

v_ename emp.ename%type;

begin

select ename,sal into v_ename,v_sal from emp where empno=&no;

if v_sal<3000 then

update emp set comm=sal*0.1 where ename=v_ename;

else

null;

end if;

end;