一个小程序

今天面试的一个小存储过程。
要求：写一个存储过程，从1循环到10，每次循环输出时间及行号，然后停顿一分钟继续输出其余行。

首先必须将dbms_lock.sleep()显示给用户赋权。

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lock to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create or replace procedure proc_test
  2  as
  3  begin
  4  for i in 1 .. 10 loop
  5      dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||' '||i);
  6      dbms_lock.sleep(60);
  7  end loop;
  8  end;
  9  /

Procedure created.

SQL> set serveroutput on
SQL> begin
  2  proc_test;
  3  end;
  4  /

2013-07-30 19:09:34 1
2013-07-30 19:10:34 2
2013-07-30 19:11:34 3
2013-07-30 19:12:34 4
2013-07-30 19:13:34 5
2013-07-30 19:14:34 6
2013-07-30 19:15:34 7
2013-07-30 19:16:34 8
2013-07-30 19:17:34 9
2013-07-30 19:18:34 10

PL/SQL procedure successfully completed.