CT PID, PNAMEРFROM PRODUCTSРWHERE PID NOT INР?(SELECT PID FROM SALESР?WHERE PDATE > SYSDATE – 300);Р5、РSELECT * FROM EРWHERE INDATE=Р(SELECT MAX(INDATE) FROM E);Р6、РUPDATE PRODUCTSРSET PNAME= UPPER(PNAME);Р四、写出以下语句的含义(15分,每小题5分)Р1、答:从PRODUCTS表中删除那些没有进货过,或者现在库存量为零的产品(5分)Р2、答:创建一个视图,包括每个产品的PID和总成本。(5分)Р3、答:列出总销售成本最少的前5种产品及其总成本。(5分)Р五、编写存储过程,完成以下要求(15分)Р(其中基本框架10分,细节5分。)Р参考答案:РCREATE PROCEDURE MAKE_SALE(APID CHAR, QTY_WILL NUMBER, ASID CHAR)РASРBEGINР?DECLAREР CURSOR CUR1 IS Р SELECT INID, QTY_SOCKET, PRICEР FROM EР WHERE PID= APIDР AND QTY_SOCKET > 0Р ORDER BY INDATE ASC;Р REST NUMBER;Р TCOST NUMBER;Р BEGINР SELECT SUM(QTY_SOCKET) INTO RESTР FROM E WHERE PID= APID;Р IF (REST < QTY_WILL)Р RETURN;Р END IF;Р TCOST?:=?0;Р FOR R_1 IN CUR1 LOOPР IF (QTY_WILL > R_1.QTY_SOCKET) THENР BEGINР UPDATE E
全文预览
