ORACLE 的简单提示

/* 数据库版本 */
select * from v$version;



/* 数据库编码 */
select userenv('language') from dual;



/* 存储过程－循环语句 */
1 + 2 + 3 + 4…… + 100？

create or replace procedure ceshi is
num number default 0;

begin

  for i in 1..100 loop
    num:=i+num;
  end loop;

  dbms_output.put_line(num);
end ceshi;



/* 存储过程－记录循环语句 */
for A in (select user_id, limit_num from user_limit
      where user_id like '%*' and length(user_id) > 1) loop
  begin
    if (userId = A.user_id) then
        v_limit_num := A.limit_num;
        exit;
    end if;
  end;
end loop;



/* 某记录以某条件排序时的排名 */
SELECT A.R POINT_SORT FROM (
SELECT ROWNUM R, USER_ID FROM (
  SELECT USER_ID FROM table_name ORDER BY POINT_COUNT DESC
  )
) A
WHERE A.USER_ID = '001'



continue...... 实例

CREATE OR REPLACE PROCEDURE UPDATE_USER_LEVEL IS
ul varchar2(20);
findul varchar2(1) default 'N';

BEGIN

    for A in (select USER_ID, POINT_COUNT from MF_USER_ACTIVITY) loop
        begin
       
            for B in (select CLASS_CODE, VALUE1 from MF_MASTER_CODE
                        where MASTER_CLASS = 'USER_LEVEL' order by CLASS_CODE desc) loop
                begin
               
                    ul := B.CLASS_CODE;
                   
                    if (A.POINT_COUNT >= B.VALUE1) then
                        update MF_USER_ACTIVITY set USER_LEVEL = B.CLASS_CODE where USER_ID = A.USER_ID;
                        findul := 'Y';
                        exit;
                    end if;
                   
                end;
            end loop;
           
            if (findul = 'N') then
                update MF_USER_ACTIVITY set USER_LEVEL = ul where USER_ID = A.USER_ID;
            end if;
           
            findul := 'N';
           
     end;
    end loop;

END UPDATE_USER_LEVEL;

 

/

 

原文：http://lingoosoft.blog.ccidnet.com/blog-htm-itemid-142132-do-showone-type-blog-uid-37700.html