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;