GAUSSDB-存储过程

举报
yd_294088545 发表于 2026/01/13 21:51:03 2026/01/13
【摘要】 语法格式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)、存储过程不支持分段提交。

示例

--删除表
DROP TABLE IF EXISTS gaussdb.student;
--创建表
create table student(logid serial,starttime timestamp(0) not null,primary key (logid));

编写存储过程，生成记录，传入学生个数，学生LOGID从1000000开始，starttime为当前时间，生成student；
【方法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;
/

2、存储过程练习2
存在表Teacher（ID，姓名，部门编号，工资，职称）和部门表（2个字段，ID和部门名称）
--删除表
DROP TABLE IF EXISTS gaussdb.Teacher;
DROP TABLE IF EXISTS gaussdb.Dept;

--创建表
create table Teacher(ID int, name text, dept_no int, salary int, Title text);
create table Dept(id int, name text);

insert into Teacher select 1,'zhangsan1', 1, 5000, '教授';
insert into Teacher select 2,'zhangsan2', 1, 4900, '教授';
insert into Teacher select 3,'zhangsan3', 1, 4500, '副教授';
insert into Teacher select 4,'zhangsan4', 1, 1500, '讲师';
insert into Teacher select 5,'zhangsan5', 1, 1200, '讲师';
insert into Teacher select 6,'zhangsan6', 1, 4000, '教授';
insert into Teacher select 7,'zhangsan7', 2, 4000, '教授';
insert into Teacher select 8,'zhangsan8', 2, 3500, '副教授';
insert into Teacher select 9,'zhangsan9', 2, 3000, '副教授';
insert into Teacher select 10,'zhangsan10', 2, 4000, '教授';
insert into Teacher select 11,'zhangsan11', 2, 4000, '教授';
insert into Teacher select 12,'zhangsan12', 2, 1000, '讲师';

insert into Dept select 1,'清华';
insert into Dept select 2,'北大';

按指定要求输出各部门人数，输出格式如下：
部门名称1---部门人数
部门名称2---部门人数

【方法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;
/

3、存储过程练习3
存在表Teacher（ID，姓名，部门编号，工资，职称）和部门表（2个字段，ID和部门名称）
--删除表
DROP TABLE IF EXISTS gaussdb.Teacher;
DROP TABLE IF EXISTS gaussdb.Dept;

--创建表
create table Teacher(ID int, name text, dept_no int, salary int, Title text);
create table Dept(id int, name text);

insert into Teacher select 1,'zhangsan1', 1, 5000, '教授';
insert into Teacher select 2,'zhangsan2', 1, 4900, '教授';
insert into Teacher select 3,'zhangsan3', 1, 4500, '副教授';
insert into Teacher select 4,'zhangsan4', 1, 1500, '讲师';
insert into Teacher select 5,'zhangsan5', 1, 1200, '讲师';
insert into Teacher select 6,'zhangsan6', 1, 4000, '教授';
insert into Teacher select 7,'zhangsan7', 2, 4000, '教授';
insert into Teacher select 8,'zhangsan8', 2, 3500, '副教授';
insert into Teacher select 9,'zhangsan9', 2, 3000, '副教授';
insert into Teacher select 10,'zhangsan10', 2, 4000, '教授';
insert into Teacher select 11,'zhangsan11', 2, 4000, '教授';
insert into Teacher select 12,'zhangsan12', 2, 1000, '讲师';

insert into Dept select 1,'清华';
insert into Dept select 2,'北大';

输出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();

4、存储过程练习4
--删除表
DROP TABLE IF EXISTS gaussdb.student;
--创建表
create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);
--导入数据
insert into student values(1001,56,84,65,35),(1001,63,46,82,46),(1001,85,65,32,85);
insert into student values(1002,81,86,95,72),(1002,65,46,96,45),(1002,76,54,85,68);
insert into student values(1003,69,85,76,76),(1003,78,68,31,57),(1003,46,95,94,65);
insert into student values(1004,76,95,76,62),(1004,63,96,45,96),(1004,16,58,34,69);
insert into student values(1005,96,63,52,75),(1005,95,86,42,85),(1005,96,45,78,65);
insert into student values(1006,85,68,26,76),(1006,95,76,85,45),(1006,86,95,54,68);
insert into student values(1007,76,58,95,49),(1007,85,65,45,88),(1007,46,85,75,35);
insert into student values(1008,76,85,96,45),(1008,66,22,33,88),(1008,46,89,56,85);
insert into student values(1009,56,78,96,59),(1009,75,86,95,75),(1009,89,65,45,25);
insert into student values(1010,76,86,95,45),(1010,76,95,85,36),(1010,76,82,96,35);
insert into student values(1011,88,99,77,66),(1011,56,85,69,85),(1011,76,85,69,85);

编写存储过程，输入学生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;
/

5、存储过程练习5
--删除表
DROP TABLE IF EXISTS gaussdb.student;
--创建表
create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);
--导入数据
insert into student values(1001,56,84,65,35),(1001,63,46,82,46),(1001,85,65,32,85);
insert into student values(1002,81,86,95,72),(1002,65,46,96,45),(1002,76,54,85,68);
insert into student values(1003,69,85,76,76),(1003,78,68,31,57),(1003,46,95,94,65);
insert into student values(1004,76,95,76,62),(1004,63,96,45,96),(1004,16,58,34,69);
insert into student values(1005,96,63,52,75),(1005,95,86,42,85),(1005,96,45,78,65);
insert into student values(1006,85,68,26,76),(1006,95,76,85,45),(1006,86,95,54,68);
insert into student values(1007,76,58,95,49),(1007,85,65,45,88),(1007,46,85,75,35);
insert into student values(1008,76,85,96,45),(1008,66,22,33,88),(1008,46,89,56,85);
insert into student values(1009,56,78,96,59),(1009,75,86,95,75),(1009,89,65,45,25);
insert into student values(1010,76,86,95,45),(1010,76,95,85,36),(1010,76,82,96,35);
insert into student values(1011,88,99,77,66),(1011,56,85,69,85),(1011,76,85,69,85);

编写存储过程，输入学号和科目名称，返回对应的平均成绩
【方法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;
/

6、存储过程练习6
--删除表
DROP TABLE IF EXISTS gaussdb.student;
--创建表
create table student(student_id int not null,math int not null,pysical int not null,art int not null,music int not null);
--导入数据
insert into student values(1001,56,84,65,35),(1001,63,46,82,46),(1001,85,65,32,85);
insert into student values(1002,81,86,95,72),(1002,65,46,96,45),(1002,76,54,85,68);
insert into student values(1003,69,85,76,76),(1003,78,68,31,57),(1003,46,95,94,65);
insert into student values(1004,76,95,76,62),(1004,63,96,45,96),(1004,16,58,34,69);
insert into student values(1005,96,63,52,75),(1005,95,86,42,85),(1005,96,45,78,65);
insert into student values(1006,85,68,26,76),(1006,95,76,85,45),(1006,86,95,54,68);
insert into student values(1007,76,58,95,49),(1007,85,65,45,88),(1007,46,85,75,35);
insert into student values(1008,76,85,96,45),(1008,66,22,33,88),(1008,46,89,56,85);
insert into student values(1009,56,78,96,59),(1009,75,86,95,75),(1009,89,65,45,25);
insert into student values(1010,76,86,95,45),(1010,76,95,85,36),(1010,76,82,96,35);
insert into student values(1011,88,99,77,66),(1011,56,85,69,85),(1011,76,85,69,85);

编写存储过程，对于学生每科成绩，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;
/

7、存储过程练习7
--创建表并初始化数据
create table score(id varchar(20),score int,cid varchar(20));
insert into score values('''001''',86,'''c1'''),('''002''',95,'''c2''');

要求输入编号和课程返回成绩信息(课程和 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;
/

8、存储过程练习8
create table TEACHER(
	ID INTEGER NOT NULL,
    NAME VARCHAR(50) NOT NULL,
	DEPTNO INTEGER NOT NULL,
    SALARY FLOAT NOT NULL,
    TITLE VARCHAR(100) NOT NULL --职称：讲师、副教授、教授
)

create table DEPARTMENT(
	ID INTEGER NOT NULL,
    NAME VARCHAR(50) NOT NULL);
   

    
insert into TEACHER values(1,'Zhangsan',20,50000.00,'教授'),
(2,'XiaoMing',20,20000.00,'讲师'),
(3,'lisi',30,20000.00,'副教授'),
(4,'XiaoMing',30,20000.00,'副教授');

insert into TEACHER values(5,'Zhangwukun',40,50000.00,'教授'),
(6,'WuSong',20,21000.00,'讲师'),
(7,'liuSan',50,20500.00,'副教授'),
(8,'Sehu',30,26000.00,'副教授');

insert into TEACHER values(9,'yiyuqian',40,1.00,'教授'),
(10,'liangyuanqian',20,2.00,'讲师'),
(11,'sanyuan',50,3.00,'副教授'),
(12,'siyuanqian',30,4.00,'副教授');

insert into TEACHER values(13,'liuwan',40,60000.00,'教授'),
(14,'qiwan',20,70000.00,'讲师'),
(15,'bawan',50,80000.00,'副教授'),
(16,'jiuwan',40,90000.00,'副教授');

insert into DEPARTMENT values(20,'机电工程学院'),(30,'计算机学院');
insert into DEPARTMENT values(40,'自动化学院'),(50,'管理学院');



创建存储过程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;
/



创建存储过程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();

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入