PS/SQL
前言 :
- PL/SQL基本结构
- 变量常量的用法
- PS/SQL条件结构
- PS/SQL循环结构
- 理解游标的使用
1. 程序结构及其变量
什么是PS/SQL?
PS/SQL是一种高级数据库程序设计语言,由于该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行高效处理,它对于处理比较复杂的业务流程如循环,判断,处理异常有着更好的解决.
PS/SQL程序的基本结构是块,可以嵌套使用(在begin到end中再置入一个PS/SQL块)
PS/SQL块结构如下:
declare
/* 声明部分,用于声明PL/SQL常量 变量 游标 用户自定义的异常等,不需要定义声明部分时,declare就不用写 */
begin
/* 可执行部分 */
exception
/* 错误处理部分,不需要处理就不加 */
end;
要查看运行结果需要提前在sqlplus或可视化工具内输入:set serveroutput on
案例: 在控制台打印这两句话
begin
dbms_output.put_line('我喜欢学习java课程'); /* dbms_output.put_line();是oracle内置的程序包,用于打印方法(不换行直接.put就可以) */
dbms_output.put_line('我特别喜欢java课程');
end;
/
PS/SQL不区分大小写,标识符首字符必须为字母
Oracle在定义标识符时,标识符在前,类型在后,在定义变量习惯使用v_名 作为变量名,v是变量的缩写;定义游标也习惯于使用c_名 作为游标名,并非要严格遵守,变量名不要和数据库中的表名或字段名相同
案例 : 编写一个PL/SQL程序,该程序输出长方形的面积,其中长和宽的值有键盘随机输入.
declare -- 需要声明变量,要用到declare
v_length number:=&length; /* 在键盘上接收的临时变量放在length中,赋值给v_length, := 表示复赋值, & 表示获取你在键盘上输入的数据 */
v_width number:=&width;
v_area number;
begin
v_area := v_length * v_width;
dbms_output.put_line('面积是:' || v_area);
end;
/
在语句赋值中,你就把into当作 “赋值给” 就行了
案例 : 打印人员个人信息,包括: 姓名,薪水,地址
declare
v_name varchar2(20) :='张三'; -- 在声明变量时直接赋值, 也属于直接赋值
v_money number;
v_address varchar2(100);
begin
v_money :=1580; --直接赋值
select '家里蹲' into v_address from dual; /* 语句赋值,select需要配合from使用,需要用到虚拟表 将家里蹲赋值给v_adress */
dbms_output.put_line('姓名:' || v_name || ',薪水:' || v_money || ',地址:' || v_address); -- || 相当于java中的+可以拼接字符串
end;
/
引用型变量:变量的长度取决于表中字段的类型和长度;
通过 表名.列名%TYPE 指定变量的类型和长度,eg: v_name emp.ename%TYPE;
引用变量的好处:使用普通变量定义方式要提前知道表中字段的类型, 而使用引用类型不需要考虑字段的类型
案例 : 查询emp表中7369号员工的个人信息,打印姓名和薪水
declare
v_name emp.ename%TYPE; -- 引用型变量
v_sal emp.sal%TYPE;
begin
select ename,sal into v_name,v_sal from emp where empno = 7369;
dbms_output.put_line('姓名:' || v_name || ',薪水:' || v_sal);
end;
/
记录型变量 : 接收表中的一整行记录, 相当于Java中的一个对象
语法: 变量名称 表名%ROWTYPE eg: v_emp emp%rowtype;
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line('姓名:' || v_emp.ename || ',薪水:' || v_emp.sal);
end;
/
2. 条件分支
**if条件语句:**用法类似于java中的 if else语句
格式:
begin
if 条件1 then 执行语句1;
elsif 条件2 then 执行语句2;
else 执行语句3;
end if;
end;
案例 : 判断emp表中记录数是否超过20条, 10~20之间, 或者10条以下
declare
-- 声明变量v_count用来接收查询到emp表中的记录数
v_count number;
begin
select count(ename) into v_count from emp;
if v_count >20 then
dbms_output.put_line('超过20条:' || v_count );
elsif v_count >= 10 and v_count <=20 then -- 直接>=10也可
dbms_output.put_line('在10到20之间:' || v_count );
else
dbms_output.put_line('10条以下:' || v_count );
end if;
end;
/
case条件语句
begin
case selector
when 表达式1 then 执行语句1;
when 表达式1 then 执行语句2;
...
when 表达式1 then 执行语句n;
else 执行语句n+1;
end case;
end;
3. 循环
简单loop循环
格式:
begin
loop
exit when 退出循环的条件;
循环体
end loop; -- 相当于java中的break;用于结束循环
end
案例 : 打印数字 1到10
declare
v_num number := 1;
begin
loop
exit when v_num >10;
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end;
/
while循环
格式:
begin
while 判断条件 -- 如果为false结束循环,注意这没有分号!
loop
循环体
end loop;
end
案例 : 使用while循环求10的阶乘
declare
v_num number :=2;
v_result number :=1;
begin
while v_num<=10 --注意没有分号
loop
v_result := v_num * v_result;
v_num := v_num + 1;
end loop;
dbms_output.put_line('10的阶乘:' || v_result);
end;
/
for循环
格式:
begin
/* in reverse表示循环变量counter从大到小依次取值(反向),省略掉reverse表示从小到大一次取值 */
for counter in [reverse] start_range..end_range
loop
循环体
end loop;
end
/
案例 : 使用反向for循环结构求10的阶乘
declare
v_num number := 1;
begin
for i in reverse 2..10 --反向for,从10到2
loop
v_num := v_num * i;
end loop;
dbms_output.put_line('10的阶乘:' || v_num);
end;
/
注意 :
- 简单循环loop循环和while循环的循环次数都是不确定的, for循环的次数是固定的;
- counter是一个隐式声明的变量,不需要在declare中被定义
- start_range和end_range指明循环次数.
- reverse表示循环变量从最大值向最小值迭代.
4. 游标
什么是游标?
用于临时存储一个查询返回的多行数据(类似于jdbc中的结果集ResultSet), 通过遍历游标, 可以处理该结果集的数据.
格式:
declare
-- 声明游标,将查询语句的结果放在游标中
cursor 游标名[(参数列表)] is 查询语句;
变量列表
begin
open 游标名; -- 打开游标
-- 获取游标中的值, 将游标的数据放在变量列表;fetch表示获取的意思
fetch 游标名 into 变量列表;
close 游标名; -- 游标的关闭
end
游标的属性:
使用时 --> 游标名%游标属性 eg : c_emp%NOTFOUND
游标的属性 | 返回值类型 | 说明 |
---|---|---|
%ROWCOUNT | 整型 | 获得fetch语句返回的数据行数 |
%FOUND | 布尔型 | 最近的fetch语句返回一行数据则为真, 否则为假 |
%NOTFOUND | 布尔型 | 与%found属性返回值相反 |
%ISOPEN | 布尔型 | 游标已经打开时值为真,否则为假 |
其中 %NOTFOUND是在游标中找不到元素时候返回true,通常用来判断何时退出循环
案例 : 使用游标查询emp表中多有员工的姓名和工资,并将其依次打印出来。
declare
cursor c_emp is select ename,sal from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open c_emp;
loop
-- 将游标中的值赋值给v_ename,v_sal
fetch c_emp into v_ename,v_sal;
exit when c_emp%notfound;
dbms_output.put_line(v_ename || '的工资是:' || v_sal);
end loop;
close c_emp;
end;
/