PL SQL

PL SQL

PL/SQL简介

​ PL/SQL也是一种程序语言。PL 是Procedural Language的缩写。

​ PL/SQL是Oracle数据库对SQL语句的扩展，增加了编程语言的特点。

​ 数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算

​ 优点：

• 改善了性能
• 可重用性
• 模块化

DECLARE -- 可选部分                                 宣布
	• 变量、常量、游标、用户定义异常声明
BEGIN -- 必要部分                                   
	• SQL语句
	• PL/SQL语句
EXCEPTION --可选部分                                例外
	• 程序出现异常时，捕捉异常并处理异常
END; -- 必要部分

 BEGIN
    DBMS_OUTPUT.PUT_LINE(‘Hello’);                dbms :数据库管理系统   
 END;

--sqlplus
SET SERVEROUTPUT ON(如果使用sqlplus需要设置)
在sqlplus中需要以/结尾！！！

变量声明

​ PL/SQL中可使用标识符来声明变量，常量，游标，用户定义的异常等，并在SQL语句或过程化的语句中使用。

identifier [CONSTANT] datatype [NOT NULL] [:= |DEFAULT expr]
 标识符

变量类型

简单变量

​ 简单变量不包括任何组合，只能保存一个值。

• v_sal NUMBER(9,2) := 0;
• %TYPE 属性：v_ename emp.ename%TYPE;

--变量赋值
select sal into x from emp where empno = 7369;

​ 使用%TYPE 属性的好处：

• 在编程时，可以不去查询数据库中字段的数据类型
• 数据库中字段的数据类型可能被改变
• 为了和前面的变量的类型始终保持一致

复合（组合）变量

​ 一个复合变量可以存放多个值。

• %ROWTYPE属性：%ROWTYPE的前缀是数据库表名。RECORD中的域，与表的字段的名称和数据类型完全相同

操作符

语句

if

IF condition THEN
statements；
[ELSIF condition THEN
statements；]
[ELSE
statements；]
END IF；

loop

LOOP
语句体;
[EXIT | EXIT WHEN 条件;]
END LOOP；

​ 简单循环的特点，循环体至少执行一次.

​ 在使用LOOP语句时必须使用EXIT语句，强制循环结束，否则将死循环。

while

WHILE 条件 LOOP
语句体;
END LOOP;

for

FOR counter IN [REVERSE] start_range..end_range LOOP
语句体;
END LOOP;

​ REVERSE:正常计数器从小到大递增，使用REVERSE将使计数器从大到小递减。

PL/SQL与Oracle交互

SELECT语句

​ 必须使用INTO子句。

​ 查询必须并且只能返回一行。

​ 可以使用完整的SELECT 语法。

SELECT [DISTICT|ALL]{*|column[,column,...]}
    INTO (variable[,variable,...] |record)
    FROM {table|(sub-query)}[alias]
    [WHERE 子句]

DML语句

​ 通过使用DML 命令，可对数据库中表的数据实现下列操作：

• INSERT
• UPDATE
• DELETE

事务语句

• commit
• rollback

游标

​ 游标的作用就是用于临时存储从数据库中提取的数据。

声明游标

--在DECLARE部分按以下格式声明游标： 
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
	IS SELECT语句; 

--参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。
--如果定义了参数，则必须在打开游标时传递相应的实际参数。 

打开游标

--在可执行部分，按以下格式打开游标： 
OPEN 游标名[(实际参数1[，实际参数2...])]; 

--打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 

提取数据

​ 在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

​ 游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

• FETCH 游标名 INTO 变量名1[，变量名2…]; 第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
• FETCH 游标名 INTO 记录变量;

关闭游标

CLOSE 游标名; 
--显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

游标例子

游标属性：

• %ROWCOUNT 整型 获得FETCH语句返回的数据行数
• %FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假
• %NOTFOUND 布尔型 与%FOUND属性返回值相反
• %ISOPEN 布尔型 游标已经打开时值为真，否则为假

--使用loop遍历游标
declare
 cursor my is select * from dept;
 v_dept  my%rowtype; 
begin
    open my;
    loop
    	fetch my into v_dept;
    	exit when my%notfound;
      	dbms_output.put_line(v_dept.dname);    
    end loop;
    close my;
end;

--使用for in时不用手动打开和关闭游标。
declare
 cursor my is select * from dept;
 v_dept  my%rowtype; 
begin  
    for v_dept in my loop
       dbms_output.put_line(v_dept.dname);
    end loop;     
end; 

--使用带参光标cursor，查询10号部门的员工姓名和工资。
declare
    cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
    pename emp.ename%type;
    psal emp.sal%type; 
begin 
    open cemp(&deptno);
    loop
        fetch cemp into pename,psal;	 
        exit when cemp%notfound;
        dbms_output.put_line(pename||'的薪水是'||psal);
    end loop;
    close cemp;
end;

异常处理

​ PL/SQL用异常和异常处理器来实现错误处理。

EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
语句体1;
. . .
[WHEN exceptionN] THEN
语句体n
. . .]
[WHEN OTHERS THEN
语句体n+1
. . .]

​ 预定义异常：

• NO_DATA_FOUND --没有找到数据
• TOO_MANY_ROWS --找到多行数据
• INVALID_CURSOR --失效的游标
• ZERO_DIVIDE --除数为零
• DUP_VAL_ON_INDEX –唯一索引中插入了重复值

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		ROLLBACK;
		DBMS_OUTPUT.PUT_LINE(’没有50号部门记录 ’);
	WHEN TOO_MANY_ROWS THEN
		ROLLBACK;
		DBMS_OUTPUT.PUT_LINE(‘返回多条记录.’);
	WHEN OTHERS THEN
		ROLLBACK;
		DBMS_OUTPUT.PUT_LINE (’ 出现其他错误.’);