PLSQL
PL/SQL是一种块结构的语言,这意味着PL/SQL程序被划分和编写代码的逻辑块。每块由三个子部分组成:
DECLARE
/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 */
BEGIN
/* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */
EXCEPTION
/* 执行异常部分: 错误处理 */
END;
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
字符集
1.算数运算符
运算符 | 描述 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
** | 乘方 |
2.关系运算符
运算符 | 描述 |
---|---|
= | 等于 |
!= <> ~= | 不等于 |
> | 大于 |
< | 小于 |
= | 大于等于 |
<= | 小于等于 |
BETWEEN AND | 检测两值之间的内容 |
IN | 匹配列表中的值 |
LIKE | 模糊查询 |
IS NULL | 非空值判断 |
3.逻辑运算符
运算符 | 描述 |
---|---|
AND | 两个表达式同时为真结果为真 |
OR | 有一个为真则为真 |
NOT | 取相反的逻辑值 |
条件结构
IF - THEN 语句
DECLARE
a number(2) := 10;
BEGIN
--判断条件是否成立
IF( a < 20 ) THEN
-- 成立则输出
dbms_output.put_line('a is less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
IF-THEN-ELSE语句
DECLARE
a number(3) := 10;
BEGIN
IF( a < 20 ) THEN
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
IF-THEN-ELSIF-THEN-ELSE语句
DECLARE
a number(3) := 20;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
循环结构
LOOP-EXIT-END语句
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x >=20 THEN
exit; --满足条件退出循环
END IF;
END LOOP;
dbms_output.put_line('After Exit x is: ' || x);
END;
LOOP-EXIT-WHEN-END语句
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
dbms_output.put_line('After Exit x is: ' || x);
END;
WHILE-LOOP-END语句
DECLARE
i number(3);
j number(3);
BEGIN
i := 2;
LOOP
j:= 2;
LOOP
exit WHEN ((mod(i, j) = 0) or (j = i));
j := j +1;
END LOOP;
IF (j = i ) THEN
dbms_output.put_line(i || ' is prime');
END IF;
i := i + 1;
exit WHEN i = 50;
END LOOP;
END;
FOR-IN-LOOP-END语句
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
选择和跳转
CASE语句
DECLARE
grade char(1) := 'B';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('You passed');
when 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
goto语句
DECLARE
a number(2) := 10;
BEGIN
<<loopstart>> --标号
WHILE a < 20 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 15 THEN
a := a + 1;
GOTO loopstart;--跳转
END IF;
END LOOP;
END;
异常
DECLARE
c_id customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
.