Kingbase数据库实验五 PL/SQL程序设计

一、实验目的

1. 能够使用流程控制语句完成简单程序的编写。
2. 能够使用系统函数。
3. 能够自定义简单地函数，并调用函数。
4. 能够使用简单的系统存储过程。
5. 能够创建和执行用户自定义存储过程。
6. 能够完成存储过程的修改、删除等管理任务。
7. 能够理解触发器调用的机制。
8. 能够使用PL/SQL命令创建DML触发器。
9. 能够完成触发器的修改、删除等管理任务。

二、实验内容及源代码

针对第6章设计的电子商务系统，做如下操作。
1．如果商品表中有价格在6000元以上的商品，把该商品的商品名称、商品类别、商品价格、生产厂家、商品的详细信息和商品的缩略图查询出来，否则输出“没有价格在6000元以上的商品”。
CREATE
    FUNCTION "PUBLIC".TEST_1() RETURNS VOID 
    AS DECLARE
    V_gdsName VARCHAR;V_gdsClass VARCHAR;V_gdsPrice INT;
    V_company VARCHAR;V_gdsExp VARCHAR;
    CURSOR V_gdsInfo FOR SELECT"GDSNA","CLASSTABLE"."CLANA","GDSPRICE","COMPANYTABLE"."COMPANYNA","GDSEXP"
	FROM ("GOODSTABLE" 
		INNER JOIN "CLASSTABLE" ON
		"GOODSTABLE"."CLSNUM" = "CLASSTABLE"."CLSNUM"
		)INNER JOIN "COMPANYTABLE" ON 
		"COMPANYTABLE"."COMPANYNUM" = "GOODSTABLE"."CLSNUM";
    BEGIN OPEN V_gdsInfo;
	    LOOP FETCH V_gdsInfo INTO
	    	V_gdsName,V_gdsClass,V_gdsPrice,V_company,V_gdsExp;
			IF V_gdsInfo%FOUND THEN
				IF V_gdsPrice > 6000 THEN
	raise notice '商品名称:% 商品类别:% 商品价格:% 生产厂家:% 商品的详细信息:%',V_gdsName,V_gdsClass,V_gdsPrice,V_company,V_gdsExp;
			END IF;
			Else exit;
			END IF; END LOOP;
			CLOSE V_gdsInfo;
	END;
CALL TEST_1();

2．在商品表中，查询某种商品，如果有，就修改该商品的名称，并输出商品的信息，否则输出“没有该商品！”
CREATE
    FUNCTION "PUBLIC".TEST_2(VgdsNa VARCHAR) RETURNS VOID 
    AS DECLARE
    V_gdsNum CHAR(10);V_gdsNa VARCHAR;V_gdsPrice INT;V_gdsExp VARCHAR;
    V_classNum CHAR(10);
    CURSOR cur_gdsInfo FOR 
    SELECT * FROM "GOODSTABLE" WHERE "GDSNA" = VgdsNa;
BEGIN OPEN cur_gdsInfo;
	LOOP FETCH cur_gdsInfo INTO V_gdsNum,V_gdsNa,V_gdsPrice,V_gdsExp,V_classNum;
	IF cur_gdsInfo%FOUND THEN
		raise notice '商品号:% 商品名称:% 商品价格:% 商品的详细信息:% 商品类别号：% ',V_gdsNum,V_gdsNa,V_gdsPrice,V_gdsExp,V_classNum;
update "GOODSTABLE" set "GDSNA" = '选中的商品' where "GDSNA" = VgdsNa;
	ELSE
	EXIT;
	END IF;
	END LOOP;
	CLOSE cur_gdsInfo;    
END;
CALL TEST_2('床');
SELECT * FROM "GOODSTABLE";
update "GOODSTABLE" set "GDSNA" = '床' where "GDSNA" = '选中的商品';

3．查询商品购买信息，将商品的购买数量都加1（提示：使用流程控制语句while）。
CREATE
    FUNCTION "PUBLIC".TEST_3() RETURNS VOID 
    AS
    DECLARE
    V_orNum VARCHAR(10);V_vNum VARCHAR(10);V_gdsNum CHAR(10);
    V_num INTEGER;V_subTime DATE;V_caseNum CHAR(10);
    CURSOR cur_orderInfo FOR 
    SELECT * FROM "订购表";
BEGIN
	OPEN cur_orderInfo;
	LOOP
	FETCH cur_orderInfo INTO V_orNum ,V_vNum ,V_gdsNum ,V_num ,V_subTime ,V_caseNum;
	IF cur_orderInfo%FOUND THEN
	raise notice '订单号:% 会员号:% 商品号:% 订购数量:% 下单时间：% 当前状态：% ',V_orNum ,V_vNum ,V_gdsNum ,V_num ,V_subTime ,V_caseNum ;
	ELSE
	EXIT;
	END IF;
	END LOOP;
	update "订购表" set "NUM"= "NUM"+1;
	CLOSE cur_orderInfo;    
END;
CALL TEST_3();
SELECT * FROM "订购表";

4．定义一个用户自定义的函数，能够根据订单号，查询商品的购买数量，如果购买数量>2，输出订单号、商品名称和购买数量。
CREATE
    FUNCTION "PUBLIC".TEST_4(V_orderNum VARCHAR) RETURNS VOID 
    AS DECLARE
    V_vorderNum VARCHAR;V_gdsName VARCHAR;V_num INT4;
    BEGIN SELECT "订购表"."NUM","GOODSTABLE"."GDSNA","订购表"."ORDERNUM"
	INTO V_num,V_gdsName,V_vorderNum
	FROM "订购表" LEFT JOIN "GOODSTABLE"
	ON "订购表"."GDSNUM" = "GOODSTABLE"."GDSNUM"
	WHERE "订购表"."ORDERNUM" = V_orderNum;
	IF V_num > 2 THEN
	raise notice '订单号% 商品名称% 购买数量%',V_vorderNum,V_gdsName,V_num;
	ELSE RAISE NOTICE '商品购买数量不到2';
	END IF;
END;
ALTER FUNCTION "PUBLIC".TEST_4(
    V_orderNum VARCHAR
) OWNER TO "SYSTEM";
CALL TEST_4('00001');

5．创建存储过程proc_1，显示购买人信息表中性别为“男”的用户信息，并调用此存储过程，显示执行结果。
CREATE
    PROCEDURE "PUBLIC".PROC_1() AS DECLARE 
    V_VNUM VARCHAR;V_VNA  VARCHAR;V_VSEX CHAR(6);V_VMAIL VARCHAR;
	CURSOR V_INFO FOR SELECT "VIPTABLE"."VIPNUM","VIPNA","VIPSX","VIPEMAIL"
	FROM "VIPTABLE","订购表"
	WHERE "VIPTABLE"."VIPNUM" = "订购表"."VIPNUM" AND "VIPTABLE"."VIPSX" = '男';
BEGIN OPEN V_INFO;
	LOOP FETCH V_INFO INTO V_VNUM,V_VNA,V_VSEX,V_VMAIL;
	IF V_INFO%FOUND THEN
	raise notice '会员号：% 姓名：% 性别：% 邮箱：%',V_VNUM,V_VNA,V_VSEX,V_VMAIL;
	Else exit;
	END IF;
	END LOOP;
	CLOSE V_INFO;
END;
ALTER PROCEDURE "PUBLIC".PROC_1() OWNER TO "SYSTEM";
CALL PROC_1();

6．创建存储过程proc_2，实现为购买人信息表添加一条记录，记录内容自己定义，并调用此存储过程，显示执行结果。
CREATE OR REPLACE PROCEDURE PROC_2(IN V_orNum VARCHAR(10),
    IN V_VNum VARCHAR(10),IN V_gdsNum CHAR(10),IN V_NUM  INT4,
    IN V_subTime DATE,IN V_case CHAR(10)
    ) AS BEGIN INSERT INTO "PUBLIC"."订购表"
	("ORDERNUM", "VIPNUM", "GDSNUM", "NUM", "SUBMITTIME", "CASENUM")
	VALUES(V_orNum,V_VNum,V_gdsNum,V_NUM,V_subTime,V_case);
END;
CALL PROC_2('00007', 'v999', '101', 1000, '2020-12-6', '4');
SELECT * FROM "PUBLIC"."订购表";

7．创建存储过程proc_3，实现根据商品编号查询某一商品的名称和价格，并调用此存储过程，显示执行结果。
CREATE OR REPLACE PROCEDURE PROC_3(
		IN V_gdsNum "GOODSTABLE"."GDSNUM" % TYPE,
		OUT V_gdsNA "GOODSTABLE"."GDSNA" % TYPE,
		OUT V_gdsPrice "GOODSTABLE"."GDSPRICE" % TYPE
	) AS BEGIN SELECT
		"GDSNA",
		"GDSPRICE" INTO V_gdsNA,
		V_gdsPrice
	FROM "GOODSTABLE"
	WHERE "GOODSTABLE"."GDSNUM" = V_gdsNum;
END;
CALL PROC_3('101');

8．修改存储过程proc_1，改为显示购买人信息表中性别为“女”的用户信息。
CREATE
    PROCEDURE "PUBLIC".PROC_1_1() AS DECLARE 
    V_VNUM VARCHAR;V_VNA  VARCHAR;V_VSEX CHAR(6);V_VMAIL VARCHAR;
	CURSOR V_INFO FOR SELECT "VIPTABLE"."VIPNUM","VIPNA","VIPSX","VIPEMAIL"
	FROM "VIPTABLE","订购表"
	WHERE "VIPTABLE"."VIPNUM" = "订购表"."VIPNUM" AND "VIPTABLE"."VIPSX" = '女';
BEGIN OPEN V_INFO;
	LOOP FETCH V_INFO INTO V_VNUM,V_VNA,V_VSEX,V_VMAIL;
	IF V_INFO%FOUND THEN
	raise notice '会员号：% 姓名：% 性别：% 邮箱：%',V_VNUM,V_VNA,V_VSEX,V_VMAIL;
	Else exit;
	END IF;
	END LOOP;
	CLOSE V_INFO;
END;
ALTER PROCEDURE "PUBLIC".PROC_1() OWNER TO "SYSTEM";
CALL PROC_1_1();

9．删除存储过程proc_1。
DROP PROCEDURE "PUBLIC"."PROC_1"();

10．创建触发器tr1，实现当修改商品表中的数据时，显示提示信息“商品表信息被修改了。”
CREATE TRIGGER tr1 AFTER UPDATE
    ON "PUBLIC"."GOODSTABLE" FOR EACH STATEMENT 
    AS BEGIN RAISE NOTICE '商品被修改了';   
END;
ALTER TABLE "PUBLIC"."GOODSTABLE" ENABLE TRIGGER tr1;
update "GOODSTABLE" set "GDSNA" = '蹦床' where "GDSNA" = '床';
update "GOODSTABLE" set "GDSNA" = '床' where "GDSNA" = '蹦床';

11．使用触发器tr2，实现当修改商品表中某种商品的商品编号时，对应购买信息表中的商品编号也要修改。
CREATE TRIGGER tr2 AFTER UPDATE OF "GDSNUM" ON "GOODSTABLE"
    FOR EACH ROW WHEN (NEW."GDSNUM" != OLD."GDSNUM")
    AS BEGIN
	update "订购表" set "GDSNUM" = NEW."GDSNUM" where "订购表"."GDSNUM" = OLD."GDSNUM";
END;
ALTER TABLE
    "PUBLIC"."GOODSTABLE" ENABLE TRIGGER tr2;
	update "GOODSTABLE" set "GDSNUM" = '123' where "GDSNA" = '铅笔';
	SELECT * FROM "GOODSTABLE";
	update "GOODSTABLE" set "GDSNUM" = '101' where "GDSNA" = '铅笔';

12．删除商品表上的触发器tr1。
DROP TRIGGER "TR1" ON "PUBLIC".GOODSTABLE;