Oracle PL/SQL Review (P4)

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