代码是下面这样的,第一次写存储过程,肯定有挺多地方做得不对,希望高手能帮忙指出
1.现在是一条提交一次,可以改成多条吗?怎么个改法?
2.车和驾驶员存在关系表tr_vhc_driver,驾驶员和图标存在关系表tr_driver_icon,表之间有外键关联,不知道多条一块儿提交能执行吗?
3.代码如下,挺长挺乱,辛苦大家了CREATE OR REPLACE PROCEDURE prc_cutin_driver(icon_id IN VARCHAR2, p_out OUT NUMBER) IS
/* 驾驶员信息
* 参数:图标id(与驾驶员绑定)
* 需要基础数据:集团信息,车辆信息
*/
CURSOR driver_cursor IS
SELECT * FROM ut_driver t;
rec_ts driver_cursor%ROWTYPE; v_driver_id VARCHAR2(50);
v_corp_id VARCHAR2(50);
v_corp_amount NUMBER;--集团个数
v_vehicle_id VARCHAR2(50);
v_vehicle_amount NUMBER;--车辆个数
v_icon_amount NUMBER;--图标个数
v_sex NUMBER; -- 性别:男1,女2
v_birthday VARCHAR2(15); -- 生日
no_car_exception EXCEPTION; -- 无车辆异常
no_corp_exception EXCEPTION; -- 无集团异常
no_icon_exception EXCEPTION; -- 无图标异常
li_count NUMBER;BEGIN
OPEN driver_cursor;
LOOP
BEGIN
FETCH driver_cursor INTO rec_ts;
EXIT WHEN driver_cursor%NOTFOUND; -- 有无集团
SELECT COUNT(t.id) INTO v_corp_amount FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
IF v_corp_amount = 1 THEN
SELECT t.id INTO v_corp_id FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
ELSIF v_corp_amount = 0 THEN
RAISE no_corp_exception;
END IF; -- 有无图标
SELECT COUNT(t.id) INTO v_icon_amount FROM te_iconcollection t WHERE t.id = icon_id;
IF v_icon_amount = 0 THEN
RAISE no_icon_exception;
END IF; -- 有无车辆
SELECT COUNT(t.id)
INTO v_vehicle_amount
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
IF v_vehicle_amount = 1 THEN -- 驾驶员有车对应
SELECT t.id
INTO v_vehicle_id
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
ELSIF v_vehicle_amount = 0 THEN -- 驾驶员无车对应
RAISE no_car_exception;
END IF;
--像te_driver表中插入记录(v_corp_id为上面赋值)
IF rec_ts.driver_sex = '男' THEN
v_sex := 1;
ELSIF rec_ts.driver_sex = '女' THEN
v_sex := 2;
END IF;
v_birthday := substr(rec_ts.driver_birthday,1,8); v_driver_id := fun_gid();
INSERT INTO te_driver
(intro,driver_sex,driver_birthday,identity_id,driver_address,driver_work_id,telephone_number,
mobile_number,driver_state,NAME,photourl,re,corpid,id)
VALUES
(rec_ts.intro,v_sex,to_date(v_birthday,'yyyy-mm-dd'),rec_ts.identity_id,rec_ts.driver_address,
rec_ts.driver_work_id,rec_ts.telephone_number,rec_ts.mobile_number,rec_ts.driver_state,rec_ts.NAME,
rec_ts.photourl,rec_ts.re,v_corp_id,v_driver_id); -- 驾驶员-车辆 : tr_vhc_driver
INSERT INTO tr_vhc_driver
(vhc_id,drv_id,id)
VALUES
(v_vehicle_id,v_driver_id,fun_gid()); -- 新增一条图标记录 tr_driver_icon(图标id由传入参数提供)
INSERT INTO tr_driver_icon
(drv_id,icon_id,id)
VALUES
(v_driver_id,icon_id,fun_gid());
COMMIT;
EXCEPTION
WHEN no_car_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的车辆',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的车辆'); WHEN no_corp_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的企业',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的企业'); WHEN no_icon_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应图标',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应图标'); WHEN OTHERS THEN
ROLLBACK;
li_count:=fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,SQLCODE,SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',异常编号:'||SQLCODE||',异常描述:'||SQLERRM); END;
END LOOP;
CLOSE driver_cursor;
p_out := 1;END prc_cutin_driver;
1.现在是一条提交一次,可以改成多条吗?怎么个改法?
2.车和驾驶员存在关系表tr_vhc_driver,驾驶员和图标存在关系表tr_driver_icon,表之间有外键关联,不知道多条一块儿提交能执行吗?
3.代码如下,挺长挺乱,辛苦大家了CREATE OR REPLACE PROCEDURE prc_cutin_driver(icon_id IN VARCHAR2, p_out OUT NUMBER) IS
/* 驾驶员信息
* 参数:图标id(与驾驶员绑定)
* 需要基础数据:集团信息,车辆信息
*/
CURSOR driver_cursor IS
SELECT * FROM ut_driver t;
rec_ts driver_cursor%ROWTYPE; v_driver_id VARCHAR2(50);
v_corp_id VARCHAR2(50);
v_corp_amount NUMBER;--集团个数
v_vehicle_id VARCHAR2(50);
v_vehicle_amount NUMBER;--车辆个数
v_icon_amount NUMBER;--图标个数
v_sex NUMBER; -- 性别:男1,女2
v_birthday VARCHAR2(15); -- 生日
no_car_exception EXCEPTION; -- 无车辆异常
no_corp_exception EXCEPTION; -- 无集团异常
no_icon_exception EXCEPTION; -- 无图标异常
li_count NUMBER;BEGIN
OPEN driver_cursor;
LOOP
BEGIN
FETCH driver_cursor INTO rec_ts;
EXIT WHEN driver_cursor%NOTFOUND; -- 有无集团
SELECT COUNT(t.id) INTO v_corp_amount FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
IF v_corp_amount = 1 THEN
SELECT t.id INTO v_corp_id FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
ELSIF v_corp_amount = 0 THEN
RAISE no_corp_exception;
END IF; -- 有无图标
SELECT COUNT(t.id) INTO v_icon_amount FROM te_iconcollection t WHERE t.id = icon_id;
IF v_icon_amount = 0 THEN
RAISE no_icon_exception;
END IF; -- 有无车辆
SELECT COUNT(t.id)
INTO v_vehicle_amount
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
IF v_vehicle_amount = 1 THEN -- 驾驶员有车对应
SELECT t.id
INTO v_vehicle_id
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
ELSIF v_vehicle_amount = 0 THEN -- 驾驶员无车对应
RAISE no_car_exception;
END IF;
--像te_driver表中插入记录(v_corp_id为上面赋值)
IF rec_ts.driver_sex = '男' THEN
v_sex := 1;
ELSIF rec_ts.driver_sex = '女' THEN
v_sex := 2;
END IF;
v_birthday := substr(rec_ts.driver_birthday,1,8); v_driver_id := fun_gid();
INSERT INTO te_driver
(intro,driver_sex,driver_birthday,identity_id,driver_address,driver_work_id,telephone_number,
mobile_number,driver_state,NAME,photourl,re,corpid,id)
VALUES
(rec_ts.intro,v_sex,to_date(v_birthday,'yyyy-mm-dd'),rec_ts.identity_id,rec_ts.driver_address,
rec_ts.driver_work_id,rec_ts.telephone_number,rec_ts.mobile_number,rec_ts.driver_state,rec_ts.NAME,
rec_ts.photourl,rec_ts.re,v_corp_id,v_driver_id); -- 驾驶员-车辆 : tr_vhc_driver
INSERT INTO tr_vhc_driver
(vhc_id,drv_id,id)
VALUES
(v_vehicle_id,v_driver_id,fun_gid()); -- 新增一条图标记录 tr_driver_icon(图标id由传入参数提供)
INSERT INTO tr_driver_icon
(drv_id,icon_id,id)
VALUES
(v_driver_id,icon_id,fun_gid());
COMMIT;
EXCEPTION
WHEN no_car_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的车辆',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的车辆'); WHEN no_corp_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的企业',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的企业'); WHEN no_icon_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应图标',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应图标'); WHEN OTHERS THEN
ROLLBACK;
li_count:=fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,SQLCODE,SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',异常编号:'||SQLCODE||',异常描述:'||SQLERRM); END;
END LOOP;
CLOSE driver_cursor;
p_out := 1;END prc_cutin_driver;
解决方案 »
- 一个关于游标的问题
- 如何取table集合中的任意一个值赋予某个变量
- oracle版本文件导入导出的问题
- 【同一表重分组信息,取每组第一条记录,这样的select语句如何写?先谢谢各位!】
- 怎么登陆sql*plus
- 急问:Oracle客户端有没有For Server的版本呀?
- Create table a(a1 number(1,2))
- 如何分区表使用方案?
- 请问有没有SQL Server 和 Oracle SQL脚本互换的软件(或是相关资料)?
- oracle9i 有三张光盘,安装到百分之60的时候就过不去了, 换机器还是这样. 谢谢帮助!
- 触发器只对刚刚操作的记录进行操作
- orcle中 如何把多个表中的数据量统计到一张表中显示出来?要求显示在一行上。在线等!
/* 驾驶员信息
* 参数:图标id(与驾驶员绑定)
* 需要基础数据:集团信息,车辆信息
*/
CURSOR driver_cursor IS
SELECT * FROM ut_driver t; rec_ts driver_cursor%ROWTYPE; v_driver_id VARCHAR2(50);
v_corp_id VARCHAR2(50);
v_corp_amount NUMBER; --集团个数
v_vehicle_id VARCHAR2(50);
v_vehicle_amount NUMBER; --车辆个数
v_icon_amount NUMBER; --图标个数
v_sex NUMBER; -- 性别:男1,女2
v_birthday VARCHAR2(15); -- 生日
no_car_exception EXCEPTION; -- 无车辆异常
no_corp_exception EXCEPTION; -- 无集团异常
no_icon_exception EXCEPTION; -- 无图标异常
li_count NUMBER; -- 新增
I Number;
BEGIN
I := 0;
OPEN driver_cursor;
LOOP
BEGIN
FETCH driver_cursor INTO rec_ts;
EXIT WHEN driver_cursor%NOTFOUND;
--
I := I + 1;
--
-- 有无集团
SELECT COUNT(t.id) INTO v_corp_amount FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
IF v_corp_amount = 1 THEN
SELECT t.id INTO v_corp_id FROM te_corporation t WHERE t.cwt_business_id = rec_ts.cwt_business_id;
ELSIF v_corp_amount = 0 THEN
RAISE no_corp_exception;
END IF; -- 有无图标
SELECT COUNT(t.id) INTO v_icon_amount FROM te_iconcollection t WHERE t.id = icon_id;
IF v_icon_amount = 0 THEN
RAISE no_icon_exception;
END IF; -- 有无车辆
SELECT COUNT(t.id)
INTO v_vehicle_amount
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
IF v_vehicle_amount = 1 THEN -- 驾驶员有车对应
SELECT t.id
INTO v_vehicle_id
FROM te_vehicletarget t
WHERE t.NAME = rec_ts.car_name
AND t.corp_id =
(SELECT tc.id
FROM te_corporation tc
WHERE tc.cwt_business_id = rec_ts.cwt_business_id);
ELSIF v_vehicle_amount = 0 THEN -- 驾驶员无车对应
RAISE no_car_exception;
END IF; --像te_driver表中插入记录(v_corp_id为上面赋值)
IF rec_ts.driver_sex = '男' THEN
v_sex := 1;
ELSIF rec_ts.driver_sex = '女' THEN
v_sex := 2;
END IF;
v_birthday := substr(rec_ts.driver_birthday,1,8); v_driver_id := fun_gid();
INSERT INTO te_driver
(intro,driver_sex,driver_birthday,identity_id,driver_address,driver_work_id,telephone_number,
mobile_number,driver_state,NAME,photourl,re,corpid,id)
VALUES
(rec_ts.intro,v_sex,to_date(v_birthday,'yyyy-mm-dd'),rec_ts.identity_id,rec_ts.driver_address,
rec_ts.driver_work_id,rec_ts.telephone_number,rec_ts.mobile_number,rec_ts.driver_state,rec_ts.NAME,
rec_ts.photourl,rec_ts.re,v_corp_id,v_driver_id); -- 驾驶员-车辆 : tr_vhc_driver
INSERT INTO tr_vhc_driver
(vhc_id,drv_id,id)
VALUES
(v_vehicle_id,v_driver_id,fun_gid()); -- 新增一条图标记录 tr_driver_icon(图标id由传入参数提供)
INSERT INTO tr_driver_icon
(drv_id,icon_id,id)
VALUES
(v_driver_id,icon_id,fun_gid());
// 2000笔提交一次
if Mod(I, 2000) = 0 THEN
COMMIT;
end if;
EXCEPTION
WHEN no_car_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的车辆',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的车辆'); WHEN no_corp_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应的企业',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应的企业'); WHEN no_icon_exception THEN
ROLLBACK;
li_count := fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,'无对应图标',SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',无对应图标'); WHEN OTHERS THEN
ROLLBACK;
li_count:=fun_data_log('ut_driver','驾驶员id:'||rec_ts.id,SQLCODE,SQLERRM,1);
dbms_output.put_line('ut_driver,驾驶员id:'||rec_ts.id||',异常编号:'||SQLCODE||',异常描述:'||SQLERRM); END;
END LOOP;
CLOSE driver_cursor;
// 最后不够2000条了提交
commit;
p_out := 1; END prc_cutin_driver;