我的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 ......那段。
解决方案 »
- 关于Oracle连接报错的问题!!!
- 设置日志归档模式问题
- 字段分割,汉字问题
- 求一条SQL语句, 高手帮忙哈
- sql中同事满足两个条件的,求助
- 请教oracle9i中文显示问题.
- 使用SQL plus至少要打开哪些服务?
- 请教VB中利用ADO访问远程服务器上的Oracle数据库,Connectiongstring连接字符串怎么写?全分!
- 请问在Oracal的PL/SQL中如何获得精确到百分之一秒的时间信息(能精确到千分之一秒更好)?
- 求各位大大教我怎么用使用 Tkprof 分析 ORACLE 跟踪文件
- ORACLE DATAGUARD配置的问题!!
- oracle 专用的pl/sql 技巧,语句收集,大家帮帮忙
所以你需要
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 是这样引用吗?谢谢!
简单地说就是这样,你给的错误信息太少了,,,,