SQL> declare
2 v_num number:=9;
3 begin
4 v_num:=v_num+1;
5 dbms_output.put_line(v_num);
6 end;
7 /
--定义记录类型,类似结构体,用select...into
--定义记录类型,类似结构体,用select...into
SQL> declare
type employee_type is record(
no_number number,
name_string varchar2(20),
sal_number number);
employee employee_type;
begin
select empno,ename,sal
into employee
from scott.emp
where empno = '7369';
dbms_output.put(employee.no_number);
dbms_output.put(' ' || employee.name_string);
dbms_output.put_line(' ' ||employee.sal_number);
end;
/
--判断两个整数大小,输出不同结果
declare
number1 integer:=80;
number2 integer:=90;
begin
if number1>=number2 then
dbms_output.put_line(number1 || '>=' || number2);
else
dbms_output.put_line(number1 || '<' || number2);
end if;
end;
/
--%type类型使用
SQL>declare
var_name scott.emp.ename%type;
var_no scott.emp.empno%type;
var_sal scott.emp.sal%type;
begin
select empno,ename,sal
into var_no,var_name,var_sal
from scott.emp
where empno = '7839';
dbms_output.put_line(var_no||' ' ||var_name||' '||var_sal);
end;
/
--判断是否为闰年
declare
year_date number;
leap boolean;
begin
year_date:=2010;
if mod(year_date,4) <> 0 then
leap := false;
elsif mod(year_date,100) <> 0 then
leap:=true;
elsif mod(year_date,400) <> 0 then
leap:=false;
else
leap:=true;
end if;
if leap then
dbms_output.put_line(year_date||'是闰年');
else
dbms_output.put_line(year_date||'是平年');
end if;
end;
/
--for计算1到100的和
declare
sum_sum number:=0;
begin
for i in 1..100 loop
sum_sum := sum_sum + i;
end loop;
dbms_output.put_line(sum_sum);
end;
/
--依次输出1到10之间的平方数
declare
i number:=1;
begin
loop
dbms_output.put_line(i || '的平方数为:' || i*i);
i := i+1;
exit when i > 10;
end loop;
end;
/
--%rowtype类型使用
--%rowtype类型使用
SQL>declare
row_employee scott.emp%rowtype;
begin
select * into row_employee
from scott.emp
where empno = '7839';
dbms_output.put_line(row_employee.empno);
dbms_output.put_line(row_employee.ename);
dbms_output.put_line(row_employee.job);
dbms_output.put_line(row_employee.sal);
end;
/
--使用while循环打印50以内能被3整除的数
declare
i number:=1;
begin
while i < 50 loop
if mod(i,3) = 0 then
dbms_output.put_line(i);
end if;
i:=i+1;
end loop;
end;
/
--case语句的使用
declare
i number:=0;
begin
while i < 5 loop
case i
when 0 then
dbms_output.put_line(i || 'is zero');
when 1 then
dbms_output.put_line(i || 'is one');
when 2 then
dbms_output.put_line(i || 'is two');
else
dbms_output.put_line(i || 'is more than two');
end case;
i:=i+1;
end loop;
end;
/