DECLARE
ls_sql VARCHAR2(200);
ls_col_cod VARCHAR2(20);
li_wage_sum_all_sum NUMBER;
ls_yearmon VARCHAR2(10);
ls_cod_nam VARCHAR2(10);
ls_heji VARCHAR2(10);
li_col_3_sum_all NUMBER;BEGIN
ls_col_cod := SUBSTR('col_6',2,6);
li_wage_sum_all_sum :=4;
ls_yearmon :='200311';
ls_cod_nam :='机关';
ls_heji :='合计';
li_col_3_sum_all :=27;LS_SQL := 'UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_ALL_SUM||' WHERE YEAR_MON = '||LS_YEARMON||' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_heji||' AND COL_3 = '||LI_COL_3_SUM_ALL ;
EXECUTE IMMEDIATE LS_SQL;END;
错误提示是 无效列名 应该是我的ls_sql有问题 大家帮忙看看
ls_sql VARCHAR2(200);
ls_col_cod VARCHAR2(20);
li_wage_sum_all_sum NUMBER;
ls_yearmon VARCHAR2(10);
ls_cod_nam VARCHAR2(10);
ls_heji VARCHAR2(10);
li_col_3_sum_all NUMBER;BEGIN
ls_col_cod := SUBSTR('col_6',2,6);
li_wage_sum_all_sum :=4;
ls_yearmon :='200311';
ls_cod_nam :='机关';
ls_heji :='合计';
li_col_3_sum_all :=27;LS_SQL := 'UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_ALL_SUM||' WHERE YEAR_MON = '||LS_YEARMON||' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_heji||' AND COL_3 = '||LI_COL_3_SUM_ALL ;
EXECUTE IMMEDIATE LS_SQL;END;
错误提示是 无效列名 应该是我的ls_sql有问题 大家帮忙看看
解决方案 »
- 求解
- linux64位 下 Oracle C++开发 编译出现 /usr/bin/ld: warning: libstdc++.so.5,
- 当给某表INERT预定记录时通过触发器同时再向该表追加几条记录?
- Oracle高手帮忙,谢谢!
- PLS-00428: 在此 SELECT 语句中缺少 INTO 子句
- 求一简单的SQL语句,效率执行找十万条数据
- 菜鸟提问 ADO 连接 ORACLE
- oracle 8i DBA studio 编辑表字段类型的小问题?
- (100分)如何用Java读出Oracle LOng字段类型中的BMP图片
- 在oracle中在两个整型值之间执行按位逻辑与运算的运算符是什么?
- 问题追加30分
- 如何判断clob字段为空?
--求出主要工资台帐中的统计项目(一个单位统计项目包括若干个单位,包括三个工种 :1。全部2。长期工3。短期工)
AS
LS_YEARMON VARCHAR2(10); --当前年月
LS_SQL VARCHAR2(200); --动态SQL语句
ls_sql2 VARCHAR2(200);
ls_sql3 VARCHAR2(200);
LS_CODE M_PORT_OPER.CODE%TYPE; --代码(单位项目)
LS_COD_NAM M_PORT_OPER.COD_NAM%TYPE; --代码名称
LS_COD_COL M_PORT_OPER.COD_COL%TYPE; --代码列
LS_COD_TAB M_PORT_OPER.COD_TAB%TYPE; --对应表
LS_COD_TAB_COL M_PORT_OPER.COD_TAB_COL%TYPE; --对应表字段
LS_COD_TAB_COL_DAT M_PORT_OPER.COD_TAB_COL_DAT%TYPE; --数据
LI_COL_3_ALL M_WORKER_TYP_WAGE_PORT.COL_3%TYPE; --期末人数(合计)单
LI_COL_3_GD M_WORKER_TYP_WAGE_PORT.COL_3%TYPE; --期末人数(固定工)
LI_COL_3_FY M_WORKER_TYP_WAGE_PORT.COL_3%TYPE; --期末人数(费用工)
LI_COL_3_SUM_ALL M_WORKER_TYP_WAGE_PORT.COL_3%TYPE; --期末人数(合计)总
LI_COL_3_SUM_GD M_WORKER_TYP_WAGE_PORT.COL_3%TYPE;
LI_COL_3_SUM_FY M_WORKER_TYP_WAGE_PORT.COL_3%TYPE;
LS_COL M_PORT_OPER.CODE%TYPE ; --代码(统计项目)
LS_COL_ITM M_PORT_OPER.COD_TAB_COL_DAT%TYPE; --代码对应的实际项目(如:f1)
LI_WAGE_SUM_ALL M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(合计) 单
LI_WAGE_SUM_GD M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(固定工)
LI_WAGE_SUM_FY M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(费用工)
LI_WAGE_SUM_ALL_SUM M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(合计)总
LI_WAGE_SUM_GD_SUM M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(固定工)
LI_WAGE_SUM_FY_SUM M_WORKER_TYP_WAGE_PORT.COL_6%TYPE; --工资项目(费用工)
ls_col_cod VARCHAR2(10);
ls_heji VARCHAR2(10); --'合计'
ls_gudinggong VARCHAR2(10); --'固定工'
ls_feiyonggong VARCHAR2(10); --'费用工'
CURSOR CUR_DEPT IS SELECT DISTINCT CODE , COD_NAM FROM M_PORT_OPER WHERE SUBSTR(CODE, 1, 1) = '0';--取出单位所对应的代码
CURSOR CUR_DEPT_COD IS SELECT COD_TAB_COL_DAT FROM M_PORT_OPER WHERE CODE = LS_CODE;--取出单位包括部门
CURSOR CUR_COL IS SELECT DISTINCT CODE FROM M_PORT_OPER WHERE SUBSTR(CODE, 1, 1) = '1';--取出统计的项目所对应的代码
CURSOR CUR_COL_ITM IS SELECT COD_TAB_COL_DAT FROM M_PORT_OPER WHERE CODE = LS_COL;--取出列所对应的项目
CURSOR CUR_COD_COL IS SELECT DISTINCT COD_COL FROM M_PORT_OPER WHERE CODE = LS_COL;--取出应该插在哪一行
BEGIN
OPEN CUR_DEPT;--取出代表单位的代码列
LOOP
FETCH CUR_DEPT INTO LS_CODE, LS_COD_NAM;
EXIT WHEN CUR_DEPT%NOTFOUND;
OPEN CUR_DEPT_COD;
LI_COL_3_SUM_ALL := 0;
LI_COL_3_SUM_GD := 0;
LI_COL_3_SUM_FY := 0;
LOOP
FETCH CUR_DEPT_COD INTO LS_COD_TAB_COL_DAT;--按单位计算
EXIT WHEN CUR_DEPT_COD%NOTFOUND;
SELECT COUNT(WORKER_NO) INTO LI_COL_3_ALL FROM WORKER_INF WHERE DEPT_WORK LIKE LS_COD_TAB_COL_DAT AND NVL(LEAVE_ID, '0') <> '1';
LI_COL_3_SUM_ALL := LI_COL_3_ALL + LI_COL_3_SUM_ALL;--计算全部人数
SELECT COUNT(WORKER_NO) INTO LI_COL_3_GD FROM WORKER_INF WHERE DEPT_WORK LIKE LS_COD_TAB_COL_DAT AND (NVL(WORKER_CLS,'0') = '0') AND NVL(LEAVE_ID, '0') <> '1';
LI_COL_3_SUM_GD := LI_COL_3_GD + LI_COL_3_SUM_GD; --计算固定工人数
SELECT COUNT(WORKER_NO) INTO LI_COL_3_FY FROM WORKER_INF WHERE DEPT_WORK LIKE LS_COD_TAB_COL_DAT AND (WORKER_CLS = '1' )AND NVL(LEAVE_ID, '0') <> '1';
LI_COL_3_SUM_FY := LI_COL_3_FY + LI_COL_3_SUM_FY; --计算费用工人数
END LOOP;
CLOSE CUR_DEPT_COD; --设置列
OPEN CUR_DEPT_COD;
LOOP
FETCH CUR_DEPT_COD INTO LS_COD_TAB_COL_DAT;--按单位计算
EXIT WHEN CUR_DEPT_COD%NOTFOUND;
-- 下面这段代码计算各单位的统计项目 (全部费用)
OPEN CUR_COL;--取出代表列项目的代码
LOOP
FETCH CUR_COL INTO LS_COL;
EXIT WHEN CUR_COL%NOTFOUND;
OPEN CUR_COL_ITM;--具体工资项目
LI_WAGE_SUM_ALL_SUM := 0;
LI_WAGE_SUM_GD_SUM := 0;
LI_WAGE_SUM_FY_SUM := 0;
OPEN CUR_COD_COL;--列信息
FETCH CUR_COD_COL INTO LS_COD_COL;
EXIT WHEN CUR_COD_COL%NOTFOUND;
CLOSE CUR_COD_COL;
LOOP
FETCH CUR_COL_ITM INTO LS_COL_ITM;
EXIT WHEN CUR_COL_ITM%NOTFOUND;
SELECT NVL(SUM(NVL(WORKER_WAGE.WAGE_NUM, 0 )),0) INTO LI_WAGE_SUM_ALL FROM WORKER_WAGE, WORKER_INF WHERE WORKER_WAGE.WORKER_NO = WORKER_INF.WORKER_NO AND WORKER_WAGE.YEAR_MON = LS_YEARMON AND WORKER_WAGE.DEPT_COD LIKE LS_COD_TAB_COL_DAT AND NVL(WORKER_INF.LEAVE_ID, '0') <> '1' AND WORKER_WAGE.WAGE_ITM_COD = LS_COL_ITM;
LI_WAGE_SUM_ALL_SUM := LI_WAGE_SUM_ALL_SUM + LI_WAGE_SUM_ALL;
SELECT NVL(SUM(NVL(WORKER_WAGE.WAGE_NUM, 0 )),0) INTO LI_WAGE_SUM_GD FROM WORKER_WAGE, WORKER_INF WHERE WORKER_WAGE.WORKER_NO = WORKER_INF.WORKER_NO AND WORKER_WAGE.YEAR_MON = LS_YEARMON AND WORKER_WAGE.DEPT_COD LIKE LS_COD_TAB_COL_DAT AND NVL(WORKER_INF.LEAVE_ID, '0') <> '1' AND WORKER_WAGE.WAGE_ITM_COD = LS_COL_ITM AND (NVL(WORKER_INF.WORKER_CLS,'0') = '0');
LI_WAGE_SUM_GD_SUM := LI_WAGE_SUM_GD_SUM + LI_WAGE_SUM_GD;
SELECT NVL(SUM(NVL(WORKER_WAGE.WAGE_NUM, 0)),0) INTO LI_WAGE_SUM_FY FROM WORKER_WAGE, WORKER_INF WHERE WORKER_WAGE.WORKER_NO = WORKER_INF.WORKER_NO AND WORKER_WAGE.YEAR_MON = LS_YEARMON AND WORKER_WAGE.DEPT_COD LIKE LS_COD_TAB_COL_DAT AND NVL(WORKER_INF.LEAVE_ID, '0') <> '1' AND WORKER_WAGE.WAGE_ITM_COD = LS_COL_ITM AND (NVL(WORKER_INF.WORKER_CLS,'0') = '1');
LI_WAGE_SUM_FY_SUM := LI_WAGE_SUM_FY_SUM + LI_WAGE_SUM_FY;
END LOOP;
CLOSE CUR_COL_ITM;
ls_heji := '合计';
ls_col_cod := SUBSTR(ls_cod_col,1,5);
ls_sql := 'begin UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_ALL_SUM||' WHERE YEAR_MON = '||LS_YEARMON||' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_heji||' AND COL_3 = '||LI_COL_3_SUM_ALL||' end; ' ; EXECUTE IMMEDIATE LS_SQL;
LS_SQL2 := 'UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_GD_SUM||' WHERE YEAR_MON = '||LS_YEARMON||' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_gudinggong||' AND COL_3 = '||LI_COL_3_SUM_GD ;
EXECUTE IMMEDIATE LS_SQL2;
LS_SQL3 := 'UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_FY_SUM||' WHERE YEAR_MON = '||LS_YEARMON||' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_feiyonggong||' AND COL_3 = '||LI_COL_3_SUM_FY ;
EXECUTE IMMEDIATE LS_SQL3;
--
END LOOP;
CLOSE CUR_COL;
-- 结束
END LOOP;
CLOSE CUR_DEPT_COD;
END LOOP;
CLOSE CUR_DEPT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,ls_sql);
ROLLBACK;
END;
/
LS_SQL := 'UPDATE M_WORKER_TYP_WAGE_PORT SET '||ls_col_cod||' = '||LI_WAGE_SUM_ALL_SUM||' WHERE YEAR_MON = '''||LS_YEARMON||''' AND COL_1 = '||LS_COD_NAM||' AND COL_2 = '||ls_heji||' AND COL_3 = '||LI_COL_3_SUM_ALL ;
http://expert.csdn.net/Expert/topic/2492/2492452.xml?temp=.5642053
beckham也很敬业 所以~~~~~~~~~~~~~