oracle 11g PL/SQL Programming学习二

mgbpphwavn

-------------------------------------------------------------

-----------------Cryking 学习笔记系列-----------------

------------------转载请注明出处,谢谢!----------------
　　
　　第二章 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标量数据类型汇总图: