第六章 PL/SQL编程

来源：互联网发布：呼死你淘宝搜索什么编辑：程序博客网时间：2024/06/07 20:03

PL/SQL作为对SQL的过程化补充，允许通过对变量，过程和事物等数据库对象进行数据操作实现SQL编程

SQL中的对象包括

存储过程、表、视图、函数、触发器。。。。。。等

一、PL/SQL编程

1、变量

变量时PL/SQL中存储单个数据的基本单元

创建变量

declare 变量名数据类型

变量赋值

变量名:=值

--创建变量
--变量必须结合过程使用
declare
v_age int:=100;
v_name varchar2(20);

使用键盘接收

变量赋值时在变量前加 &变量名

--使用键盘接收
v_sex varchar2(10):='&v_sex';

begin

语句块;

end;

begin end;称为过程块。

变量定义后必须在begin end 语句块中使用也就是说，变量必须结合过程使用。

在语句块中使用 dbms_output.put_line() 打印显示数据

注意点：Oracle的过程中只能写DML语句，如果出现了DQL语句，则该语句中必须要有into关键字，

into关键字用于将查询的结果写入变量。

例子：

--打印年龄和tom一样大的学生的姓名和性别
declare
v_age int;
v_stuName varchar2(20);
v_sex varchar2(10);
begin
--查询tom的年龄,并将查询后的结果写入变量
--使用into写入变量的结果必须是单行数据
select age into v_age from users where username='tom';
--查询比v_age一样大的学生信息
select username,sex into v_stuName,v_sex from users where age=v_age and username!='tom';
--打印
     dbms_output.put_line(v_stuName||'  '||v_sex);
end;
--打印显示综合讨论区版块内回帖最多的主贴标题，发帖人姓名和该贴的回帖数
declare
v_title varchar2(50);
v_name varchar2(2);
v_replyCount int;
--综合讨论区的版块编号
v_sid int;
--回帖最高的主贴编号
v_tid int;
--发帖人的编号
v_uid int;
begin
--查询综合讨论区的版块编号
select sid into v_sid from bbssection where sname='综合讨论';
--查询综合讨论区版块最高的主贴的回帖数
select max(replyCount)into v_replyCount from((select rtid,count(*)as replyCount from bbsreply where rsid=v_sid groupby rtid));
--根据最高的回帖数和版块编号查询对应的主贴编号
select rtid into v_tid from bbsreply where rsid=v_sid groupby rtid having count(*)=v_replyCount;
--根据主贴编号查询主贴的标题和发帖人的编号
select ttopic,tuid into v_title,v_uid from bbstopic where tid=v_tid;
--根据发帖人的编号查询发帖人的姓名
select uname into v_name from bbsusers where userid=v_uid;
end;

2、属性列变量

属性变量可以根据表的列类型动态定义相应的数据类型

declare 变量名表名.列名%type

例子：

--查询Java课程成绩最高的学生的姓名和他的成绩
declare
--使用属性列来动态作用变量的数据类型
v_name student.stuname%type;
v_score score.score%type;
v_courseId course.courseid%type;
v_stuId student.stuid%type;
begin
--查询Java课程的编号
select courseId into v_courseId from course where lower(courseName)='java';
--根据课程编号查询最高的成绩
select max(score)into v_score from score where courseId=v_courseId;
--根据课程编号和最高成绩查询学号
select stuId into v_stuId from score where courseId=v_courseId and score=v_score;
--根据学号查询学生的姓名
select stuName into v_name from student where stuId=v_stuId;
     dbms_output.put_line('Java课程成绩最高的同学是：'||v_Name||'，成绩为：'||v_score);
end;
--打印sales部门第一位入职的员工姓名和入职时间
declare
v_firstName emp.first_name%type;
v_lastName emp.last_name%type;
v_date emp.hire_date%type;
v_depId int;
begin
--查询salse部门的编号
select dep.department_id into v_depId from dep where dep.department_name='Sales';
--查询该部门第一位入职的员工信息
select first_name,last_name,hire_date into v_firstName,v_lastName,v_date from
(select first_name,last_name,hire_date from emp where emp.department_id=v_depId order by hire_date)
where rownum=1;
     dbms_output.put_line('sales部门第一位入职的员工姓名是：'||v_firstName||v_lastName||',入职时间是：'||to_char(v_date,'yyyy-mm-dd'));
end;

3、属性行变量

declare 变量名表名%rowtype

该变量表示行数据

行类型必须使用变量名.列名的方式分别获取该行中每个列的值

可以将部分数据写入行类型变量，如果行类型中有部分列没有进行过赋值则该列的默认值为空字符。

--打印员工编号为100的员工的所有信息
declare
--属性行类型，该变量表示emp表中的行数据
v_empInfo emp%rowtype;
begin
--将整行数据写入变量
--select*into v_empInfo from emp where emp.employee_id=100;
--打印显示数据,行类型必须使用变量名.列名的方式分别获取该行中每个列的值进行打印
--dbms_output.put_line(v_empInfo.employee_id||' '||v_empInfo.first_name);
--将部分数据写入行类型变量
select first_name,last_name,hire_date into v_empInfo.first_name,v_empInfo.last_name,v_empInfo.hire_date from emp where emp.employee_id=100;
--如果行类型中有部分列没有进行过赋值则该列的默认值为空字符
     dbms_output.put_line(v_empInfo.first_name||v_empInfo.last_name||v_empInfo.hire_date||v_empInfo.salary);
end;

4、复合类型

复合类型即自定义的属性变量（变量名基本使用 t_ 开头）

declare type 变量名 is record

（

变量名数据类型,

变量名数据类型，

......

变量名数据类型

）

设置复合属性的结构时，可以使用属性列或者属性行。

--打印显示工资最高的员工的姓名，薪水，所在部门名称，岗位名称
--定义一个复合类型，复合类型即自定义属性行
declare 
type t_myRow is record
(
--设定复合类型的结构时，可以使用属性列或者属性行
 firstName emp.first_name%type,
 lastName emp.last_name%type,
 depName varchar2(50),
 jobName varchar2(50)
);
--创建变量
v_max emp.salary%type;
v_info t_myRow;
v_depId int;
v_jobId job.job_id%type;
begin
--查询最高的工资
select max(salary)into v_max from emp;
--根据最高工资查询员工的姓名，部门编号，岗位编号
select first_name,last_name,department_id,job_id into v_info.firstName,v_info.lastName,v_depId,v_jobId from emp where salary=v_max;
--根据部门编号查询部门名称
select dep.department_name into v_info.depName from dep where dep.department_id=v_depId;
--根据岗位编号查询岗位名称
select job.job_title into v_info.jobName from job where job.job_id=v_jobId;
     dbms_output.put_line(v_info.firstName||'  '||v_info.lastName||'  '||v_info.depName||'  '||v_info.jobName);
end;

使用属性列的复合类型

--查询部门人数最多的部门名称和平均薪资，最高薪资以及最低薪资
declare
type empType is record
(
 depName dep.department_name%type,
 avgSalary float,
 maxSalary float,
 minSalary float
);
v_emp empType;
v_depId int;
begin
--select emp.department_id,count(*)as empCount from emp groupby emp.department_id order by empCount desc;
select emp.department_id into v_depId from emp groupby emp.department_id having count(*)=
(select max(empCount)as maxCount from(select count(*)as empCount from emp groupby emp.department_id));
--根据部门编号查询平均薪资，最高薪资，最低薪资
select avg(salary),max(salary),min(salary)into v_emp.avgSalary,v_emp.maxSalary,v_emp.minSalary from emp where emp.department_id=v_depId;
--根据部门编号查询部门名称
select dep.department_name into v_emp.depName from dep where dep.department_id=v_depId;
     dbms_output.put_line(v_emp.depName||' '||round(v_emp.avgSalary,1)||' '||v_emp.maxSalary||' '||v_emp.minSalary);
end;

使用属性行的复合类型

--查询所有成绩分数最高的学生的详细信息，课程名称和分数
declare
type stuType is record
(
 stuInfo users%rowtype,
 courseName course.coursename%type,
 score float
);
v_stu stuType;
v_stuId int;
v_courseId int;
begin
--查询成绩最高的学生的学号,课程编号，分数
select stuId,courseId,score into v_stuId,v_courseId,v_stu.score from score where score=(select max(score)from score);
--根据学号查询学生的信息
select*into v_stu.stuInfo from users where userId=v_stuId;
--根据课程编号查询课程名称
select courseName into v_stu.courseName from course where courseId=v_courseId;
     dbms_output.put_line(v_stu.stuInfo.userName||'  '||v_stu.courseName||'  '||v_stu.score);
end;

二、PL/SQL的流程控制

PL/SQL可以实现对数据进行条件判断循环处理等操作

（1）条件判断的语句

case （相当于JAVA的switch结构）

（2）循环语句

loop

while

for

1）

if 条件

if 表达式 then

语句;

end if;

if else条件

if 表达式 then

语句；

else

语句；

end if;

if elsif else语句

if 表达式 then

语句;

elsif 表达式 then

语句;

else

语句;

end if；

declare
v_num1 int:='&v_num1';
v_num2 int:='&v_num2';
begin
if v_num1>v_num2 then
        dbms_output.put_line('num1大于num2');
elsif v_num1=v_num2 then
        dbms_output.put_line('num1等于num2');
else
        dbms_output.put_line('num1小于num2');
endif;
end;

例：

--对DavidAustin进行加薪，如果David的薪水是高于该部门的平均薪水则加薪100，如果是和平均相同则加薪300，如果是低于平均薪资则加500
declare
v_salary float;
v_avg float;
v_empId int;
v_money float;
begin
--查询DavidAustin的薪水
--into v_salary
select salary,employee_id into v_salary,v_empId from emp where first_name||last_name='DavidAustin';
--查询他所在部门的平均薪资
select avg(salary)into v_avg from emp where department_id=(select department_id from emp where first_name||last_name='DavidAustin');
--比较薪资
if v_salary>v_avg then
        v_money:=100;
elsif v_salary=v_avg then
        v_money:=300;
else
        v_money:=500;
endif;
     update emp set salary=salary+v_money where emp.employee_id=v_empId;
end;

2） case 条件

case 变量

when 值 then

语句;

when 值 then

语句；

else

语句；

end case；

--使用case进行等值判断
case v_num1
when10then
               dbms_output.put_line('num1的值是10');
when20then
               dbms_output.put_line('num1的值是20');
endcase;

case 语句可以代替if elsif 语句

case

when 表达式 then

语句;

when 表达式 then

语句;

end case;

--使用case实现ifelsif语句
case
when v_num1>v_num2 then
              dbms_output.put_line('num1大于num2');
when v_num1=v_num2 then
              dbms_output.put_line('num1等于num2');
when v_num1<v_num2 then
              dbms_output.put_line('num1小于num2');
endcase;

在查询语句中可以使用case

select case 列

when 值 then

语句

when 值 then

语句

end case

from 表名；

--查询所有用户的姓名和性别，性别要求以中文来呈现
select userName,case sex
when'male'then'男'
when'female'then'女'
endcase
from users
select userName,
case
when sex='male'then'男'
when sex='female'then'女'
endcase
from users

if 和 case 的区别：

case语句与if语句都能实现条件结构

case语句可以结合DQL以及DML语句使用，也可以在过程块中使用

if语句只能在过程块中使用

3）loop循环

loop

循环语句

end loop；

通常loop循环需要结合 if条件一起使用

loop

if 条件 then

exit；

end if;

end loop；

但是一般使用 exit when 条件；退出循环 --满足条件退出循环

--实现打印此10次的循环
declare
i int:=1;
begin
     loop
/*if i>10 then
            --退出循环
            exit;
         end if;*/
exitwhen i>10;        
         dbms_output.put_line('我是第'||i||'次循环');
         i:=i+1;
end loop;
end;

4）while loop循环

while 表达式

loop

循环语句;

end loop;

--while循环
declare
i int:=1;
begin
while i<=10
     loop
         dbms_output.put_line('我是第'||i||'次循环');
         i:=i+1;
end loop;
end;

5）for 循环

for 变量 in 起始数值 .. 结束数值

loop

循环语句;

end loop;

for循环中的变量在for语句中已经初始化并自动递增1

--for循环
declare
i int:=1;
begin
for i in1..10
     loop
         dbms_output.put_line('我是第'||i||'次循环');
--换行
         dbms_output.new_line();
end loop;
end;

dbms_output.new_line();表示换行

0 0