gaussdb200 存储过程

教程 犀牛 ⋅ 于 2021-06-09 22:35:32 ⋅ 最后回复由 yang 2021-06-10 14:19:10 ⋅ 2371 阅读

gaussdb200存储过程

  • 声明部分:声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数。

    DECLARE
  • 执行部分:过程及SQL语句,程序的主要部分。必选。

    BEGIN
  • 执行异常部分:错误处理。可选。

    EXCEPTION
  • 结束

    END;
    /
  • 存储过程简单示例

    DECLARE
    var varchar(20);
    num1 integer;
    BEGIN
    var := 'xiniu';
    dbms_output.put_line('hello '||var);
    num1 := 1/0;
    EXCEPTION
      WHEN division_by_zero THEN
          RAISE NOTICE 'caught division_by_zero';
    END;
    /

file

  • 存储过程变量声明与赋值

    DECLARE
    -- 变量声明并赋值
    VAR1 integer := 1;
    -- 变量声明
    VAR2 varchar(20);
    BEGIN
    -- dbms_output.put_line为打印到控制台语句,|| 为字符串拼接
    dbms_output.put_line('变量var1声明时的初始值:'||VAR1);
    -- 为变量赋值
    VAR1 := 2;
    VAR2 := VAR1 * 100;
    dbms_output.put_line('变量var1重新赋值后的值:'||VAR1);
    dbms_output.put_line('变量var2的值:'||VAR2);
    END;
    /
    

file

  • 调用语句

    -- 创建存储过程customer_address_procedure
    CREATE OR REPLACE PROCEDURE customer_address_procedure
    (
    -- 没有带输入输出关键字的字段是输入字段
    ca_address_sk_in     NUMBER(6),
    -- out 关键字代表是输出字段
    sk_sum out NUMBER(8,2),
    sk_cnt out INTEGER
    )
    IS
    BEGIN
    -- 使用into关键字为后面的变量赋值
    SELECT sum(ca_address_id), count(*) INTO sk_sum, sk_cnt FROM myschema.customer_address where ca_address_sk = ca_address_sk_in;
    END;
    /
    
    -- 创建存储过程customer_address_procedure2
    CREATE OR REPLACE PROCEDURE customer_address_procedure2
    AS
    -- 声明变量
    v_num NUMBER(8,2);
    v_sum INTEGER;
    BEGIN
    -- 调用语句 并且给两个变量赋值
    customer_address_procedure(1, v_sum, v_num);
    -- 打印输出
    dbms_output.put_line(v_sum||'#'||v_num);
    -- 返回语句
    RETURN;
    END;
    /
    
    -- 调用存储过程customer_address_procedure2.
    CALL customer_address_procedure2();
    
    -- 清除存储过程
    DROP PROCEDURE customer_address_procedure2;
    DROP PROCEDURE customer_address_procedure;
    
    -- 创建函数func_return.
    CREATE OR REPLACE FUNCTION func_return returns void
    language plpgsql
    AS $$
    DECLARE
    v_num INTEGER := 1;
    BEGIN
    dbms_output.put_line(v_num);
    -- 返回语句
    RETURN;
    END $$;
    
    -- 调用函数func_return
    CALL func_return();
    
    -- 清除函数
    DROP FUNCTION func_return;

file

file

gaussdb200函数与返回值

  • 无参函数声明,指定类型返回

    -- 无参函数 返回类型为integer
    CREATE OR REPLACE FUNCTION void_func() RETURNS integer 
    -- 必须指定过程语言(PL/PGSQL)
    language plpgsql
    AS $$
    DECLARE
    -- 声明一个int类型变量并赋初始值为0
    res integer := 0;
    BEGIN
    -- 循环1到10
      FOR i IN 1 .. 10
      LOOP
      -- 给res赋值
         res:=res + i;
         dbms_output.put_line('i:'||i);
         dbms_output.put_line('当前res值:'||res);
      END LOOP;
      -- 返回res
      RETURN res;
    END $$;
    
    -- 调用函数
    call void_func();
    
    -- 删除函数
    drop function void_func();

file

  • 有参函数构建

    -- 创建一张临时表并插入数据
    CREATE TABLE t1(a int);
    INSERT INTO t1 VALUES(1),(10),(100),(1000),(10000);
    
    -- 创建有参函数
    create or replace function func(insert_data int) returns void
    language plpgsql
    as $$
    begin
    -- 插入数据
    insert into t1 values (insert_data);
    end $$;
    -- 调用函数
    call func(100000);
    -- 查询表
    select * from t1;
    -- 删除函数
    drop function func;
    -- 清除表
    drop table t1;

file

存储过程操作分区

  • 创建分区
-- 创建测试表
CREATE TABLE myschema.procedure_opt_partition
(
    pk                  integer                  NOT NULL   ,
    datadate            date                     NOT NULL   ,
    col1                character(10)                       ,
    col2                character varying(60)               ,
    col3                character(15)               
)
TABLESPACE xiniu
DISTRIBUTE BY HASH (pk)
PARTITION BY RANGE (datadate)
(
        PARTITION p_10000101 VALUES LESS THAN('10000101')
)
ENABLE ROW MOVEMENT;
-- 需求说明:
-- 如上表每天有一个分区,使用datadate作为分区字段,如果一次性创建好未来若干年的分区,对索引影响较大,所以我们每天创建分区,此时就使用存储过程来创建。
DROP FUNCTION IF EXISTS add_partition(varchar(10),varchar(100),varchar(10),varchar(10));
CREATE OR REPLACE FUNCTION add_partition(schema_name varchar(10),table_name varchar(100),partition_name varchar(10), batch_date varchar(10))
RETURNS void AS  $$
DECLARE
    V_SQL1 TEXT;
    V_SQL2 TEXT;
    V_RETURN TEXT;
    V_RETURN2 TEXT;
BEGIN
    raise notice 'jedge partiton exists or no !';
    V_SQL1 = '
        select count(1) from DBA_TAB_PARTITIONS where lower(table_name)='''||table_name||''' and lower(schema)='''||schema_name||''' and partition_name='''||partition_name||'''
    ';
    raise notice 'jedge partition sql:%',V_SQL1;
    EXECUTE V_SQL1 INTO V_RETURN;
    raise notice 'jedge table result:%',V_RETURN;
    if V_RETURN = 0 then
        V_SQL2 = 'alter table '||schema_name||'.'||table_name||' add partition '||partition_name||' values less than  (date'''||batch_date||''')';
        raise notice 'add partition sql:%',V_SQL2;
        EXECUTE V_SQL2;
    end if;
END
$$ LANGUAGE plpgsql;
-- 调用
call add_partition('myschema','procedure_opt_partition','p_20210609','20210609');
-- 查询分区
select * from DBA_TAB_PARTITIONS where lower(table_name)= 'procedure_opt_partition' and lower(schema)= 'myschema';
-- 清除表
drop table myschema.procedure_opt_partition;

file

file

版权声明:原创作品,允许转载,转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海汼部落-犀牛,http://hainiubl.com/topics/75661
本帖由 青牛 于 1年前 解除加精
回复数量: 1
  • yang
    2021-06-10 14:19:10

    犀牛老师辛苦了:heartbeat:

暂无评论~~
  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif,教程
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
Ctrl+Enter