Valid XHTML 1.0 TransitionalOracle PL/SQL Review (P4)

Page: [index.html] (v2017-06-05_17-00)

  1. PL/SQL 基本概念:……
  2. PL/SQL 编程:
    1. 基本赋值、输入、输出:……
    2. 关于“块”:
      ……

      分类:
      1. 匿名块:……
      2. 命名块:
        ……

        分类:
        1. 过程:……
        2. 函数:……
        3. 包  :
          “命名块”的一种,侧重于将与同一业务相关的各“过程”、“函数”之类集中打包在一起,以简化应用程序设计;
          支持参数,且必须有返回值。

          样例 - (伪) 根据组 ID 取得组名:
          • “包”头:
            声明“包”内的全局变量以及包内的“过程”、“函数”等“块”。
            • 程序:
              -- Package head block.
              create or replace package tfwPkgGroupMgr
              as
              	-- A package-range variable.
              	vch2PkgTmpText varchar2(128);
              
              	-- A function.
              	function funcReturnGroupName(numGroupId in number) return varchar2;
              
              	-- A procedure.
              	procedure procSendoutGroupName(numGroupId in number, vch2OutText out varchar2);
              end;
              / -- Block executing command?
              
          • “包”体:
            实际实现“包”头内声明的“过程”、“函数”等各个“块”。
            • 程序:
              -- Package head block.
              create or replace package body tfwPkgGroupMgr
              as
              	-- Implementation of declared function.
              	function funcReturnGroupName(numGroupId in number) return varchar2
              	as
              	begin
              		dbms_output.put_line('Function - Getting user name of ID '||numGroupId||':');
              
              		-- Call the inside-package procedure and save result into package range variable.
              		procSendoutGroupName(numGroupId, vch2PkgTmpText);
              
              		-- Return the package range variable.
              		return vch2PkgTmpText;
              	exception
              		when others then -- No matter what exception it is.
              			vch2PkgTmpText := dbms_utility.format_error_backtrace||' '||SQLERRM;
              			dbms_output.put_line('Exception: '||vch2PkgTmpText);
              			-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
              			return vch2PkgTmpText;
              	end;
              
              	-- Implementation of declared procedure.
              	procedure procSendoutGroupName(numGroupId in number, vch2OutText out varchar2)
              	as
              	begin
              		dbms_output.put_line('Procedure - Getting user name of ID '||numGroupId||':');
              
              		if (0=numGroupId) then vch2OutText := 'root';
              		elsif (1=numGroupId) then vch2OutText := 'admin';
              		elsif (2=numGroupId) then vch2OutText := 'dept_01_mgr';
              		elsif (3=numGroupId) then vch2OutText := 'dept_02_mgr';
              		elsif (4=numGroupId) then vch2OutText := 'common';
              		else vch2OutText := 'not found';
              		end if;
              
              		dbms_output.put_line('Group ID: ['||numGroupId||'] Group Name: ['||vch2OutText||'].');
              	exception
              		when others then -- No matter what exception it is.
              			vch2OutText := dbms_utility.format_error_backtrace||' '||SQLERRM;
              			dbms_output.put_line('Exception: '||vch2OutText);
              			-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
              	end;
              end;
              
              / -- Block executing command?
              
          • 创建:
            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> -- Package head block.
            create or replace package tfwPkgGroupMgr
            as
            	-- A package-range variable.
            	vch2PkgTmpText varchar2(128);
            
            	-- A function.
            	function funcReturnGroupName(numGroupId in number) return varchar2;
            
            	-- A procedure.
            	procedure procSendoutGroupName(numGroupId in number, vch2OutText out varchar2);
            end;
            SQL>   2    3    4    5    6    7    8    9   10   11   12  /
            
            程序包已创建。
            
            SQL> show errors;
            没有错误。
            SQL> -- Package head block.
            create or replace package body tfwPkgGroupMgr
            as
            	-- Implementation of declared function.
            	function funcReturnGroupName(numGroupId in number) return varchar2
            	as
            	begin
            		dbms_output.put_line('Function - Getting user name of ID '||numGroupId||':');
            
            		-- Call the inside-package procedure and save result into package range variable.
            		procSendoutGroupName(numGroupId, vch2PkgTmpText);
            
            		-- Return the package range variable.
            		return vch2PkgTmpText;
            	exception
            		when others then -- No matter what exception it is.
            			vch2PkgTmpText := dbms_utility.format_error_backtrace||' '||SQLERRM;
            			dbms_output.put_line('Exception: '||vch2PkgTmpText);
            			-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
            			return vch2PkgTmpText;
            	end;
            
            	-- Implementation of declared procedure.
            	procedure procSendoutGroupName(numGroupId in number, vch2OutText out varchar2)
            	as
            	begin
            		dbms_output.put_line('Procedure - Getting user name of ID '||numGroupId||':');
            
            		if (0=numGroupId) then vch2OutText := 'root';
            		elsif (1=numGroupId) then vch2OutText := 'admin';
            		elsif (2=numGroupId) then vch2OutText := 'dept_01_mgr';
            		elsif (3=numGroupId) then vch2OutText := 'dept_02_mgr';
            		elsif (4=numGroupId) then vch2OutText := 'common';
            		else vch2OutText := 'not found';
            		end if;
            
            		dbms_output.put_line('Group ID: ['||numGroupId||'] Group Name: ['||vch2OutText||'].');
            	exception
            		when others then -- No matter what exception it is.
            			vch2OutText := dbms_utility.format_error_backtrace||' '||SQLERRM;
            			dbms_output.put_line('Exception: '||vch2OutText);
            			-- raise; -- Similar to "throw" in Java; commented, not to throw in this sample.
            	end;
            end;
            
            SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45  /
            
            程序包体已创建。
            
            SQL> show errors;
            没有错误。
            SQL> exit;
            从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
            [typhoon@TFW-CENT6-LT typhoon]$
          • 调用:
            [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 vch2GroupName varchar2(128);
            SQL> call tfwPkgGroupMgr.procSendoutGroupName(0, :vch2GroupName);
            Procedure - Getting user name of ID 0:
            Group ID: [0] Group Name: [root].
            
            调用完成。
            
            SQL> print vch2GroupName;
            
            VCH2GROUPNAME
            --------------------------------------------------------------------------------
            root
            
            
            SQL> call tfwPkgGroupMgr.funcReturnGroupName(5) into :vch2GroupName;
            Function - Getting user name of ID 5:
            Procedure - Getting user name of ID 5:
            Group ID: [5] Group Name: [not found].
            
            调用完成。
            
            SQL> print vch2GroupName;
            
            VCH2GROUPNAME
            --------------------------------------------------------------------------------
            not found
            
            
            SQL> call tfwPkgGroupMgr.funcReturnGroupName('2') into :vch2GroupName;
            Function - Getting user name of ID 2:
            Procedure - Getting user name of ID 2:
            Group ID: [2] Group Name: [dept_01_mgr].
            
            调用完成。
            
            SQL> print vch2GroupName;
            
            VCH2GROUPNAME
            --------------------------------------------------------------------------------
            dept_01_mgr
            
            
            SQL> call tfwPkgGroupMgr.procSendoutGroupName(null, :vch2GroupName);
            Procedure - Getting user name of ID :
            Group ID: [] Group Name: [not found].
            
            调用完成。
            
            SQL> exit;
            从 Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 断开
            [typhoon@TFW-CENT6-LT typhoon]$
        4. ……