postgresqlprocedure存储过程
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 >100then
return counts;
小程序怎么打开else
return0;
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;
paddles导联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 ';  --打印消息
return unbound_refcursor; --为函数返回⼀个游标
exception when others then--抛出异常
raise exception 'error-----(%)',sqlerrm;--字符“%”是后⾯要显⽰的数据的占位符xml文件用什么语言写的
end;  --结束
$BODY$
LANGUAGE plpgsql;  --规定语⾔
select cursor_demo()；--调⽤
总结⼀下：
定义变量是在begin前
python中replace函数的用法
变量赋值时使⽤：=
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;
mysql语句的执行顺序$$ language plpgsql;
2、存储过程的对象不可以直接⽤变量，要⽤ quote_ident(objVar)
3、$1  $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后⾯的不换结果：select max(myTableName) from myFeildname
4、注意：SQL语句中的⼤写全部会变成⼩写，要想⼤写存⼤，必须要⽤双引号。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。