plsql

1、简介

          PLSQL是oracle对sql扩展的过程化语言

          pl/sql联合了sql的易用性、灵活性和程序化的结构化编程语言的功能

         有些复杂的业务流程要求相应的程序来描述，sql不能实现

         oracle是c/s架构的数据库，平常的sql，需要向服务器发出请求，服务器才进行编译执行，而用pl/sql写成的procedure、function、package，是在服务器端编译的，所以不需要网络传输

无名块（匿名块）：也就是没有命名的 PL/SQL 块，它可以是嵌入某一个应用之中的一个 PL/SQL 块。只保存在客户端，私有，可以共享

命名块：保存在服务器端 ，在user_source数据字典中，包含以下对象
存储过程/函数：也就是命名了的 PL/SQL 块，它可以接收参数，并可以重复地被调用。
包：命名了的 PL/SQL 块，由一组相关的过程、函数，和标识符组成。
数据库触发器：是一个与具体表相关联的存储 PL/SQL 程序。每当一个 SQL 操作影响到该数据库表时，系统就自动执行相应的数据库触发器。每个表最多可以有 12 个触发器。

2、注释

 -- 只能在一行注释。

 /* */ 来加一行或多行注释。

3、标志符号的命名规范

1. 当定义变量时，建议用 v_作为前缀 v_sal
2. 当定义常量时，建议用 c_作为前缀 c_rate
3. 当定义游标时，建议用_cursor 作为后缀 emp_cursor
4. 当定义例外时，建议用 e_作为前缀 e_error
5. 定义参数时，用 p_作为前缀
6. 定义记录类型 ，emp_record_type
7. 定义复合数据类型用，cp_作为前缀

4、PL/SQL 块（匿名块）结构

匿名块（anonymous）动态生成，只执行一次。
带名块（named）是带有标签的匿名块，动态生成，只执行一次。
子程序（subprogram）是存储在数据库内部的过程、函数和包。可多次执行。
触发器（trigger）是存储在数据库内部的带名块，可多次执行。
由触发事件（triggering event）来触发。

块(BLOCK)是 PL/SQL 的基本程序单元，编写 PL/SQL 程序实际上就是编写 PL/SQL 块，要完成相对简单的应用功能，可能只需要编写一个 PL/SQL 块，但是如果想要实现复杂的功能，可能需要在一个 PL/SQL 块中嵌套其它的 PL/SQL 块。

一个基本的 PL/SQL 块由三部分组成：定义部分，可执行部分以及例外部分。

定义部分：定义将在可执行部分中调用的所有变量，常量，游标，和用户自定义的例外处理，这部分可以没有。
可执行部分：包括对数据中进行操作的 SQL 语句，以及对块中进行给织、控制的 PL/SQL 语句。这部分必须存在。
例外（异常）处理部分：对可执行部分中的语句，在执行过程中出错或出现非正常现象时所做出的处理。这部分可以没有。

declare/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ ，declare 里面定义的局部变量用分号隔开；
begin/*执行部分——要执行的 pl/sql 语句和 sql 语句*/
exception/*例外处理部分——处理运行的各种错误*/
end;

---constant(常量)  ,begin 后面的null代表不执行任何语句

---内部块中的变量不能被外部块使用，去掉注释则报错

---如果内外部块名字相同，则使用《》打标签，使用时语法outer.v_x

 ---%type emp表列是什么类型，参数就是什么类型

 ---record 复合类型

 ---一列数据

eg：
set serveroutput on;-- 打开输出选项
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
-- 执行部分
SELECT ename,sal into v_ename,v_sal from scott.emp WHERE empno=&aa;     ---将emp表中的值传入到变量中
-- 在控制台显示用户名
dbms_output.put_line('用户名是：'||v_ename||' 工资：'||v_sal);
-- 异常处理
exception
WHEN no_data_found then
dbms_output.put_line('朋友，你的编号输入有误！');
end;

5、PL/SQL 的流程控制语句, 包括如下三类 ：

条件语句: IF 语句
循环语句: LOOP 语句, EXIT 语句
顺序语句: GOTO 语句, NULL 语句

5.1 if

pl/sql 中提供了三种条件分支语句
1 if—then—end if ;
2 if – then – else —end if;
3 if – then– elsif –then— end if;

declare
v_sal scott.emp.sal%type := &salary ;
begin
if v_sal < 1500 then
dbms_output.put_line('太少了');
elsif (v_sal < 3000 and v_sal >=1500) then
dbms_output.put_line('还成');
elsif v_sal < 5000 then
dbms_output.put_line('挺好');
else
dbms_output.put_line('真的吗？');
if v_sal <8000 then
dbms_output.put_line('什么工作啊？');
else
dbms_output.put_line('玩我的吧？');
end if;
end if;
end;

5.2 loop

LOOP
要执行的语句;
EXIT WHEN <条件语句> /* 条件满足，退出循环语句 */
END LOOP;

举例：

SET SERVEROUTPUT ON

DECLARE
V_NUM NUMBER(2) := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(V_NUM);
V_NUM := V_NUM + 1;
EXIT WHEN V_NUM =5;
END LOOP;
END;
/

5.3 while

WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;

举例：

SET SERVEROUTPUT ON
DECLARE
V_SUM NUMBER(3) := 0;
V_NUM NUMBER(2) := 1;
BEGIN
WHILE V_NUM <=10 LOOP
DBMS_OUTPUT.PUT_LINE(V_NUM);
V_SUM := V_SUM + V_NUM;
V_NUM := V_NUM + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM IS: ' || V_SUM);
END;
/

5.4 for循环

SET SERVEROUTPUT ON
DECLARE
V_SUM NUMBER(4) := 0 ;
BEGIN
FOR X IN 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE(X);
V_SUM := V_SUM + X;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_SUM);
END;
/

6、游标

在open时数据调到内存中使用。获取一次，内存中就少一行。
游标获取后应该使用相关变量接收游标的值，类似select ...into一样

显示游标使用流程：
1、声明 declare 2、打开open 3、获取 fetch 4、关闭 close

7、异常处理

---异常处理部分一般放在 PL/SQL 程序体的后半部,结构为 :
EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >

oracle 将例外分为预定义例外，非预定义例外和自定义例外三种。

 (1)预定义异常 ( Predefined )

 

 

（2）自定义异常示例

 （3）抛出异常（raise）

 

 (4）存过中捕获异常的行号(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)

DECLARE
N1 VARCHAR(10);
V_ERROR VARCHAR(4000);
BEGIN
SELECT ENAME INTO N1 FROM SCOTT.EMP WHERE EMPNO = 2;
EXCEPTION
WHEN OTHERS THEN
V_ERROR := SQLCODE || ',' || SQLERRM || CHR(13) ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
P_INSERT_LOG(V_ERROR);
END;
/