07_查询数据_使用函数和运算符_自定义函数_存储过程

一、在GP中使用函数

1、不变型函数

注：传递的值一样的，那么结果就是一样的 ；

 

 2、稳定型函数

 

 注：current_date返回当前的日期；

3、不稳定型函数

 

二、使用函数和运算符-常用内置函数和运算符

 

注：trim是去除两边的空格； 

       lpad是填充；

       upper转换为大写；lower转换为小写；

代码：

select extract(month from date '2022-10-30') ;

select date '2022-10-20' + interval '1 month' ;

select date '2022-10-31' + time '5:21' ;

select lpad('abcd', 10, 'z') ;（原本只有四位，需要填充为10位）

 

三、使用函数和运算符-窗口函数

 

四、使用函数和运算符-自定义函数

 GP支持SQL/PYTHON/PERL/C语言构建函数，介绍一下SQL存储过程：

 1、一个存储过程就是一个事务，包括对子过程的调用都在一个事务内存储过程结构：

注：自定义函数的加不是 + 而是 || ;

        存储过程的对象不可以直接用变量，要用 quote_ident(duixiang)

注：REPLACE（如果存在就是替换），increment（函数名），RETURNS（返回），DECLARE（变量的声明），plpgsql（使用的什么语言）；

自定义函数&存储过程： 

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS interger AS  $$

     DECLARE

              j int:=100;

     BEGIN

             RETURN i*j;

     END;

$$ LANGUAGE plpgsql;

代码：

 CREATE OR REPLACE FUNCTION increment(i integer) RETURNS interger AS  $$

     DECLARE

              j int:=100;

     BEGIN

             RETURN i*j;

     END;

$$ LANGUAGE plpgsql;

select incremen(6) ; （使用这个自定义函数，会返回600）

 

语法：

create or replace function 过程名(参数名 参数类型,......) returns 返回值类型 as
$body$
--声明变量
declare
--变量名变量类型;
--如：name varchar(255) ;
--变量赋值方式（变量名类型 := 值 ; ）
--如：str text := 值; / str text ; str := 值 ;
begin
--函数体
return 变量名 ; --存储过程中的返回语句
end ;
$body$
language plpgsql ;

 

 2、赋值

静态赋值：

 

动态赋值：

select name into student_name from table_class where stu_id = 1 ;

或者

execute ' select name from class where stu_id = 1 ' into student_name ; 

 

3、存储过程中，使用RAISE NOTICE可以在运行时将变量输出显示

（1）if语句

IF ... THEN ... END IF;
IF ... THEN ... ELSE ... END IF;
IF ... THEN ... ELSE ... THEN ... ELSE ... END IF;
--例：
if student_name = '张静' then
    RAISE NOTICE '我是张静';
else if student_name like '%李%' then
    RAISE NOTICE '我姓李';
else
    RAISE NOTICE '我不是张静，也不姓李';

 

（2）case语句

CASE ... WHEN ... THEN ... ELSE ... END CASE;
CASE WHEN ... THEN ... ELSE ... END CASE;
--例：
case student_name when '张静','晓静' then
    RAISE NOTICE '张静和晓静都是我的名称';
else 
    RAISE NOTICE '你叫错名字了';
end ;

 

（3）循环

  1）LOOP

LOOP
    循环体语句;
    EXIT [ label ] [ WHEN 判断条件表达式 ];
END LOOP [ label ];

--例-计算1到100的和：
sum := 0;
i := 0;
loop
    i := i + 1;
    sum := sum + i;
    exit when i = 100 ;
end loop;
RAISE NOTICE '1到100的和为：%',sum;

 

  2）WHILE

WHILE 判断条件表达式 LOOP
    循环体语句;
END LOOP [ label ];

--例 - 计算1到100的和：
sum := 0;
i := 1;
while i<=100 loop
    sum := sum + i;
    i := i + 1;
end loop
RAISE NOTICE '1到100的和为：%',sum;

 

  3）

FOR 循环控制变量 IN [ REVERSE ] 循环范围 [ BY expression ] LOOP
    循环体语句;
END LOOP [ label ];

--计算1到100的和：
--例1 - 循环执行过程类似于：for(i=1;i<=100;i++){}
sum := 0;
for i in 1..100 loop
    sum := sum + i;
end loop;
RAISE NOTICE '1到100的和为：%',sum;

--例2 - 循环执行过程类似于：for(i=100;i>=1;i--){}
sum := 0;
for i in REVERSE 100..1 loop
    sum := sum + i;
end loop;
RAISE NOTICE '1到100的和为：%',sum;

--计算1到100之间所有奇数的和
--例3 - 循环执行过程类似于：for(i=1;i<=100;i=i+2){}
sum := 0;
for i in 1..100 by 2 loop
    sum := sum + i;
end loop;
RAISE NOTICE '1到100的和为：%',sum;

FOR 变量 IN 查询语句 LOOP
    循环体语句;
END LOOP [ label ];

--例 - 遍历班级中每个人的名字：
for student_name in select name from class loop
    RAISE NOTICE '姓名：%',student_name;
end loop;

 

 

例子：

create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
    mysql text;
    myID integer;
begin
    mysql:='select max('
        || quote_ident(myFeildName) 
        ||') from '
        || quote_ident(myTableName);
    execute mysql into myID; 
    --using myTableName,myFeildName;
    
    if myID is null or myID=0 then return 1;
    else return myID+1;
      end if;
end;
$$ language plpgsql;

 

注：function的参数可以用$1、$2等，如：quote_ident(myFeildName)可写为$2                                                                

                                                            quote_ident(myTableName) 可写为$1

 

4、执行一个没有结果的查询：PERFORM query;

 

 

 注：command-string（可以使字符串），RETUEN（返回一次），RETURN NEXT（一直返回）

代码：

select id into var_id from tb_cp_02 ;（获取tb_cp_02的id）

select * from var_id ;

 

 

总结：

  运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快；

  可维护性：存储过程有时候比程序更容易维护，这是因为可以实时更新存储过程。有些bug直接改存储过程中的业务逻辑，就搞定了；

  可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后额扩展和DBA维护优化；