oracle 循环使用

3循环使用

BEGIN

DECLARE  p_name varchar(100);

         a number;       

BEGIN 

  a:=1;

loop

select USER_NAME into p_name from U_USER WHERE fullname='xlc';

dbms_output.put_line(p_name||a);

exit  WHEN  (a=5);

a :=a+1;

   END loop;

END;

end;

 

--4:while 循环

begin

DECLARE  p_name varchar(100);

           a number;       

BEGIN 

  a:=1;

while (a<=5) loop

select USER_NAME into p_name from U_USER WHERE fullname='xlc';

dbms_output.put_line(p_name||a);

   a :=a+1;

   END loop;

END;

END;

SELECT user_name from (SELECT ROWNUM as n,u.* from U_USER u) where n <=2

 

--5:for in 使用cursor 

BEGIN

DECLARE p_name VARCHAR(100);

CURSOR culResult is  SELECT user_name  from (SELECT ROWNUM as n,u.* from U_USER u) where n <=2;

BEGIN 

FOR a in culResult loop

BEGIN 

p_name :=a.user_name;

dbms_output.put_line(p_name||' ok');

END;

END loop;

END;  

END;

**/

http://wenku.baidu.com/link?url=kqIBe8JDBYnE3lLGyan5LzAEgvpHinyZqxrguUE3WNOgNXLx3Na6XQrUZwO_jd6VWTGJ9czS8NEwzhkhfpyzUxWBn36KmJhw8KFz9prM20i

/**

--3循环使用

BEGIN

DECLARE  p_name varchar(100);

         a number;       

BEGIN 

  a:=1;

loop

select USER_NAME into p_name from U_USER WHERE fullname='xlc';

dbms_output.put_line(p_name||a);

exit  WHEN  (a=5);

a :=a+1;

   END loop;

END;

end;

 

--4:while 循环

begin

DECLARE  p_name varchar(100);

           a number;       

BEGIN 

  a:=1;

while (a<=5) loop

select USER_NAME into p_name from U_USER WHERE fullname='xlc';

dbms_output.put_line(p_name||a);

   a :=a+1;

   END loop;

END;

END;

SELECT user_name from (SELECT ROWNUM as n,u.* from U_USER u) where n <=2

 

--5:for in 使用cursor 

BEGIN

DECLARE p_name VARCHAR(100);

CURSOR culResult is  SELECT user_name  from (SELECT ROWNUM as n,u.* from U_USER u) where n <=2;

BEGIN 

FOR a in culResult loop

BEGIN 

p_name :=a.user_name;

dbms_output.put_line(p_name||' ok');

END;

END loop;

END;  

END;

**/

 

1：先执行：创建MD5

 

CREATE OR REPLACE 

FUNCTION MD5(

passwd IN VARCHAR2)

RETURN VARCHAR2

IS

retval varchar2(32);

BEGIN

retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;

RETURN retval;

END;

 

 

2：删除无效数据

DELETE from U_USER WHERE last_logtime  is NULL

 

 

3:修复总部员工编号重复

BEGIN

DECLARE 

tempSize VARCHAR(20);

i number;

CURSOR user_cur IS

 SELECT USER_ID,JOB_NO from U_USER WHERE job_no IN(SELECT JOB_NO FROM  CLSPUSER.U_USER  GROUP BY JOB_NO HAVING count(1)>1);

c_row user_cur%rowtype;

BEGIN

FOR c_row IN user_cur loop

   SELECT max(substr(JOB_NO,length(JOB_NO)-3,length(JOB_NO)))+1 INTO tempSize from U_USER  WHERE JOB_NO LIKE 'ZHHQ%';

   i:=to_number(length(tempSize));

       if(i=1) then

tempSize:='ZHHQ000';

 ELSE IF (i=2) THEN

tempSize:='ZHHQ00';

 dbms_output.put_line(tempSize);

         ELSE if (i=3) then

          tempSize:='ZHHQ0';

 dbms_output.put_line(tempSize);

          end if;

          end if;

end if;

--修改

UPDATE U_USER 

    SET 

        user_name=(SELECT  tempSize|| (max(substr(JOB_NO,length(JOB_NO)-3,length(JOB_NO)))+1) FROM  CLSPUSER.U_USER  WHERE JOB_NO LIKE 'ZHHQ%'),

        password='123456',

        passwd_MD5=lower(substr(MD5('123456'), 11, 16)),

        job_no=(SELECT  'ZHHQ000'|| (max(substr(JOB_NO,length(JOB_NO)-3,length(JOB_NO)))+1) FROM  CLSPUSER.U_USER  WHERE JOB_NO LIKE 'ZHHQ%')

   WHERE USER_ID=c_row.user_ID;

END loop;

end;

END;

 

 

4：在执行批量修改

merge into  CLSPUSER.U_USER clsp

 using (SELECT emp_no, substr(id_card, length(id_card) - 6, 6) password from SALARYUSER.crf_salary_p2p_emp) sale

 ON (clsp.job_no = sale.emp_no)

      WHEN matched THEN

        UPDATE

           SET clsp.user_name  = sale.emp_no,

               clsp.password   = sale.password,

               clsp.passwd_md5 = (SELECT lower(substr(MD5(sale.password), 11, 16))  from dual);