PLSQL 基础

发表于 | 更新于 | 分类于 | 阅读次数：

官方文档

Database PL/SQL Language Reference
PL/SQL Packages and Types Reference

所有文档位置Oracle Database Documentation
可选择对应版本的数据库并查看对应BookShelf

简介与HelloWorld

Procedure Language/SQL是Oracle对SQL语言的过程化扩展，是面向过程的语言。
不同数据库的SQL扩展：Oracle: PL/SQL;DB2: SQL/PL;SQL Server: Transac-SQL(T-SQL)

1
2
3
4
5
6
7
8
9
10
set serveroutput on
-- 打开输出开关
declare
    --说明部分
begin
    -- 程序体
    -- put_line里面的连接符是||
    dbms_output.put_line('Hello World !');
end;
/ -- /只需要在SQL Plus中使用

Oracle默认事务级别是read Only所以需要在更改完成后commit;回滚使用rollback;
可以在SQL Plus中使用命令desc 程序包名查看各个函数如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
PROCEDURE GET_LINES
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(32767) OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE GET_LINES
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 LINES                          DBMSOUTPUT_LINESARRAY   OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN
PROCEDURE PUT_LINE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

基础语法

PL/SQL的程序结构

1
2
3
4
5
6
7
declare
--声明部分（变量，光标，例外）
begin
--语句序列（DML语句）
exception
--例外处理
end;

定义基本变量类型：char,varchar2,date,number,boolean,long
赋值运算:=和into（查询语句中使用）
引用型变量var1 table.column%type
记录型变量var2 table%rowtype，变量分量的引用var2.column

if语句

1
2
3
4
5
6
7
8
9
10
IF 条件 THEN 语句;语句; END IF;

IF 条件 THEN 语句;语句;
ELSE 语句;语句;
END IF;

IF 条件 THEN 语句;语句;
ELSIF 条件 THEN 语句;
ELSE 语句;语句;
END IF;
1
2
3
4
5
6
7
8
9
10
set serveroutput on
accept num prompt'输入数字：'
declare
pn number(7,1) := #
begin
if pn<=0 then SYS.DBMS_OUTPUT.PUT_LINE('小于0');
elsif pn=0 then SYS.DBMS_OUTPUT.PUT_LINE('等于0');
else SYS.DBMS_OUTPUT.PUT_LINE('大于0');
end if;
end;

循环语句

while循环while 条件 loop 循环体 end loop;
loop循环loop EXIT when 条件1;[EXIT when 条件2;] 循环体 end loop;（常用，易于控制光标）
for循环for I in m..n loop 循环体 end loop;如for p in 1..10 loop dbms_output.put_line(p) end loop;

光标Cursor

相当于集合，因为Oracle没有数组集合
语法CURSOR 光标名 [(参数名 数据类型[,参数名 数据类型]...)] IS SELECT 语句;
打开光标open 光标名;
取值fetch 光标名 into 变量名;
关闭释放close 光标名;
光标的属性：%found,%notfound,%isopen,%rowcount(影响的行数)
光标数限制：默认一个会话300个
show parameter cursor
修改光标数alter system set open_cursors=400 scope=both(scope:both,memroy,spfile-数据库需重启)

带参数的光标：相当于高维数组的一维选择，定义和打开的时候带参数，定义带参数名，打开带具体参数。

例外Exception

系统例外：no_data_found, too_many_rows, zero_divide, value_error, timeout_on_resource

1
2
3
exception
 when no_data_found then 语句;
 when others then 语句;

自定义例外
定义类型是exception的变量然后用raise抛出

Oracle的一些函数：

  1. NVL(expr1,expr2)
    如果expr1和expr2的数据类型一致，则：
    如果expr1为空(null),那么显示expr2，
    如果expr1的值不为空，则显示expr1。

  2. NVL2(expr1,expr2, expr3)
    如果expr1不为NULL，返回expr2； expr1为NULL，返回expr3。
    expr2和expr3类型不同的话，expr3会转换为expr2的类型，转换不了，则报错。

  3. NULLIF(expr1,expr2)
    如果expr1和expr2相等则返回空(NULL)，否则返回expr1。

  4. coalesce(expr1, expr2, expr3,…,exprn)
    返回表达式中第一个非空表达式，如果都为空则返回空值。
    所有表达式必须是相同类型，或者可以隐式转换为相同的类型，否则报错。