Valid XHTML 1.0 TransitionalOracle PL/SQL Review (P2)

Page: [index.html] (v2017-07-07_15-50)
  1. PL/SQL 基本概念：……
  2. PL/SQL 编程：
    1. 基本赋值、输入、输出：……
    2. 关于“块”：
      “块”是 PL/SQL 中的一种代码组织形式。
      一个正确的“块”至少含有以下结构：
      begin
	-- Executive code, MANDATORY!
end;

      分类：
      1. 匿名块：
        特指直接存在于 SQL-Plus 之类环境下、没有指定名称的块 (不包含嵌套在“命名块”中的无名“块”) ；已在先前的样例中用过。

        不被 Oracle 保存，一次性使用，现用现写，无法重用；测试“命名块”时可用“匿名块”临时搭建测试环境；不适合执行任何稍微复杂的逻辑。

        后续演示均在命名块中进行。
      2. 命名块：
        有名称的“块”，被 Oracle 编译并保存，在需要使用时可用名称调用。

        分类：
        1. 过程：
          “命名块”的一种，侧重用于集中执行一组数据操作，有时需要用处理结果将传入的参数值覆盖掉；
          支持参数，但不支持返回值。

          参数：
          “块”内局部变量中的“特殊品种”，可以被指定为不同的模式：
          • in     ：纯入模式。
            参数被指定为此种模式时，仅用于在“块”的逻辑开始前接收“块”外传入的数据；在“块”内改变此参数赋值对“块”不产生直接影响。
            可以指定默认值。
          • in     ：纯出模式。
            参数被指定为此种模式时，仅用于在“块”的逻辑结束后将数据传出“块”外；此参数的值将覆盖“块”外相应变量的值。
            不能指定默认值。
          • in out ：双向模式。
            参数被指定为此种模式时，既在“块”的逻辑执行结束后将数据传出“块”外，也在“块”的逻辑结束后将数据传出“块”外，覆盖“块”外相应变量的值。
            可以指定默认值。

          样例：
          1. 打印系统日期：
            • 过程：
              -- Procedure block.
create or replace procedure tfwProcPrintSysdateText
	-- Area for argument(s).
as -- Block local variable(s) declaring.
begin -- Mandatory core of the block.
	dbms_output.put_line('Procedure - System date printing:');
	dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
end;
/ -- Block executing command?
              说明：
              • “tfwProcPrintSysdateText”：“过程”的名称。
              • “as”：对于“过程”，此处用“as”或“is”皆可。
            • 创建：
              [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> create or replace procedure tfwProcPrintSysdateText
  2  as
  3  begin
  4     dbms_output.put_line('Procedure - System date printing:');
  5     dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  6  end;
  7  /

过程已创建。

SQL> desc tfwProcPrintSysdateText;
PROCEDURE tfwProcPrintSysdateText

SQL> show errors;
没有错误。
SQL> exit;
从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
[typhoon@TFW-CENT6-LT ~]$
            • 调用：
              1. “exec”：
                [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> exec tfwProcPrintSysdateText;
Procedure - System date printing:
2017-05-30 20:30:10

PL/SQL 过程已成功完成。

SQL>
                说明：
                • SQL-Plus 、SQL Developer 等部分工具限定使用的专用调用关键字。
                • 调用无参“块”时，“块”名后可以没有括号。
              2. “call”：
                SQL> call tfwProcPrintSysdateText();
Procedure - System date printing:
2017-05-30 20:30:50

PL/SQL 过程已成功完成。

SQL> exit;
从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
[typhoon@TFW-CENT6-LT typhoon]$
                说明：
                • 通用调用关键字，可在更多中工具中使用。
                • 无论被调用的“块”有没有参数，“块”后均必须有括号。
          2. 从“块”外接收参数 (日期格式) ，并将结果 (系统日期格式化后的字符串) 从另一参数送出“块”外：
            • 过程：
              -- Procedure block.
create or replace procedure tfwProcFmtAndAssignSysdateText
	-- Block local variable(s) that binded to argument(s).
	(vch2Format in varchar2, vch2SysdateTextCopy out varchar2)
as -- Block local variable(s) that not binded.
	vch2FormatCopy varchar2(32);
	vch2SysdateText varchar2(32);
begin -- Mandatory core of the block.
	dbms_output.put_line('Procedure - Format and assign system date as text:');
	dbms_output.put_line('Incoming format : ['||vch2Format||']');

	-- Assigning into INNER-BLOCK VARIAVLE.
	vch2FormatCopy := vch2Format;
	dbms_output.put_line('Copied   format : ['||vch2FormatCopy||']');

	-- Getting, formatting system date into an INNER-BLOCK VARIAVLE.
	vch2SysdateText := (to_char(systimestamp, vch2Format));
	dbms_output.put_line('System date text: ['||vch2SysdateText||']');

	-- Assigning into INNER-BLOCK VARIAVLE to send out.
	vch2SysdateTextCopy := vch2SysdateText;
	dbms_output.put_line('Text to send out: ['||vch2SysdateTextCopy||']');
exception -- Exception handler.
	when others then -- No matter what exception it is.
		vch2SysdateTextCopy := dbms_utility.format_error_backtrace||' '||SQLERRM;
		dbms_output.put_line('Exception: '||vch2SysdateTextCopy);
		-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
end;
/ -- Block executing command?
            • 创建：略
            • 调用：
              1. 硬编码调用：
                SQL> set serveroutput on;
SQL> variable vch2OuterSysdateText varchar2(32);
SQL> call tfwProcFmtAndAssignSysdateText('yyyy-mm-dd hh12:mm:ss am', :vch2OuterSysdateText);
Procedure - Format and assign system date as text:
Incoming format : [yyyy-mm-dd hh12:mm:ss am]
Copied   format : [yyyy-mm-dd hh12:mm:ss am]
System date text: [2017-05-31 06:10:20 下午]
Text to send out: [2017-05-31 06:10:20 下午]

PL/SQL 过程已成功完成。

SQL> print vch2OuterSysdateText;

VCH2OUTERSYSDATETEXT
--------------------------------
2017-05-31 06:10:20 下午

SQL>
              2. 手动输入格式：
                SQL> set serveroutput on;
SQL> variable vch2OuterSysdateText varchar2(32);
SQL> accept bufFormat prompt 'Enter date format: ';
Enter date format: yyyy-mm-dd hh24:mm:ss.ff
SQL> call tfwProcFmtAndAssignSysdateText('&bufFormat', :vch2OuterSysdateText);
Procedure - Format and assign system date as text:
Incoming format : [yyyy-mm-dd hh24:mm:ss.ff]
Copied   format : [yyyy-mm-dd hh24:mm:ss.ff]
System date text: [2017-05-31 18:40:20.169049000]
Text to send out: [2017-05-31 18:40:20.169049000]

PL/SQL 过程已成功完成。

SQL> print vch2OuterSysdateText;

VCH2OUTERSYSDATETEXT
--------------------------------
2017-05-31 18:40:20.169049000

SQL>
              3. 人为制造异常：
                SQL> set serveroutput on;
variable vch2OuterSysdateText varchar2(128);
call tfwProcFmtAndAssignSysdateText('yyyy-mm-dd hh24:mm:ss.sss', :vch2OuterSysdateText);
print vch2OuterSysdateText;
SQL> SQL> Procedure - Format and assign system date as text:
Incoming format : [yyyy-mm-dd hh24:mm:ss.sss]
Copied  format  : [yyyy-mm-dd hh24:mm:ss.sss]
Exception: ORA-06512: 在 "SCOTT.TFWPROCFMTANDASSIGNSYSDATETEXT", line 16

ORA-01821: 日期格式无法识别

PL/SQL 过程已成功完成。

SQL> print vch2OuterSysdateText;
VCH2OUTERSYSDATETEXT
--------------------------------------------------------------------------------
ORA-06512: 在 "SCOTT.TFWPROCFMTANDASSIGNSYSDATETEXT", line 16
 ORA-01821: 日期格式无法识别


SQL>
                说明：
                • “variable vch2OuterSysdateText varchar2(128);”：本次“过程”样例将异常信息写进输出参数，需要容量足够长的变量以容纳。
                • “yyyy-mm-dd hh24:mm:ss.sss”：故意送入不支持的格式，以观察异常处理部分是否工作。
          3. 在“过程”内读取用户输入 buffer 内容，赋值给内外变量：
            • 过程：
              create or replace procedure tfwProcAcceptAndAssign
	(vch2Text out varchar2)
as
begin
	dbms_output.put_line('Procedure - Accept value from prompt, then assign to OUTER-BLOCK VARIABLE, then print:');
	vch2Text := '&bufOfText';
	dbms_output.put_line('Value of [vch2Text] : ['||vch2Text||']');
exception when others then
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
end;
/
              说明：
              在编译前会要求给定 buffer 值，并在编译期间将值固化在“过程”中，无法做到动态赋值，无意义。
          4. 在“过程”内部接收用户输入，并赋值给变量：
            • 过程：
              create or replace procedure tfwProcAcceptInBlock
	(vch2Text out varchar2)
as
begin
	dbms_output.put_line('Procedure - Accepting IN block:');
	accept bufText_ prompt 'Enter text (length ≤ 16) here: ';
	vch2Text := '&bufText_';
	dbms_output.put_line('Value of [vch2Text] : ['||vch2Text||']');
exception when others then
	dbms_output.put_line('Exception: '||dbms_utility.format_error_backtrace||' '||SQLERRM);
end;
/
              说明：
              在编译前会要求给定 buffer 值，之后编译失败。
          5. 根据传入的参数值的长度决定是否将参数值覆盖为“长度超标”：
            • 过程：
              -- Procedure block.
create or replace procedure tfwProcOverrideWhileOverBytes
	-- Block local variable(s) that binded to argument(s).
	(numBytesLimit in number, vch2Text in out varchar2)
as -- Block local variable(s) that not binded.
	numBytes number;
begin -- Mandatory core of the block.
	dbms_output.put_line('Procedure - Override the over-'||numBytesLimit||'-byte(s) text:');

	if (vch2Text is null) then
		dbms_output.put_line('Incoming text is null.');
	elsif (''=vch2Text) then
		dbms_output.put_line('Incoming text is empty.');
	else
		numBytes := lengthb(vch2Text);
		dbms_output.put_line('Incoming '||numBytes||' bytes.');
		if (numBytesLimit<numBytes) then
			vch2Text := 'Over '||numBytesLimit||' bytes!';
			dbms_output.put_line(vch2Text);
		end if;
	end if;
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?
            • 调用：
              SQL> set serveroutput on;
SQL> variable vch2OuterText varchar2(16);
SQL> begin :vch2OuterText := null;end;
  2  /

PL/SQL 过程已成功完成。

SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------


SQL> call tfwProcOverrideWhileOverBytes(16, :vch2OuterText);
Procedure - Override the over-16-byte(s) text:
Incoming text is null.

调用完成。


SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------



              SQL> begin :vch2OuterText := '一二三四五';end;
  2  /

PL/SQL 过程已成功完成。

SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------
一二三四五

SQL> call tfwProcOverrideWhileOverBytes(16, :vch2OuterText);
Procedure - Override the over-16-byte(s) text:
Incoming 15 bytes.

调用完成。


SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------
一二三四五


              SQL> call tfwProcOverrideWhileOverBytes(12, :vch2OuterText);
Procedure - Override the over-12-byte(s) text:
Incoming 15 bytes.
Over 12 bytes!

调用完成。


SQL> print vch2OuterText;

VCH2OUTERTEXT
--------------------------------
Over 12 bytes!

SQL>
        2. ……