Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

DB2常用SQL的写法(二)

Posted on 2010-03-18 22:32 Prayer 阅读(867) 评论(0)  编辑 收藏 引用 所属分类: DB2
-- for循环语句的用法 
begin atomic
 declare fullname char(40);
 for vl as
   select firstnme, midinit, lastname from employee
  do
  set fullname = lastname concat ','
    concat firstnme concat ' ' concat midinit;
  insert into tnames values (fullname);
 end for
end
 
-- leave的用法
create procedure leave_loop(out counter integer)
 language sql
 begin
   declare v_counter integer;
   declare v_firstnme varchar(12);
   declare v_midinit char(1);
   declare v_lastname varchar(15);
   declare at_end smallint default 0;
   declare not_found condition for sqlstate '02000';
   declare c1 cursor for
  select firstnme, midinit, lastname
    from employee;
   declare continue handler for not_found
  set at_end = 1;
   set v_counter = 0;
   open c1;
   fetch_loop:
   loop
  fetch c1 into v_firstnme, v_midinit, v_lastname;
  if at_end <> 0 then leave fetch_loop;
  end if;
  set v_counter = v_counter + 1;
   end loop fetch_loop;
   set counter = v_counter;
   close c1;
 end
 
-- if语句的用法
   create procedure update_salary_if
     (in employee_number char(6), inout rating smallint)
     language sql
     begin
       declare not_found condition for sqlstate '02000';
       declare exit handler for not_found
         set rating = -1;
       if rating = 1
         then update employee
         set salary = salary * 1.10, bonus = 1000
         where empno = employee_number;
       elseif rating = 2
         then update employee
         set salary = salary * 1.05, bonus = 500
         where empno = employee_number;
       else update employee
         set salary = salary * 1.03, bonus = 0
         where empno = employee_number;
       end if;
     end
 
-- loop的用法
   create procedure loop_until_space(out counter integer)
     language sql
     begin
       declare v_counter integer default 0;
       declare v_firstnme varchar(12);
       declare v_midinit char(1);
       declare v_lastname varchar(15);
       declare c1 cursor for
         select firstnme, midinit, lastname
           from employee;
       declare continue handler for not found
         set counter = -1;
       open c1;
       fetch_loop:
       loop
         fetch c1 into v_firstnme, v_midinit, v_lastname;
         if v_midinit = ' ' then
           leave fetch_loop;
         end if;
         set v_counter = v_counter + 1;
       end loop fetch_loop;
       set counter = v_counter;
       close c1;
     end
 
-- return的用法
   begin
   ...
     goto fail
   ...
     success: return 0
     fail: return -200
   end
 
-- set变量 的用法
set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
set (new_var.salary, new_var.comm) = (10000, new_var.salary);
set (new_var.salary, new_var.comm)
  = (select avg(salary), avg(comm)
    from employee e
    where e.workdept = new_var.workdept);
 
-- whenever的用法
   exec sql whenever sqlerror goto handlerr;
   exec sql whenever sqlwarning continue;
   exec sql whenever not found go to enddata;
 
-- while的用法
   create procedure dept_median
     (in deptnumber smallint, out mediansalary double)
     language sql
     begin
       declare v_numrecords integer default 1;
       declare v_counter integer default 0;
       declare c1 cursor for
         select cast(salary as double)
           from staff
           where dept = deptnumber
           order by salary;
       declare exit handler for not found
         set mediansalary = 6666;
       set mediansalary = 0;
       select count(*) into v_numrecords
         from staff
         where dept = deptnumber;
       open c1;
       while v_counter < (v_numrecords / 2 + 1) do
         fetch c1 into mediansalary;
         set v_counter = v_counter + 1;
       end while;
       close c1;
     end
 
-- set schema的用法
set schema rick
 
-- DB2保留关键字
add                deterministic  leave         restart
after              disallow       left          restrict
alias              disconnect     like          result
all                distinct       linktype      result_set_locator
allocate           do             local         return
allow              double         locale        returns
alter              drop           locator       revoke
and                dsnhattr       locators      right
any                dssize         lock          rollback
application        dynamic        lockmax       routine
as                 each           locksize      row
associate          editproc       long          rows
asutime            else           loop          rrn
audit              elseif         maxvalue      run
authorization      encoding       microsecond   savepoint
aux                end            microseconds  schema
auxiliary          end-exec       minute        scratchpad
before             end-exec1      minutes       second
begin              erase          minvalue      seconds
between            escape         mode          secqty
binary             except         modifies      security
bufferpool         exception      month         select
by                 excluding      months        sensitive
cache              execute        new           set
call               exists         new_table     signal
called             exit           no            simple
capture            external       nocache       some
cardinality        fenced         nocycle       source
cascaded           fetch          nodename      specific
case               fieldproc      nodenumber    sql
cast               file           nomaxvalue    sqlid
ccsid              final          nominvalue    standard
char               for            noorder       start
character          foreign        not           static
check              free           null          stay
close              from           nulls         stogroup
cluster            full           numparts      stores
collection         function       obid          style
collid             general        of            subpages
column             generated      old           substring
comment            get            old_table     synonym
commit             global         on            sysfun
concat             go             open          sysibm
condition          goto           optimization  sysproc
connect            grant          optimize      system
connection         graphic        option        table
constraint         group          or            tablespace
contains           handler        order         then
continue           having         out           to
count              hold           outer         transaction
count_big          hour           overriding    trigger
create             hours          package       trim
cross              identity       parameter     type
current            if             part          undo
current_date       immediate      partition     union
current_lc_ctype   in             path          unique
current_path       including      piecesize     until
current_server     increment      plan          update
current_time       index          position      usage
current_timestamp  indicator      precision     user
current_timezone   inherit        prepare       using
current_user       inner          primary       validproc
cursor             inout          priqty        values
cycle              insensitive    privileges    variable
data               insert         procedure     variant
database           integrity      program       vcat
day                into           psid          view
days               is             queryno       volumes
db2general         isobid         read          when
db2genrl           isolation      reads         where
db2sql             iterate        recovery      while
dbinfo             jar            references    with
declare            java           referencing   wlm
default            join           release       write
defaults           key            rename        year
definition         label          repeat        years
delete             language       reset
descriptor         lc_ctype       resignal
 
-- SQL99关键字
absolute       describe        module      session
action         destroy         names       session_user
admin          destructor      national    sets
aggregate      diagnostics     natural     size
are            dictionary      nchar       smallint
array          domain          nclob       space
asc            equals          next        specifictype
assertion      every           none        sqlexception
at             exec            numeric     sqlstate
bit            false           object      sqlwarning
blob           first           off         state
boolean        float           only        statement
both           found           operation   structure
breadth        grouping        ordinality  system_user
cascade        host            output      temporary
catalog        ignore          pad         terminate
class          initialize      parameters  than
clob           initially       partial     time
collate        input           postfix     timestamp
collation      int             prefix      timezone_hour
completion     integer         preorder    timezone_minute
constraints    intersect       preserve    trailing
constructor    interval        prior       translation
corresponding  large           public      treat
cube           last            real        true
current_role   lateral         recursive   under
date           leading         ref         unknown
deallocate     less            relative    unnest
dec            level           role        value
decimal        limit           rollup      varchar
deferrable     localtime       scope       varying
deferred       localtimestamp  scroll      whenever
depth          map             search      without
deref          match           section     work
desc           modify          sequence    zone
 

只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理