DECLARE
声明部分
BEGIN
编写主题
EXCEPTION
捕获异常
END;
//结果不显示时 set serveroutput on
SSH模式下:打开输出
SQL> set serveroutput on
SQL语句最后加上斜杠 /
例1:
DECLARE
i number;
BEGIN
i:=30;
dbms_output.put_line(i);
end;
例2:
DECLARE
i number;
BEGIN
i:=1/0;
exception
when zero_divide THEN
dbms_output.put_line('error');
end;
例3Loop 循环(do…while):
DECLARE
cou number;
BEGIN
cou:=1;
loop
dbms_output.put_line(cou);
exit when cou>10;
cou:=cou+1;
end loop;
end;
例4(while 循环):
DECLARE
x number;
BEGIN
x:=1;
while(x<5)loop
dbms_output.put_line(x);
x:=x+1;
end loop;
end;
例5(for 循环):
DECLARE
x number;
BEGIN
FOR x IN 1..7LOOP
DBMS_OUTPUT.put_line(x) ;
end loop;
end;
例6(IF 语句):
DECLARE
x number;
BEGIN
x:=3;
if x>2 THEN
dbms_output.put_line(x);
end if;
end;
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('fail');
end IF;
end;
例8(IF…ELSIF…ELSE 语句):
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
elsif x>3 THEN
dbms_output.put_line('less');
else
dbms_output.put_line('fail');
end if;
end;
例9
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('big');
else
dbms_output.put_line('small');
end if;
end;
/
例10
declare
x number;
en varchar2(30);
begin
x:=&no;
select ename into en from emp where empno=x;
dbms_output.put_line(en);
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例11
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('>10');
elsif x<5 then
dbms_output.put_line('<5');
else
dbms_output.put_line('error');
end if;
end;
/
例12
declare
x number;
s number;
begin
x:=&no;
select sal into s from emp where empno=x;
if s>3000 then
dbms_output.put_line('high');
elsif s>2000 then
dbms_output.put_line('mid');
else
dbms_output.put_line('low');
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例13
declare
x number;
s number;
d number;
begin
x:=&no;
select deptno,sal into d,s from emp where empno=x;
if d=10 then
if s*1.1>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.1 where empno=x;
end if;
elsif d=20 then
if s*1.2>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.2 where empno=x;
end if;
elsif d=30 then
if s*1.3>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.3 where empno=x;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
声明部分
BEGIN
编写主题
EXCEPTION
捕获异常
END;
//结果不显示时 set serveroutput on
SSH模式下:打开输出
SQL> set serveroutput on
SQL语句最后加上斜杠 /
例1:
DECLARE
i number;
BEGIN
i:=30;
dbms_output.put_line(i);
end;
例2:
DECLARE
i number;
BEGIN
i:=1/0;
exception
when zero_divide THEN
dbms_output.put_line('error');
end;
例3Loop 循环(do…while):
DECLARE
cou number;
BEGIN
cou:=1;
loop
dbms_output.put_line(cou);
exit when cou>10;
cou:=cou+1;
end loop;
end;
例4(while 循环):
DECLARE
x number;
BEGIN
x:=1;
while(x<5)loop
dbms_output.put_line(x);
x:=x+1;
end loop;
end;
例5(for 循环):
DECLARE
x number;
BEGIN
FOR x IN 1..7LOOP
DBMS_OUTPUT.put_line(x) ;
end loop;
end;
例6(IF 语句):
DECLARE
x number;
BEGIN
x:=3;
if x>2 THEN
dbms_output.put_line(x);
end if;
end;
/
例7(IF…ELSE 语句):DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('fail');
end IF;
end;
例8(IF…ELSIF…ELSE 语句):
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
elsif x>3 THEN
dbms_output.put_line('less');
else
dbms_output.put_line('fail');
end if;
end;
例9
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('big');
else
dbms_output.put_line('small');
end if;
end;
/
例10
declare
x number;
en varchar2(30);
begin
x:=&no;
select ename into en from emp where empno=x;
dbms_output.put_line(en);
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例11
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('>10');
elsif x<5 then
dbms_output.put_line('<5');
else
dbms_output.put_line('error');
end if;
end;
/
例12
declare
x number;
s number;
begin
x:=&no;
select sal into s from emp where empno=x;
if s>3000 then
dbms_output.put_line('high');
elsif s>2000 then
dbms_output.put_line('mid');
else
dbms_output.put_line('low');
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例13
declare
x number;
s number;
d number;
begin
x:=&no;
select deptno,sal into d,s from emp where empno=x;
if d=10 then
if s*1.1>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.1 where empno=x;
end if;
elsif d=20 then
if s*1.2>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.2 where empno=x;
end if;
elsif d=30 then
if s*1.3>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.3 where empno=x;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/