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