存储过程打印多条数据
create or replace procedure STUDENT_LIST
is
cursor cur is select * from tb_s;
getSno CHAR(6 BYTE);
getSname VARCHAR2(50 BYTE);
begin
open cur;
fetch cur into getSno,getSname;
while(cur%FOUND) loop
dbms_output.put_line('sno:'||getSno||
',sname:'||getSname);
fetch cur into getSno,getSname;
end loop;
close cur;
end STUDENT_LIST;
begin
STUDENT_LIST();
end;
创建游标,将要获取的数据放进游标内,然后通过fetch
取得函数
fetch
检索游标,将游标中的数据以记录为单位逐条访问,其语法格式为:
FETCH cursor_name into record_variable|variable_list
record_variable和variable_list表示的变量需要在个数、顺序及数据类型上与游标指向的记录包含的属性个数、顺序及数据类型对应一致。
fetch每执行一次,游标指针指向向下一行记录。但游标指针只能向下移动,不能后退
%FOUND 检查最近一次使用fetch操作是否检索到数据,如果检索到数据,返回true,否则返回false
模糊查询的存储过程
create or replace procedure PRO_NAME(
setName in varchar2
)
is
CURSOR cur is select * from tb_s where sname like '%'||setName||'%';
getSNO CHAR(6 BYTE);
getSNAME VARCHAR2(50 BYTE);
begin
open cur;
fetch cur into getSNO,getSNAME;
while(cur%FOUND) loop
dbms_output.put_line('sno:'||getSNO||
',sname:'||getSNAME);
fetch cur into getSNO,getSNAME;
end loop;
close cur;
end;
关键点在于
select * from tb_s where sname like ‘%’||setName||’%’;
Oracle的字符连接应该用||
进行连接,而不能用加号
select * from tb_s where sname like ‘%’+sname+’%’;也是不行的,会报:ORA-01722: 无效数字