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

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);
一、游标变量
例：使用游标变量取出部门表的所有相关数据。
法I：
DECLARE
TYPE cursortype IS REF CURSOR;
v_cs cursortype;--区别于法II
v_dept_info dept%ROWTYPE;
TYPE type_emp_info IS RECORD (
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)
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);
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;
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;
例:给过程一个员工编号,返回员工的姓名;
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;
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;
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
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);
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
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;
例:
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,
print v_name;
函数和过程的区别：函数偏向于执行的结果；而过程偏向功能的实现。
返回游标：
例：返回dept标的游标，对表数据进行显示。
CREATE OR REPLACE FUNCTION test_cur
RETURN SYS_REFCURSOR
AS
v_vc SYS_REFCURSOR;
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);
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);
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 sel_ename_by_no(t_empno IN NUMBER,t_dname OUT VARCHAR2)