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个)