Oracle PLSQL
语法:
declare 声明(定义变量、常量)
begin
exception 例外处理
end;
/
变量的定义及使用
基本类型变量和常量的定义:
例子:
declare
name varchar2(20); --变量声明
age number(3) :=20; --常量
begin
name :=‘kun’; --变量赋值
dbms_output.put_line(name);
end;
引用类型变量:
例子:
declare
name employee.ename%type;
begin
select e.ename into name from employee e where e.empno=1234;
dbms_output.put_line(name);
end;
记录类型变量
declare
rowdata(变量名) employee%rowtype;
begin
select * into rowdata from employee e where e.empno=1234;
dbms_output.put_line(rowdata.ename);
end;
if 语法
例:
declare
eno number(3) := &num ;
begin
if eno=100 then
dbms_output.put_line(‘eno等于100’);
elsif eno=110 then
dbms_output.put_line(‘eno等于110’);
else
dbms_output.put_line(‘eno等于其它’);
end if;
end;
循环loop语法
例子:
declare
num number(10) :=0;
begin
loop
exit when num = 100; --何时跳出循环
num := num +1;
end loop;
end;
或者:
declare
num number(10) := 0 ;
begin
while num <10 loop
num := num+1;
end loop;
end;
游标
游标可以存储返回的多条数据
例子:
使用游标输出employee表中员工姓名
declare
row employee%rowtype;
cursor cur is select * from employee; --游标定义格式 cursor 游标名 is 查询语句
begin
open cur; --打开游标
loop
fetch cur into row; --取一行游标的值 取值后游标会自动向下移动一个位置
exit when cur%notfound; --结束循环条件
dbms_output.put_line(row.ename);
end loop;
close cur; —关闭游标
end;
给商品降价,类型为food降价50,类型为water降价10
declare
row goods%rowtype;
cursor cur is select * from goods;
money number(3);
begin
open cur;
loop
fetch cur into row;
exit when cur%notfound;
if row.goodsType = ‘food’ then
money := 50;
elsif row.goodsType = ‘water’ then
money := 10;
end if;
update goods g set g.price = g.price + money where g.gid = row.gid;
end loop;
close cur;
commit; --使update生效
end;
给类别编号为10的商品涨价一百
declare
cursor cur(goodsType goods.gtype%type) is select * from goods g where g.gtype=goodsType ;
row goods%rowtype;
begin
open cur(10000);
loop
fetch cur into row;
exit when cur%notfound ;
update goods g set g.price = g.price+100 where g.gid = row.gid;
end loop;
close cur;
commit;
end;