结构:declare
定义变量:三种定义方式
1、非面向对象: num1 number:=0; num2 number;
2、半面向对象:num1 表名.列名%type;
3、对象:num1 表%Rowtype;
begin 里面写方法、判断、输出等操作。。。。。。
end; 结束
例子
-----------if then else end if结构
declare
tname scott.emp.ename%type;
begin
select ename into tname from scott.emp where ename='SMITH';
if tname='SMITH'
then update scott.emp set ename='xxx' where ename='SMITH';
end if;
commit;
DBMS_OUTPUT.PUT_LINE('修改成功');
end
;
--------case when then end case结构
declare
salay scott.emp.sal%type;
begin
select sal into salay from scott.emp where ename='SMITH';
case
when salay>10000
then update scott.emp set sal=sal-5000;
when salay between 8000 and 10000
then update scott.emp set sal=sal-2000;
else
update scott.emp set sal=sal+1000;
end case;
commit;
end
;
------------循环结构
---1到一百相加
declare
i number:=0;
sum1 number:=0;
begin
while(i<100)
loop
i:=i+1;
sum1:=sum1+i;
end loop;
DBMS_OUTPUT.PUT_LINE(sum1);
end;
--------1到100偶数和
declare
i number:=0;
sum1 number:=0;
begin
while(i<100)
loop
i:=i+1;
if(mod(i,2)=0)
then sum1:=sum1+i;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(sum1);
end;
--员工表工资大于8000的占三分之二以上
declare
sumperson number:=0;
baqianperson number:=0;
begin
select count('X') into sumperson from scott.emp;
select count('X') into baqianperson from scott.emp where sal>8000;
while(baqianperson/sumperson<0.66)
loop
update scott.emp set sal=sal+100;
select count('X') into baqianperson from scott.emp where sal>8000;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('操作成功');
end
;