oracle 11g PL/SQL Programming学习二

sjk2013

浏览: 2182728 次

最近访客更多访客>>

BDQN磊

w11h22j33

devcang

hedgehog12

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (3465)

社区版块

存档分类

2014-05 ( 13)
2014-04 ( 74)
2014-03 ( 72)
更多存档...

第二章 PL/SQL基础
PL/SQL语言和SQL一样是一种大小写不敏感的语言.
1.PL/SQL块结构(Oracle PL/SQL Block Structure)
PL/SQL是基于结构化程序的模型理念开发的.它有静态数据类型、模块性、异常管理.
PL/SQL支持两种类型的程序:有名块和匿名块
例:SET SERVEROUTPUT ON SIZE 1000000 --如使用了本博客的SQL*PLUS脚本设置,可省略
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World.');
END;
SQL*PLUS支持替换变量(&)

23:50:29 hr@orcl> DECLARE
23:50:31   2    MY_VAR VARCHAR2(30);
23:50:31   3  BEGIN
23:50:31   4    MY_VAR := '&input';--赋值操作符(:=)
23:50:31   5    DBMS_OUTPUT.PUT_LINE('Hello ' || MY_VAR);
23:50:31   6  END;
23:50:31   7  /
输入 input 的值:  cryking
Hello cryking

PL/SQL 过程已成功完成。

SQLPLUS也可以直接执行SQL脚本文件(使用@)

23:52:49 hr@orcl> @123.SQL
输入 input 的值:  CRYKING
Hello CRYKING

异常管理方面,例:

23:57:56 hr@orcl> DECLARE
23:58:12   2    MY_VAR VARCHAR2(10);
23:58:12   3  BEGIN
23:58:12   4    MY_VAR := '&input';
23:58:12   5    DBMS_OUTPUT.PUT_LINE('Hello ' || MY_VAR);
23:58:12   6  EXCEPTION
23:58:12   7    WHEN OTHERS THEN
23:58:12   8      DBMS_OUTPUT.PUT_LINE(SQLERRM);
23:58:12   9  END;
23:58:12  10  /
输入 input 的值:  AAAAAAAAAAAAAAAAAA
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小

PL/SQL 过程已成功完成。

匿名块可以嵌套匿名块,有名块也可以嵌套匿名块.最外层的程序块控制整个程序流。
注意:声明模块的异常,本块是无法捕捉到的.

2.变量、赋值和操作符(Variables, Assignments, and Operators)
PL/SQL支持所有的SQL数据类型及其子类型,还支持标量和复杂变量.
变量以字母开头,可以包含字母、数字、$、_、#等.变量有其作用域范围.
声明部分没有显示赋值的变量,都默认为NULL。

00:09:35 hr@orcl> DECLARE
00:09:49   2    VARIABLE_NAME  NUMBER;
00:09:49   3    VARIABLE_NAME1 VARCHAR2(10);
00:09:49   4  BEGIN
00:09:49   5    IF VARIABLE_NAME IS NULL THEN
00:09:49   6      DBMS_OUTPUT.PUT_LINE('VARIABLE_NAME is NULL!');
00:09:49   7    END IF;
00:09:49   8    IF VARIABLE_NAME1 IS NULL THEN
00:09:49   9      DBMS_OUTPUT.PUT_LINE('VARIABLE_NAME1 is NULL!');
00:09:49  10    END IF;
00:09:49  11  END;
00:09:49  12  /
VARIABLE_NAME is NULL!
VARIABLE_NAME1 is NULL!

PL/SQL 过程已成功完成。

oracle赋值执行很多隐式的转换操作.(可能会丢失精度)
如:

00:13:18 hr@orcl> DECLARE
00:13:20   2    VARIABLE_NAME  NUMBER;
00:13:20   3    I_VAR INTEGER;
00:13:20   4  BEGIN
00:13:20   5    VARIABLE_NAME:=2.3234;
00:13:20   6    I_VAR:=VARIABLE_NAME;
00:13:20   7    DBMS_OUTPUT.PUT_LINE(I_VAR);--输出为2,丢失了小数精度0.3234
00:13:20   8  END;
00:13:20   9  /
2

PL/SQL 过程已成功完成。

PLSQL隐式类型转换汇总图如下:

PL/SQL同样也支持等号操作符(=),用来判断两个值是否相等,也支持一般的逻辑操作符,如(>, >=, <,<=,<>, !=, ~=,^=).
你可以在声明部分定义游标语句,游标可以把你视图或表中的数据带到PL/SQL程序中来进行处理.
游标可以有0个或多个参数.

3.控制结构
PL/SQL支持IF,ELSIF,ELSE,CASE等控制语句.
条件结构
IF [NOT] left_operand1 >|=|< right_operand1 [[AND|OR]
[NOT] left_operand2 = right_operand2 [[AND|OR]
[NOT] boolean_operand ]] THEN
NULL;
ELSE
NULL;
END IF;
或
CASE [ TRUE | [selector_variable]]
WHEN [criterion1 | expression1] THEN
criterion1_statements;
WHEN [criterion2 | expression2] THEN
criterion2_statements;
WHEN [criterion(n+1) | expression(n+1)] THEN
criterion(n+1)_statements;
ELSE
block_statements;
END CASE;

注意NULL既不是TRUE也不是FALSE,所以建议变量在可能为NULL时,使用NVL函数先进行转换,再判断.
如:

00:24:28 hr@orcl> DECLARE
00:24:29   2    -- Define a Boolean variable.
00:24:29   3    MY_VAR BOOLEAN;
00:24:29   4  BEGIN
00:24:29   5    -- Use an NVL function to substitute a value for evaluation.
00:24:29   6    IF NOT NVL(MY_VAR, FALSE) THEN
00:24:29   7      DBMS_OUTPUT.PUT_LINE('This should happen!');
00:24:29   8    ELSE
00:24:29   9      DBMS_OUTPUT.PUT_LINE('This can''t happen!');--注意其中有转义字符'
00:24:29  10    END IF;
00:24:29  11  END;
00:24:29  12  /
This should happen!

PL/SQL 过程已成功完成。

00:30:44 hr@orcl> BEGIN
00:30:46   2    CASE nvl(NULL,FALSE)--找到为FALSE的
00:30:46   3      WHEN (1 > 3) THEN
00:30:46   4        DBMS_OUTPUT.PUT_LINE('One is greater than three.');
00:30:46   5      WHEN (3 < 5) THEN
00:30:46   6        DBMS_OUTPUT.PUT_LINE('Three is less than five.');
00:30:46   7      WHEN (1 = 2) THEN
00:30:46   8        DBMS_OUTPUT.PUT_LINE('One equals two.');
00:30:46   9      ELSE
00:30:46  10        DBMS_OUTPUT.PUT_LINE('Nothing worked.');
00:30:46  11    END CASE;
00:30:46  12  END;
00:30:46  13  /
One is greater than three.

PL/SQL 过程已成功完成。

4.循环结构
PL/SQL支持FOR, SIMPLE,WHILE,LOOP等循环.循环通常和游标结合来处理问题.
1.for循环结构
FOR支持数字和游标的循环.
数字:
FOR i IN starting_number..ending_number LOOP
statement;
END LOOP;
如:

00:35:05 hr@orcl> BEGIN
00:35:07   2    FOR I IN 1 .. 10 LOOP
00:35:07   3      DBMS_OUTPUT.PUT_LINE('The index value is [' || I || ']');
00:35:07   4    END LOOP;
00:35:07   5  END;
00:35:07   6  /
The index value is [1]
The index value is [2]
The index value is [3]
The index value is [4]
The index value is [5]
The index value is [6]
The index value is [7]
The index value is [8]
The index value is [9]
The index value is [10]

PL/SQL 过程已成功完成。

游标的:
FOR i IN {cursor_name[(parameter1,parameter(n+1))] | (sql_statement)} LOOP
statement;
END LOOP;
如:
--显示游标
DECLARE
CURSOR C IS
SELECT ITEM_TITLE FROM ITEM;
BEGIN
FOR I IN C LOOP
DBMS_OUTPUT.PUT_LINE('The title is [' || I.ITEM_TITLE || ']');
END LOOP;
END;
--隐式游标
BEGIN
FOR I IN (SELECT ITEM_TITLE FROM ITEM) LOOP
DBMS_OUTPUT.PUT_LINE('The title is [' || I.ITEM_TITLE || ']');
END LOOP;
END;

2.单循环结构(LOOP)
OPEN cursor_name [(parameter1,parameter(n+1))];
LOOP
FETCH cursor_name
INTO row_structure_variable | column_variable1 [,column_variable(n+1)];
EXIT WHEN cursor_name%NOTFOUND;
statement;
END LOOP;
CLOSE cursor_name;

Oracle提供6种游标属性:%FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT,SQL%BULK_ROWCOUNT,SQL%BULK_EXCEPTIONS
3.while循环
OPEN cursor_name [(parameter1,parameter(n+1))];
WHILE condition LOOP
FETCH cursor_name
INTO row_structure_variable | column_variable1 [,column_variable(n+1)];
EXIT WHEN cursor_name%NOTFOUND;
statement;
END LOOP;
CLOSE cursor_name;
例:

15:01:12 SCOTT@orcl> DECLARE
15:01:14   2    v_name emp.ename%TYPE;
15:01:14   3    CURSOR c IS
15:01:14   4      SELECT ename FROM emp where rownum<5;
15:01:14   5  BEGIN
15:01:14   6    OPEN c;
15:01:14   7    WHILE c%ISOPEN
15:01:14   8    LOOP
15:01:14   9      FETCH c
15:01:14  10        INTO v_name;
15:01:14  11      IF c%NOTFOUND THEN --退出循序的条件,必须要,否则报错
15:01:14  12        CLOSE c;
15:01:14  13      END IF;
15:01:14  14      dbms_output.put_line('The ename is [' || v_name || ']');
15:01:14  15    END LOOP;
15:01:14  16  END;
15:01:14  17  /
The ename is [test110]
The ename is [test111]
The ename is [SMITH]
The ename is [ALLEN]
The ename is [ALLEN]

PL/SQL 过程已成功完成。

5.函数、存储、包
Oracle用命名空间列表维护所有的表名、视图名、存储名、函数名等.
1.函数
函数原型:
FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )]]]
RETURN [ sql_data_type | plsql_data_type ]
[ AUTHID [ DEFINER | CURRENT_USER ]]
[ DETERMINISTIC | PARALLEL_ENABLED ]
[ PIPELINED ]
[ RESULT_CACHE [ RELIES ON table_name ]] IS
declaration_statements
BEGIN
execution_statements
RETURN variable;
[EXCEPTION]
exception_handling_statements
END [function_name];
函数必须返回一个值.
如:
CREATE OR REPLACE FUNCTION join_strings
(
string1 VARCHAR2
,string2 VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN string1 || ' ' || string2 || '.';
END;

2.存储过程
PROCEDURE procedure_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )]]]
[ AUTHID DEFINER | CURRENT_USER ] IS
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END [procedure_name];
存储过程可以没有形参.形参可以是传值或者传引用类型的.传引用的还分IN和OUT模式.

--传引用的IN模式的变量存储中不能给其赋值(缺省就是传引用的)  
15:11:53 SCOTT@orcl> CREATE OR REPLACE PROCEDURE format_string(string_in  VARCHAR2) IS
15:12:21   2  BEGIN
15:12:21   3    string_in := '[' || string_in || ']';
15:12:21   4  END;
15:12:21   5  /

警告: 创建的过程带有编译错误。

已用时间:  00: 00: 00.09
15:12:22 SCOTT@orcl> show err
PROCEDURE FORMAT_STRING 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: Statement ignored
3/3      PLS-00363: 表达式 'STRING_IN' 不能用作赋值目标

存储过程调用方法(SQLPLUS下)

15:21:52 SCOTT@orcl> VARIABLE session_var VARCHAR2(30);
15:22:39 SCOTT@orcl> CALL join_strings('Hello','World') INTO :session_var;--调用函数join_strings给变量赋值

调用完成。

已用时间:  00: 00: 00.03
15:22:56 SCOTT@orcl> CALL format_string(:session_var);--调用存储,也可以是EXECUTE format_string(:session_var);

调用完成。

已用时间:  00: 00: 00.00
15:23:03 SCOTT@orcl> print session_var

SESSION_VAR
--------------------------------
[Hello World.]

或者

15:23:10 SCOTT@orcl> DECLARE
15:24:50   2    session_var VARCHAR2(30);
15:24:50   3  BEGIN
15:24:50   4    session_var:=join_strings('Hello','World');
15:24:50   5    format_string(session_var);
15:24:50   6    DBMS_OUTPUT.PUT_LINE(session_var);
15:24:50   7  END;
15:24:50   8  /
[Hello World.]

PL/SQL 过程已成功完成。

3.包
在11G里包将是存储的骨干。它由函数和存储组成.它还可以提供函数和存储的重载功能.
包体中的存储\函数必须和包中的存储过程、函数签名一致.

6.事务范围
3个命令(TCL,事务控制语言)控制会话的事务范围.
1.COMMIT语句--提交当前事务的所有的DML改变,并结束当前事务
2.SAVEPOINT语句--把事务分为两期,也就是将事务分为两个相对的时间点
3.ROLLBACK语句--撤销事务的所有的改变,如有设置SAVEPOINT,也可以撤销到指定的状态,否则撤销当前事务所有的改变,并结束当前事务

单一事务范围
BEGIN
-- Set savepoint.
SAVEPOINT new_member;
-- First insert.
INSERT INTO member
VALUES
(member_s1.nextval,
1005,
'D921-71998',
'4444-3333-3333-4444',
1006,
2,
SYSDATE,
2,
SYSDATE);
-- Second insert.
INSERT INTO contact
VALUES
(contact_s1.nextval,
member_s1.currval + 1,
1003,
'Bodwin',
'Jordan',
'',
2,
SYSDATE,
2,
SYSDATE);
-- Print success message and commit records.
dbms_output.put_line('Both succeeded.');
COMMIT;
EXCEPTION
WHEN others THEN
-- Roll back to savepoint, and raise exception message.
ROLLBACK TO new_member;
dbms_output.put_line(SQLERRM);
END;
上面的两个INSERT操作,要么全部成功,要么全部失败.

多事务范围
可以使用AUTONOMOUS_TRANSACTION预编译命令,使得存储或函数、触发器使用自治事务.(与当前事务分离开,成为单独的一个事务)

7.触发器
在触发器中不能使用DCL(COMMIT,ROLLBACK等),除非使用了自治事务.
在11g里,你可以使用5种类型的触发器:
1.DDL触发器--当你创建、更改、重命名、删除对象的时候触发,它们常用来监视数据库对象的各种操作.
2.DML触发器或行级触发器--当你对一个表做DML操作时触发,它们常用来审计、更改值、检查条件、和序列联用生成主键等等.
3.复合触发器--语句级和行级触发的综合,可以捕获4个时间点的信息:a,语句触发前;b,每行改变发生前;c,每行改变发生后;d,语句触发后
4.INSTEAD OF触发器--停止当前的DML语句,重定向到另外的DML语句.常用来更新视图.
5.系统级或数据库级--当一个数据库的活动事件发生的时候触发,像登录/登出数据库.常用来跟踪数据库系统事件.

第三章语法基础(比较简单,但比较基础,部分省略,详细请查看官方文档吧)

15:57:27 SCOTT@orcl> begin dbms_output.put_line(3**3); end;--**代表多少次方,如2**10方等于1024
15:57:57   4  /
27

PL/SQL 过程已成功完成。

15:59:16 SCOTT@orcl> DECLARE
16:00:00   2  "End" NUMBER := 1;
16:00:00   3  BEGIN
16:00:00   4  dbms_output.put_line('A quoted identifier End ['||"End"||']');
16:00:00   5  END;
16:00:01   6  /
A quoted identifier End [1]

PL/SQL 过程已成功完成。


--类型长度,VARCHAR2和CHAR的区别
16:00:02 SCOTT@orcl> DECLARE
16:10:17   2    c CHAR(32767) := ' ';
16:10:17   3    v VARCHAR2(32767) := ' ';
16:10:17   4  BEGIN
16:10:17   5    dbms_output.put_line('c is [' || LENGTH(c) || ']');
16:10:17   6    dbms_output.put_line('v is [' || LENGTH(v) || ']');
16:10:17   7    v := v || ' ';
16:10:17   8    dbms_output.put_line('v is [' || LENGTH(v) || ']');
16:10:17   9  END;
16:10:17  10  /
c is [32767]
v is [1]
v is [2]

PL/SQL 过程已成功完成。

--子类型使用(SUBTYPE)
16:10:18 SCOTT@orcl> DECLARE
16:11:44   2    SUBTYPE code IS CHAR(1 CHAR);
16:11:44   3    c CHAR(1 CHAR) := 'A';
16:11:44   4    d CODE;
16:11:44   5  BEGIN
16:11:44   6    d := c;
16:11:44   7  END;
16:11:44   8  /

PL/SQL 过程已成功完成。

--INTERVAL类型的使用
16:14:06 SCOTT@orcl> DECLARE
16:15:22   2    var2 INTERVAL YEAR(3) TO MONTH;
16:15:22   3  BEGIN
16:15:22   4    -- Shorthand for a 101 year and 3 month interval.
16:15:22   5    var2 := '101-3';
16:15:22   6    dbms_output.put_line(var2);
16:15:22   7    var2 := INTERVAL '101-3' YEAR TO MONTH;
16:15:22   8    dbms_output.put_line(var2);
16:15:22   9    var2 := INTERVAL '101' YEAR;
16:15:22  10    dbms_output.put_line(var2);
16:15:22  11    var2 := INTERVAL '3' MONTH;
16:15:22  12    dbms_output.put_line(var2);
16:15:22  13  END;
16:15:22  14  /
+101-03
+101-03
+101-00
+000-03

PL/SQL 过程已成功完成。

--数组类型的使用
16:15:23 SCOTT@orcl> DECLARE
16:17:12   2    TYPE number_varray IS VARRAY(10) OF NUMBER;
16:17:12   3    list NUMBER_VARRAY := number_varray(1, 2, 3, 4, 5, 6, 7, 8, NULL, NULL);
16:17:12   4  BEGIN
16:17:12   5    FOR i IN 1 .. list.LIMIT
16:17:12   6    LOOP
16:17:12   7      dbms_output.put('[' || list(i) || ']');
16:17:12   8    END LOOP;
16:17:12   9    dbms_output.new_line;
16:17:12  10  END;
16:17:12  11  /
[1][2][3][4][5][6][7][8][][]

PL/SQL 过程已成功完成。

--嵌套表类型的使用
16:17:13 SCOTT@orcl> DECLARE
16:18:12   2    TYPE number_table IS TABLE OF NUMBER;
16:18:12   3    list NUMBER_TABLE := number_table(1, 2, 3, 4, 5, 6, 7, 8);
16:18:12   4  BEGIN
16:18:12   5    list.DELETE(2);
16:18:12   6    FOR i IN 1 .. list.COUNT
16:18:12   7    LOOP
16:18:12   8      IF list.EXISTS(i) THEN
16:18:12   9        dbms_output.put('[' || list(i) || ']');
16:18:12  10      END IF;
16:18:12  11    END LOOP;
16:18:12  12    dbms_output.new_line;
16:18:12  13  END;
16:18:12  14  /
[1][3][4][5][6][7]

PL/SQL 过程已成功完成。

--关联数组的使用
16:19:49 SCOTT@orcl> DECLARE
16:21:00   2    TYPE number_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
16:21:00   3    list NUMBER_TABLE;
16:21:00   4  BEGIN
16:21:00   5    FOR i IN 1 .. 6
16:21:00   6    LOOP
16:21:00   7      list(i) := i; -- Explicit assignment required for associative arrays.
16:21:00   8    END LOOP;
16:21:00   9    list.DELETE(2);
16:21:00  10    FOR i IN 1 .. list.COUNT--COUNT为5
16:21:00  11    LOOP
16:21:00  12      IF list.EXISTS(i) THEN
16:21:00  13        dbms_output.put('[' || list(i) || ']');
16:21:00  14      END IF;
16:21:00  15    END LOOP;
16:21:00  16    dbms_output.new_line;
16:21:00  17  END;
16:21:01  18  /
[1][3][4][5]

PL/SQL 过程已成功完成。

--引用型游标的使用
16:27:31 SCOTT@orcl> VARIABLE refcur REFCURSOR
16:28:21 SCOTT@orcl> DECLARE
16:28:26   2    TYPE weakly_typed IS REF CURSOR;
16:28:26   3    quick WEAKLY_TYPED;
16:28:26   4  BEGIN
16:28:26   5    OPEN quick FOR
16:28:26   6      SELECT ename, COUNT(*)
16:28:26   7        FROM emp
16:28:26   8       GROUP BY ename;
16:28:26   9    :refcur := quick;
16:28:26  10  END;
16:28:27  11  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.00
16:28:28 SCOTT@orcl> select :refcur from dual;

:REFCUR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME        COUNT(*)
---------- ----------
ALLEN               1
JONES               1
FORD                1
CLARK               1
MILLER              1
SMITH               1
WARD                1
MARTIN              1
SCOTT               1
TURNER              1
ADAMS               1
BLAKE               1
KING                1
JAMES               1
test110             1
test111             1

已选择16行。

附plsql标量数据类型汇总图: