CREATE TABLE ORDER_DETAIL
(
ORDERNO VARCHAR2(5) PRIMARY KEY,
ODATE DATE,
VENCODE VARCHAR2(5),
itemcode VARCHAR2(10),
qty_ord NUMBER,
qty_deld NUMBER,
OSTATUS CHAR(1),
DEL_DATE DATE,
ORDER_COST NUMBER
);
CREATE TABLE salary_records
(
EMPNO VARCHAR2(10),
BRANCHCODE VARCHAR2(15),
DEPTCODE VARCHAR2(15),
EMP_NAME CHAR(20),
WORKING_DAYS NUMBER,
EMPSAL NUMBER
);
INSERT INTO salary_records VALUES('E001', 'BH01','DP02','John Smith',30,12000);
INSERT INTO salary_records VALUES('E002','BH02','DP02','Jane',25, 15000);
INSERT INTO salary_records VALUES('E003','BH03','DP02','Harry',30,12000);
INSERT INTO salary_records VALUES('E004','BH01','DP02','WILLIAMS',24,13000);
COMMIT;
=================================================
DECLARE
TYPE cursor_type IS REF CURSOR;
cursor_ex cursor_type;
empTable employee%ROWTYPE;
optionval VARCHAR2(10);
BEGIN
optionval:='&Department_Code';
OPEN cursor_ex FOR SELECT * FROM employee
WHERE deptcode=optionval;
LOOP
FETCH cursor_ex INTO empTable;
EXIT WHEN cursor_ex%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('职员姓名：
'||empTable.ename);
END LOOP;
CLOSE cursor_ex;
END;
/
BEGIN
FOR dept_details IN (SELECT deptcode, deptname
FROM dept_details)
LOOP
DBMS_OUTPUT.PUT_LINE('部门');
DBMS_OUTPUT.PUT_LINE('-----------');
DBMS_OUTPUT.PUT_LINE(dept_details.DEPTNAME);
FOR employee in (SELECT empno, ename
FROM employee
WHERE DEPTCODE = DEPT_DETAILS.DEPTCODE)
