PL/SQL基本语法
体系结构
块头标
- 包含程序单元的类型、程序单元的名称和用于调用该程序单元的任意参数。
- 块头标的定义如下:
PROGRAM_TYPE name
([parameter IN/OUT/IN OUT para_type_specs,] …)
[RETURN datatype]
- PROGRAM_TYPE
FUNCTION
PROCEDURE
PACKAGE - Parameter
IN
OUT
IN OUT - para_type_specs,可以指明某参数是NOT NULL
- RETURN用于指明该函数的返回值的类型
比如:希望某个PL/SQL函数返回一个包含客户所居地理位置的数字。函数需要接受州名缩写、城市名称与州名缩写或邮政编码。
Function getLocation(state in varchar2) return number
Function getLocation(city in varchar2, state in varchar2)
return number
Function getLocation(zip in number) return number
声明节
1. 定义
name [CONSTANT] datatype [(constraint)] [ := value];
- Constraint:是对变量的一种约束,例如NOT NULL;如在使用变量的过程中,违反了变量的约束,PL/SQL将返回错误。
- 在声明变量的最后,可以为该变量赋值或定义默认值。
2.变量通常在PL/SQL程序单元的声明节中声明,但是借助以下语法可以在PL/SQL程序单元的任意位置声明变量或常量
DECLARE
name [CONSTANT] datatype [(constraint)] [ := value];
……
执行节
- 执行节完成PL/SQL程序单元中所有的行为。以关键字BEGIN开头,END结束(END后必须有一个分号结束)。
- 任何PL/SQL必须包含执行节
- 执行节必须包含至少一个可执行语句,尽管该语句可以是关键字NULL。此时NULL表示为空语句。
- PL/SQL块是可以嵌套的,因此必须考虑变量的作用域问题。
注意
任何特殊标识符的作用于都限于声明它的块以及包含在该块的任何子块内。要求在作用域内保持一致
<<main>>
DECALRE
myname varchar2;
BEGIN
DECLARE
myname VARCHAR2;
myname := main.myname;
END;
异常节
- 异常节是最后一节,是可选的。
- 异常节用于处理在执行该块的逻辑期间所发生的任何错误。
EXCEPTION
WHEN exception_name
THEN
error handling statements; ……
[WHEN OTHERS
default exception handling;]
异常相关函数
- 异常处理过程中,需要明确知道发生异常的原因。
- 在WHEN OTHERS语句中,将众多的错误原因放在一起进行处理,需要明确知道错误的类型和原因。
- Oracle提供了两个系统变量用以处理此问题:
–SQLCODE,返回错误的错误代码;
–SQLERRM,返回错误的出错信息;
exception
when others
then
dbms_output.put_line (‘Error’ || SQLERRM || ‘occurred.’);//“||”数据库中字符串连接符
运算符
算术运算符
逻辑运算符
常用函数
应用例子:
DECLARE
todays_date DATE;
current_day VARCHAR2(9);
current_day_length BINARY_INTEGER;
BEGIN
todays_date := SYSDATE;
-- 得到今天的日期赋给变量
current_day := TO_CHAR(todays_date, 'DAY');
-- 返回今天星期几
current_day := RTRIM(current_day);
-- 去掉右空格
current_day := INITCAP(current_day);
-- 日期长度
current_day_length := LENGTH(current_day);
DBMS_OUTPUT.PUT_LINE('Today''s date is ' || current_day || ', ' ||
TO_CHAR(todays_date));
DBMS_OUTPUT.PUT_LINE('The length of the word ' || current_day
|| ' is ' || TO_CHAR(current_day_length) || ' characters.');
END;
实现逻辑
- 通过使用条件性逻辑、分支和循环逻辑,介绍在PL/SQL中实现逻辑的基本方法。
条件逻辑 –IF
— IF … THEN … ELSE
— CASE
分支
循环
— LOOP
— FOR … LOOP
— WHILE … LOOP
//1
if condition
then
logic1;
end if;
//2
if condition1
then
logic1;
elseif condition2
logic2;
else
logic3;
end if;
//3
if condition
then
logic1;
else
logic2;
end if;
- 例子:nEmpId为参数
declare
mysal real(6,2);
mycomm real(6,2);
begin
select sal, comm into mysal, mycomm from emp
where empid = :nEmpId for update of comm;
if mysal+mycomm <= 100000 then
update emp set comm=100000 -sal where empid=:nEmpId;
else
update emp set sal=9999.99 where empid=:nEmpId;
end if;
commit;
END;
条件逻辑 – CASE
- 如果IF分支语句中的分支过多,容易造成IF语句结构变得复杂,此时可以使用CASE语句来代替。
- CASE语句中,包含多个WHEN条件。如果WHEN的条件满足,则执行其后的语句。如果所有的WHEN条件都没有满足,则代码会产生一个异常:CASE_NOT_FOUND,并将控制转移给代码块的异常节。
case myoffice
when ‘Boston’
then mycomm := mycomm * 2;
when ‘Atlanta’
then mycomm := mycomm + 100;
else
begin
mycomm := mycomm;
end;
end case;
分支
- GOTO:可以将程序定向至某个标号(LABEL)所指定的程序位置。其中,标号的声明需要用双尖括号包围起来,例如<< Label1>>,goto lavel1。一般情况下不要用GOTO分支
- RETURN:中止当前程序的执行,并将控制返回给调用程序。
if grade = ‘A’ then
goto label1;
end if;
……
<<lable1>>
if flag = ‘N’ then
insert …
else
update …
end if;
循环
- LOOP
- FOR
- WHILE
LOOP循环
LOOP
LOGIC
EXIT;
END LOOP;
LOOP
LOGIC
EXIT WHEN condition;//带有循环终止条件
END LOOP;
注意:1. 循环中可以包含多个EXIT。但只要执行EXIT,在循环中后续代码都忽略。
2.如没有EXIT,循环将无限制地执行下去
FOR 循环
- FOR … LOOP结构允许指定LOOP循环的次数
for counter [reverse] low … high//指定循环次数
loop
logic;
end loop;
- WHILE … LOOP
WHILE condition
LOOP
logic
END LOOP;
- 与FOR循环的差异
FOR循环有明确的循环次数,WHILE循环没有
FOR循环至少执行一次,WHILE可能一次也没有。与LOOP的第二种形式类似。
例子:
编写PL/SQL,该程序向某个班添加一个学生。过程中检查该班注册人数是否达到其极限。
如果该班有空位置,则将该班学生人数加1;
如果该班已经满员,该过程报一个错误,让调用程序知道存在问题。
首先,需要创建块的头标部分。
必须首先以PL/SQL编译器指令开头。需要CREATE或者REPLACE关键字。REPLACE关键字的意思是用新代码替换现有的具有相同名称的程序单元。
create or replace procedure addstudent
( roomin in integer ) is
后续的步骤有:
- 添加变量的声明
- 添加事务逻辑
- 添加异常处理
CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
IS
//变脸声明
roomname VARCHAR2 (20);
studentcount PLS_INTEGER;
capacity PLS_INTEGER;
noroom EXCEPTION;
//执行节
BEGIN
SELECT scount, cap, rname
INTO studentcount, capacity, roomname
FROM scott.rooms
WHERE room_id = roomin;
IF studentcount > capacity - 1
THEN
RAISE noroom;
ELSE
UPDATE scott.rooms
SET scount = studentcount + 1
WHERE room_id = roomin;
COMMIT;
DBMS_OUTPUT.put_line ( 'Student count now ' || studentcount || ' in ' || roomname );
END IF;
EXCEPTION
WHEN noroom THEN
DBMS_OUTPUT.put_line ('There is no room in ' || roomname);
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error ' || SQLERRM || ' occurred.');
END;