oracle循环语句变量,《Oracle从入门到精通》读书笔记第五章 PL/SQL编程之一

5.1 PL/SQL概述

5.1.1 PL/SQL块结构

PL/SQL程序都是以块(BLOCK)为基本单位

语法结构：

[DECLEAR]

--声明部分，可选

BEGIN

--执行部分，必须

[EXCEPTION]

--异常处理部分，可选

END

每一条语句必须以分号结束。

SQL> set serveroutput on

SQL> declare

2 a int:=100;

3 b int:=200;

4 c number;

5 begin

6 c:=(a+b)/(a-b);

7 dbms_output.put_line(c);

8 exception

9 when zero_divide then

10 dbms_output.put_line('除数不许为零!');

11 end;

12 /

-3

PL/SQL procedure successfully completed.

5.1.2 代码注释和标识符

1、单行注释

由两个连字符“--”开始

SQL> declare

2 Num_sal number; --声明一个数值变量

3 Var_ename varchar2(20); --声明一个字符串变量

4 begin

5 select ename,sal into Var_ename,Num_sal from emp

6 where empno=7369; --检索指定的值并存储到变量中

7 dbms_output.put_line(Var_ename||'的工资是'||Num_sal);

8 end;

2、多行注释

由/*开头，由*/结尾

3、PL/SQL字符集

5.2 数据类型和定义变量和常量

5.2.1 基本数据类型

1、数值类型

number,pls_integer,binary_integer

number(p,s)

Num_Money number(9,2);

2、字符类型

varchar2,char,long,nchar,nvarchar2

varchar2(maxlength)

char(maxlength)

3、日期类型

date

存储空间是7个字节，分别使用一个字节存储世纪，年，月，天，小时，分钟和秒。

4、布尔类型

boolean

变量值可以是TRUE,FALSE和NULL

5.2.2 特殊数据类型

1、%type类型

声明一个与指定列名称相同的数据类型，紧跟在指定列名的后面。

var_job emp.job%type;

SQL> conn scott/tiger

Connected.

SQL> set serveroutput on

SQL> declare

2 var_ename emp.ename%type;

3 var_job emp.job%type;

4 begin

5 select ename,job into var_ename,var_job

6 from emp

7 where empno=7369;

8 dbms_output.put_line(var_ename||'的职务是

9 '||var_job);

10 end;

11 /

SMITH的职务是

CLERK

PL/SQL procedure successfully completed.

由于into子句中的变量只能存储一个单独的值，所以要求select子句只能返回一行数据。

2、record类型

定义record类型的语法格式：

type record_type_name is record

(

var_member1 data_type [not null][:=default_value],

...

var_member   date_type [not null][:=default_value])

declare

type emp_type is record

(

var_name varchar2(20),

var_job varchar2(20),

var_sal number

);

empinfo emp_type;

begin

select ename,job,sal

info empinfo

from emp

where empno=7369;

dems_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);

end;

SQL> set serveroutput on

SQL> declare

2 type emp_type is record

3 (

4 var_ename varchar2(20),

5 var_job varchar2(20),

6 var_sal number

7 );

8 empinfo emp_type;

9 begin

10 select ename,job,sal

11 into empinfo

12 from emp

13 where empno=7369;

14 dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);

15 end;

16 /

雇员SMITH的职务是CLERK,工资是2758.33

PL/SQL procedure successfully completed.

3、%rowtype类型

存储从数据表中检索到的一行数据。

语法结构：

rowVar_name table_name%rowtype;

SQL> set serveroutput on

SQL> declare

2 rowvar_emp emp%rowtype;

3 begin

4 select *

5 into rowvar_emp

6 from emp

7 where empno=7369;

8 dbms_output.put_line('雇员'||rowvar_emp.ename||'的编号是'||rowvar_emp.empno||',职务是'||rowVar_emp.job);

9 end;

10 /

雇员SMITH的编号是7369,职务是CLERK

PL/SQL procedure successfully completed.

5.2.3 定义变量和常量

1、定义变量

语法格式：

[(长度):=];

var_countryname varchar2(50):='中国'；

2、定义常量

语法格式：

constant:=;

con_day constant integer:=365;

5.3 流程控制语句

5.3.1 选择语句

1、if...then语句

语法格式：

ifthen

plsql_sentence

end if;

if last_name is null then

...

end if;

编辑器中编辑如下：

declare

var_name1 varchar2(50);

var_name2 varchar2(50);

begin

var_name1:='east';

var_name2:='xiaoke';

if length(var_name1)

dbms_output.put_line('字符串"'||var_name1||'"的长度比字符串"'||var_name2||'"的长度小');

end if;

end;

/

SQL> set serveroutput on

SQL>

字符串"east"的长度比字符串"xiaoke"的长度小

PL/SQL procedure successfully completed

2、if...then...else语句

语法格式：

ifthen

plsql_sentence1；

else

plsql_sentence2；

end if;

declare

age int:=55;

begin

if age>=56 then

dbms_output.put_line('您可以申请退休了！');

else

dbms_output.put_line('您小于56岁，不可以申请退休了！');

end if;

end;

/

SQL>

您小于56岁，不可以申请退休了！

PL/SQL procedure successfully completed

3、if...then...elsif语句

语法格式：

ifthen

plsql_sentence1；

elsifthen

plsql_sentence2；

...

else

plsql_sentencen；

end if;

declare

month int:=10;

begin

if month>=0 and month <=3 then

dbms_output.put_line('这是春季');

elsif month>=4 and month <=6 then

dbms_output.put_line('这是夏季');

elsif month>=7 and month <=9 then

dbms_output.put_line('这是秋季');

elsif month>=10 and month <=12 then

dbms_output.put_line('这是冬季');

else

dbms_output.put_line('对不起，月份不合法！');

end if;

end;

/

SQL>

这是冬季

PL/SQL procedure successfully completed

4、case语句

语法格式：

case

whenthen plsql_sentence_1;

whenthen plsql_sentence_2;

...

whenthen plsql_sentence_n;

[else plsql_sentence;]

end case;

declare

season int:=3;

aboutinfo varchar2(50);

begin

case season

when 1 then

aboutinfo:=season||'季度包括1,2,3月份';

when 2 then

aboutinfo:=season||'季度包括4,5,6月份';

when 3 then

aboutinfo:=season||'季度包括7,8,9月份';

when 4 then

aboutinfo:=season||'季度包括10,11,12月份';

else

aboutinfo:=season||'季节不合法';

end case;

dbms_output.put_line(aboutinfo);

end;

/

SQL>

3季度包括7,8,9月份

PL/SQL procedure successfully completed

5.3.2 循环语句

1、loop语句

程序至少执行一次循环体

语法格式：

loop

plsql_sentence;

exit when end_condition_exp;

end loop;

declare

sum_i int:=0;

i int:=0;

begin

loop

i:=i+1;

sum_i:=sum_i+i;

exit when i=100;

end loop;

dbms_output.put_line('前100个自然数的和是：'||sum_i);

end;

/

SQL>

前100个自然数的和是：5050

PL/SQL procedure successfully completed

2、while语句

语法格式：

while condition_expression loop

plsql_sentence;

end loop;

declare

sum_i int:=0;

i int:=0;

begin

while i<=99 loop

i:=i+1;

sum_i:=sum_i+i;

end loop;

dbms_output.put_line('前100个自然数的和是：'||sum_i);

end;

/

SQL>

前100个自然数的和是：5050

PL/SQL procedure successfully completed

3、for语句

语法格式：

for variable_counter in [reverse] lower_limit..upper_limit loop

plsql_sentence;

end loop;

declare

sum_i int:=0;

begin

for i in 1..100 loop

if mod(i,2)=0 then

sum_i:=sum_i+i;

end if;

end loop;

dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);

end;

/

SQL>

前100个自然数中偶数之和是：2550

PL/SQL procedure successfully completed

5.4 PL/SQL游标

游标的作用相当于指针

显式游标和隐式游标

5.4.1 显式游标

由用户声明和操作的一种游标，通常用于操作查询结果集。

处理数据的步骤：声明游标，打开游标，读取游标和关闭游标。

游标每次只能读取一行数据

1、声明游标

语法格式：

cursor cur_name[(input_parameter[,input_parameter2]...)]

[return ret_type]

is select_sentence;

ret_type：执行游标操作后的返回值类型。

select_sentence：游标所使用的select语句，为游标的反复读取提供结果集。

input_parameter1：作为游标的输入参数，指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：

para_name [in] datatype[{:=|default}para_value]

定义游标应该放在PL/SQL块得declare部分。

declare

cursor cur_emp(var_job in varchar2:='SALESMAN')

is select empno,ename,sal

from emp

where job=var_job;

2、打开游标

语法格式：

open cur_name[(para_value1[,para_value2]...)]'

3、读取游标

语法格式：

fetch cur_name into {variable};

declare

cursor cur_emp(var_job in varchar2:='SALESMAN')

is select empno,ename,sal

from emp

where job=var_job;

type record_emp is record

(

var_empno emp.empno%type,

var_ename emp.ename%type,

var_sal emp.sal%type

);

emp_row record_emp;

begin

open cur_emp('MANAGER');

fetch cur_emp into emp_row;

while cur_emp%found loop

dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'，工资是'||emp_row.var_sal);

fetch cur_emp into emp_row;

end loop;

close cur_emp;

end;

/

SQL>

JONES的编号是7566，工资是2975

BLAKE的编号是7698，工资是2850

CLARK的编号是7782，工资是2450

PL/SQL procedure successfully completed

4、关闭游标

语法格式：

close cur_name;

5.4.2 游标的属性

%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为false。

%notfound：布尔型属性，与%fonud相反。

%rowcount：数字型属性，返回受SQL语句影响的行数。

%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

declare

var_ename varchar2(50);

var_job varchar2(50);

cursor cur_emp

is select ename,job

from emp

where empno=7499;

begin

open cur_emp;

fetch cur_emp into var_ename,var_job;

if cur_emp%found then

dbms_output.put_line('编号是7499的雇员名称为：'||var_ename||'，的职务是：'||var_job);

else

dbms_output.put_line('无数据记录');

end if;

end;

/

SQL>

编号是7499的雇员名称为：ALLEN，的职务是：SALESMAN

PL/SQL procedure successfully completed

4.3 隐式游标

begin

update emp

set sal=sal*(1+0.2)

where job='SALESMAN';

if sql%notfound then

dbms_output.put_line('没有雇员需要上调工资');

else

dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');

end if;

end;

/

SQL>

有4个雇员工资上调20%

PL/SQL procedure successfully completed

5.4.4 通过for语句循环游标

(1)在for语句中遍历隐式游标中的数据时，通常在关键字“in”的后面提供由select语句检索的结果集，在检索结果集的过程中，oracle系统会自动提供一个隐式的游标sql

begin

for emp_record in (select empno,ename,sal from emp where job='SALESMAN')

loop

dbms_output.put('雇员编号： '||emp_record.empno);

dbms_output.put('；雇员名称： '||emp_record.ename);

dbms_output.put_line('；雇员工资： '||emp_record.sal);

end loop;

end;

/

SQL>

雇员编号： 7499；雇员名称： ALLEN；雇员工资： 1920

雇员编号： 7521；雇员名称： WARD；雇员工资： 1500

雇员编号： 7654；雇员名称： MARTIN；雇员工资： 1500

雇员编号： 7844；雇员名称： TURNER；雇员工资： 1800

PL/SQL procedure successfully completed

(2)在for语句中遍历显式游标中的数据时，通常在关键字“in”的后面提供游标的名称

语法格式：

for var_auto_record in cur_name

loop

plsql sentence;

end loop;

declare

cursor cur_emp is

select * from emp

where deptno=30;

begin

for emp_record in cur_emp

loop

dbms_output.put('雇员编号： '||emp_record.empno);

dbms_output.put('；雇员名称： '||emp_record.ename);

dbms_output.put_line('；雇员职务： '||emp_record.job);

end loop;

end;

/

SQL>

雇员编号： 7499；雇员名称： ALLEN；雇员职务： SALESMAN

雇员编号： 7521；雇员名称： WARD；雇员职务： SALESMAN

雇员编号： 7654；雇员名称： MARTIN；雇员职务： SALESMAN

雇员编号： 7698；雇员名称： BLAKE；雇员职务： MANAGER

雇员编号： 7844；雇员名称： TURNER；雇员职务： SALESMAN

雇员编号： 7900；雇员名称： JAMES；雇员职务： CLERK

PL/SQL procedure successfully completed

在使用游标(包括显式和隐式)的for循环中，可以声明游标，但不要进行打开游标，读取游标和关闭游标等操作，这些由oracle系统内部自动完成。