pl/sql之各参数详解（“箱子模型“）

变量（“普通箱子”）

未封装的箱子，其中内容可随意替换,新加入的东西会替换旧东西(只能装一个，装了新的旧的就得扔)

declare
	a  number:=1;
begin
	a:=2;
	select 3 into a from dual;
	--打印a的值
	dbms_output.put_line('a的值：'||a);
end;
1
2
3
4
5
6
7
8

常量（“胶带封口的箱子”）

在declare中装好东西封装完毕的箱子，且不能在begin…end之间打开重放（即初始化后不能二次赋值），代码示例如下：

declare
	a constant number:=1;
begin
	--下语句不被允许
	a:=2;
	select 3 into a from dual;
end;
1
2
3
4
5
6
7

not null变量（“必须有东西的箱子”）

常量不必应用该约束，因为常量必须初始化

--NOT NULL(错误示例)
DECLARE
	a NUMBER NOT NULL;
BEGIN
	NULL;
END;
/
--正确示例
DECLARE
	--default作用等同于:=
	a NUMBER NOT NULL DEFAULT 1;
BEGIN
	dbms_output.put_line(a);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 在变量声明时给定具体数值我们称之为初始化（在declare之后begin之前给定具体值）
• 变量声明时不给具体数值我们称之为赋值（即在begin…end中给定具体值）

特殊类型变量（“箱中箱”）

该类型变量在装东西时不会直接装而是会先装一个箱子然后在装进外部箱中，直接查询外部箱无数据，必须指明其箱中箱的标签

--错误示范（直接访问外部箱不指明内部箱标签）
DECLARE
    --声明箱中箱外部箱变量
    a emp%ROWTYPE;
BEGIN
    --外部箱装箱（赋值）
    SELECT * INTO a FROM EMP WHERE EMPNO=7369;
    --输出
    dbms_output.put_line(a);
END;
/
--正确示范
DECLARE
    --声明箱中箱外部箱变量
    a emp%ROWTYPE;
BEGIN
    --外部箱装箱（赋值）
    SELECT * INTO a FROM EMP WHERE EMPNO=7369;
    --输出
    dbms_output.put_line(a.ename);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• rowtype（“一个外部箱装多个不同种类箱子”）

rowtype中装箱类型由rowtype前表对象决定,在数据库中不可用，只可在pl/sql中使用

--rowtype示例
DECLARE
    --声明一个外部箱a其中包含deptno、dname、loc三个箱子
    a DEPT%ROWTYPE;
BEGIN
    --赋值
    SELECT * INTO A FROM DEPT WHERE DEPTNO=10;
    --输出子箱中内容
    dbms_output.put_line(a.DEPTNO||', '||a.dname||', '||a.loc);
END;
1
2
3
4
5
6
7
8
9
10

• record（“一个外部箱装多个不同种类箱子”）

与rowtype的区别在于该外部箱装的箱子类型由用户自由设定

--record记录
DECLARE
    --声明一个record类型(即声明要包含箱子的类型)
    TYPE a IS RECORD(
        NAME VARCHAR2(200),
        AGE NUMBER(2)
    );
    --声明外部箱承载record箱
    b a;
BEGIN
    --赋值
    b.NAME:='刘琳';
    b.AGE:=18;
    --输出子箱中内容
    dbms_output.put_line(b.name||', '||b.AGE);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 集合（“多个同类型箱子”）

--rowtype
DECLARE
    --声明一个集合其中承载同类型箱体
    TYPE a IS table OF VARCHAR2(200);
    --声明箱体统一标签('先前我称之为集合变量')
    b a;
BEGIN
    --赋值
    SELECT ENAME BULK COLLECT INTO b FROM EMP;
    --输出子箱中内容(由于有多个值所以要用到for循环)
    FOR i IN b.first..b.last LOOP
        dbms_output.put_line(b(i));
    END LOOP;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• record+集合/rowtype+集合（“多个外部箱装多个类型箱子”）

--record与集合
DECLARE
    TYPE a IS RECORD(
        q dept.DEPTNO%TYPE,
        s DEPT.DNAME%TYPE,
        o DEPT.LOC%TYPE
    );
    TYPE b IS TABLE OF a;
    c b;
BEGIN
    SELECT * BULK COLLECT INTO c FROM DEPT;
    FOR i IN c.first..c.last LOOP
        dbms_output.put_line(c(i).q||', '||c(i).s||c(i).o);
    END LOOP;
END;
/
--rowtype与集合
DECLARE
    TYPE a IS table OF EMP%ROWTYPE;
    b a;
BEGIN
    SELECT * BULK COLLECT INTO b FROM EMP;
    FOR i IN b.first..b.last LOOP
        dbms_output.put_line(b(i).ENAME||', '||b(i).empno);
    END LOOP;
END;
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

游标与参数

fetch 游标名 into 变量名

• 该语句一次赋一条数据即一次给一个rowtype类型变量赋值
• 写在循环里可实现多条数据输入

--fetch...into...
DECLARE
    CURSOR a IS SELECT * FROM DEPT;
    c DEPT%ROWTYPE;
BEGIN
    OPEN a;
        LOOP
            FETCH a INTO c;
            EXIT WHEN a%notfound;
            dbms_output.put_line(c.deptno);
        END LOOP;
    CLOSE a;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13

fetch 游标名 bulk collect into 集合名

• 该语句一次赋多条数据即一次给一个集合类型变量赋值
• 在循环外书写即可完成赋值，但值过多需借助循环输出

--fetch...bulk collect into...
DECLARE
    CURSOR a IS SELECT * FROM DEPT;
    TYPE b IS TABLE OF DEPT%ROWTYPE;
    c b;
BEGIN
    OPEN a;
        
            FETCH a BULK COLLECT INTO c;
        FOR i IN c.first..c.last LOOP
            dbms_output.put_line(c(i).deptno);
        END LOOP;
    CLOSE a;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

实参与形参

带参数存储过程或函数中函数名和存储过程名后（）里的内容一般称之为形参，带有实际值的变量且与存储过程或函数有相应交换过程我们称之为实参

• in
  外部实际装东西的箱子将箱子里东西借助”形式箱子”给内部（仅外部对箱子里内容有更改权）

• out
  内部实际装东西的箱子将箱子里东西借助形式箱子给外部（仅内部对箱子里内容有更改权）

• in out
  内外均对箱子里内容有更改权

函数

若函数在形参处不指明[in|out|in out],则默认为in

• in

create OR REPLACE FUNCTION q1(a IN NUMBER) RETURN NUMBER
IS
BEGIN
    --给in类型参数赋值的一系列语句均不被允许如下：a:=1;
    --返回a
    RETURN a;
END;
1
2
3
4
5
6
7

• out（用户可将return后的变量看作一个out类型参数）

--return
create OR REPLACE FUNCTION q1 RETURN NUMBER
IS
BEGIN
    RETURN 3;
END;
--普通输出
create OR REPLACE FUNCTION q2(a OUT NUMBER) RETURN NUMBER
IS
BEGIN
    a:=3;
    RETURN a;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13

• in out

create OR REPLACE FUNCTION q3(a IN OUT NUMBER) RETURN NUMBER
IS
BEGIN
    a:=3;
    RETURN a;
END;
1
2
3
4
5
6

存储过程

若存储过程在形参处不指明[in|out|in out],则默认为in,相关规则与函数大同小异

• in

create OR REPLACE procedure w1(a IN NUMBER) 
IS
BEGIN
    NULL;
END;
1
2
3
4
5

• out

create OR REPLACE procedure w2(a OUT NUMBER)
IS
BEGIN
    a:=3;
END;
1
2
3
4
5

• in out

create OR REPLACE procedure w3(a IN OUT NUMBER)
IS
BEGIN
    a:=3;
END;
1
2
3
4
5