postgresql procedure 存储过程

QUOTE_IDENT 函数以双引号字符串的形式返回指定字符串,以便此字符串可用作 SQL 语句中的标识符。

在 sql 语句中，大写，全部会变成小写，如果想保留大写，需要加 双引号

基础表

create table student
(
    id   integer not null primary key ,
    name varchar(255),
    sex  varchar
);

存储过程（存过）

select * from student;

create table teacher as select * from student;

---------------11111111111111111
CREATE extension "uuid-ossp";
SELECT    uuid_generate_v4 ( );

--立即执行存储过程
DO $$
DECLARE
    v_idx INTEGER := 1;
BEGIN
        while
        v_idx < 300000
        loop
        v_idx = v_idx + 1;
    INSERT INTO "public"."student" ( "id", "name" )
    VALUES
        ( uuid_generate_v4 ( ), 'bobo' );

END loop;
END $$;

---------------22222222222222222


create or replace function count_student1()
returns integer as $$

declare
 pgSqlScript text;
 counts integer;

begin
pgSqlScript:='select count("name") from "student"';
execute pgSqlScript into counts;
return counts;
end;

$$ language plpgsql;


select count_student1();



---------------333333333333333
create or replace function count_student2(tableName text, columnName text)
returns text as $$

declare
 pgSqlScript text;
 counts integer;
begin
pgSqlScript:='select count('    || quote_ident(columnName)    || ') from '    || quote_ident(tableName);

return pgSqlScript;

end;

$$ language plpgsql;

select count_student2('student','name');




---------------4444444444444444
drop function count_student3;
create or replace function count_student3(tableName text, columnName text)
returns integer as $$

declare
 pgSqlScript text;
 counts integer;
begin
pgSqlScript:='select count('    || quote_ident(columnName)    || ') from '    || quote_ident(tableName);
execute pgSqlScript into counts;

if counts > 100 then
    return counts;
else
    return 0;
end if;

end;

$$ language plpgsql;

select count_student3('student','name');





---------------555555555555555
create or replace function count_student5()
returns integer as $$

declare
 pgSqlScript text;
 myid integer;
 myname varchar;

begin
pgSqlScript:='select id,name from student order by "id" asc ';
execute pgSqlScript into myid,myname;--　可以同时赋多个值，但只会塞入最后一行数据

raise notice 'result is %' , myname; --打印语句
return myid;
end;

$$ language plpgsql;


select count_student5();


select id,name from student order by id;

delete from teacher;
select * from teacher;
select * from student;
insert into teacher select * from student;

update teacher T1 set name = T2.name from student T2 where T1.id = T2.id;

 游标使用

CREATE OR REPLACE FUNCTION cursor_demo() 
  RETURNS refcursor AS  --返回一个游标
$BODY$ 
declare  --定义变量及游标
    unbound_refcursor refcursor;  --游标
    t_accid varchar;    --变量
        t_accid2 int;    --变量
 
begin  --函数开始
    open unbound_refcursor for execute 'select name from cities_bak';  --打开游标 并注入要搜索的字段的记录
    loop  --开始循环
        fetch unbound_refcursor into t_accid;  --将游标指定的值赋值给变量
           
        if found then  --任意的逻辑
            raise notice '%-',t_accid; 
        else 
            exit; 
        end if; 
    end loop;  --结束循环
    close unbound_refcursor;  --关闭游标
    raise notice 'the end of msg...';  --打印消息
    return unbound_refcursor; --为函数返回一个游标
exception when others then  --抛出异常
    raise exception 'error-----(%)',sqlerrm;--字符“%”是后面要显示的数据的占位符
end;  --结束
$BODY$ 
 
  LANGUAGE plpgsql;  --规定语言

select cursor_demo()； --调用

总结一下：

定义变量是在begin前

变量赋值时使用 ：=

select 中赋值使用into

1、存储过程（FUNCITON）变量可以直接用  || 拼接。上面没有列出，下面给个栗子：

　　 

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

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

3、$1  $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后面的不换 结果 ：select max(myTableName) from myFeildname

4、注意：SQL语句中的大写全部会变成小写，要想大写存大，必须要用双引号。

Postgresql 存储过程(plpgsql)两层for循环的操作==>https://www.jb51.net/article/204229.htm