CREATE OR REPLACE Procedure p_insert_tb_vulgatecar
(
plateid in Nvarchar2,
platetype in Nvarchar2,
crossingname in Nvarchar2,
crossingtime in Nvarchar2,
wayname in Nvarchar2,
aspectname in Nvarchar2,
processstate in Nvarchar2,
platecolor in Nvarchar2,
speed in Nvarchar2,
illegaltype in Nvarchar2,
checktime in Nvarchar2,
punishpoint in Nvarchar2,
punishcash in Nvarchar2,
blackstate in Number,
filepath in Nvarchar2
)
as
BEGIN
insert into sysadmin.tb_vulgatecar values (VuIdentity.nextval,plateid,platetype,crossingname,to_date(crossingtime,'YYYY-MM-DD HH24:MI:SS'),wayname,aspectname,processstate,platecolor,speed,illegaltype,to_date(checktime,'YYYY-MM-DD HH24:MI:SS'),punishpoint,punishcash,blackstate,filepath);
commit;
End;
我有这样一个执行插入语句的存储过程,现在我想要执行成功返回1,执行失败返回0,请问应该要怎么修改这个存储过程呢?
(
plateid in Nvarchar2,
platetype in Nvarchar2,
crossingname in Nvarchar2,
crossingtime in Nvarchar2,
wayname in Nvarchar2,
aspectname in Nvarchar2,
processstate in Nvarchar2,
platecolor in Nvarchar2,
speed in Nvarchar2,
illegaltype in Nvarchar2,
checktime in Nvarchar2,
punishpoint in Nvarchar2,
punishcash in Nvarchar2,
blackstate in Number,
filepath in Nvarchar2
)
as
BEGIN
insert into sysadmin.tb_vulgatecar values (VuIdentity.nextval,plateid,platetype,crossingname,to_date(crossingtime,'YYYY-MM-DD HH24:MI:SS'),wayname,aspectname,processstate,platecolor,speed,illegaltype,to_date(checktime,'YYYY-MM-DD HH24:MI:SS'),punishpoint,punishcash,blackstate,filepath);
commit;
End;
我有这样一个执行插入语句的存储过程,现在我想要执行成功返回1,执行失败返回0,请问应该要怎么修改这个存储过程呢?
解决方案 »
- 求助:关于单位和部门的排序问题
- [求助]关于PL/SQL 8.0非常奇怪的问题?
- 急!!!请问个位高手谁知道这个错误
- 网络通信的储存过程
- ORA-01033: ORACLE initialization or shutdown in progress
- 请问通过console创建的表空间、用户、数据库怎么才知道它们是否关联呢?
- 可能有段时间不能来了. 散分.
- Oracle 8i 在 XP 下的安装问题
- ???????ora-00101:invalid specification for system parameter MTS-DISPATCHERS
- 请教关于数据库恢复的问题
- 复杂查询
- oracle用127.0.0.1接连不上
(
plateid in Nvarchar2,
platetype in Nvarchar2,
crossingname in Nvarchar2,
crossingtime in Nvarchar2,
wayname in Nvarchar2,
aspectname in Nvarchar2,
processstate in Nvarchar2,
platecolor in Nvarchar2,
speed in Nvarchar2,
illegaltype in Nvarchar2,
checktime in Nvarchar2,
punishpoint in Nvarchar2,
punishcash in Nvarchar2,
blackstate in Number,
filepath in Nvarchar2,
result_flag out number
)
AS
BEGIN
insert into sysadmin.tb_vulgatecar values (VuIdentity.nextval,plateid,platetype,crossingname,to_date(crossingtime,'YYYY-MM-DD HH24:MI:SS'),wayname,aspectname,processstate,platecolor,speed,illegaltype,to_date(checktime,'YYYY-MM-DD HH24:MI:SS'),punishpoint,punishcash,blackstate,filepath);
commit;
result_flag:=1;
exception
when others then
result_flag:=0;
End;
--方法2,可以使用函数让函数返回return
对于向这样有返回值的,建议使用函数。
(
plateid in Nvarchar2,
platetype in Nvarchar2,
crossingname in Nvarchar2,
crossingtime in Nvarchar2,
wayname in Nvarchar2,
aspectname in Nvarchar2,
processstate in Nvarchar2,
platecolor in Nvarchar2,
speed in Nvarchar2,
illegaltype in Nvarchar2,
checktime in Nvarchar2,
punishpoint in Nvarchar2,
punishcash in Nvarchar2,
blackstate in Number,
filepath in Nvarchar2,
result_flag out number
)return number
AS
BEGIN
insert into sysadmin.tb_vulgatecar values (VuIdentity.nextval,plateid,platetype,crossingname,to_date(crossingtime,'YYYY-MM-DD HH24:MI:SS'),wayname,aspectname,processstate,platecolor,speed,illegaltype,to_date(checktime,'YYYY-MM-DD HH24:MI:SS'),punishpoint,punishcash,blackstate,filepath);
commit;
return(1);
exception
when others then
return(0);
End;
(
plateid in Nvarchar2,
......
filepath in Nvarchar2,
rtn out number
)
as
BEGIN
insert into ......
exception
rtn:=0;
commit;
rtn:=1;
End;
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'P_INSERT_TB_VULGATECAR' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
报这个错误
判断是否为日期的函数CREATE OR REPLACE FUNCTION is_date(parmin VARCHAR2) RETURN NUMBER IS
val DATE;
BEGIN
val := TO_DATE(NVL(parmin, 'a'), 'yyyy-mm-dd hh24:mi:ss');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;to_date(checktime,'YYYY-MM-DD HH24:MI:SS')改成
case is_date(checktime) when 1 then to_date(checktime,'YYYY-MM-DD HH24:MI:SS') else null end ,