PLSQL 变量/cursor / function / procedure / package

变量，cursor，function，procedure ,package
1.	变量：
        命名规则：添加前缀.
        Constant                             c_
        Global package variable         g_
        Local variable                       l_
        Parameter                          p_
        Cursor                                csr_
        Record                               rec_

 保证所有变量只被赋值一次。每个变量的功能只有一个。尽可能不声明多余的变量，用只读的function代替，或作为实参传入function。因为作为参数在function中是只读的，不会担心值会被变更。去除多余的变量，也是重构的主要手段。

例子：1.1
declare
  FUNCTION get_name(p_empno IN VARCHAR2) RETURN ps_employee.name%type IS
    retval ps_employee.name%type;
    CURSOR csr_emp IS
      SELECT name FROM ps_employee WHERE empno = p_empno;
  BEGIN
    OPEN csr_emp;
    FETCH csr_emp
      INTO retval;
    CLOSE csr_emp;
    RETURN retval;
  END;
  procedure show_name(p_empno in varchar2) is
  begin
    dbms_output.put_line(get_name(p_empno));
  end;
begin
  dbms_application_info.set_client_info(104);
  show_name('C6081');
end;

2.	Cursor:
隐式cursor，显式cursor, cursor reference 。
            隐式cursor：select into。。。只有在使用 max，min，sum......其他情况尽可能使用显示cursor。
            显式cursor：声明后的cursor。 尽可能避免select into语句。
            cursor reference：声明后的cursor变量，一般与动态sql结合使用。在使用时才决定sql语句。

如果fetch into 多个变量时，建议使用record或type，而不是声明多个变量。

例子：2.1
A:隐式cursor：
declare
  l_name ps_employee.name%type;
begin
  select name
    into l_name
    from ps_employee
   where empno = 'C6081';
  dbms_output.put_line(l_name);
end;
	B: 顯式cursor： 参考例1.1。
	C: Cursor Reference:
例子:2.2
declare
  Function get_name(p_empno in varchar2) Return ps_employee.name%type Is
    Retval  ps_employee.name%type;
    csr_emp sys_refcursor;
  Begin
    Open csr_emp For 'Select name from ps_employee where empno = :1'
      Using p_empno;
    Fetch csr_emp
      Into Retval;
    Close csr_emp;
    Return Retval;
  End;
begin
  dbms_output.put_line(get_name('C6081'));
end;

如果想确认是否有值，不要使用count。使用cursor fetch 1条语句就够了。
例子2.3:

declare
  Function has_emp(p_empno in varchar2) Return BOOLEAN Is
    Retval  boolean;
    l_dummy number;
    csr_emp sys_refcursor;
  Begin
    Open csr_emp For 'Select 1 from ps_employee where empno = :1'
      Using p_empno;
    Fetch csr_emp
      Into l_dummy;
    IF csr_emp%notfound then
      retval := false;
    else
      retval := true;
    end if;
    Close csr_emp;
    Return Retval;
  End;
begin
  if has_emp('C6081') then
    dbms_output.put_line('HAS EMP');
  else
    dbms_output.put_line('HAS NOT EMP');
  end if;
end;

使用动态sql：
1，运行时执行语法检查
2，查询sql动态绑定变量。
3，绕过安全机制
例如：不同 db link，如何在编译时绕过安全机制。

3.	Function：我们用来代替变量，进行简单查询，对程序进行重构。降低程序或sql的复杂度。

取一个好名字，能达到自我描述，前缀一般是get_,is_,has_ ... function中不建议含有update语句，做无公害function。 function中建议不超过5句分号，尽量缩小function的颗粒度，尽可能的重用。
只有一个return 语句， one way in，one way out（门卫模式下除外）。

 例子3.1：
select empno,
        gk_hr_fnd.decode('TITLE_CODE', TITLE_CODE) title,
        gk_hr_fnd.decode('SHAPE', SHAPE) SHAPE
FROM PS_EMPLOYEE   

函数模板：
  FUNCTION get_  (p_ IN VARCHAR2) RETURN  type IS
    retval  type;
    CURSOR csr_emp IS
      SELECT name FROM ps_employee WHERE empno = p_empno;
  BEGIN
    OPEN csr_emp;
    FETCH csr_emp
      INTO retval;
    CLOSE csr_emp;
    RETURN retval;
  END;

4.	procedure：取一个好名字，一般不要超过5个分号。
procedure中不要hard code，尽量不要放sql语句。sql语句也是hard code。
我们的程序中的异常大都是sql所抛出的：破坏主键唯一性，栏位不可为空等。。。
可以特别建立insert ，update，delete 的procedure，供其他程序调用。还可以在这些proc中添加check的动作，或其他check業務邏輯的代码。

 procedure 和 function还解决一些常见问题：重复代码，方法过长，条件逻辑太复杂等。
重复代码例子： 例4.1
declare
  l_title varchar2(100);
  l_shape varchar2(100);
  l_emp   ps_employee%rowtype;
begin
  dbms_application_info.set_client_info(250);
  select * INTO l_emp from ps_employee where empno = 'C6081';
  select meaning
    into l_title
    from ps_lookups
   where lookup_type = 'TITLE_CODE'
     AND lookup_code = l_emp.title_code;
  select meaning
    into l_shape
    from ps_lookups
   where lookup_type = 'SHAPE'
     AND lookup_code = l_emp.shape;
  DBMS_OUTPUT.PUT_LINE(l_title || ',' || l_shape);
end;

declare
  l_emp ps_employee%rowtype;
begin
  dbms_application_info.set_client_info(250);
  l_emp := gk_hr_fnd.emp('C6081');
  DBMS_OUTPUT.PUT_LINE(gk_hr_fnd.decode('TITLE_CODE', l_emp.TITLE_CODE) || ',' ||
                       gk_hr_fnd.decode('SHAPE', l_emp.shape));
end;

function过长例子： 大function提煉成小function

条件逻辑太复杂例子：例6
declare
  l_emp ps_employee%rowtype;
  function is_boy return boolean is
  begin
    return l_emp.sex = 'M';
  end;

  function is_adult return boolean is
  begin
    return(sysdate - l_emp.bir_date) / 365 > 20;
  end;

  function is_man return boolean is
  begin
    return is_boy and is_adult;
  end;
begin
  l_emp := gk_hr_fnd.emp('C6081');
  IF is_man then
    dbms_output.put_line(1);
  end if;
end;
5.	package：任何时候都应该使用package：较抽象的设计代码，可以将func，proc by功能模块化代码。较好的组织代码，可以重载function，procedure，2种访问模式：public，private。
重载的例子：（签名不同）例5.1
SELECT Gk_Hr_Utils.Get_Start_Date() FROM DUAL
SELECT Gk_Hr_Utils.Get_Start_Date('200808') FROM DUAL
全局变量是by session的，值也是by session缓冲的。。。
PKG: Gk_Hr_Duty_Kind_Pkg

也可以使用block 级别的func，proc。在func或proc中再声明proc或func。作用域仅是该funct，proc。 隐藏细节，封装业务。