Oracle的子查询和程序包上机1

--1、
--利用问题1中创建的表salayr_details表，创建work_days()函数，
--接受EMPCODE雇员编号，并检查所有雇员的工作天数。
--标准工作天数22天，雇员每少工作一天从其工资中扣除50元。
--函数返回雇员最后的工资，工作天数多于标准天数不加工资，
--工资扣完为止，不可以为负值。
CREATE OR REPLACE FUNCTION work_days(ecode NUMBER)
   RETURN NUMBER
   AS
   days NUMBER;
   sal  NUMBER;
BEGIN
   SELECT workdays,salary INTO days,sal FROM salary_detail
          WHERE empcode=ecode;
   days := 22-days;       --少工作的天数
   IF days>=1 THEN
      sal := sal-50*days; --实际薪水
      IF sal<0 THEN
         sal := 0;
      END IF;
   END IF;
   RETURN sal;   
END;

SELECT * FROM salary_detail;
SELECT work_days(1004) FROM dual;


--2、
--创建包含一个过程和一个函数的toyspack包，
--将过程和函数分别命名为updateToyPrice和avgToyPrice。
--过程应当将每个玩具的单价增加10％，直到玩具的
--平均价格达到400。此外，过程还应保证任一玩具的价格
--不超过500。
1.直接使用条件
2.使用更新游标

--使用更新游标
CREATE OR REPLACE PACKAGE pkg_toys
AS
    CURSOR c_toy RETURN my_toys%ROWTYPE;
    PROCEDURE updateToyPrice;
    FUNCTION avgToyPrice RETURN NUMBER;
END pkg_toys;

CREATE OR REPLACE PACKAGE BODY pkg_toys
AS
    --更新游标
    CURSOR c_toy RETURN my_toys%ROWTYPE
           IS SELECT * FROM my_toys FOR UPDATE;
    --求平均价格函数     
    FUNCTION avgToyPrice RETURN NUMBER
      AS
      v_avg NUMBER;
    BEGIN
        SELECT AVG(price) INTO v_avg FROM my_toys;
        RETURN v_avg;
    END avgToyPrice;
    --价格增加10%过程
    PROCEDURE updateToyPrice
      AS
      v_avg NUMBER;
    BEGIN
        v_avg := avgToyPrice;
        WHILE v_avg<400 LOOP
            FOR v_toy IN c_toy LOOP
                IF v_toy.price*1.1<=500 THEN
                    UPDATE my_toys SET price=price*1.1 WHERE CURRENT OF c_toy;
                END IF;          
            END LOOP;
            v_avg := avgToyPrice;
        END LOOP;
    END updateToyPrice;
END pkg_toys;

SELECT * FROM my_toys;
select AVG(price) FROM my_toys;

CALL pkg_toys.updateToyPrice();


--直接使用条件
CREATE OR REPLACE PACKAGE pkg_toys
AS  
    PROCEDURE updateToyPrice;
    FUNCTION avgToyPrice RETURN NUMBER;
END pkg_toys;

CREATE OR REPLACE PACKAGE BODY pkg_toys
AS   
    --求平均价格函数     
    FUNCTION avgToyPrice RETURN NUMBER
      AS
      v_avg NUMBER;
    BEGIN
        SELECT AVG(price) INTO v_avg FROM my_toys;
        RETURN v_avg;
    END avgToyPrice;
    --价格增加10%过程
    PROCEDURE updateToyPrice
      AS
      v_avg NUMBER;
    BEGIN
        v_avg := avgToyPrice;
        WHILE v_avg<400 LOOP          
            UPDATE my_toys SET price=price*1.1 WHERE price*1.1<=500;                    
            v_avg := avgToyPrice;
        END LOOP;
    END updateToyPrice;
END pkg_toys;


--3、
Create table billInfo
(
    billID varchar2(14),    --账单编号，格式为‘ZD201711130002’
    billDate date            --开单时间
);
--要求编写过程实现开单功能，即生成账单编号并插入记录。
--编号格式:ZD+年月日+自增四位数
SELECT * FROM billinfo;
SELECT to_char(SYSDATE,'yyyymmdd') from dual;
INSERT INTO billinfo VALUES('ZD201711130001',SYSDATE);

CREATE OR REPLACE PROCEDURE proc_bill(bid OUT VARCHAR2)
AS
   i NUMBER;
   v_max VARCHAR2(14);
BEGIN
   bid := 'ZD'||to_char(SYSDATE,'yyyymmdd');
   SELECT COUNT(1) INTO i FROM billinfo
          WHERE billid LIKE bid||'%';
   IF i>0 THEN
       SELECT MAX(billid) INTO v_max FROM billinfo;
       bid:=bid||SUBSTR((SUBSTR(v_max,11)+10001),2);
   ELSE
       bid:=bid||'0001';
   END IF;
END;

DECLARE
   bid VARCHAR2(14);
BEGIN
   proc_bill(bid);
   dbms_output.put_line(bid);
END;