GAUSSDB-存储过程
【摘要】 语法格式CREATE [ OR REPLACE ] PROCEDURE procedure_name [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ] [ { IMMUTABLE | STABLE | VOLATILE } | { SHIP...
语法格式
CREATE [ OR REPLACE ] PROCEDURE procedure_name
[ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ]
[
{ IMMUTABLE | STABLE | VOLATILE }
| { SHIPPABLE | NOT SHIPPABLE }
| {PACKAGE}
| [ NOT ] LEAKPROOF
| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
| {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER}
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter { [ TO | = ] value | FROM CURRENT }
][ ... ]
{ IS | AS }
plsql_body
/
参数详细说明
OR REPLACE
当存在同名的存储过程时，替换原来的定义。
procedure_name
创建的存储过程名字，可以带有模式名。
取值范围：字符串，要符合标识符的命名规范。
argmode
参数的模式。
须知：
VARIADIC用于声明数组类型的参数。
取值范围： IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数后面能跟VARIADIC。并且OUT和INOUT模式的参数不能用在RETURNS TABLE的过程定义中。
argname
参数的名字。
取值范围：字符串，要符合标识符的命名规范。
argtype
参数的数据类型。
取值范围：可用的数据类型。
说明：
argname和argmode的顺序没有严格要求，推荐按照argname、argmode、argtype的顺序使用。
IMMUTABLE、STABLE等
行为约束可选项。各参数的功能与CREATE FUNCTION类似，详细说明见5.18.17.13-CREATE FUNCTION
plsql_body
PL/SQL存储过程体。
须知：
当在存储过程体中进行创建用户等涉及用户密码相关操作时，系统表及csv日志中会记录密码的明文。因此不建议用户在存储过程体中进行涉及用户密码的相关操作。
注意事项
(1)、函数创建也适用于存储过程，更多内容请参考CREATE FUNCTION。
(2)、如果创建存储过程时参数或返回值带有精度，不进行精度检测。
(3)、创建存储过程时，存储过程定义中对表对象的操作建议都显示指定模式，否则可能会导致存储过程执行异常。
(4)、在创建存储过程时，存储过程内部通过SET语句设置current_schema和search_path无效。执行完函数search_path和current_schema与执行函数前的search_path和current_schema保持一致。
(5)、如果存储过程参数中带有出参，SELECT调用存储过程必须缺省出参，CALL调用存储过程适配Oracle，调用非重载函数时必须指定出参，对于重载的package函数，out参数可以缺省，具体信息参见CALL的示例。
(6)、存储过程指定package属性时支持重载。
(7)、在创建procedure时，不能在avg函数外面嵌套其他agg函数，或者其他系统函数。
(8)、创建存储过程后查询定义时会返回CREATE FUNCTION的定义。
(9)、存储过程可以有多个返回值，也可以没有返回值。无返回值的存储过程调用后会有空的回显。
(10)、存储过程不支持分段提交。
示例
【方法1】
create procedure insert_data(num int)
as
declare id int;
begin
for logid in 1000000..(1000000+num-1) loop
insert into student values(id,now());
dbe_output.print_line('id='||id); --|| 用于做字符拼接
end loop;
end;
/
【方法2】
create or replace procedure insert_data(num int)
as
declare i int;
id int;
begin
id :=100000;
for i in 1..num loop
insert into student values(id,sysdate);
id :=id+1;
dbe_output.print_line('id='||id); --|| 用于做字符拼接
end loop;
end;
/
【方法3】
create or replace procedure insert_data(num int)
as
begin
for i in 1.. num loop
insert into student values(100000+i-1,now());
RAISE NOTICE 'id=%',i;
end loop;
RAISE NOTICE '插入操作完成';
end;
/
【方法4】
create or replace procedure insert_data(num int)
as
begin
for id in 100000 .. (100000 + num-1) loop
insert into student values(id,now());
RAISE NOTICE 'id=%',id;
end loop;
RAISE NOTICE '插入操作完成';
end;
/
【方法1】
create or replace procedure proc_dept()
as
declare
v_dept_id int;
v_dept_name text;
v_dept_count int;
cursor c_dept is select id,name from dept;
begin
open c_dept;
loop
fetch c_dept into v_dept_id,v_dept_name;
exit when c_dept%NOTFOUND;
v_dept_count := (select count(*) from Teacher where dept_no=v_dept_id);
RAISE NOTICE '% --- %', v_dept_name, v_dept_count;
end loop;
close c_dept;
end;
/
【方法2】
create or replace procedure pro_count()
as
declare
cursor C1 is select ,count(*) from dept d,teacher t where d.id=t.dept_no group by ;
v_name varchar(50);
v_count int;
begin
open C1;loop
fetch next from C1 into v_name,v_count;
exit when C1%NOTFOUND;
raise notice '%---%',v_name,v_count;
end loop;
close C1;
end;
/
【方法3】
---创建存储过程
create or replace procedure pro_curs_1()
as
--定义游标
declare cursor cur1 is
select d.name as dn,count(*) as pc from teacher t,department d
where t.deptno = d.id group by d.name order by pc desc;
--调用游标
begin
for i in cur1 loop
--使用连接操作符“ ||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。
---DBE_OUTPUT.PRINT无法换行，导致无法输出
--DBE_OUTPUT.PRINT_LINE函数的参数必须是字符串类型（VARCHAR2）。
--当你使用||操作符连接字符串时，所有参与连接的操作数都必须是字符串类型。如果pc是int类型，直接与字符串连接会导致类型不匹配的错误。
DBE_OUTPUT.PRINT_LINE(i.dn||'---'||i.pc::varchar);
end loop;
end;
/
输出teacher表salary最高的3个和最低的3个教师信息。
【方法1】
create or replace procedure proc_teacher() as
declare
v_id int;
v_name text;
v_depname text;
v_salary int;
v_title text;
cursor c_teacher is
select
,
,
dep,
salary,
Title
from
(
select
ID ,
name ,
dept_no ,
salary ,
Title,
rank() over(order by salary desc) as rn_desc,
rank() over(order by salary) as rn_asc
from teacher) t
inner join dept on dep = t.dept_no
where rn_desc <= 3 or rn_asc <= 3;
begin
open c_teacher;
loop
fetch c_teacher into v_id,v_name,v_depname,v_salary,v_title;
exit when c_teacher%NOTFOUND;
raise info '%,%,%,%,%',v_id,v_name,v_depname,v_salary,v_title;
end loop;
close c_teacher;
end;
/
【方法2】
create or replace procedure proc_teacher() as
declare
v_id int;
v_name text;
v_depname text;
v_salary int;
v_title text;
type cursor_type is ref cursor;
c_teacher cursor_type;
begin
open c_teacher for
select
,
,
dep,
salary,
Title
from
(
select
ID ,
name ,
dept_no ,
salary ,
Title,
rank() over(order by salary desc) as rn_desc,
rank() over(order by salary) as rn_asc
from teacher) t
inner join dept on
dep = t.dept_no
where
rn_desc <= 3 or rn_asc <= 3;
loop
fetch c_teacher into v_id,v_name,v_depname,v_salary,v_title;
exit when c_teacher%NOTFOUND;
raise info '%,%,%,%,%',v_id,v_name,v_depname,v_salary,v_title;
end loop;
close c_teacher;
end;
/
【方法3】
create or replace procedure proc_csor2()
as
declare
v_id int;
v_name text;
v_dept_name text;
v_salary int;
v_title text;
type my_cursor_type is ref cursor;
csor1 my_cursor_type;
begin
open csor1 for 'SELECT * FROM (
SELECT , , , t.salary, t.title,
RANK() OVER(ORDER BY t.salary DESC) AS ranking_sal_desc,
RANK() OVER(ORDER BY t.salary ASC) AS ranking_sal_asc
FROM Teacher t, Dept d
WHERE t.dept_no=d.id
) a
WHERE a.ranking_sal_desc<=3 OR a.ranking_sal_asc<=3';
LOOP
FETCH NEXT FROM csor1 INTO v_id, v_name, v_dept_name, v_salary, v_title;
EXIT WHEN NOT FOUND;
raise info '%,%,%,%,%',v_id,v_name,v_dept_name,v_salary,v_title;
END LOOP;
close csor1;
end;
/
【方法4】
create or replace procedure get_sal_detail()
as
declare
var_teacher Teacher%ROWTYPE;
cursor cur is select id,name,dept_no,salary,Title from
(
select *,
dense_rank() over(order by salary desc) rd,
dense_rank() over(order by salary asc) ra
from Teacher
) where rd<=3 or ra<=3;
begin
open cur;
loop
fetch cur into var_teacher;
exit when cur%NOTFOUND;
raise notice '%,%,%,%,%',var_teacher.id,var_teacher.name,var_teacher.dept_no,var_teacher.salary,var_teacher.Title;
end loop;
close cur;
end;
/
【方法5】
创建存储过程pro_curs_2,使用游标读取薪水按降序排序的前三位老师和后三位老师的信息，分别获取ID，姓名，部门名称，薪水和职称，请按以下格式打印ID-姓名-部门名称-薪水-职称
create or replace procedure pro_curs_2() as
declare cursor curs_2 is
select * from ((select t.id,t.name as tname,d.name as dname,t.salary from TEACHER t,department d where t.deptno=d.id order by salary desc limit 3)
union all (select t.id,t.name as tname,d.name as dname,t.salary from TEACHER t,department d where t.deptno=d.id order by salary limit 3)) order by salary desc;
begin
for i in curs_2 loop
DBE_OUTPUT.PRINT_LINE(i.id::varchar||'--'||i.tname||'--'||i.dname||'--'||i.salary::varchar);
end loop;
end;
/
call pro_curs_2();
编写存储过程，输入学生id返回总成绩
【方法1】
create or replace procedure id_print_score(var_id int,total_score out int )
as
begin
select sum(math+pysical+art+music) into total_score from student where student_id=var_id group by student_id;
return total_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN raise EXCEPTION '没有对应id的记录';
end;
/
【方法2】
create or replace procedure gettotalscore(vid int,totalscore out int)
as
begin
return (select sum(math+pysical+art+music) from student where student_id=vid);
end;
/
【方法3】
create or replace procedure pro_total_score(id int,totalscore out int)
as
begin
totalscore:=(select sum(math+pysical+art+music) from student where student_id=id);
end;
/
编写存储过程，输入学号和科目名称，返回对应的平均成绩
【方法1】
create or replace procedure pro_avg_score(id int,coursename varchar(20),avgscore out int)
as
begin
case
when coursename='math' then
select avg(math) into avgscore from student where student_id=id;
when coursename='pysical' then
select avg(pysical) into avgscore from student where student_id=id;
when coursename='art' then
select avg(art) into avgscore from student where student_id=id;
when coursename='music' then
select avg(music) into avgscore from student where student_id=id;
else raise notice 'input error';
end case;
end;
/
【方法2】
create or replace procedure id_km_avg(var_id int,var_km text,avg_score out decimal(10,2))
as
declare var_sql text;
begin
var_sql:='select avg('||var_km||') from student where student_id='||var_id;
execute var_sql into avg_score ;
IF avg_score IS NOT NULL then return avg_score;
else raise exception '没有对应的记录1';
end if;
exception
when no_data_found then raise exception '没有对应的记录2';
when others then raise exception '没有对应的记录3';
end;
/
编写存储过程，对于学生每科成绩，0-59，绩点0,60-69绩点0.1,70-79绩点0.2,80-89绩点0.3,90-100绩点0.4，输入学号和科目名称，返回对应的绩点
【方法1】
CREATE OR REPLACE PROCEDURE jd_point(id int,km varchar(200),point OUT float)
AS
BEGIN
IF km='math' THEN
SELECT CASE
WHEN math<=59 THEN '0'
WHEN math<=69 THEN '0.1'
WHEN math<=79 THEN '0.2'
WHEN math<=89 THEN '0.3'
WHEN math<=100 THEN '0.4'
ELSE '0'
END
INTO point FROM student s WHERE s.student_id=id LIMIT 1;
ELSEIF km='pysical' THEN
SELECT CASE
WHEN pysical<=59 THEN '0'
WHEN pysical<=69 THEN '0.1'
WHEN pysical<=79 THEN '0.2'
WHEN pysical<=89 THEN '0.3'
WHEN pysical<=100 THEN '0.4'
ELSE '0'
END
INTO point FROM student s WHERE s.student_id=id LIMIT 1;
ELSEIF km='art' THEN
SELECT CASE
WHEN art<=59 THEN '0'
WHEN art<=69 THEN '0.1'
WHEN art<=79 THEN '0.2'
WHEN art<=89 THEN '0.3'
WHEN art<=100 THEN '0.4'
ELSE '0'
END
INTO point FROM student s WHERE s.student_id=id LIMIT 1;
ELSEIF km='music' THEN
SELECT CASE
WHEN music<=59 THEN '0'
WHEN music<=69 THEN '0.1'
WHEN music<=79 THEN '0.2'
WHEN music<=89 THEN '0.3'
WHEN music<=100 THEN '0.4'
ELSE '0'
END
INTO point FROM student s WHERE s.student_id=id LIMIT 1;
END IF;
END;
/
【方法2】
create or replace procedure print_point(var_id int,var_km text, var_point out decimal(10,2))
as
declare var_sql text;
begin
var_sql:= 'select
case
when '||var_km||'<60 then 0
when '||var_km||'<70 then 0.1
when '||var_km||'<80 then 0.2
when '||var_km||'<90 then 0.3
else 0.4 end
from student
where student_id='||var_id||' limit 1';
execute var_sql into var_point;
IF var_point IS NOT NULL then return var_point;
else raise exception '没有对应的记录1';
end if;
exception
when no_data_found then raise exception '没有对应的记录2';
when others then raise exception '没有对应的记录3';
end;
/
要求输入编号和课程返回成绩信息(课程和 id 本身带有单引号)
create or replace procedure get_score(var_id text,var_cid text,var_score out int)
as
begin
select score into var_score from score where id=concat('''',var_id,'''') and cid=concat('''',var_cid,'''');
end;
/
创建存储过程pro_curs_1,使用游标打印各部门总人数，按照人数降序排序，打印格式如下 ：部门名称1—人数部门名称2—人数打印操作可以使用DBE_OUTPUT.PRINT(outputstr)接口
---创建存储过程
create or replace procedure pro_curs_1()
as
--定义游标
declare cursor cur1 is
select d.name as dn,count(*) as pc from teacher t,department d
where t.deptno = d.id group by d.name order by pc desc;
--调用游标
begin
for i in cur1 loop
--使用连接操作符“ ||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。
---DBE_OUTPUT.PRINT无法换行，导致无法输出
--DBE_OUTPUT.PRINT_LINE函数的参数必须是字符串类型（VARCHAR2）。
--当你使用||操作符连接字符串时，所有参与连接的操作数都必须是字符串类型。如果pc是int类型，直接与字符串连接会导致类型不匹配的错误。
DBE_OUTPUT.PRINT_LINE(i.dn||'---'||i.pc::varchar);
end loop;
end;
/
select pro_curs_1();
call pro_curs_1();
--使用raise notice输出
create or replace procedure pro_curs_1()
as
declare cursor cur1 is
select d.name as dn,count(*) as pc from teacher t,department d
where t.deptno = d.id group by d.name order by pc desc;
begin
for i in cur1 loop
raise notice '%-%',i.dn,i.pc; -- 可以用raise方法输出
end loop;
end;
---简化写法，直接调用游标
BEGIN
FOR ROW_TRANS IN select d.name as dn,count(*) as pc from teacher t,department d
where t.deptno = d.id group by d.name order by pc desc
LOOP
DBE_OUTPUT.PRINT_LINE(ROW_TRANS.dn);
END LOOP;
END;
/
create or replace procedure pro_curs_2() as
declare cursor curs_2 is
select * from ((select t.id,t.name as tname,d.name as dname,t.salary from TEACHER t,department d where t.deptno=d.id order by salary desc limit 3)
union all (select t.id,t.name as tname,d.name as dname,t.salary from TEACHER t,department d where t.deptno=d.id order by salary limit 3)) order by salary desc;
begin
for i in curs_2 loop
DBE_OUTPUT.PRINT_LINE(i.id::varchar||'--'||i.tname||'--'||i.dname||'--'||i.salary::varchar);
end loop;
end;
/
call pro_curs_2();
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）