plsql编程

一、PL/SQL的基本概念：
PL/SQL(Procedural Language/SQL)是一种过程化语言，在PL/SQL中可以通过IF语句或
LOOP语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL语言
就能够实现操控程序处理的细节过程。
二、PL/SQL块结构：
  声明部分(用DECLARE开头)、执行部分(以BEGIN开头)和异常处理部分(以EXCEPTION开头)
 
[DECLARE]
--声明部分,可选
BEGIN
    --执行部分,必须
[EXCEPTION]
    --异常处理部分,可选
END

案例1：
-- 定义一个PL/SQL代码块,计算两个整数的和与这两个整数的差的商
declare
  a int:=100;
  b int:=200;
  c number;
  begin
     c:=(a+b)/(a-b);
     dbms_output.put_line(c);
  exception
     when zero_divide then
     dbms_output.put_line('除数不能为0');
  end;
三、代码注释和标识符
1、单行注释：由两个连接字符"--"开始
2、多行注释：/* */
3、PL/SQL字符集：所有PL/SQL程序元素都是右一些字符序列组合而成
合法的字符集主要包括以下内容：
1)大写和小写字母：A-Z或者a-z
    2)数字：0-9
3)非显示的字符：制表符、空格和回车
4)数学符号：+ - * / >  < =等
5)间隔符:包括()，{}，[],? ; , @ # % $ &等


四、数据类型和定义变量和常量
    1、数值类型
2、字符类型
3、日期类型
4、布尔类型
5、特殊数据类型：
1)%TYPE类型：使用%TYPE关键字可以声明一个与指定列名称相同的数据类型    （%就类似于java中的.）
  declare
    var_job emp.job%type;
案例如下：
    -- 在scott模式下，使用%type类型的变量输出emp表中编号为7369的员工名册和职务信息
declare 
var_ename emp.ename%type; -- 声明与ename列类型相同的变量
var_job emp.job%type;-- 声明与job列类型相同的变量
begin
 select ename,job
 into var_ename,var_job from emp
 where empno = 7369;
 dbms_output.put_line(var_ename||'的职务是'||var_job);
end;
2)RECORD类型(记录类型)：使用该类型的变量可以储存由多个列值组成的一行数据。
语法：
type record_type is record(
var_member1 data_type [not null][:default_value],
var_membern data_type [not null][:default_value]
)
record_type：表示要定义的记录类型的名称
var_member1：表示该记录类型的成员变量名称
data_type：表示成员变量的数据类型
案例：
-- 声明一个记录类型emp_type，然后使用该类的变量存储emp表中的一条记录信息，并输出这条记录信息
declare
type emp_type is record --声明record类型emp_type          此时emp_type只是声明记录类型，并不能直接用
(
var_ename varchar2(20),--定义字段/成员变量
var_job varchar2(20),
var_sal number
);
empinfo emp_type;--定义变量                                                此时才能直接通过  .   来访问
begin
select ename,job,sal 
into empinfo
from emp where empno = 7369;--检索数据
-- 输出雇员信息
dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||'、工资是'||empinfo.var_sal);
end;
3)%ROWTYPE类型：结合了%TYPE类型和RECORD类型变量的优点，它可以根据数据表中行的结构定义一种特殊的类型
 用来存储从数据表中检索到的一行的数据。
 
 rowVar_name table_name%rowtype;
 
 rowVar_name：表示可以存储一行数据的变量名
 table_name：指定的表名
 
 案例：
    --声明一个%ROWTYPE类型的变量rowVar_emp，然后使用该变量存储emp表中的一行数据
declare
 rowVar_emp emp%rowtype;--定义能够存储emp表中一行数据的变量          可以直接访问
 begin
 select * into rowVar_emp
 from emp
 where empno = 7369;
 dbms_output.put_line('雇员'||rowVar_emp.ename||'的编号是'||rowVar_emp.empno||',职务是'||rowVar_emp.job);
 end;
五、定义变量和常量
1、定义变量
 <变量名> <数据类型>[(长度):=<初始值>];
 var_countryname varchar2(50):='中国';
2、定义常量
 <常量名> constant<数据类型>:=<常量值>;
 con_day constant integer:=365;
 
六、流程控制语句
    1、选择语句
  1)if...then语句
  if<condition_expression> then
plsql语句
  end if;
案例：
-- if...then案例
declare
 var_name1 varchar2(50);
 var_name2 varchar2(50);
 begin
var_name1:='East';
var_name2:='xiaoke';
if length(var_name1)<length(var_name2) then
  dbms_output.put_line('字符串'||var_name1||'的长度比字符串'||var_name2||'的长度小');
  end if;
  end; 
2)if..then...else
if<condition_expression> then
plsql语句
else
plsql语句
endif;
   案例1：
-- if..then..else案例
 declare
 age int:=55;
 begin
if age>=56 then
dbms_output.put_line('您可以申请退休了');
else
dbms_output.put_line('您小于56岁，不可以申请退休了！');
end if;
 end; 
3)if...then...elsif：实现多分支判断选择
if<conditon_expression1> then
plsql语句
elsif<condtion_expression2>                    注意是elsif 而不是else if
plsql语句
else
plsql语句
end if;
案例1：
  declare
  month int:=10;--定义整型变量并赋值
  begin
if month>=0and month<=3 then --判断春季
  dbms_output.put_line('这是春季');
elsif month>=4 and month<=6 then
  dbms_output.put_line('这是夏季');
elsif month>=7and 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;
4)case语句
 case<selector>
    when <expression_1> then plsql语句1;
when <expression_2> then plsql语句2;
else 
 plsql语句;
 end case;
 案例：
-- 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月份';
  else
aboutinfo:=season||'季节不合法';
 end case;
 dbms_output.put_line(aboutinfo);
  end;
2、循环结构
1)loop语句
 loop                                                                 loop和end loop 可理解为java中的“大括号”
   plsql语句
   exit when end_condition
 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;
2)while语句
 while condition_expression loop
plsql语句
 end loop;
 案例：
 --while语句
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;
3)for语句
for variable_counter_name in [reverse] lower_limit..upper_limit loop  reverse为反转的意思
   plsql语句
end loop;
variable_counter_name:表示一个变量,通常为整数类型，用来作为计数器
lower_limit：计数器的下限值
upper_limit：计数器的上限值
 
案例：
declare
sum_i int:=0;
begin
for i in reverse 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;






 
============PL/SQL游标===========
一、游标的基本概念：
游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器的SQL语句。
游标的作用：就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行
并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。
游标分类：通过游标操作数据主要使用显式游标和隐式游标
二、显式游标
概念：显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集(即由select语句返回的查询结果)
处理数据的步骤：
1)声明游标
cursor curname[input_parameter1,[,input_parameter2]]
[return ret_type]
is select_sentence;

cur_name:表示所声明的游标名称
ret_type：表示执行游标操作后的返回值类型
select_sentence:游标所使用的select语句，它为游标的反复读取提供结果集
input_parameter1:作为游标的输入参数，可以有多个

声明一个游标，用来读取emp表中职务为销售员(SALESMAN)的雇员信息
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_iob;

2)打开游标
open cur_name(para_value1,[para_value2]...)
open cur_emp('MANAGER');
在打开游标过程中，程序首先将符合条件的记录送入内存中，然后在将指针，指向第一条记录。
3)读取游标
读取游标就是将结果集中的数据保存到变量中，读取游标使用fetch..into
fetch cur_name into {variable}
4)关闭游标
 游标使用完毕后需要关闭，以释放系统资源
 close cur_name
注意：读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录
 需要反复读取，直到游标读取不到数据为止。
 
--游标案例
-- 声明一个检索emp表中雇员信息的游标，然后打开游标，并制定检索职务是MANAGER的雇员信息
--接着使用fetch..into语句和while循环读取由表中的所有雇员信息最后输出读取的雇员信息
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;--声明一个record_emp类型的变量
begin
  open cur_emp('MANAGER');--打开游标
  fetch cur_emp into emp_row;--先让指针只想结果集中的第一行，并将值保存到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;--让指针只想结果集中的下一行，并将值保存到emp_row中
end loop;
close cur_emp;--关闭游标
end;
 
三、游标属性
无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen、%rowcount 4个属性
 %notfound  fetch是否提到数据 没有true 提到false
      %found      fetch是否提到数据 有true 没提到false
      %rowcount  返回sql语句影响的行数
      %isopen    布尔值 游标是否打开 
--游标属性案例
--声明一个游标用于检索指定员工编号的雇员信息，然后使用游标%found属性来判断是否检索到指定员工编号的雇员信息
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);
 dbms_output.put_line('编号是7499的雇员名称为:'||var_ename||'的职务是:'||var_job);
  else
dbms_output.put_line('无数据记录');--提示无记录信息
  end if;
end;
四、隐式游标
在执行SQL语句时，Oracle会自动创建一个隐式游标。这个游标在内存中处理该语句的工作区域。
隐式游标主要是处理数据操纵语句(UPDATE、DELETE语句)
由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称-SQL

--隐式游标案例
--在SCOTT模式下，把emp表中销售人员(即SALESMAN)的工资上调20%,然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量
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;
五、通过for语句循环游标
for var_auto_record in cur_name loop
 plsql语句
end loop;
var_auto_record：自动的RECORD类型的变量，可以是任意合法的变量名称
cur_name:指定的游标名称
案例1：
-- 使用隐式游标和for语句检索出职务是销售员的雇员信息并输出
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;
案例2：
--使用显式游标和for语句检索出部门编号是30的雇员信息并输出
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;