PL/SQL语言语法
一、前言
SQL全称是“结构化查询语言(Structured Query Language)”,而PL/SQL是过程语言(Procedure Language),是对SQL的扩展。PL/SQL语言比任何其他程序设计语言(C、C++、java等)操作数据库的效率要高。
二、基本语法结构
declare --定义部分:包括定义变量、游标
/* 定义游标。有游标就肯定有打开游标、循环、关闭游标
* 小技巧:定义完游标后,游标需要的变量马上在后面定义,如定义ename,sal。
*/
cursor cemp is select ename,sal from emp;
pename emp.ename%type; --定义引用型变量(即引用emp表的ename列的类型,ename是什么类型pename也跟着是什么类型)
psal number; --定义一般变量
/* 定义记录型变量(以 _rec 结尾):代表一行数据。如:select * into emp_rec from emp where empno='2';
* 使用:emp_rec.ename,emp_rec.sal
*/
emp_rec emp%rowtype;
begin
--打开游标
open cemp;
loop
fetch cemp into pename,psal; --获取一行数据
exit when cemp%notfound; --退出条件
dbms_output.put_line(pename||'的薪水是:'||psal);
end loop;
--关闭游标
close cemp;
exception
when... then...
when others... then...
end;
/
1. 定义游标
cursor 游标名 [(参数名 数据类型[,参数名 数据类型]...)] is select 语句;
如:
定义:corsor cemp(dno number) is select ename from emp where deptno=dno;
打开:open cemp(2);
2. 循环(注意退出条件)
-
loop循环
[<<label_name>>] LOOP [EXIT...WHEN...] [IF...EXIT] statement... END LOOP [label_name] 【语法说明】 <<label_name>>:LOOP结构的标签,是可选项。 LOOP:LOOP循环开始标志。 statement:LOOP语句中循环执行的语句序列。 END LOOP:LOOP循环结束标志,可以添加LOOP结构的标签。
-
for...loop循环
[<<label_name>>] FOR index_name IN [ REVERSE ] lower_bound .. upper_bound LOOP statement... END LOOP [label_name]; 【语法说明】 index_name:循环计数器,是一个变量,它可以得到当前的循环指数。需要注意的是,不能为其手工赋值。 REVERSE:可选项,指定循环方式。默认的循环方式由下标(lower_bound)到上标(upper_bound)。使用该选项则从上标界到下标界。 lower_bound:循环范围的下标界。 upper_bound:循环范围的上标界。 下标和上标之间的".."不能省略。 for example: 1. for i in 1..10 loop 2. select eno into empNo from emp; for n in empNo loop
-
while...loop循环
[<<label_name>>] WHILE boolean_expression LOOP statement... END LOOP [label_name]; 【语法说明】 boolean_expression:布尔表达式。 statement:语句序列,当boolean_expression为TRUE时,该语句序列可获得执行权。
3. 条件判断
-
if...elsif
IF 条件1 THEN statement... ELSIF 条件2 THEN statement... ELSE statement... END IF;
-
case
CASE [selector] WHEN 表达式1 THEN 语句序列1; WHEN 表达式2 THEN 语句序列2; WHEN 表达式3 THEN 语句序列3; …… [ELSE 语句序列N]; END CASE;
三、写PL/SQL思路
先把以下两个思路根据需求弄清楚再写sql,这样思路会比较清晰,程序也比较不容易出错
1. SQL语句
for example:
select ename,sal from emp;
游标 --> 循环 --> 退出条件(notfount or other)
2. 变量(初始值、如何得到需要的值)
根据需求确定需要什么变量。计算变量的值的时候,能用算术计算的就尽量用,少用sql,提高效率。如计算总工资,如果循环过程有各个职工的工资,累加即可,少用sum()。
四、属性
- 游标属性:%found、%notfound、%isopen、%rowcount:影响的行数,即当前游标执行了多少行,并不是查询数据的总行数
- 游标的限制:默认情况下,oracle只允许在同一个会话中,打开300个游标。
修改游标数据限制:
alter system set open_cursor=400 scope=both;
scope的取值:both,memory,spfile。(spfile数据库需重启,修改配置文件;memory只在当前会话生效,both包括其他2个)