-- Created on 2010-8-31 by ADMINISTRATOR
DECLARE
-- Local variables here
TYPE name_s_t IS TABLE OF employee.dp_name%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_s_t IS TABLE OF employee.dp_sal%TYPE INDEX BY BINARY_INTEGER;
name_t name_s_t;
sal_t sal_s_t;
gl VARCHAR2(100);
BEGIN
-- Test statements here
gl := 'update employee set dp_sal = dp_sal*(1+:k/100)' ||
'where dp_no = :kno' ||
'returning dp_name,dp_sal into :name_t,:sal_t';
EXECUTE IMMEDIATE gl
USING &k, &kno
RETURNING BULK COLLECT
INTO name_t, sal_t;
FOR i IN 1 .. name_t.COUNT LOOP
dbms_output.put_line('员工:' || name_t(i));
dbms_output.put_line('新工资:' || sal_t(i));
END LOOP;
END;错误信息:
ORA-06502:PL/SQL:数字或值错误
ORA-06512:在line 11表信息:
create table EMPLOYEE
(
DP_NO NUMBER,
DP_NAME VARCHAR2(50),
DP_SAL NUMBER,
DP_PHO NUMBER,
DP_ADDR VARCHAR2(100)
)INSERT INTO employee
(v_dp_no, v_dp_name, v_dp_sal, v_dp_pho, v_dp_addr)
VALUES
(10001, 张三, 3500, 13498910291, 深圳南山区);请高手解答,谢谢
DECLARE
-- Local variables here
TYPE name_s_t IS TABLE OF employee.dp_name%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_s_t IS TABLE OF employee.dp_sal%TYPE INDEX BY BINARY_INTEGER;
name_t name_s_t;
sal_t sal_s_t;
gl VARCHAR2(100);
BEGIN
-- Test statements here
gl := 'update employee set dp_sal = dp_sal*(1+:k/100)' ||
'where dp_no = :kno' ||
'returning dp_name,dp_sal into :name_t,:sal_t';
EXECUTE IMMEDIATE gl
USING &k, &kno
RETURNING BULK COLLECT
INTO name_t, sal_t;
FOR i IN 1 .. name_t.COUNT LOOP
dbms_output.put_line('员工:' || name_t(i));
dbms_output.put_line('新工资:' || sal_t(i));
END LOOP;
END;错误信息:
ORA-06502:PL/SQL:数字或值错误
ORA-06512:在line 11表信息:
create table EMPLOYEE
(
DP_NO NUMBER,
DP_NAME VARCHAR2(50),
DP_SAL NUMBER,
DP_PHO NUMBER,
DP_ADDR VARCHAR2(100)
)INSERT INTO employee
(v_dp_no, v_dp_name, v_dp_sal, v_dp_pho, v_dp_addr)
VALUES
(10001, 张三, 3500, 13498910291, 深圳南山区);请高手解答,谢谢
解决方案 »
- Oracle10g什么地方有的下载啊?
- 请问,要对索引进行分析,如下2种方式,有没有不同呢?就是他们得到的分析的结果,相同吗?
- Oracle 如何这样分区?
- ORACLE trigger 不能停用
- ado 读取oracle number型字段的问题
- WINXP下10g客户端连接linux 10g服务端问题及如何plsqldev如何连接linux 10g服务端问题!
- 关于访问自己的,net开发环境时出现ORA-03113: 通信通道的文件结束的问题
- 急急急!在ORACLE中怎样判断新的一天的到来?
- 关于oracle数据库表的纪录删除
- 对海量数据如何优化查询速度?
- 新手求教: 触发器
- oracle 透明网关 软件的下载地址
把gl增大,改为1000,就可以了
DECLARE
TYPE name_s_t IS TABLE OF employee.dp_name%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_s_t IS TABLE OF employee.dp_sal%TYPE INDEX BY BINARY_INTEGER;
name_t name_s_t;
sal_t sal_s_t;
gl VARCHAR2(1000);
BEGIN
gl := 'update employee set dp_sal = dp_sal*(1+:k/100)' ||
' where dp_no = :kno' ||
' returning dp_name,dp_sal into :name_t,:sal_t';
dbms_output.put_line(gl);
EXECUTE IMMEDIATE gl
USING &k, &kno
RETURNING BULK COLLECT INTO name_t,sal_t;
FOR i IN 1 .. name_t.COUNT LOOP
dbms_output.put_line('员工:' || name_t(i));
dbms_output.put_line('新工资:' || sal_t(i));
END LOOP;
END;
'where dp_no = :kno' ||不觉得dp_sal*(1+:k/100)和where拼到一起了?
每行结束前加空格
DP_NO DP_NAME DP_SAL DP_PHO DP_ADDR
---------- -------------------------------------------------- ---------- ---------- --------------------------------------------------------------------------------
10001 张三 3605 1349891029 深圳南山区
SQL>
SQL> DECLARE
2 TYPE name_s_t IS TABLE OF employee.dp_name%TYPE INDEX BY BINARY_INTEGER;
3 TYPE sal_s_t IS TABLE OF employee.dp_sal%TYPE INDEX BY BINARY_INTEGER;
4 name_t name_s_t;
5 sal_t sal_s_t;
6 gl VARCHAR2(1000);
7 BEGIN
8 gl := 'update employee set dp_sal = dp_sal*(1+:k/100)' ||
9 ' where dp_no = :kno' ||
10 ' returning dp_name,dp_sal into :name_t,:sal_t';
11 dbms_output.put_line(gl);
12 EXECUTE IMMEDIATE gl
13 USING &k, &kno
14 RETURNING BULK COLLECT INTO name_t,sal_t;
15 FOR i IN 1 .. name_t.COUNT LOOP
16 dbms_output.put_line('员工:' || name_t(i));
17 dbms_output.put_line('新工资:' || sal_t(i));
18 END LOOP;
19 END;
20 /
update employee set dp_sal = dp_sal*(1+:k/100) where dp_no = :kno returning dp_name,dp_sal into :name_t,:sal_t
员工:张三
新工资:3713.15
PL/SQL procedure successfully completed
SQL> 好像没问题了.Phoenix的对的.
变量一个输入的3,一个是10001