Oracle PL/SQL编程语法详解编程语言

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