PL/SQL基础

块(Block)是PL/SQL的基本程序单元，编写PL/SQL程序实际就是编写PL/SQL程序。
编写PL/SQL应用模块，块的嵌套层次没有限制。

1.PL/SQL块结构：
DECLARE
/*
*定义部分——定义常量、变量、复杂数据类型、游标、异常
*/
BEGIN
/*
*执行部分——PL/SQL语句和SQL语句
*/
EXCEPTION
/*
*异常执行部分——处理运行错误
*/
END;

DECLARE和EXCEPTION部分是可选的
例：
BEGIN
dbms_output.put_line(‘hello,everyone!’);
END;
/

2.PL/SQL块分类
2.1匿名块（没有名称的PL/SQL块），既可以内嵌到应用程序中，也可以在交互式环境（SQL*Plus）中直接使用。
2.2命名块（具有特定名称标识的PL/SQL块，命名块与匿名块类似，只不过在PL/SQL前使用<<>> 加以标记。）
2.3子程序（包括过程、函数和包）当开发PL/SQL子程序时，既可以开发客户端的子程序，也可以开发服务器端的子程序。客户端子程序主要用在Develop中，而服务器端子程序可以用在任何应用程序中。通过将商业逻辑和企业规则集成到PL/SQL子程序中，可以简化客户端程序的开发和维护。
1）过程
过程用于执行特定操作。当执行过程时，既可以指定输入参数（IN）也可以指定输出参数（OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过 使用输出参数，可以将执行部分的数据传递到应用环境。
例：
CREATE PROCEDURE update_sal(NAME VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal = newal
WHERE lower(ename) = lower(NAME);
END;
/
2）函数
函数用于返回特定数据。函数头部必须包含RETURN子句，函数体内必须要包含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）包
包用于逻辑组合相关的过程和函数，它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。
建立包规范：
CREATE PACKAGE emp_pkg IS
PROCEDURE update_sal(NAME VARCHAR2,newsal NUMBER);
FUNCTION annual_income(NAME VARCHAR2) RETURN NUMBER;
END;
/

建立包体：
CREATE PACKAGE BODY emp_pkg IS
PROCEDURE update_sal(NAME VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal
WHERE lower(ename)=lower(NAME);
END;

   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;

END;
/

当调用包的过程和函数时，在过程和函数名之前必须要带有包名作为前缀（包名.子程序名），而如果要访问其他方案的包，还必须要加方案名作为前缀（方案名.包名.子程序名）

4）触发器
触发器是指隐含执行的存储过程。当定义触发器时，必须要指定触发事件以及触发操作，常用的触发事件包括INSERT,UPDATE和DELETE语句，而触发操作实际是一个PL/SQL块。示例如下：
CREATE TRIGGER update_cascade
AFTER UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno
WHERE deptno=:old.deptno;
END;
/

3定义并使用变量
编写PL/SQL程序时，若临时存储数值，必须要定义变量和常量；若在应用环境和子程序之间传递数值，必须要为子程序指定参数。而在PL/SQL程序中定义变量、常量和参数时，则必须要为它们指定PL/SQL数据类型。在编写PL/SQL程序时，可以使用标量（Scalar）类型、复合（Composite）类型、参照（Reference）类型和LOB（Large Object）类型等四种类型。

3.1标量变量
标量变量指只能存放单个数值的变量。当编写PL/SQL程序时，最常用的变量就是标量变量。
当定义标量变量时，必须要指定标量数据类型。
标量数据类型包括数字类型、字符类型、日期类型和布尔类型，每种类型又包含相应的子类型。

常用标量标量变量
1）VARCHAR2(n)
该数据类型用于定义可变长度的字符串，n用于指定字符串的最大长度，最大值为32767字节。长度必须定义。需要注意，当在PL/SQL块中使用该数据类型操纵VARCHAR2表列时，其数值的长度不应该超过4000字节。
2）CHAR(n)
固定长度字符串，最大长度为32767字节。若定义时没有指定n，则默认值为1。需要注意，挡在PL/SQL块中使用该数据类型操纵CHAR表列时，其数值的长度不应该超过2000字节。
3）NUMBER(p,s)
定义固定长度的整数和浮点数，p表示精度，用于指定数字的总位数；s表示标度，用于指定小数点后的数字位数。
4）DATE
定义日期和时间数据，其数据长度为固定长度（7字节），需注意，当给DATE变量赋值时，数据必须要与日期格式和日期语言匹配。
5）TIMESTAMP
给TIMESTAMP变量赋值的方法与给DATE变量赋值的方法完全相同。但当显示TIMESTAMP变量数据时，不仅会显示日期，而且还会显示时间和上下午标记。
6）LONG和LONG RAW
LONG数据类型用于定义变长字符串，类似于VARCHAR2数据类型，但其字符串的最大长度为32760字节；LONG RAW数据类型用于定义变长的二进制数据，其数据最大长度为32760字节。
7）BOOLEAN
该数据类型用于定义布尔变量，其变量的值为TRUE、FALSE或NULL。需注意该数据类型是PL/SQL数据类型，表列不能采用该数据类型
8）BINARY_INTEGER
定义整数，在-2147483647和2147483647之间。该数据类型是PL/SQL数据类型，表列不能采用该数据类型。
9）BINARY_FLOAT和BINARY_DOUBLE
单精度的浮点数和双精度的浮点数。使用时应带有后缀f和d

定义标量变量
1）语法
indentifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]
2）示例
v_ename VARCHAR2(10);
v_sal NUMBER(6,2);
v_balance BINARY_FLOAT;
c_tax_rate CONSTANT NUMBER(3,2) :=5.5;
v_hiredate DATE;
v_valid BOOLEAN NOT NULL DEFAULT FALSE;

当使用%TYPE属性定义变量时，它会按照数据库列或其他变量来确定新变量的类型和长度。

复合变量
PL/SQL记录
在定义部分定义记录类型和记录变量，在执行部分引用该记录变量。示例如下：
DECLARE
TYPE emp_record_type IS RECORD (
NAME emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE
);
emp_record emp_recoed_type;
BEGIN
SELECT ename,sal,job INTO emp_record
FROM emp WHERE empno=7788;
dbms_output.put_line(‘雇员名：’||emp_record_name);
END;
/

PL/SQL表
PL/SQL表类似于高级语言中的数组。但其下标没有上下限。当使用PL/SQL表时，必须首先在定义部分定义PL/SQL表类型和PL/SQL表变量，然后在执行部分中引用该PL/SQL表变量。示例如下：
DECLARE
TYPE ename_table_type IS TABLE OF emp.name%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;
/

嵌套表
下标不能为负值，嵌套表可以作为表列的数据类型，而PL/SQL表不能作为表列的数据类型。示例如下：
CREATE OR REPLACE TYPE emp_type AS OBJECT(
NAME VARCHAR2(10),salary NUMBER(6,2),
hiredate DATE);
/
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
/
当使用嵌套表类型作为表列时，必须要为其指定专门的存储表。示例如下：
CREATE TABLE department (
deptno NUMBER(2),dname VARCHAR2(10),
employee emp_array
) NESTED TABLE empolyee store AS employee;

VARRAY(变长数组)
可作为表列和对象类型属性的数据类型。VARRAY的元素个数是有限制的。示例如下：
CREATE TYPE artical_type AS OBJECT (
title VARCHAR2(30),pubdate DATE
);
/
CREATE TYPE artical_array IS VARRAY(20) OF artical_type;
建立VARRAY类型之后，可以在表列或对象属性中将其作为用户自定义数据类型来引用。示例如下：
CREATE TYPE author (
id NUMBER(6), name VARCHAR2(10),artical artical_array);
嵌套表列数据需要存储在专门的存储表中，而VARRAY数据则与其他列数据一起存放在表段中。

参照变量
存放数值指针的变量，可使得应用程序共享相同对象，从而降低占用空间。

1）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_name,v_sal;
EXIT WHEN emp_currsor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
END;
/

2）REF obj_type
使用REF引用对象类型，可以共享相同对象。REF实际是指向对象实例的指针。
CREATE OR REPLACE TYPE home_type AS OBJECT (
street VARCHAR2(50),city VARCHAR2(20),
state VARCHAR2(20),zipcode VARCHAR2(6),
owner VARCHAR2(10)
);
/
CREATE TABLE homes OF home_type;

INSERT INTO homes VALUES(‘八卦三路’,’深圳’,’广东’,’510000’,’张三’);
INSERT INTO homes VALUES(‘八卦三路’,’深圳’,’广东’,’510000’,’李四’);
COMMIT;
–为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。
CREATE TABLE person (
id NUMBER(6) primary key,
NAME VARCHAR2(10),addr REF home_type
);
INSERT INTO person SELECT 1,’张三三’,REF(p)
FROM homes p WHERE p.owner=’张三’;
INSERT INTO person SELECT 2,’王敏’,REF(p)
FROM homes p WHERE p.owner=’张三’;
INSERT INTO person SELECT 3,’张三四’,REF(p)
FROM homes p WHERE p.owner=’张三’;
COMMIT;

LOB变量
用于存储大批量数据的变量，分为内部LOB和外部LOB。内部LOB包括CLOB，BLOB和NCLOB三种类型，它们的数据被存储在数据库中，并且支持事务操作（提交、回退、保存点）。外部LOB只有BFILE一种类型，该类型的数据被存储在OS文件中，并且不支持事务操作。
CLOB/NCLOB用于存储大批量字符数据，BLOB用于存储大批量二进制数据，而BFILE则存储指向OS文件的指针。

当在SQL*Plus或应用程序中与PL/SQL块之间进行数据交互时，需要使用SQL*Plus或应用程序变量来完成。当在PL/SQL块中引用非PL/SQL变量时，必须要在非PL/SQL变量前加冒号（‘：’）。

科学计数法3e3，5E6

幂操作符6*10**3

字符文本：单引号引住的单个字符。
字符串文本：两个或两个以上字符组成的字符值。