PL/SQL初级学习笔记
PLSQL的块
Plsql的块分为三种：匿名块、存储过程、函数。
匿名块declare：
begin
……………………….
end;
存储过程Procedure：
Procedure name is
begin
……………………
End;
函数function：
Function name return datatype is
Begin
………………………….
End;
声明变量语法：
变量名 变量类型（长度） ：= 变量初始值（可以没有）
比如：
Declare
v_hiredate date;
v_number number(2) := 10;
v_char varchar2(10) := ‘book’;
…………
也可以按照数据库的字段类型声明：
变量 table.colum_name%type
比如
Declare
v_name employees.last_name%type
…………
变量命名规则：
1、变量命名一般规则为v_表示一个变量，c_表示一个常量
2、变量命名时避免与数据库中的字段名相同
PLSQL使用sql语句：
将查询到的值赋予变量
DECLARE
v_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO v_last_name
FROM employees
WHERE employee_id = 100;
dbms_output.put_line(v_last_name);
END;
DECLARE
v_last_name employees.last_name%TYPE;
v_e_id employees.employee_id%TYPE := 100;
BEGIN
SELECT last_name
INTO v_last_name
FROM employees
WHERE employee_id = v_e_id;
dbms_output.put_line(v_last_name);
END;
需要注意的是，into的值只能赋予一个值，若where语句使用不当，导致出现多个值，则会报出多行的错误
PLSQL的控制语句
判断语句：
if 条件 then 结果;
elsif 条件 then 结果;
else 结果;
end if;
对null的判断：
And:
true and true = true,
false and false = false
true and false = false,
ture and null = null ,
false and null = false;
or:
true or true = true
true or false = true
false or false = false
false or null = null
true or null = true
循环语句：
Loop循环：
Loop
Statement1 (代码);
……
Exit (结束条件);
End loop;
While循环：
While condition(运行条件) loop
Statement(代码)
……
End loop;
For循环：
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_location_id + i), v_city, v_country_id );
END LOOP;
PLSQL自定义数据类型
Type typename is record
(name type.
name type
………………);
记录整张表的类型
变量 tablename%rowtype
游标
常用属性：
Sql%rowcount 受最近的SQL语句影响的行数
Sql%found 最近的SQL语句是否影响了一行以上的数据
Sql%notfound 最近的SQL语句是否未影响任何数据
Sql%isopen 对于隐式游标而言永远为FALSE
游标的使用：
Cursor cursorname is select....from,,,,,
Begin
Open cursorname 打开游标
Loop 遍历游标
Fetch cursorname 获取游标当前值
Into 变量 赋予变量
Exit when cursorname%notfound 当游标遍历完以后
End loop; 结束循环
End;
例子：
DECLARE
v_employee_id employees.employee_id%TYPE;
CURSOR emp_cur IS
SELECT employee_id
FROM employees;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur
INTO v_employee_id;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(v_employee_id);
END LOOP;
END;
使用For循环遍历游标：
For recordname in cursorname
Loop
If recordname.()………….
For update nowait: 锁定更新目标，防止锁定失败时出现长时间等待资源。
用法：
For update of 列名 nowait;
Where current of cursor:更新或删除数据时，快速指向游标里面的内容
使用方法
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000
THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF; END LOOP;
PLSQL例外处理
例外有两种：
- oracle内部错误抛出异常：预定义（有错误号和常量定义），非预定义（只有错误号）；
- 程序员自己显式抛出例外
例外捕获使用when………then……
例如：
When no_data_found then
Statement;
When too_many_rows then
Statement;
When others then
Statement;
………
End;
Oracle提供了两个内置函数来获取错误号和错误描述
Sqlcode、sqlerrm
使用方法：
When others then
Rollback;
Intsert into errors
Values(sqlcode,sqlerrm);
End;
非预定义的oracle错误
例子：
E_emp exception;
Pragma exception_init(e_emp,-2292);
Begin
Exception
When e_emp then
……
End;
Oracle内部错误号很多，想了解全部的Ora错误号，请参考：http://www.ora-code.com/
自定义错误：
例子：
E_emp exception;
Begin
Raise e_emp;
Exception
When e_emp then
Dbms_output.put_line();
End;
RAISE_APPLICATION_ERROR() 函数：对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么 也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的 地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述
例子：
RAISE_APPLICATION_ERROR(-20202,'This is not a valid manager');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.');
END;
PLSQL存储过程和函数
存储过程：
例子：
CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = p_id;
END raise_salary; /
过程的例外处理：
函数：
CREATE OR REPLACE FUNCTION get_sal(p_id IN employees.employee_id%TYPE) RETURN NUMBER IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_id;
RETURN v_salary;
END get_sal; /
跟存储过程唯一的区别就是函数有一个return值。
哪些SQL语句中可以使用用户自定义的函数：
- Select 语句
- Where条件和Having子句
- CONNECT BY, START WITH, ORDER BY, 和GROUP BY 子句
- INSERT的Values子句
- UPDATE的Set子句
想要在SQL语句中可以使用用户自定义的函数，那么这样的用户定义函数有哪些限制？
- 必须是个函数（不能是过程-Procedure）
- 只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）
- 只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内 存表）
- 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
- 在SQL中使用的函数，其函数体内部不能有DML语句。
- 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语
- 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)
PLSQL中的包PACKAGE
Package概念：按照业务逻辑、把相关的Func , Procedure 组织到一起，形成一个函数或者过程集合，这就是 一个Package，这是PLSQL中程序的一种组织形式。也是我们写PLSQL最主要的形式
Package组成：Package由包说明（package Specification)和包体(package body)两部分构成; 包说明 部分相当于C语言里面的.H文件，包体部分相当于 C语言里面针对.H实现的C文件。
Package好处：
1、模块化：一般把有相关性的函数和过程放到一个Package中；
2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利于分工合作；
3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数和过程相当于私有的。
4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。 这个特性也提醒我们不要去搞巨无霸的Package, 把你用到的任何 函数都写到一个Package中，这会导致严重的内存浪费。
5、重载：一个package 中可以定义同名、不同参数的函数或过程。
包说明声明公有变量以及共有过程等
包体则用来写具体的过程等
内置PLSQL工具包
动态sql：可以拼凑的sql语句，使用bdms_sql或者execute immediate语句来执行。
使用execute immediate时 into不能放在sql字符串内
程序中执行DDL：如果想在程序中执行DDL,可使用Oracle 内置包：DBMS_DDL
比如在程序中执行编译命令：
DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')；
比如在程序中执行数据收集命令：
DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')
Oracle数据库JOB：定义JOB 可以定期执行某个程序。
Oracle提供内置包 DBMS_JOB，可完成JOB的定义、提交、更改、停止、移除。
例子：：提交一个JOB 每隔1天执行一次：
DECLARE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (
job => jobno ,
what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);',
next_date => TRUNC(SYSDATE + 1),
interval => 'TRUNC(SYSDATE + 1)'
);
dbms_output.put_line(„job_no =„ || jobno )
COMMIT;
END;
例子：更改JOB的执行频率为：每4小时执行一次：
BEGIN
DBMS_JOB.CHANGE(1, NULL, TRUNC(SYSDATE+1)+6/24, ‟SYSDATE+4/24');
END;
如何找到自己提交的job号：
SELECT job, log_user, next_date, next_sec, broken, what
FROM DBA_JOBS;
数据库触发器
Trigger
时机：Before 或者 After 或 Instead of
事件：Insert 或 Update 或 Delete
对象：表名（或视图名）
类型：Row 或者 Statement级；
条件：满足特定Where条件才执行；
内容：通常是一段PLSQL块代码；
重点注意：
Instead of : 用Trigger的内容替换 事件本身的动作
Row级：SQL语句影响到的每一行都会引发Trigger
Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）
插入之前执行触发器例子：
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))
OR
(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.');
END IF;
END; /