if condition then Statement end if
该表达式的功能为：若条件为真，执行then后的语句；否则，跳出条件语句执行end if后的语句。
2.
if condition then Statements_1 else Statements_2 end if
该表达式的功能为：如果条件为真执行then后的语句，否则执行else后的语句。
3.
if condition1 then Statements_1 else if condition2 then Statements 2 end if;end if;
then Statements_2 else Statements_3 end if
该表达式的功能为：如果if后的条件成立，执行then后面的语句，否则判断else if后面的条件，条件成立执行第二个then后面的语句，否则执行else后的语句。这是条件语句嵌套。
case语句
Declare
Cursor ucur is select * from users; --声明游标
us users%rowtype;--定义与游标想匹配的变量
Begin
Open ucur;--打开游标
Fetch ucur into us;
While ucur %found loop --使用循环遍历游标的查询结果
Dbms_output.put_line('姓名：'||us.username||'生日'||us.brithday);
Fetch ucur into us;
End loop;
Close ucur; --关闭游标
End;
%rowcount显示迄今为止从显示游标中取出的行数
使用游标for循环简化处理游
Set serverout on
Declare
Cursor userrow is
Select * from users order by username desc;
Begin
for currentrow in userrow loop
Dbms_output.put_line(currentrow.username||
Currentrow.brithday);
End loop;
End;
创建存储过程
打印一行指定数量的字符
Set serveroutput on
create or replace procedure printLine (width in Integer,ch in char default '-') is
begin
for i in 1.. width loop
dbms_output.put(ch);
end loop;
dbms_output.put_line('');
end printLine;
调用存储过程
Execute pintLine(20,’*’);
用存储过程访问数据库
create or replace procedure inuser
(id in number,
name in varchar2,
brithday date,
age in number,
role number,
createtime date
)
is
Begin
insert into users values (id,name,brithday,age, createtime,role);
commit;
return;
exception when others then
dbms_output.put_line(‘error');
rollback;
return;
end inuser;
execute inuser(5,'郭镜',sysdate,3,sysdate);
带出参的存储过程
create or replace procedure copyTohistory
( id in number,result out number)
is
ree varchar2(20); hid number;
begin
select o.remark into ree from han.orders o where pk=id;
if(ree='已发货‘) then
Insert into han.historyorder h(h.pk,h.costmoerId,h.totalMoney,
h.sendDate,h.orderItemid,h.remark)
(select o.pk,o.costmoerId,o.totalMoney,o.sendDate,o.orderItemid,o.remark from han.orders o where o.pk=id);
commit;
end if;
result:=1;
exception when others then
rollback; result:=0;
end copyTohistory;
调用带out参数的存储过程
declare-
o number;--对应于out类型的参数
begin
copyTohistory(6,o);
dbms_output.put_line(o);
end;
删除存储过程drop procedure 过程名