SQL> select * from (select rownum m,tt. * from tt where rownum<=5) a where a.m>=1;
M EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
1 7369 SMITH CLERK 7902 17-DEC-80 1996.5 20
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1996.5 300 30
3 7521 WARD SALESMAN 7698 22-FEB-81 1996.5 500 30
4 7566 JONES MANAGER 7839 02-APR-81 1996.5 20
5 7654 MARTIN SALESMAN 7698 28-SEP-81 1996.5 1400 30
SQL>
还有一个 Initcap(字段) ,首字母大写的函数。
-----文本:
declare
type alan_cursor is ref cursor;
alan alan_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open alan for select ename,sal from emp where deptno=&no;
loop
fetch alan into v_ename,v_sal;
exit when alan%notfound;
dbms_output.put_line('name is :'|| v_ename || 'salary is :' || v_sal);
end loop;
close alan;
end;
分页存储过程案例:
---------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
例外处理: