PL/SQL Package
First Step:
Second Step:
Third Step:
Fourth Step:
Code Example:ShowRecord.rar
create table DEPT
(
DEPTNO NUMBER(20) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
(
DEPTNO NUMBER(20) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
Second Step:
create or replace package ShowRecord is
-- Author : ANGUS TANG
-- Created : 2006-08-03 15:03:56
-- Purpose : Use Cursor
PROCEDURE SHOWALL;
PROCEDURE SHOWALL2;
PROCEDURE SHOWONE(P_NO IN VARCHAR2);
PROCEDURE SHOWONE2(P_NO IN VARCHAR2);
FUNCTION SHOWAUTHOR RETURN VARCHAR2;
end ShowRecord;
-- Author : ANGUS TANG
-- Created : 2006-08-03 15:03:56
-- Purpose : Use Cursor
PROCEDURE SHOWALL;
PROCEDURE SHOWALL2;
PROCEDURE SHOWONE(P_NO IN VARCHAR2);
PROCEDURE SHOWONE2(P_NO IN VARCHAR2);
FUNCTION SHOWAUTHOR RETURN VARCHAR2;
end ShowRecord;
Third Step:
create or replace package body ShowRecord is
PROCEDURE SHOWALL IS
CURSOR DEPT IS SELECT * FROM DEPT;
BEGIN
FOR T IN DEPT LOOP
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
END LOOP;
END SHOWALL;
PROCEDURE SHOWALL2 IS
CURSOR DEPT IS SELECT * FROM DEPT;
T DEPT%ROWTYPE;
BEGIN
IF NOT DEPT%ISOPEN THEN
OPEN DEPT;
END IF;
FETCH DEPT INTO T;
LOOP
EXIT WHEN DEPT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
FETCH DEPT INTO T;
END LOOP;
IF DEPT%ISOPEN THEN
CLOSE DEPT;
END IF;
END SHOWALL2;
PROCEDURE SHOWONE(P_NO IN VARCHAR2) IS
CURSOR DEPT(NO VARCHAR2) IS SELECT * FROM DEPT WHERE DEPTNO=NO;
BEGIN
FOR T IN DEPT(P_NO) LOOP
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
END LOOP;
END SHOWONE;
PROCEDURE SHOWONE2(P_NO IN VARCHAR2) IS
CURSOR DEPT(NO VARCHAR2) IS SELECT * FROM DEPT WHERE DEPTNO=NO;
T DEPT%ROWTYPE;
BEGIN
IF NOT DEPT%ISOPEN THEN
OPEN DEPT(P_NO);
END IF;
FETCH DEPT INTO T;
LOOP
EXIT WHEN DEPT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
FETCH DEPT INTO T;
END LOOP;
IF DEPT%ISOPEN THEN
CLOSE DEPT;
END IF;
END SHOWONE2;
FUNCTION SHOWAUTHOR RETURN VARCHAR2 IS
BEGIN
RETURN 'ANGUS TANG';
END;
end ShowRecord;
PROCEDURE SHOWALL IS
CURSOR DEPT IS SELECT * FROM DEPT;
BEGIN
FOR T IN DEPT LOOP
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
END LOOP;
END SHOWALL;
PROCEDURE SHOWALL2 IS
CURSOR DEPT IS SELECT * FROM DEPT;
T DEPT%ROWTYPE;
BEGIN
IF NOT DEPT%ISOPEN THEN
OPEN DEPT;
END IF;
FETCH DEPT INTO T;
LOOP
EXIT WHEN DEPT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
FETCH DEPT INTO T;
END LOOP;
IF DEPT%ISOPEN THEN
CLOSE DEPT;
END IF;
END SHOWALL2;
PROCEDURE SHOWONE(P_NO IN VARCHAR2) IS
CURSOR DEPT(NO VARCHAR2) IS SELECT * FROM DEPT WHERE DEPTNO=NO;
BEGIN
FOR T IN DEPT(P_NO) LOOP
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
END LOOP;
END SHOWONE;
PROCEDURE SHOWONE2(P_NO IN VARCHAR2) IS
CURSOR DEPT(NO VARCHAR2) IS SELECT * FROM DEPT WHERE DEPTNO=NO;
T DEPT%ROWTYPE;
BEGIN
IF NOT DEPT%ISOPEN THEN
OPEN DEPT(P_NO);
END IF;
FETCH DEPT INTO T;
LOOP
EXIT WHEN DEPT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(DEPT%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(T.DEPTNO);
DBMS_OUTPUT.PUT_LINE(T.DNAME);
DBMS_OUTPUT.PUT_LINE(T.LOC);
FETCH DEPT INTO T;
END LOOP;
IF DEPT%ISOPEN THEN
CLOSE DEPT;
END IF;
END SHOWONE2;
FUNCTION SHOWAUTHOR RETURN VARCHAR2 IS
BEGIN
RETURN 'ANGUS TANG';
END;
end ShowRecord;
Fourth Step:
BEGIN
ShowRecord.Showall;
END;
BEGIN
ShowRecord.Showall2;
END;
BEGIN
ShowRecord.Showone('11');
END;
BEGIN
ShowRecord.Showone2('11');
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(ShowRecord.Showauthor);
END;
ShowRecord.Showall;
END;
BEGIN
ShowRecord.Showall2;
END;
BEGIN
ShowRecord.Showone('11');
END;
BEGIN
ShowRecord.Showone2('11');
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(ShowRecord.Showauthor);
END;
Code Example:ShowRecord.rar