存储过程
edit进入文本编辑器中
/指定语句
DBMS_OUTPUT.PUT_LINE('操作完毕'||a);输出一段话
commit;提交
set serverout on 打印一段话之前要先设置
基本类型
declare
//设置变量,直接赋值可以:=9,不赋值a number(5);
a number(5):=9;
begin
//查询老师为:王斌的工资(wage)并赋值给a
select wage into a from teachers where name='王彤';
if(a<5000)
then
update teachers set wage=wage+300 where name='王彤';
else
update teachers set wage=wage-300 where name='王彤';
end if;
//提交
commit;
//下面如果想成功后输出语句,就要在命令行设置 set serverout on
DBMS_OUTPUT.PUT_LINE('操作完毕'||a);
end;
一.对象类型
declare
jw TEACHERS%ROWTYPE;
begin
select * into jw from teachers where name='王彤';
if(jw.WAGE>2000)
then
update teachers set wage=wage+500 where name='王彤';
else
update teachers set wage=wage-500 where name='王彤';
end if;
commit;
dbms_output.put_line('操作完毕'||jw.wage);
end;
二.swich 1
declare
a number:=1;
begin
case a
when 0 then DBMS_OUTPUT.PUT_LINE('A=0');
WHEN 1 then DBMS_OUTPUT.PuT_LINE('A=1');
else DBMS_OUTPUT.PUT_LINE('都不是');
END CASE;
END;
二.2.
declare
gongzi teachers.wage%type;
begin
select wage into gongzi FROM teachers where name='林飞';
case
when gongzi>1000
then update teachers set wage=wage*1.2 where name='林飞';
when gongzi between 2000 and 4000
then update teachers set wage=wage*1.3 where name='林飞';
else update teachers set wage=wage*1.5 where name='林飞';
end case;
commit;
DBMS_OUTPUT.PUT_LINE(gongzi);
end;
二.3
select case name when '孔世杰' then '陈总' else name end ,wage from Teachers;
三.死循环
declare
i number:=0;
sum1 number:=0;
begin
loop
i:=i+1;
sum1:=sum1+1;
exit when i=100;
end loop;
DBMS_OUTPUT.PUT_LINE(SUM1);
end;
四.while
declare
avgSal Teachers.wage%Type;
begin
select avg(wage)into avgSal FROM Teachers;
while(avgSal<2000)
loop
update Teachers set wage=wage+100;
select avg(wage)into avgSal FROM Teachers;
exit when avgSal>3000;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('cg');
end;
五.for循环
declare
i number:=0;
sum1 number:=0;
begin
for i in 1..100
loop
sum1:=sum1+i;
end loop;
DBMS_OUTPUT.PUT_LINE(sum1);
end;
六.普通顺序
七. go结构
them goto b
<<b>>
八.带参数的存储过程
create or replace procedure aa(name2 teachers.name%type)
as
gongzi TEACHERS.WAGE%type;
begin
select wage into gongzi from teachers where name=name2;
DbMS_OUTPUT.PUT_LINE('CG');
end;
execute aa('xx')
九.
create or replace function fun_aa(name2 varchar2或则name2 teachers.name%type)
return teachers.wage%type
is
gz teachers.wage%type;
begin
select wage into gz from teachers where name=name2;
return gz;
DbMS_OUTPUT.PUT_LINE('CG');
end;
select function('jw') from dual;
十.带隐式游标、全局变量SQL%ROWCOUNT
begin
update TEACHERS SET wage=2000 WHERE NAME='林飞';
if(SQL%ROWCOUNT>1)
THEN
ROLLBACK;
UPDATE TEACHERS SET WAGE=1000;
END IF;
COMMIT;
end;