Valid XHTML 1.0 TransitionalOracle PL/SQL Review (P1)

Page: [index.html] (v2017-06-02_21-20)
  1. PL/SQL 基本概念：
    1. 是 Oracle 对 SQL 的扩展。
    2. 是由标准、通用的数据库操作语言扩展为数据库编程语言。
    3. 可在 Oracle 数据库上构建交互式程序和自动执行程序。
  2. PL/SQL 编程：
    1. 基本赋值、输入、输出：
      1. 为变量赋值，并打印：
        • 程序：
          -- Global variable(s) declaring.
variable vch2Hello varchar2(16);

-- The most simple and basic value assignment, in a block.
begin :vch2Hello := 'Hello Kitty!';end;
/ -- Block executing command?

-- Printing global variable value(s).
print vch2Hello;
print :vch2Hello;
          说明：
          1. “块”外能声明变量，但包括赋值在内的大多数操作只能在“块”内完成。
          2. 在“块”内处理“块”外的全局变量时，要在全局变量前加冒号。
        • 运行：
          [typhoon@TFW-CENT6-LT ~]$ NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8" ORACLE_SID=███ sqlplus ███/███;

SQL*Plus: Release 11.2.0.1.0 Production on █████ █ ████ ██

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> variable vch2Hello varchar2(16);
SQL> begin :vch2Hello := 'Hello Kitty!';end;
  2  /

PL/SQL 过程已成功完成。

SQL> print vch2Hello;

VCH2HELLO
--------------------------------
Hello Kitty!

SQL> print :vch2Hello;

VCH2HELLO
--------------------------------
Hello Kitty!

SQL> exit;
从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
[typhoon@TFW-CENT6-LT ~]$
          说明：
          “块”外打印时，全局变量前加不加冒号似乎没有区别。
      2. 在前一样例基础上做“块”内打印：
        • 程序：
          -- Enable display option for "dbms_output.put_line(...)".
set serveroutput on;

-- Global variable(s) declaring.
variable vch2Hello varchar2(16);

-- Operation block.
begin -- Mandatory core of the block.
	dbms_output.put_line('Block - OUTER-BLOCK VARIABLE value assigning and printing:');
	:vch2Hello := 'Hello Kitty!';
	dbms_output.put_line('Value of [:vch2Hello]: ['||:vch2Hello||']');
end;
/ -- Block executing command?

-- Printing global variable value(s).
print vch2Hello;
print :vch2Hello;
          说明：
          “块”外能声明变量，但包括赋值在内的大多数操作只能在“块”内完成。
        • 运行：
          [typhoon@TFW-CENT6-LT ~]$ NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8" ORACLE_SID=███ sqlplus ███/███;

SQL*Plus: Release 11.2.0.1.0 Production on █████ █ ████ ██

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> set serveroutput on;
SQL> variable vch2Hello varchar2(16);
SQL> begin
  2     dbms_output.put_line('Block - OUTER-BLOCK VARIABLE value assigning and printing:');
  3     :vch2Hello := 'Hello Kitty!';
  4     dbms_output.put_line('Value of [:vch2Hello]: ['||:vch2Hello||']');
  5  end;
  6  /
Block - OUTER-BLOCK VARIABLE value assigning and printing:
Value of [:vch2Hello]: [Hello Kitty!]

PL/SQL 过程已成功完成。

SQL> print vch2Hello;

VCH2HELLO
--------------------------------
Hello Kitty!

SQL> print :vch2Hello;

VCH2HELLO
--------------------------------
Hello Kitty!

SQL> exit;
从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
[typhoon@TFW-CENT6-LT ~]$
      3. 接收用户数入、将输入内容赋给变量、打印变量值及处理异常：
        • 程序：
          -- Enable display option for "dbms_output.put_line(...)".
set serveroutput on;

-- Accepting user input into buffer(s) with prompt.
accept bufNum prompt 'Enter number here: ';
accept bufText prompt 'Enter text (length ≤ 16) here: ';

-- Global variable(s) declaring.
variable numNum number;
variable vch2Text varchar2(16);

-- Operation block.
begin -- Mandatory core of the block.
	dbms_output.put_line('Block - Accept value from prompt, then assign into OUTER-BLOCK VARIABLE, then print:');

	-- Getting non-text value from buffer, no need (but able) to use single quote symbol.
	:numNum := &bufNum;
	dbms_output.put_line('Value of [:numNum]: ['||:numNum||']');

	-- Getting text value from buffer, requires single quote symbol.
	:vch2Text := '&bufText';
	dbms_output.put_line('Value of [:vch2Text]: ['||:vch2Text||']');
exception when others then -- No matter what exception it is.
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
	-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
end;
/ -- Block executing command?

-- Printing global variable value(s).
print numNum;
print vch2Text;
          说明：
          “exception ...”：用户输入是不可信任的，需要考虑出现异常的情况；添加此保险措施，捕获异常、打印详细信息。
        • 运行：
          1. 正常运行：
            SQL> set serveroutput on;
SQL> accept bufNum prompt 'Enter number here: ';
Enter number here: 255
SQL> accept bufText prompt 'Enter text (length ≤ 16) here: ';
Enter text (length ≤ 16) here: Hello Apple!
SQL> variable numNum number;
SQL> variable vch2Text varchar2(16);
SQL> begin
  2     dbms_output.put_line('Block - Accept value from prompt, then assign into OUTER-BLOCK VARIABLE, then print:');
  3     :numNum := &bufNum;
  4     dbms_output.put_line('Value of [:numNum]: ['||:numNum||']');
  5     :vch2Text := '&bufText';
  6     dbms_output.put_line('Value of [:vch2Text]: ['||:vch2Text||']');
  7  exception when others then
  8     dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
  9  end;
 10  /
原值    3:      :numNum := &bufNum;
新值    3:      :numNum := 255;
原值    5:      :vch2Text := '&bufText';
新值    5:      :vch2Text := 'Hello Apple!';
Block - Accept value from prompt, then assign into OUTER-BLOCK VARIABLE, then print:
Value of [:numNum]: [255]
Value of [:vch2Text]: [Hello Apple!]

PL/SQL 过程已成功完成。

SQL> print numNum;

    NUMNUM
----------
       255

SQL> print vch2Text;

VCH2TEXT
--------------------------------
Hello Apple!

SQL>
          2. 人为制造“长度超过缓冲区”异常：
            SQL> set serveroutput on;
SQL> accept bufNum prompt 'Enter number here: ';
Enter number here: -512
SQL> accept bufText prompt 'Enter text (length ≤ 16) here: ';
Enter text (length ≤ 16) here: Long long long long...
SQL> variable numNum number;
SQL> variable vch2Text varchar2(16);
SQL> begin
  2     dbms_output.put_line('Block - Accept value from prompt, then assign into OUTER-BLOCK VARIABLE, then print:');
  3     :numNum := &bufNum;
  4     dbms_output.put_line('Value of [:numNum]: ['||:numNum||']');
  5     :vch2Text := '&bufText';
  6     dbms_output.put_line('Value of [:vch2Text]: ['||:vch2Text||']');
  7  exception when others then
  8     dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
  9  end;
 10  /
原值    3:      :numNum := &bufNum;
新值    3:      :numNum := -512;
原值    5:      :vch2Text := '&bufText';
新值    5:      :vch2Text := 'Long long long long...';
Block - Accept value from prompt, then assign into OUTER-BLOCK VARIABLE, then print:
Value of [:numNum]: [-512]
Exception: ORA-06512: 在 line 5
 ORA-06502: PL/SQL: 数字或值错误 :
字符串缓冲区太小

PL/SQL 过程已成功完成。

SQL> print numNum;

    NUMNUM
----------
      -512

SQL> print vch2Text;

VCH2TEXT
--------------------------------



SQL>
            说明：
            • 异常发生在“块”内部，第 5 行处。
            • 异常发生前的流程执行正常，数字型被值从 buffer 中取出并赋给相应的变量。
            • 从 buffer 向字符串型变量赋值时，值的长度超过变量的容纳限度，发生异常，“块”内的正常运行流程被打断，赋值未成功，也未用 dbms_output 打印。
      4. 将“块”外变量的值赋给“块”内变量，并打印：
        • 程序：
          -- Enable display option for "dbms_output.put_line(...)".
set serveroutput on;

-- Accepting user input into buffer(s) with prompt.
accept bufText prompt 'Enter text (length ≤ 16) here: ';

-- Global variable(s) declaring.
variable vch2OuterText varchar2(16);

-- Operation block.
declare
	-- Block local variable(s) declaring.
	vch2InnerText varchar2(16);
begin -- Mandatory core of the block.
	dbms_output.put_line('Block - Assigning value from OUTER-BLOCK VARIABLE to INNER-BLOCK VARIABLE, and printing:');

	-- Assigning value from buffer to OUTER-BLOCK VARIABLE.
	:vch2OuterText := '&bufText';
	dbms_output.put_line('Value of [:vch2OuterText] : ['||:vch2OuterText||']');

	-- Assigning value from buffer to INNER-BLOCK VARIABLE.
	vch2InnerText := '&bufText';
	dbms_output.put_line('Value of  [vch2InnerText] : ['||vch2InnerText||']');

	-- Assigning value from OUTER-BLOCK VARIABLE to INNER-BLOCK VARIABLE.
	vch2InnerText := :vch2OuterText;
	dbms_output.put_line('Value of [vch2InnerText] 2: ['||vch2InnerText||']');
exception when others then -- No matter what exception it is.
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
	-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
end;
/ -- Block executing command?

-- Printing global variable value(s).
print vch2OuterText;
        • 运行： 
          SQL> set serveroutput on;
SQL> accept bufText prompt 'Enter text (length ≤ 16) here: ';
Enter text (length ≤ 16) here: 测试场
SQL> variable vch2OuterText varchar2(16);
SQL> declare vch2InnerText varchar2(16); -- Block local variable(s) declaring.
  2  begin
  3     dbms_output.put_line('Block - Assigning value from OUTER-BLOCK VARIABLE to INNER-BLOCK VARIABLE, and printing:');
  4     :vch2OuterText := '&bufText';
  5     dbms_output.put_line('Value of [:vch2OuterText] : ['||:vch2OuterText||']');
  6     vch2InnerText := '&bufText';
  7     dbms_output.put_line('Value of  [vch2InnerText] : ['||vch2InnerText||']');
  8     vch2InnerText := :vch2OuterText;
  9     dbms_output.put_line('Value of [vch2InnerText] 2: ['||vch2InnerText||']');
 10  exception when others then
 11     dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
 12  end;
 13  /
原值    4:      :vch2OuterText := '&bufText';
新值    4:      :vch2OuterText := '测试场';
原值    5:      :vch2OuterText := '&bufText';
新值    5:      :vch2OuterText := '测试场';
原值    7:      vch2InnerText := '&bufText';
新值    7:      vch2InnerText := '测试场';
Block - Assigning value from OUTER-BLOCK VARIABLE to INNER-BLOCK VARIABLE, and
printing:
Value of [:vch2OuterText] : [测试场]
Value of  [vch2InnerText] : [测试场]
Value of [vch2InnerText] 2: [测试场]

PL/SQL 过程已成功完成。

SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------
测试场

SQL>
      5. 将“块”内变量的值赋给“块”外变量，并打印：
        • 程序：
          -- Enable display option for "dbms_output.put_line(...)".
set serveroutput on;

-- Accepting user input into buffer(s) with prompt.
accept bufText prompt 'Enter text (length ≤ 16) here: ';

-- Global variable(s) declaring.
variable vch2OuterText varchar2(16);

-- Operation block.
declare vch2InnerText varchar2(16); -- Block local variable(s) declaring.
begin -- Mandatory core of the block.
	dbms_output.put_line('Block - Assigning value from INNER-BLOCK VARIABLE to OUTER-BLOCK VARIABLE, and printing:');

	-- Assigning value from buffer to INNER-BLOCK VARIABLE.
	vch2InnerText := '&bufText';
	dbms_output.put_line('Value of  [vch2InnerText] : ['||vch2InnerText||']');

	-- Assigning value from INNER-BLOCK VARIABLE to OUTER-BLOCK VARIABLE.
	:vch2OuterText := vch2InnerText;
	dbms_output.put_line('Value of [:vch2OuterText] : ['||:vch2OuterText||']');
exception when others then -- No matter what exception it is.
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
	-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
end;
/ -- Block executing command?

-- Printing global variable value(s).
print vch2OuterText;
        • 运行： 
          SQL> set serveroutput on;
SQL> accept bufText prompt 'Enter text (length ≤ 16) here: ';
Enter text (length ≤ 16) here: 菜市口
SQL> variable vch2OuterText varchar2(16);
SQL> declare vch2InnerText varchar2(16);
  2  begin
  3     dbms_output.put_line('Block - Assigning value from INNER-BLOCK VARIABLE to OUTER-BLOCK VARIABLE, and printing:');
  4     vch2InnerText := '&bufText';
  5     dbms_output.put_line('Value of  [vch2InnerText] : ['||vch2InnerText||']');
  6     :vch2OuterText := vch2InnerText;
  7     dbms_output.put_line('Value of [:vch2OuterText] : ['||:vch2OuterText||']');
  8  exception when others then
  9     dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
 10  end;
 11  /
原值    4:      vch2InnerText := '&bufText';
新值    4:      vch2InnerText := '菜市口';
Block - Assigning value from INNER-BLOCK VARIABLE to OUTER-BLOCK VARIABLE, and
printing:
Value of  [vch2InnerText] : [菜市口]
Value of [:vch2OuterText] : [菜市口]

PL/SQL 过程已成功完成。

SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------
菜市口

SQL>
      6. “块”嵌套：
        • 程序：
          -- Enable display option for "dbms_output.put_line(...)".
set serveroutput on;

-- Global variable(s) declaring and initializing.
variable vch2TextOuter varchar2(32);
begin :vch2TextOuter := 'Global Unchanged.';end;
/ -- Block executing command?
print vch2TextOuter;

-- Outer block.
declare vch2Text varchar2(16) := 'Unchanged.';
begin
	dbms_output.put_line('[S] Outer block');
	dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');
	dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');
	-- Inner block.
	declare vch2TextDeep varchar2(16) := 'Deep.';
	begin
		dbms_output.put_line('[S] Inner block');
		vch2Text := 'Changed.';
		:vch2TextOuter := 'Global Changed.';

		-- Changing the value of the IN-BLOCK VARIABLE of outer-block.
		dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');

		-- Changing the value of the GLOBAL VARIABLE.
		dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');

		dbms_output.put_line('[E] Inner block');
	exception when others then -- No matter what exception it is.
		dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
	end;
	dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');
	dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');

	-- Causes "not declared" error.
	-- dbms_output.put_line('Value of [vch2TextDeep]  : ['||vch2TextDeep||']');

	dbms_output.put_line('[E] Outer block');
exception when others then -- No matter what exception it is.
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
end;
/
-- Showing possible error(s).
show errors;

-- Printing global variable value(s).
print vch2TextOuter;
        • 运行： 
          SQL> set serveroutput on;
SQL> variable vch2TextOuter varchar2(32);
SQL> begin :vch2TextOuter := 'Global Unchanged.';end;
  2  /

PL/SQL 过程已成功完成。

SQL> print vch2TextOuter;

VCH2TEXTOUTER
--------------------------------
Global Unchanged.

SQL> declare vch2Text varchar2(16) := 'Unchanged.';
  2  begin
  3     dbms_output.put_line('[S] Outer block');
  4     dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');
  5     dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');
  6     declare vch2TextDeep varchar2(16) := 'Deep.';
  7     begin
  8             dbms_output.put_line('[S] Inner block');
  9             vch2Text := 'Changed.';
 10             :vch2TextOuter := 'Global Changed.';
 11             dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');
 12             dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');
 13             dbms_output.put_line('[E] Inner block');
 14     exception when others then
 15             dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
 16     end;
 17     dbms_output.put_line('Value of [vch2Text]      : ['||vch2Text||']');
 18     dbms_output.put_line('Value of [:vch2TextOuter]: ['||:vch2TextOuter||']');
 19     dbms_output.put_line('[E] Outer block');
 20  exception when others then
 21     dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
 22  end;
 23  /
[S] Outer block
Value of [vch2Text]      : [Unchanged.]
Value of [:vch2TextOuter]: [Global Unchanged.]
[S] Inner block
Value of [vch2Text]      : [Changed.]
Value of [:vch2TextOuter]: [Global Changed.]
[E] Inner block
Value of [vch2Text]      : [Changed.]
Value of [:vch2TextOuter]: [Global Changed.]
[E] Outer block

PL/SQL 过程已成功完成。

SQL> show errors;
没有错误。
SQL> print vch2TextOuter;

VCH2TEXTOUTER
--------------------------------
Global Changed.

SQL>
          说明：
          • 内层“块”  能访问、修改外层“块”中声明的变量，也能访问、修改“块”外全局变量。
          • 外层“块”不能访问、修改内层“块”中声明的变量。
    2. ……