我的SP如下:
/* Formatted on 2008/04/20 20:06 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE mappinguser.declaredata (
paramid VARCHAR,
returnvalue OUT VARCHAR
)
IS
tmpvar varchar(20);
BEGIN
tmpvar := NULL; SELECT CASE
WHEN mapopstate = '0'
THEN '已终止'
WHEN mapopstate = '1'
THEN '已添加'
WHEN mapopstate = '2'
THEN '已申报'
WHEN mapopstate = '3'
THEN '已审批'
WHEN mapopstate = '4'
THEN '已打回'
WHEN mapopstate = '5'
THEN '已申请修改'
WHEN mapopstate = '6'
THEN '已同意修改'
WHEN mapopstate = '7'
THEN '已拒绝修改'
WHEN mapopstate = '8'
THEN '已申请变更'
WHEN mapopstate = '9'
THEN '已同意变更'
WHEN mapopstate = '10'
THEN '已拒绝变更'
WHEN mapopstate = '11'
THEN '已使用'
WHEN mapopstate = '12'
THEN '已完成'
END as aa
INTO tmpvar
FROM mapopinfo
WHERE iteminfoid = paramid; IF tmpvar <> '已添加'
THEN
returnvalue := tmpvar;
ELSE
UPDATE mapopinfo
SET mapopstate = '2'
WHERE iteminfoid = paramid;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END declaredata;
/提示SQL语句不正确。就是 select case ......那段。
/* Formatted on 2008/04/20 20:06 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE mappinguser.declaredata (
paramid VARCHAR,
returnvalue OUT VARCHAR
)
IS
tmpvar varchar(20);
BEGIN
tmpvar := NULL; SELECT CASE
WHEN mapopstate = '0'
THEN '已终止'
WHEN mapopstate = '1'
THEN '已添加'
WHEN mapopstate = '2'
THEN '已申报'
WHEN mapopstate = '3'
THEN '已审批'
WHEN mapopstate = '4'
THEN '已打回'
WHEN mapopstate = '5'
THEN '已申请修改'
WHEN mapopstate = '6'
THEN '已同意修改'
WHEN mapopstate = '7'
THEN '已拒绝修改'
WHEN mapopstate = '8'
THEN '已申请变更'
WHEN mapopstate = '9'
THEN '已同意变更'
WHEN mapopstate = '10'
THEN '已拒绝变更'
WHEN mapopstate = '11'
THEN '已使用'
WHEN mapopstate = '12'
THEN '已完成'
END as aa
INTO tmpvar
FROM mapopinfo
WHERE iteminfoid = paramid; IF tmpvar <> '已添加'
THEN
returnvalue := tmpvar;
ELSE
UPDATE mapopinfo
SET mapopstate = '2'
WHERE iteminfoid = paramid;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END declaredata;
/提示SQL语句不正确。就是 select case ......那段。
所以你需要
FROM mapopinfo
WHERE iteminfoid = paramid and rownum=1
SELECT ename,
(CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'Unassigned'
END ) as Department
FROM emp;
--CASE语句
CASE employee_type
WHEN 'S' THEN
award_salary_bonus(employee_id);
WHEN 'H' THEN
award_hourly_bonus(employee_id);
WHEN 'C' THEN
award_commissioned_bonus(employee_id);
ELSE
RAISE invalid_employee_type;
END CASE;
CASE
WHEN salary >= 10000 AND salary <=20000 THEN
give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN
give_bonus(employee_id, 1000);
WHEN salary > 40000 THEN
give_bonus(employee_id, 500);
ELSE
give_bonus(employee_id, 0);
END CASE;CASE
WHEN salary > 40000 THEN
give_bonus(employee_id, 500);
WHEN salary > 20000 THEN
give_bonus(employee_id, 1000);
WHEN salary >= 10000 THEN
give_bonus(employee_id, 1500);
ELSE
give_bonus(employee_id, 0);
END CASE;
如:
table_name1 varchar2(20);
select to_char(sysdate,'yyyymmdd') into table_name1 from dual;
create table data_new_$table_name1 是这样引用吗?谢谢!
简单地说就是这样,你给的错误信息太少了,,,,