Oracle PL/SQL編程文法

標籤：out   while   sea   color   else   sum   tput   文法   upd   

--plsql塊結構,計算a，b的和declare  a int:=10; b int:=20; c int; begin   c:=a+b;   dbms_output.put_line(c);   end;--%type資料類型，輸出員工名稱和職務資訊declarevar_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自然數之和declaresum_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; --whiledeclaresum_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;--fordeclaresum_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%beginupdate scott.empset 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;---------------------------------------------------------------------------------------------                

 

Oracle PL/SQL編程文法