【PL/SQL】PL/SQL语言基础

一、PL/SQL的块

 

（1）块（Block）是PL/SQL的基本程序单元

（2）一个PL/SQL应用程序由一个或多个块组成

1.PL/SQL块的基本组成

（1）定义部分（declare）

——定义常量、变量、游标等

——可选（不是块的必须部分）

（2）执行部分（begin…end;）

——执行SQL语句或PL/SQL语句

（3）异常部分（exception）

——处理运行时的错误

——可选（不是块的必须部分）

2.PL/SQL块的分类

（1）匿名块（anonymous）：动态构造，只能执行一次，可调用其他程序，但不能被其他程序调用。

①无变量匿名块

SQL> begin

  2 

  3  dbms_output.put_line('Hello World '）；

  4 

  5  end;

  6  /

 

PL/SQL procedure successfully completed

 

SQL> /

Hello World

PL/SQL procedure successfully completed

②有变量的匿名块，定义变量：

SQL> declare

  2  v_ename VARCHAR2(10);

  3  v_sal   NUMBER(7,2);

  4 

  5  begin

  6 

  7   select ename,sal into v_ename,v_sal from emp where empno=#

  8 

  9  dbms_output.put_line('Employees name is: '||v_ename)；

 10  dbms_output.put_line('Employees salary is: '||v_sal);

 11 

 12  end;

 13  /

Employees name is: SCOTT

Employees salary is: 9000

（2）命名块（named）：是带有名称的匿名块，这个名称就是标签。

（3）子程序（subprogram）：在数据库上建立好后可在其他程序中调用它们。

——过程

——函数

（4）触发器（trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

（5）程序包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其他程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。

3.PL/SQL 变量

在PL/SQL程序中临时存储数据；

程序之间传递数据

（1）PL/SQL变量类型

①标量类型

BINARY_DOUBEL BINARY_FLOAT BINARY_INTEGER BEC BECIMAL FLOAT INT INTEGER NATURAL NATURALN NUMBER NUMERIC PLS_INTEGER POSITIVE REAL SIGNTYPE SMALLINT

CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID STRING UROWID VARCHAR VARCHAR2

BOOLEAN

DATE

②复合类型

RECORD

TABLE

VARRAY

③参考类型

REF CURSOR

REF object_type

④大对象类型

BFILE

BLOB

CLOB

NCLOB

（2）PL/SQL变量定义语法

identifier  [CONSTRANT]  datatype  [NOT NULL]  [:= | DEFAULT expr]

变量定义实例： 

V_ename varchar2(20);

V_sal number(7,2);

V_balance binary_float;

V_hiredate date;

V_valid Boolean not null default false;

·定义常量和变量并赋值：

declare

v_ename VARCHAR2(10);

v_sal   NUMBER(7,2);

c_empno  NUMBER(4):=7788;               //声明一个常量

 

begin

 

 select ename,sal into v_ename,v_sal from emp where empno=c_empno;

 

dbms_output.put_line('Employees name is: '||v_ename)；

dbms_output.put_line('Employees salary is: '||v_sal);

 

end;

·在执行部分给变量赋值：

SQL> declare

  2  v_ename VARCHAR2(10);

  3  v_sal   NUMBER(7,2);

  4  v_empno  NUMBER(4);

  5 

  6  begin

  7   v_empno := 7788;

  8 

  9   select ename,sal into v_ename,v_sal from emp where empno=v_empno;

 10 

 11  dbms_output.put_line('Employees name is: '||v_ename)；

 12  dbms_output.put_line('Employees salary is: '||v_sal);

 13 

 14  end;

（3）%TYPE 属性

变量用于存放列值时，为了保证合适的数据类型和长度，可以使用%TYPE属性进行定义

V_ename emp.ename%type;

（4）复合变量

①用于存放多个值的变量

②复合变量的类型：

—PL/SQL记录

每个PL/SQL记录保护多个成员；

类似于c语句中的结构体；

通过record类型处理一行多列的数据；

1.record：在一个变量中可以存储多个区域，每个区域可以是一个标量、记录或table （用于处理一行多列的信息）

案例：

SQL> declare

  2     type emp_record  is record                          //定义变量类型为record

  3     ( v_ename emp.ename%type,

  4       v_sal   emp.sal%type,

  5       v_deptno emp.deptno%type

  6      );

  7 

  8     emp_rcd  emp_record ;

  9 

 10     begin

 11 

 12       select ename,sal,deptno into  emp_rcd from emp where empno=#

 13 

 14       dbms_output.put_line ('Employees name is: '||emp_rcd.v_ename);

 15       dbms_output.put_line ('Employees salary is: '||emp_rcd.v_sal);

 16       dbms_output.put_line ('Employees deparment number is: '||emp_rcd.v_deptno);

 17     end;

 18  /

Employees name is: SCOTT

Employees salary is: 9000

Employees deparment number is: 20

PL/SQL procedure successfully completed

 

2、通过%rowtype处理一行数据

 

SQL> declare

  2     emp_rcd  emp%rowtype ;

  3 

  4     begin

  5 

  6       select * into  emp_rcd from emp where empno=#

  7 

  8       dbms_output.put_line ('Employees name is: '||emp_rcd.ename);

  9       dbms_output.put_line ('Employees salary is: '||emp_rcd.sal);

 10       dbms_output.put_line ('Employees hiredate is: '||to_char

(emp_rcd.hiredate,'yyyy-mm-dd'));

 11       dbms_output.put_line ('Employees deparment number is: '||emp_rcd.deptno);

 12     end;

 13  /

Employees name is: SCOTT

Employees salary is: 9000

Employees hiredate is: 1987-04-19

Employees deparment number is: 20

PL/SQL procedure successfully completed

—PL/SQL表

table:  相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制

语法：

TYPE  ename_table_type IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

Ename_table  ename_table_type;

案例：

SQL> declare

  2   type emp_tab  is table of emp.ename%type            //定义变量类型为table类型

  3   index by binary_integer;                            //建立索引

  4 

  5   emp_table   emp_tab;

  6 

  7  begin

  8 

  9    select ename into emp_table(1) from emp where empno=7788;

 10     select ename into emp_table(2) from emp where empno=7369;

 11      select ename into emp_table(3) from emp where empno=7499;

 12 

 13    dbms_output.put_line('Employees 7788 name is: '||emp_table(1));

 14    dbms_output.put_line('Employees 7369 name is: '||emp_table(2));

 15    dbms_output.put_line('Employees 7499 name is: '||emp_table(3));

 16  end;

 17  /

Employees 7788 name is: SCOTT

Employees 7369 name is: SMITH

Employees 7499 name is: ALLEN

PL/SQL procedure successfully completed

 

 

SQL>

SQL> declare

  2   type emp_tab  is table of emp.ename%type

  3   index by binary_integer;

  4 

  5    type emp_t  is table of emp.sal%type

  6   index by binary_integer;

  7 

  8   emp_table   emp_tab;

  9   emp_t_sal   emp_t;

 10 

 11  begin

 12 

 13    select ename into emp_table(1) from emp where empno=7788;

 14     select ename into emp_table(2) from emp where empno=7369;

 15      select ename into emp_table(3) from emp where empno=7499;

 16 

 17     select sal into emp_t_sal(1) from emp where empno=7788;

 18     select sal into emp_t_sal(2) from emp where empno=7369;

 19     select sal into emp_t_sal(3) from emp where empno=7499;

 20 

 21    dbms_output.put_line('Employees 7788 name is: '||emp_table(1));

 22    dbms_output.put_line('Employees 7369 name is: '||emp_table(2));

 23    dbms_output.put_line('Employees 7499 name is: '||emp_table(3));

 24 

 25      dbms_output.put_line('Employees 7788 salary is: '||emp_t_sal(1));

 26    dbms_output.put_line('Employees 7369 salary is: '||emp_t_sal(2));

 27    dbms_output.put_line('Employees 7499 salary is: '||emp_t_sal(3));

 28  end;

 29  /

Employees 7788 name is: SCOTT

Employees 7369 name is: SMITH

Employees 7499 name is: ALLEN

Employees 7788 salary is: 9000

Employees 7369 salary is: 6800

Employees 7499 salary is: 7600

PL/SQL procedure successfully completed

—嵌套表

在table类型中嵌套record类型，可以处理多行多列数据。

案例：

SQL> declare

  2          type emp_table_type is table of emp%rowtype

  3           index by binary_integer;

  4            emp_tab emp_table_type;

  5 

  6        begin

  7          select * into emp_tab(1) from emp where empno=7788;

  8          select * into emp_tab(2) from emp where empno=7369;

  9         dbms_output.put_line('7788 Ename is: '||emp_tab(1).ename );

 10         dbms_output.put_line('7788 Salary is: '|| emp_tab(1).sal);

 11         dbms_output.put_line('7788 Deparment ID is: '||emp_tab(1).deptno);

 12         dbms_output.put_line('7369 Ename is: '||emp_tab(2).ename);

 13         dbms_output.put_line('7369 Salary is: '|| emp_tab(2).sal);

 14         dbms_output.put_line('7369 Deparment ID is: '||emp_tab(2).deptno);

 15        end;

 16  /

7788 Ename is: SCOTT

7788 Salary is: 9000

7788 Deparment ID is: 20

7369 Ename is: SMITH

7369 Salary is: 6800

7369 Deparment ID is: 20

PL/SQL procedure successfully completed

—VARRAY

数组：类似于嵌套表，但元素个数要指定上限。

TYPE VARRAY_NAME  IS  VARRY(SIZE) OF ELEMENT_TYPE [NOT NULL];

案例：

SQL> declare

  2  type tab_varray_type is varray(4) of varchar2(1);

  3  v_tab_varray  tab_varray_type;

  4  begin

  5    v_tab_varray :=tab_varray_type('A','B','C','D');

  6    dbms_output.put_line(v_tab_varray(1));

  7    dbms_output.put_line(v_tab_varray(2));

  8    dbms_output.put_line(v_tab_varray(3));

  9    dbms_output.put_line(v_tab_varray(4));

 10    dbms_output.put_line(v_tab_varray(1)||'-'||v_tab_varray(4));

 11    end;

 12  /

A

B

C

D

A-D

PL/SQL procedure successfully completed

 

 

—参照变量

是值用于存放数值指针的变量

参照变量的类型

游标变量（REF CURSOR）

对象类型变量（REF obj_type）

 

 

PL/SQL 代码标准化

①变量以v_开头 ②常量以c_开头 ③游标以_cursor结尾 ④异常以e_开头 ⑤表类型以_table_type结尾 ⑥表变量以_table结尾 ⑦记录类型以_record_type结尾 ⑧记录变量以_record结尾

①SQL关键字采用大写 ②PL/SQL关键字采用大写 ③数据类型采用大写 ④标识符和参数采用小写 ⑤数据库对象和列采用小写

代码缩进： ①同级代码应对齐 ②下一级代码要进行缩进