PL/SQL

PL/SQL有点：

1.面向过程，基于事物

2.可重用性

3.安全控制

4.书写方便

5.优化网络传输，提高执行效率

6.全面支持SQL数据类型和函数，支持Oracle对象类型。

模块结构;

DECLARE

..

BEGIN

..

END;

变量作用：1.临时存储数据。2.操作存储的数值。3.重用。4.简化维护。

在PL/SQL中使用变量：1.在变量定义部分声明并初始化变量。2.在可执行部分为变量分配新值

3.通过参数为PL/SQL块提供传入值。4.通过输出型变量查看程序执行结构。

变量的类型：PL/SQL：标量类型（Scalar），复合类型（Composite），引用类型（Reference），大对象型（LOB --large objects）

非PL/SQL变量：绑定变量（Bind variables），宿主变量（host variables）

声明变量：identifier[CONSTANT] datatype [NOT NULL]  [:= DEFAULT expr];

Declare

v_hiredate  DATE;

v_deptno  NUMBER(2) NOT　NULL :=10;

v_location VARCHAR2(13) :='Atlanta';

c_comm CONSTANT NUMBER :=1400;

DEFAULT 关键字

基本标量类型：

VARCHAR2 NUMBERDATE CHARLONG LONG RAWBOOLEAW BINARY_INTEGERPLS_INTEGER;

v_job VARCHAR2(2);

v_count BINARY_INTEGER :=0;

v_total_sal NUMBER(9,2) :=0;

v_orderdate DATE := SYSDATE+7;

c_tax_rate CONSTANT NUMBER(3,2) :=8.25;

v_valid BOOLEAN NOT NULL :=TRUE;

PL/SQL程序块分类：

匿名块：一次定义，一次执行

命名块：可被重复调用     函数，过程，包，触发器

PL/SQL变量：

变量的用途：1.临时存储数据，2.操作存储的数值，3.重用，4.简化维护。

在PL/SQL中使用变量：

1.在变量定义部分声明并初始化变量，

2.在可执行部分为变量分配新值，

3.通过参数为PL/SQL块提供传入值，

4.通过输出型变量查看程序执行结果。

变量的类型：

PL/SQL变量：标准类型（Scalar），复合类型（Composite），引用类型（Reference），大对象型（LOB--large objects）。

非PL/SQL变量：绑定变量（Bind variables），宿主变量（host variables）。

声明变量：语法 identifier[CONSTANT] datatype[NOT NULL] [:= | DEFAULT expr];

例子：Declare  date   DATE;

指南：1.遵循命名习惯，2.为NOT NULL型变量赋初值，3.使用赋值符（：=）或DEFAULT保留字给变量赋值，4.一行上最多定义一个变量

命名规则：1.在不同的PL/SQL快中，两个变量可以同名，2.变量名不能与块中用到的表名或列名相同。

给变量赋值：

语法 identifier := expr;   date := "03-01-2011";

变量初始化与关键字：1.:=赋值符号，2.DEFAULT关键字，3.NOT NULL 属性。

标量数据类型：1.存放单个值，2.没有内部组成部分。

基本标量类型：VARCHAR2(maximum_length),NUMBER[(percision,scale)],DATE,

   CHAR[(maximum_length)],LONG,LONG RAW,BOOLEAN,

   BINARY_INTEGER,PLS_INTEGER.

%TYPE属性：1.用于定义变量的类型与（1）数据库表的列定义相同（2）另一个变量的定义相同。

2.用%TYPE后缀于（1）数据库表.列（2）前面定义过的变量名。

例子：v_name   emp.name%TYPE;  v_m  v_ba%TYPE := 10;

声明布尔型变量：1.布尔型变量只能去TRUE和FALSE，或者为空，2.布尔类型变量相互之间只能通过AND、OR和NOT进行运算，

3.布尔型变量的运算结果只能是TRUE、FALSE或NULL，4.数值型，字符型和日期型表达式都可以返回一个布尔值。

PL/SQL块语法指南：1.语法可以放在多个行上，2.语法单元可以用一个或者多个空格分开。

语法单元包括：1.有特殊意义的符号(如“+”)，2.各种标示符，3.字符和字符串，4.注释项。

标示符：1.最长30个字符，2.不能包含保留字，确要使用的要用双引号括起来，3.必须以字符开头，3.不能与数据库表名和列明相同。

例子：v_name := 'name';

注释：1.单行注释以双横线（--）开始，双横线后为注释内容。2.多行注释放在分隔符/*和*/之间。

PL/SQL中的SQL函数：

可用函数：1.单行数值函数，2.单行字符函数，3.数据类型转换函数，4.日期函数。

不可用函数：1.GREATEST,2.LEAST,3.DECODE,4.分组函数。

例子：v_mal := name || CHR(10) || address || CHR(10) || state || CHR(10) || zip;

name := LOWER(ename);

数据类型转换：1.将数据转换成兼容的数据类型，2.混用不同类型数据可能导致出错和影响执行性能，3.类型转换函数：TO_CHAR,TO_DATE,TO_NUMBER.

数据类型转换例子：str := USER || ': '||SYSDATE;  应该改为  str := USER || ': '|| TO_CHAR(SYSDATE);

块的嵌套与变量的作用域：1.块可以嵌套在任何放置可执行语句的地方，2.一个嵌套的块相当于一条语句，

3，异常处理段也可以包含嵌套块，4.变量的作用域指的是程序中可以引用变量的代码范围。

块的嵌套与变量的作用域：1.块中标识符可以在其嵌套的块中被引用，2.块不能引用在其嵌套块中定义标示符。

PL/SQL中的运算符：1.逻辑运算符，2.算数运算符，3.链接符(||)，4.括号（改变运算次序），5.幂运算符（**）

递增循环变量： count := count + 1;  

设置布尔标志：e_value = (v_n1 = v_n2);

验证是否空：v_value = (v_em IS NOT NULL);

使用绑定变量：1.在PL/SQL中引用绑定变量，必须在它前面加是冒号(:),

例子：  DECLARE  v_val  em.sal%TYPE;

BEGIN 

SELECT sal

INTO v_sal

FROM em

WHERE em_no = 11111;

:salary := v_sal;

END;

程序编写指南：1.注释，2.采用固定的大小写习惯，3.采用固定的标识符合对象命名习惯，4.采用缩进方式书写程序。

命名习惯：

Variable  v_name

Constant  c_name

Cursor  name_cursor

Exception  e_name

Table Type name_table_type

Record Type name_record_type

Record name_record

SQL*Plus sub stitution parameter p_name

SQL *Plus global variable g_name

缩进编码。

PL/SQL中的SELECT语句：

使用SELECT语句从数据库中检索数据 SELECT select_list INTO(variable_name....) FROM table WHERE condition;

PL/SQL中的SELECT语句中INTO子句：DECLARE v_name  VARCHAR2(...); BEGIN SELECT xxx, xxx INTO xxx , xxx, FROM table WHERE name = 'SALES'; END;

插入语句：DECLARE v_name em.emp %TYPE; BEGIN SELECT em_seq.NEXTVAL INTO v_em FROM dual; INSERT INTO em(xx,xx) END;

修改数据：DECLARE v_in em.sal%TYPE := 300; BEGIN UPDATE emp SET sal = sal + v_sal WHERE job = "xxx"; END;

删除数据：DECLARE v_depno em.depno % TYPE := 10;BEGIN DELETE FROM em WHERE depno = v_depno; END;

从第一个DML语句或者COMMIT和ROLLBACK语句开始一个事务，使用COMMIT或ROLLBACK语句显示结束一个事务。

SQL游标：1.游标是一个私有的SQL工作区，2.有两种游标（1）隐式游标（2）显示游标，3.Oracle服务器才有隐式游标对SQL

语句进行分析并执行它。2.显示游标是由程序员进行定义并使用的。

SQL游标的属性：使用SQL游标的属性，可以测试SQL语句的输出情况。

SQL%ROWCOUNT:当前已经从游标中获取的数据行数。

SQL%FOUND:最近的SQL是否从游标中获取得到了数据。

SQL%NOTFOUND:最近的SQL是否从游标中获取到了数据，未获取为TRUE，否则为FALSE。

SQL%ISOPEN:游标的当前状态，打开状态为TRUE，否则为FALSE。

例子：VARIABLE  rows_del 

DECLARE

v_or NUMBER ：= 100;

WHERE or = v_or;

rows_del := SQL%ROWCOUNT || 'rows deleted.');

END:

PRINT rows_del

控制PL/SQL执行流：1.使用条件IF语句和循环控制结构改变语句的逻辑执行顺序

2.条件IF语句：IF-THEN-END IF,IF-THEN-ELSE-END IF,IF-THEN-ELSEIF-END IF

语法： IF condition THEN 

statements;

[ELSIF condition THEN

statements;]

[ELSE

statements;]

END IF;

例子：IF v_value = 'xxx' THEN

v_xxx := 'xxxx';

...

END IF;

设置逻辑条件：1.对于null值只能用IS NULL运算符进行测试，

2.连接运算中的null值会被当成空字符串，

3.其它任何含null的表达式都会得到NULL值。

循环控制：LOOP语句。基本循环，FOR循环，WHILE循环。

基本循环语句LOOP：

LOOP

statement1;

....

EXIT [WHEN condition]; --EXIT statement

END LOOP;

基本循环语句LOOP：

DECLARE 

v_or item.or % TYPE := 100;

v_counter NUMBER(2) := 1;

BEGIN

LOOP

INSERT INTO item(or, itemid)

VALUES(v_or, v_counter);

v_counter := v_counter + 1;

EXIT WHEN v_counter > 10;

END;

FOR循环：

FOR counter in [xxxx]

statement1;

END LOOP;

1.使用FOR循环避免对循环变量的反复测试，2.循环变量不用定义，它的定义是隐含进行的。

指南：1.循环变量只在循环内有定义，在循环外是不能引用的。2.循环中可以引用循环变量的值。3.不能给循环变量赋值。

例子：

DECLARE

v_or item.or%TYPE := 10;

BEGIN 

FOR i IN 1...10 LOOP

INSERT INTO item(or, itemid)

VALUES(v_or,  i);

END LOOP;

END;

WHILE循环：只有条件为真时，语句才会被执行，如果第一次执行时条件就为假则语句将一次也不执行。

例子：

DECLARE

v_or  NUMBER(8) := 1;

v_r NUMBER(7,2) := 0;

BEGIN

WHILE v_r  < &p_item LOOP

...

v_r := v_q * &p_num;

END LOOP;