操作题第三题题库.xls

CREATE OR REPLACE PROCEDURE ADD emp empID NUMBER empName VARCHAR2 empEnterdate DATE Sal NUMBER groupNo NUMBER IS BEGIN INSERT INTO EMP02 VALUES empID empName empEnterdate sal groupNo END CREATE OR REPLACE FUNCTION GET SALARY empno number RETURN NUMBER IS salary emp02 sal TYPE BEGIN IF valid id empno THEN SELECT sal INTO salary FROM emp02 WHERE empID empno RETURN salary ELSE DBMS OUTPUT PUT LINE 20010 该员工不存在 END IF END DECLARE v specialty EMP02 groupNo TYPE CURSOR emp cur IS SELECT empName empEnterdate FROM EMP02 WHERE groupNo v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 员工姓名 雇佣日期 FOR emp record IN emp cur LOOP DBMS OUTPUT PUT LINE emp cur ROWCOUNT emp record empName emp record empEnterdate END LOOP END CREATE OR REPLACE PROCEDURE ADD STU stuNO NUMBER stuName VARCHAR2 subjectName VARCHAR2 marks NUMBER classNo VARCHAR2 IS BEGIN INSERT INTO STU02 VALUES stuNO stuName subjectName marks classNO END CREATE OR REPLACE FUNCTION GET MARKS stuID number subName varchar2 RETURN number IS stuMarks STU02 marks TYPE BEGIN IF valid id stuID THEN SELECT marks INTO stuMarks FROM STU02 WHERE stuNo stuID and subjectName subName RETURN stuMarks ELSE DBMS OUTPUT PUT LINE 20010 该学生不存在 END IF END DECLARE v specialty STU02 classNo TYPE CURSOR stu cur IS SELECT stuName subjectName marks FROM STU02 WHERE classNo v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 学生姓名 学科名 分数 FOR stu record IN stu cur LOOP DBMS OUTPUT PUT LINE stu cur ROWCOUNT stu record stuName stu record subjectName stu record marks END LOOP END CREATE OR REPLACE PROCEDURE ADD COMPUTER computerID VARCHAR2 hardDiskCapacity NUMBER memoryCapacity NUMBER mainboardCatagory VARCHAR2 deptNo VARCHAR2 IS BEGIN INSERT INTO COMPUTER01 VALUES computerID hardDiskCapacity memoryCapacity mainboard deptNO END CREATE OR REPLACE FUNCTION GET MAINBOARD compno varchar2 RETURN VARCHAR2 IS Mainboardstyle COMPUTER01 mainboardCatagory TYPE BEGIN IF valid id compno THEN SELECT mainboardCatagory INTO Mainboardstyle FROM COMPUTER01 WHERE computerID compno RETURN Mainboardstyle ELSE DBMS OUTPUT PUT LINE 20010 该电脑不存在 END IF END DECLARE v specialty COMPUTER01 deptNo TYPE CURSOR computer cur IS SELECT hardDiskCapacity memoryCapacity FROM COMPUTER01 WHERE deptNo v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 硬盘容量 内存容量 FOR computer record IN computer cur LOOP DBMS OUTPUT PUT LINE computer cur ROWCOUNT computer record hardDiskCapacity computer record memoryCapacity END LOOP END CREATE OR REPLACE PROCEDURE ADD BOOK bookID VARCHAR2 bookName VARCHAR2 bookCatagory VARCHAR2 price NUMBER libraryID VARCHAR2 IS BEGIN INSERT INTO BOOKS01 VALUES bookID bookName bookCatagory price libraryID END CREATE OR REPLACE FUNCTION GET PRICE bookno varchar2 RETURN NUMBER IS Bookprice BOOK01 price TYPE BEGIN IF valid id bookno THEN SELECT price INTO Bookprice FROM BOOK01 WHERE bookID bookno RETURN Bookprice ELSE DBMS OUTPUT PUT LINE 20010 该书籍不存在 END IF END DECLARE v specialty BOOKS01 libraryID TYPE CURSOR book cur IS SELECT bookName bookCatagory FROM BOOKS01 WHERE libraryID v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 书籍名称 书籍种类 FOR book record IN book cur LOOP DBMS OUTPUT PUT LINE book cur ROWCOUNT book record bookName book record bookCatagory END LOOP END CREATE OR REPLACE PROCEDURE ADD PHONE phoneID VARCHAR2 phoneBrand VARCHAR2 phoneCatagory VARCHAR2 phonePrice NUMBER storeID VARCHAR2 IS BEGIN INSERT INTO MOBILEPHONE01 VALUES phoneID phoneBrand phoneCatagory phonePrice shoreID END CREATE OR REPLACE FUNCTION GET PRICE phoneno varchar2 RETURN NUMBER IS price MOBILEPHONE01 phonePrice TYPE BEGIN IF valid id phoneno THEN SELECT price INTO price FROM MOBILEPHONE01 WHERE phoneID phoneno RETURN price ELSE DBMS OUTPUT PUT LINE 20010 该手机不存在 END IF END DECLARE v specialty MOBILEPHONE01 storeID TYPE CURSOR phone cur IS SELECT phoneBrand phoneCatagory FROM MOBILEPHONE01 WHERE storeID v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 手机品牌 品牌型号 FOR phone record IN phone cur LOOP DBMS OUTPUT PUT LINE phone cur ROWCOUNT phone record phoneBrand phone record phoneCatagory END LOOP END CREATE OR REPLACE PROCEDURE ADD PLANE planeID VARCHAR2 planeCatagory VARCHAR2 manufacturedate Date price NUMBER computerID VARCHAR2 IS BEGIN INSERT INTO AIRPLANE01 VALUES planeID planeCatagory manufacturedate price computerID END CREATE OR REPLACE FUNCTION GET PRICE planeno varchar2 RETURN NUMBER IS planeprice AIRPLANE01 price TYPE BEGIN IF valid id planeno THEN SELECT price INTO planeprice FROM AIRPLANE01 WHERE planeID planeno RETURN planeprice ELSE DBMS OUTPUT PUT LINE 20010 该飞机不存在 END IF END DECLARE v specialty AIRPLANEputerID TYPE CURSOR plane cur IS SELECT planeCatagory manufacturedate FROM AIRPLANE01 WHERE computerID v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 飞机型号 制造日期 FOR plane record IN plane cur LOOP DBMS OUTPUT PUT LINE plane cur ROWCOUNT plane record planeCatagory plane record manufacturedate END LOOP END CREATE OR REPLACE PROCEDURE ADD AUTO autoID VARCHAR2 autoCatagory VARCHAR2 manufacturedate Date price NUMBER computerID VARCHAR2 IS BEGIN INSERT INTO AUTO01 VALUES autoID autoCatagory manufacturedate price computerID END CREATE OR REPLACE FUNCTION GET PRICE autono varchar2 RETURN NUMBER IS autoprice AUTO01 price TYPE BEGIN IF valid id planeno THEN SELECT price INTO autoprice FROM AUTO01 WHERE autoID autono RETURN autoprice ELSE DBMS OUTPUT PUT LINE 20010 该汽车不存在 END IF END DECLARE v specialty AUTOputerID TYPE CURSOR auto cur IS SELECT autoCatagory manufacturedate FROM AUTO01 WHERE computerID v specialty BEGIN v specialty DBMS OUTPUT PUT LINE 序号 汽车型号 制造日期 FOR auto record IN auto cur LOOP DBMS OUTPUT PUT LINE auto cur ROWCOUNT auto record autoCatagory auto record manufacturedate END LOOP END CREATE OR REPLACE PROCEDURE ADD BAG bagID VARCHAR2 bagCatagory VARCHAR2 manufacturedate Date price NUMBER storeID VARCHAR2 IS BEGIN INSERT INTO SCHOOLB