昨天的状态有些不好,所以没写多少内容,今天状态跟昨天差不多,但是今天尽量写多点
游标
--游标
----参数化游标
declare
cursor c(t date) is select * from s_emp where hire > t;
e s_emp%rowtype;
begin
open c(to_date('2019-3-3','yyyy-mm-dd'));
loop
fetch c into e;
exit when c%notfound;
dbms_output.put_line(e.ename);
end loop;
close c;
end;
----参数默认值
declare
cursor c(t date:=sysdate) is select * from s_emp where hire<t;
e s_emp%rowtype;
begin
open c();
loop
fetch c into e;
exit when c%notfound;
dbms_output.put_line(e.ename);
end loop;
close c;
end;
存储过程
--存储过程
----创建存储过程(声明变量的时候,后面必须用逗号,不能用分号,不然调用存储过程时会出错)
create or replace procedure p(a in int,b in int,c out int) as
i int;
begin
c := 0;
for i in a..b loop
c := c + i;
end loop;
end;
----调用存储过程
declare
k int;
begin
p(1,100,k);
dbms_output.put_line(k);
end;
----显示错误信息
select * from user_errors;
/*参数与返回值
in,用于向过程传一个值,该值在过程体中不能更改;
out,用于被调用的过程中返回一个值,该值可以更改,不能有默认值;
in out,用于向过程体中传入一个初始值,并返回更新后的值,不能有默认值。
存储过程中不能使用return返回值,不过可以定义多个输出参数。
*/
----这个是教材的案例,调用时报错(与变量声明后的符号无关)
create or replace procedure pc(s in number)
as
cursor cur is select * from emp where sar<s;
em emp%rowtype;
begin
open cur;
loop
fetch cur into em;
exit when cur%notfound;
if em.sar*1.1<s then
update emp set sar=s where eid=em.eid;
else
update emp set sar=trunc(sar*1.1,2) where eid=em.eid;
end if;
end loop;
close cur;
end;
----计算某数阶乘的存储过程
create or replace procedure p(a in number := 1, b out number) as
c number := 1;
begin
b := 1;
while c <= a loop
b := b * c;
c := c + 1;
end loop;
end;
declare
d number := 10;
e number;
begin
p(d,e);
dbms_output.put_line(d||'的阶乘是:'||e);
end;
自定义函数
--自定义函数
----创建函数
create or replace function f(a in s_emp.eid%type)
return s_emp.ename%type as
na s_emp.ename%type;
begin
select ename into na from s_emp where eid = a;
return na;
end;
----调用函数
declare
begin
dbms_output.put_line(f(5));
end;
后面还有“包”,“触发器”,“异常”