--plsql块结构,计算a，b的和
declare
a int:=10;
b int:=20;
c int;
begin
c:=a+b;
dbms_output.put_line(c);
end;
--%type数据类型，输出员工名称和职务信息
declare
var_ename scott.emp.ename%type;
var_job scott.emp.job%type;
begin
select ename,job
into var_ename,var_job
from scott.emp
where empno=7369;
dbms_output.put_line(var_ename||'的职务是:'||var_job);
end;
--record类型
declare
type emp_type is record
(
var_ename varchar2(50),
var_job varchar2(20),
var_sal number
);
empinfo emp_type;
begin
select ename,job,sal
into empinfo
from scott.emp
where empno=7369;
dbms_output.put_line(empinfo.var_ename);
end;
--%rowtype数据类型
declare
rowVar_emp scott.emp%rowtype;
begin
select *
into rowVar_emp
from scott.emp
where empno=7369;
dbms_output.put_line(rowVar_emp.ename);
end;
----------------------------------------------------------------流程控制------------------------------------------------------------
--if ...then 比较字符串长短，输出长的字符串
declare
var_name1 varchar2(50);
var_name2 varchar2(50);
begin
var_name1:='dog100';
var_name2:='dog232332';
if
length(var_name1)>length(var_name2)
then
dbms_output.put_line(var_name1);
else
dbms_output.put_line(var_name2);
end if;
end;
--case 输出季节的月份
declare
season int:=2;
info varchar2(100);
begin
case season
when 1 then
info:='1,2,3';
when 2 then
info:='4,5,6';
when 3 then
info:='7,8,9';
when 4 then
info:='10,11,12';
else
info :='dog';
end case;
dbms_output.put_line(info);
end;
-------------------------------------------------------------------循环语句---------------------------------------------------------
--loop 计算1到100自然数之和
declare
sum_i int:=0;
i int:=0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i=100;
end loop;
dbms_output.put_line(sum_i);
end;
--while
declare
sum_i int:=0;
i int:=0;
begin
while
i<=100
loop
sum_i:=sum_i+i;
i:=i+1;
end loop;
dbms_output.put_line(sum_i);
end;
--for
declare
sum_i int:=0;
begin
for i in reverse 1..100
loop
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(sum_i);
end;
--------------------------------------------------------------------游标-------------------------------------------------
--显式游标,读取雇员信息
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from scott.emp
where job=var_job;
type record_emp is record
(
var_empno scott.emp.empno%type,
var_ename scott.emp.ename%type,
var_sal scott.emp.sal%type
);
emp_row record_emp;
begin
open cur_emp('MANAGER');
fetch cur_emp into emp_row;
while cur_emp%found
loop
dbms_output.put_line(emp_row.var_ename);
fetch cur_emp into emp_row;
end loop;
close cur_emp;
end;
--隐式游标，工资上调20%
begin
update scott.emp
set sal=sal*(1+0.2)
where job='SALESMAN';
if sql%notfound then
dbms_output.put_line('No');
else
dbms_output.put_line(sql%rowcount);
end if;
end;
--通过for循环语句循环游标，隐式游标
begin
for emp_record in (select * from scott.emp where job='SALESMAN')
loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
--通过for循环语句循环游标，显式游标
declare
cursor cursor_emp is
select * from scott.emp where job='SALESMAN';
begin
for emp_record in cursor_emp
loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
---------------------------------------------------------------------------------------------
