oracle-游标-存储过程-函数-包
时间：2021-07-01 10:21:17
帮助过：25人阅读
&aa‘;
open c1(v_customerName);
fetch c1 into v_customer;
while(c1%found) loop
dbms_output.put_line(v_customer.customerName||‘ ‘||v_customer.sex);
fetch c1 into v_customer;
end loop;
end;
五、包
包:包中可以有多个方法，包包括包声明与包主体，包声明中声明的方法名，参数名，类型，个数必须与包主体的方法完全一样。
包声明中声明的变量是全局变量，大家都可以用
--实现包声明
create or replace package my_p
as
function getReverse(v_name varchar2) return varchar2;
procedure teacher_modify_column(teacher_id number,column_name varchar2,column_value1 number);
end;
create or replace package body my_p
as
procedure teacher_modify_column(teacher_id number,column_name varchar2,column_value1 number)
is
v_sql varchar2(200);
begin
v_sql:=‘update teachers set ‘||column_name||‘ = ‘||column_value1||‘ where teachers.teacher_id = ‘||teacher_id;
EXECUTE IMMEDIATE v_sql;
end teacher_modify_column;
function getReverse(v_name varchar2) return varchar2
as
v_title varchar2(200);
i int:=1;
j int:=0;
begin
j:=length(v_name);
while (j>0) loop
v_title:= substr(v_name,i,1)||v_title;
i:=i+1;
j:=j-1;
end loop;
return v_title;
end;
end;
create or replace package MyPackage as
type c_type is ref cursor;
function MyReverse(source varchar2) return varchar2;
procedure splitPage(p_sql varchar2,page int,pageSize int,result out c_type,pageCount out int);
end;
--实现包主体
create or replace package body MyPackage as
function MyReverse(source varchar2) return varchar2
as
i int;
j int;
result varchar2(2000):=‘‘;
begin
j:=length(source);
i:=1;
while(i<=j) loop
result:=substr(source,i,1)||result;
i:=i+1;
end loop;
return result;
end;
// 查询语句 页数
procedure splitPage(p_sql varchar2,page int,pageSize int,result out c_type,pageCount out int)
as
v_sql varchar2(500);
startPage int;
endPage int;
v_rowCount int;
begin
v_sql:=‘select count(*) from (‘||p_sql||‘)‘;
dbms_output.put_line(v_sql);
execute immediate v_sql into v_rowCount;
pageCount:=ceil(v_rowCount/pageSize);
if(page=0) then
raise_application_error(-20001,‘申请的页面太小‘);
end if;
if(page>pageCount) then
raise_application_error(-20001,‘申请的页面太大‘);
end if;
startPage:=(page-1)*pageSize;
endPage:=page*pageSize;
v_sql:=‘select * from (‘||p_sql||‘) where rowNum<=‘||to_char(endPage);
v_sql:=v_sql||‘ minus ‘;
v_sql:=v_sql||‘select * from (‘||p_sql||‘) where rowNum<=‘||to_char(startPage);
dbms_output.put_line(v_sql);
open result for v_sql;
end;
end;
--调用包中的方法
select MyPackage.MyReverse(sex) from customer;
--在pl/sql块中调用
declare
pageCount int;
c1 mypackage.c_type;
v_customer customer%rowtype;
begin
mypackage.splitPage(‘select * from customer‘,1,2,c1,pageCount);
dbms_output.put_line(‘总页数是‘||pageCount);
fetch c1 into v_customer;
while(c1%found) loop
dbms_output.put_line(v_customer.customername||‘ ‘||v_customer.sex);
fetch c1 into v_customer;
end loop;
close c1;
mypackage.splitPage(‘select * from customer‘,2,2,c1,pageCount);
fetch c1 into v_customer;
while(c1%found) loop
dbms_output.put_line(v_customer.customername||‘ ‘||v_customer.sex);
fetch c1 into v_customer;
end loop;
close c1;
mypackage.splitPage(‘select * from customer‘,3,2,c1,pageCount);
fetch c1 into v_customer;
while(c1%found) loop
dbms_output.put_line(v_customer.customername||‘ ‘||v_customer.sex);
fetch c1 into v_customer;
end loop;
close c1;
mypackage.splitPage(‘select * from customer‘,4,2,c1,pageCount);
fetch c1 into v_customer;
while(c1%found) loop
dbms_output.put_line(v_customer.customername||‘ ‘||v_customer.sex);
fetch c1 into v_customer;
end loop;
close c1;
end;
oracle-游标-存储过程-函数-包
标签：package count in out immediate upd tab 返回结果 执行函数 pre
