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. ……