VastbaseG100

包的使用

CREATE PACKAGE

• 语法

  CREATE [ OR REPLACE ] PACKAGE name
  is
  [ variable type ] [; ...]
  [ { PROCEDURE proc_name
  [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
  [, ...]) ];
  |
  FUNCTION func_name
  [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
  [, ...]) ]
  RETURN rettype ;
  }
  ] [, ...]
  end;
  

• 示例

  create or replace package pkg
  is
  procedure prc(var_out int);
  FUNCTION func(var_out Out int) return int; --函数有out参数的情况
  FUNCTION func1(var_in text, var_out Out text) return text; --函数有out参数的情况
  end;
  /
  
  create package pkg2
  is 
  FUNCTION set_public(v int) RETURN int;
  FUNCTION get_public() RETURN int;
  FUNCTION set_private(v int) RETURN int;
  FUNCTION get_private() RETURN int;
  
  public_var int := (0 + 1);
  end;
  /
  

CREATE PACKAGE BODY

• 语法

  CREATE [ OR REPLACE ] PACKAGE BODY name
  AS 
  [ { PROCEDURE proc_name
  [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
  [, ...]) ]
  AS 
  program_body
  END ;
  |
  FUNCTION func_name
  [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
  [, ...]) ]
  RETURN rettype 
  AS 
  program_body
  END;
  }
  ] [, ...]
  

• 示例

  create or replace package body pkg
  as 
  FUNCTION func(var_out Out int) return int
  as 
  BEGIN
    select var_out into var_out;      
    end;
  
  FUNCTION func1(var_in text, var_out Out text) return text 
  as 
  BEGIN
    select var_in ;  
    select var_out ; 
  end;
  end;
  /
  
  create package body pkg2
  as 
  private_var int := (0+2);
  FUNCTION set_public(v int) RETURN int AS 
      BEGIN
          public_var :=  v;
          RETURN public_var;
      END;
  
  FUNCTION get_public() RETURN int AS 
      DECLARE
          ret int := public_var;
      BEGIN
          RETURN ret;
      END;
  
  FUNCTION set_private(v int) RETURN int AS 
      BEGIN
          private_var :=  v;
          RETURN private_var;
      END;
  FUNCTION get_private() RETURN int AS 
      DECLARE
      BEGIN
          RETURN private_var;
      END;
         
      BEGIN
          public_var := public_var + 10;
          private_var := private_var + 10;
      end;
  end;
  /