PL/SQL 基本知识

一 PL/SQL块的结构
PL/SQL 的基本程序单元是块，由三部分组成：
定义部分 包含常量，变量，游标，例外，复杂数据类型等，
执行部分 执行PL/SQL 和 SQL语句
例外部分 由于处理运行是出现的错误
基本结构如下：
DECLARE (可选)
// 定义部分
BEGIN
// 执行部分
EXCEPTION (可选)
//例外处理
END;
实例：
1:只包含执行部分
set serveroutput on
BEGIN
dbms_output.put_line('Hello,everyone!');
END;
/
2: 三部分齐全
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=&no;
dbms_output.put_line('雇员名称：'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('请输入正确的员工号!');
END;
/


二 PL/SQL块分类
1 匿名块
主要是嵌套到程序中(pro*c)和在交互式环境中使用,上面连个实例就是匿名块
2 命名块
与匿名块相识，只是在块前面使用<<>>加以标记
3 子程序
(1)过程
可以指定输入和输出参数
实例：
CREATE PROCEDURE update_sal(name VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal WHERE lower(ename)=lower(name);
END;
/
(2)函数
用于返回特定的数据，在函数头部必须包含RETURN字句
CREATE FUNCTION annual_income(name VARCHAR2)
RETURN NUMBER IS
annual_salary NUMBER(7,2);
BEGIN
SELECT sal*12+nvl(comm,0) INTO annual_salary
FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
/
(3)包
用于逻辑组合相关的过程和函数，由包规范和包体组成。

4 触发器
隐含执行的存储过程。触发事件包含（INSERT,UPDATE,DELETE）
CREATE TRIGGER update csacade
AFTER UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;
/


三 PL/SQL 数据类型
1 标量类型
2 复合变量
(1)PL/SQL 记录
DECLARE
TYPE emp_record_type IS RECORD (
name emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename,sal,job INTO emp_record
FROM emp WHERE empno=7788;
dbms_output.put_line('雇员名：'||emp_record.name);
END;
/
(1)PL/SQL 表
类似高级语言中的数组，下标可以为负的
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp WHERE empno=7788;
dbms_output.put_line('雇员名：'||ename_table(-1));
END;
/

3 REF CURSOR
静态游标：在定义显示游标时指定相应的select语句
动态游标：在定义显示游标时不需指定相应的select语句，在打开游标时指定select语句
DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor FOR
SELECT ename,sal FROM emp WHERE deptno=10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
/


四 标识符命名规则
1 当定义变量时，建议使用V_作为前缀。
2 当定义常量时，建议使用C_作为前缀。
3 当定义游标时，建议使用_cursor作为后缀。
4 当定义例外时，建议使用e_作为前缀。
5 当定PL/SQL表类型时，建议使用_table_type作为后缀。
6 当定PL/SQL表变量时，建议使用_table作为后缀。
7 当定PL/SQL记录类型时，建议使用_record_type作为后缀。
8 当定PL/SQL记录变量时，建议使用_record作为后缀。