VastbaseE100

包的使用

CREATE PACKAGE

• 语法

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

• 示例

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

CREATE PACKAGE BODY

• 语法

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

• 示例

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