CREATE DEFINER=`root`@`localhost` PROCEDURE `doctor_schedule`(IN d_id INT) BEGIN SELECT doctor.d_name, `work`.work_start, `work`.work_end,`work`.work_state FROM doctor JOIN `work` ON doctor.d_id = `work`.d_id WHERE d_id = d_id; END为什么不指定医生而是返回所有医生的信息
时间: 2023-08-16 19:45:09 浏览: 34
如果您想更改存储过程的功能,使其返回所有医生的工作安排信息,而不仅仅是指定医生的信息,可以将存储过程中的WHERE子句移除。修改后的存储过程如下所示:
```
CREATE DEFINER=`root`@`localhost` PROCEDURE `doctor_schedule_all`()
BEGIN
SELECT doctor.d_name, `work`.work_start, `work`.work_end, `work`.work_state
FROM doctor
JOIN `work` ON doctor.d_id = `work`.d_id;
END
```
上述存储过程不需要传入参数,将返回所有医生的工作安排信息。如果需要调用该存储过程,可以使用以下语句:
```
CALL doctor_schedule_all();
```
注意:在调用存储过程时,如果存储过程需要传递参数,您需要在CALL语句中指定参数值。如果存储过程不需要参数,则可以像上述示例中一样省略参数。
相关问题
CREATE DEFINER=root@localhost PROCEDURE doctor_schedule(IN d_id INT) BEGIN SELECT doctor.d_name, work.work_start, work.work_end,work.work_state FROM doctor JOIN work ON doctor.d_id = work.d_id WHERE d_id = d_id;怎么调用
您可以使用CALL语句来调用该存储过程,语法如下:
```
CALL doctor_schedule(参数);
```
其中,参数为整型值,用于指定医生的ID。例如,如果要获取医生ID为1的医生的工作安排,可以执行以下语句:
```
CALL doctor_schedule(1);
```
执行后,将返回医生名称,工作开始时间,工作结束时间和工作状态的结果集。
请将这个存储过程修改为触发器,当其余四个表插入新数据时,t_pcm_prod_own能够修改更新数据 CREATE DEFINER=root@% PROCEDURE test03() BEGIN -- 是否持有活期 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR)) THEN UPDATE T_PCM_PROD_OWN SET IS_DEP = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR); ELSE UPDATE T_PCM_PROD_OWN SET IS_DEP = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR); END IF; -- 是否持有定期 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED)) THEN UPDATE T_PCM_PROD_OWN SET IS_FIXED_DEP = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED); ELSE UPDATE T_PCM_PROD_OWN SET IS_FIXED_DEP = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED); END IF; -- 是否持有贷款 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN)) THEN UPDATE T_PCM_PROD_OWN SET IS_LOAN = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN); ELSE UPDATE T_PCM_PROD_OWN SET IS_LOAN = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN); END IF; -- 是否持有理财 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH)) THEN UPDATE T_PCM_PROD_OWN SET IS_WEALTH = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH); ELSE UPDATE T_PCM_PROD_OWN SET IS_WEALTH = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH); END IF; INSERT INTO T_PCM_PROD_OWN SELECT REPLACE(UUID(),'-','') ,T_PCM_CUST.CUST_ID ,T_PCM_CUST.LAW_ORG_ID ,T_PCM_CUST.ECIF_CUST_ID ,T_PCM_PROD_OWN.IS_DEP ,T_PCM_PROD_OWN.IS_FIXED_DEP ,T_PCM_PROD_OWN.IS_LOAN ,T_PCM_PROD_OWN.IS_WEALTH ,T_PCM_DEP_CURR.CURRENT_BAL ,T_PCM_DEP_FIXED.BAL ,T_PCM_LOAN.LOAN_MON ,T_PCM_WEALTH.CURRENT_BAL FROM T_PCM_CUST LEFT JOIN T_PCM_DEP_CURR ON T_PCM_CUST.CUST_ID = T_PCM_DEP_CURR.CUST_ID LEFT JOIN T_PCM_DEP_FIXED ON T_PCM_CUST.CUST_ID = T_PCM_DEP_FIXED.CUST_ID LEFT JOIN T_PCM_LOAN ON T_PCM_CUST.CUST_ID = T_PCM_LOAN.CUST_ID LEFT JOIN T_PCM_WEALTH ON T_PCM_CUST.CUST_ID = T_PCM_WEALTH.CUST_ID LEFT JOIN T_PCM_PROD_OWN ON T_PCM_CUST.CUST_ID = T_PCM_PROD_OWN.CUST_ID; END
您好!根据您的要求,将存储过程修改为触发器,当其余四个表插入新数据时,t_pcm_prod_own将能够修改更新数据。以下是修改后的触发器代码:
```mysql
CREATE DEFINER=root@% TRIGGER trg_test03
AFTER INSERT ON T_PCM_DEP_CURR
FOR EACH ROW
BEGIN
-- 是否持有活期
IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
UPDATE T_PCM_PROD_OWN
SET IS_DEP = '1'
WHERE CUST_ID = NEW.CUST_ID;
ELSE
UPDATE T_PCM_PROD_OWN
SET IS_DEP = '0'
WHERE CUST_ID = NEW.CUST_ID;
END IF;
END;
CREATE DEFINER=root@% TRIGGER trg_test03_2
AFTER INSERT ON T_PCM_DEP_FIXED
FOR EACH ROW
BEGIN
-- 是否持有定期
IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
UPDATE T_PCM_PROD_OWN
SET IS_FIXED_DEP = '1'
WHERE CUST_ID = NEW.CUST_ID;
ELSE
UPDATE T_PCM_PROD_OWN
SET IS_FIXED_DEP = '0'
WHERE CUST_ID = NEW.CUST_ID;
END IF;
END;
CREATE DEFINER=root@% TRIGGER trg_test03_3
AFTER INSERT ON T_PCM_LOAN
FOR EACH ROW
BEGIN
-- 是否持有贷款
IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
UPDATE T_PCM_PROD_OWN
SET IS_LOAN = '1'
WHERE CUST_ID = NEW.CUST_ID;
ELSE
UPDATE T_PCM_PROD_OWN
SET IS_LOAN = '0'
WHERE CUST_ID = NEW.CUST_ID;
END IF;
END;
CREATE DEFINER=root@% TRIGGER trg_test03_4
AFTER INSERT ON T_PCM_WEALTH
FOR EACH ROW
BEGIN
-- 是否持有理财
IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
UPDATE T_PCM_PROD_OWN
SET IS_WEALTH = '1'
WHERE CUST_ID = NEW.CUST_ID;
ELSE
UPDATE T_PCM_PROD_OWN
SET IS_WEALTH = '0'
WHERE CUST_ID = NEW.CUST_ID;
END IF;
END;
```
请注意,每个表都有一个单独的触发器,以确保在插入新数据时更新相应的列。触发器将根据新插入的数据的CUST_ID值来更新t_pcm_prod_own表中的相关列。