Oracle游标变量、存储过程、包、函数

一、游标变量
例：使用游标变量取出部门表的所有相关数据。
法I：
DECLARE
TYPE cursortype IS REF CURSOR;
v_cs cursortype;--区别于法II
v_dept_info dept%ROWTYPE;
TYPE type_emp_info IS RECORD (
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE
);
v_emp_info type_emp_info;
BEGIN
OPEN v_cs FOR SELECT * FROM dept;
LOOP
FETCH v_cs INTO v_dept_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_dept_info.deptno||' '||v_dept_info.dname||' '||v_dept_info.loc);
END LOOP;
CLOSE v_cs;
OPEN v_cs FOR SELECT empno,ename,sal FROM emp;
LOOP
FETCH v_cs INTO v_emp_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_emp_info.v_empno||' '||v_emp_info.v_ename||' '||v_emp_info.v_sal);
END LOOP;
CLOSE v_cs;
END;
法II：
DECLARE
v_cs SYS_REFCURSOR;--区别于法I
v_dept_info dept%ROWTYPE;
TYPE type_emp_info IS RECORD (
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE
);
v_emp_info type_emp_info;
BEGIN
OPEN v_cs FOR SELECT * FROM dept;
LOOP
FETCH v_cs INTO v_dept_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_dept_info.deptno||' '||v_dept_info.dname||' '||v_dept_info.loc);
END LOOP;
CLOSE v_cs;
OPEN v_cs FOR SELECT empno,ename,sal FROM emp;
LOOP
FETCH v_cs INTO v_emp_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_emp_info.v_empno||' '||v_emp_info.v_ename||' '||v_emp_info.v_sal);
END LOOP;
CLOSE v_cs;
END;

二、存储过程
语法：
create [or replace] procedure 过程名[(参数名 IN|OUT|IN OUT 参数类型[,...])]
is|as
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;
例:
CREATE OR REPLACE PROCEDURE myfirstpro
AS
v_cs SYS_REFCURSOR;--区别于法I
v_dept_info dept%ROWTYPE;
TYPE type_emp_info IS RECORD (
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE
);
v_emp_info type_emp_info;
BEGIN
OPEN v_cs FOR SELECT * FROM dept;
LOOP
FETCH v_cs INTO v_dept_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_dept_info.deptno||' '||v_dept_info.dname||' '||v_dept_info.loc);
END LOOP;
CLOSE v_cs;
OPEN v_cs FOR SELECT empno,ename,sal FROM emp;
LOOP
FETCH v_cs INTO v_emp_info;
EXIT WHEN v_cs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_cs%ROWCOUNT||'行:'||v_emp_info.v_empno||' '||v_emp_info.v_ename||' '||v_emp_info.v_sal);
END LOOP;
CLOSE v_cs;
END;
过程的使用:
法I:
begin
myfirstpro;
end;
法II:
exec myfirstpro;

例:写一个过程,向dept表插入数据.
CREATE OR REPLACE PROCEDURE insert_dept_pro(t_deptno IN NUMBER,t_dname IN VARCHAR2,t_loc IN VARCHAR2)
IS
BEGIN
INSERT INTO dept VALUES(t_deptno,t_dname,t_loc);
COMMIT;
END;
使用:
exec insert_dept_pro(70,'AAA','BBB');


例:给过程一个员工编号,通过输出参数得到员工的姓名;
CREATE OR REPLACE PROCEDURE se

l_ename_by_no(t_empno IN NUMBER,t_dname OUT VARCHAR2)
IS
BEGIN
SELECT ename INTO t_dname FROM emp WHERE empno=t_empno;
END;
使用:
DECLARE
v_test VARCHAR2(20);
BEGIN
sel_ename_by_no(7788,v_test);
DBMS_OUTPUT.PUT_LINE('取出的姓名为'||v_test);
END;

例:给定一个员工编号,通过过程得到该员工的上司编号.
CREATE OR REPLACE PROCEDURE get_mgr_by_empno(t_num IN OUT NUMBER)
IS
BEGIN
SELECT mgr INTO t_num FROM emp WHERE empno=t_num;
END;
使用:
DECLARE
v_num emp.empno%TYPE:=&no;
BEGIN
get_mgr_by_empno(v_num);
DBMS_OUTPUT.PUT_LINE('取出的员工上司编号为'||v_num);
END;

三、包：包含两部分内容包规范和包体
定义包规范：
CREATE OR REPLACE PACKAGE "SCOTT"."MYPACKAGE" as
type recordtype is record(
v_dname varchar2(10),
v_loc varchar2(10));
procedure tempprocedure2(dept_no number,myrecord out recordtype);
end mypackage;

定义包主体：
CREATE OR REPLACE PACKAGE BODY "SCOTT"."MYPACKAGE" as
procedure tempprocedure2(dept_no number,myrecord out recordtype)
as
begin
select dname,loc into myrecord from scott.dept where deptno=dept_no;
end;
end mypackage;

使用：
DECLARE
v_my_rec mypackage.recordtype;
BEGIN
mypackage.tempprocedure2(10,v_my_rec);
DBMS_OUTPUT.PUT_LINE(v_my_rec.v_dname||' '||v_my_rec.v_loc);
END;

四、函数
语法：
create [or replace] function 函数名[(参数名 IN|OUT|IN OUT 参数类型[,...])]
return 数据类型
is|as
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;

例:给过程一个员工编号,返回员工的姓名;
CREATE OR REPLACE FUNCTION get_ename_by_no(t_empno IN NUMBER)
RETURN VARCHAR2
IS
t_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO t_ename FROM emp WHERE empno=t_empno;
RETURN t_ename;
END;
使用:
1 PL/SQL程序块执行函数
DECLARE
v_test VARCHAR2(20);
BEGIN
v_test:=get_ename_by_no(7788);
DBMS_OUTPUT.PUT_LINE('取出的姓名为'||v_test);
END;
2 execute执行函数
VARIABLE v_test VARCHAR2(20);
EXECUTE:v_test:=get_ename_by_no(7788);
PRINT v_test;
3 call执行函数
variable v_name varchar2(10);
call get_ename_by_no(7788) into :v_name;
print v_name;

函数和过程的区别：函数偏向于执行的结果；而过程偏向功能的实现。

返回游标：
例：返回dept标的游标，对表数据进行显示。
CREATE OR REPLACE FUNCTION test_cur
RETURN SYS_REFCURSOR
AS
v_vc SYS_REFCURSOR;
BEGIN
OPEN v_vc FOR SELECT * FROM dept;
RETURN v_vc;
END;
使用：
DECLARE
v_test_rc SYS_REFCURSOR;
v_dept_info dept%ROWTYPE;
BEGIN
v_test_rc:=test_cur;
LOOP
FETCH v_test_rc INTO v_dept_info;
EXIT WHEN v_test_rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('第'||v_test_rc%ROWCOUNT||
'行:'||v_dept_info.deptno||'--'||v_dept_info.dname||'--'||v_dept_info.loc);
END LOOP;
CLOSE v_test_rc;
END;

练习过程
写一个分页过，通过页码的指定和每页显示的行数来显示结果。
CREATE

OR REPLACE PROCEDURE fenye(pageid IN INTEGER,pagesize IN INTEGER)
AS
BEGIN
SELECT * FROM (
SELECT * FROM emp WHERE empno NOT IN (
SELECT empno FROM (
SELECT empno FROM emp ORDER BY empno
) WHERE rownum<=(pageid-1)*pagesize
)
)
WHERE rownum<=pagesize;
END;