ORCAL SQLPLUS上机6-1

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;
/