我有一条Oracle Insert语句,我想可不可以Insert成功的时候返回1,否则返回0。
我不会写,望达人帮忙begin
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE)
VALUES('8','公告','维护','维护','1','takako', sysdate)
return 1
end我是这么写的,不行
我不会写,望达人帮忙begin
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE)
VALUES('8','公告','维护','维护','1','takako', sysdate)
return 1
end我是这么写的,不行
解决方案 »
- oracle中索引该如何使用
- 这个语句哪里有错误呢?
- 全关系系统的十二条基本准则讨论
- 求一个触发器的编写
- 有一个字符串“aa,bb,cc,dd” 怎么能得到!!!
- weblogic上的jsp连接oracle中文乱码!!!
- 使字段值自动增加的触发器问题?(内容如下)
- 分区问题
- 一个字段存储的内容很大的时候,显示的时候能不能做分页处理,有没有具体的办法呢?
- 请问如何在c#中存储Oracle9i的CLOB的对象
- 为什么PLSQL中修改表结构报ORA-25150错(不允许对区参数执行),而用sql语句却可以?
- Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1)安装文档 3
SQL> desc t;
Name Type Nullable Default Comments
------ ----------- -------- ------- --------
IID INTEGER Y
SOMETH INTEGER Y
BFLAG VARCHAR2(2) Y SQL>
SQL> declare
2 v_rn integer;
3 begin
4 begin
5 v_rn := 1;
6 insert into t VALUES(1,1,'111111111111111111111');
7 exception when others then
8 v_rn := 0;
9 end;
10 dbms_output.put_line(v_rn);
11 end;
12 /0PL/SQL procedure successfully completedSQL>
SQL> declare
2 v_rn integer;
3 begin
4 begin
5 v_rn := 1;
6 insert into t VALUES(1,1,'11');
7 exception when others then
8 v_rn := 0;
9 end;
10 dbms_output.put_line(v_rn);
11 end;
12 /1PL/SQL procedure successfully completed
rt number := 1;
begin
insert into t VALUES(1,1,'111111111111111111111');
exception
when others then
rt := 0;
return rt;
end f;
/--使用函数,使用前先编译一下CREATE OR REPLACE function insert_yn return number is
yn number:=0;
begin
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE) VALUES('8','公告','维护','维护','1','takako', sysdate);
yn:=1;
return (yn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ---------------' || SQLCODE || ' : ' || SQLERRM);
ROLLBACK;
yn:=0;
return (yn);
end;
2 RETURN NUMBER
3 IS
4 val DATE;
5 BEGIN
6 val := TO_DATE(SJ, 'yyyy-mm-dd');
7 RETURN 1;
8 EXCEPTION
9 WHEN OTHERS THEN
10 RETURN 0;
11 END;
12 /Function createdSQL> select fu_test('20081010') RV from dual; RV
----------
1SQL> select fu_test('20081033') RV from dual; --20081033是不符合日期格式的字符串 RV
----------
0SQL>
-- PROCEDURE CODE:
CREATE OR REPLACE PROCEDURE FUN_TEST_TT(RE_RESULT OUT NUMBER) IS
BEGIN
INSERT INTO TEST_TT VALUES(TO_DATE('2008-10-20','YYYY-MM-DD'),33,44,55);
COMMIT;
RE_RESULT := 1;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RE_RESULT := 0;
DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);END ;-- TEST RESULT:
SQL> SELECT * FROM TEST_TT;ADDTIME A B C
----------- ---------- ---------- ----------
8/1/2008 20 30 40
8/2/2008 10 30 40
8/3/2008 13 39 56
8/4/2008 25 56 43
8/5/2008 36 30 25
8/6/2008 20 36 67
8/7/2008 20 90 40
7/8/2009 10 50 50
8/10/2009 30 20 209 rows selectedExecuted in 1.062 secondsSQL> DECLARE
2 V_OUTPUT NUMBER;
3 BEGIN
4 FUN_TEST_TT(V_OUTPUT);
5 DBMS_OUTPUT.PUT_LINE('OUT PUT VALUE IS: '||V_OUTPUT);
6 END;
7 /OUT PUT VALUE IS: 1PL/SQL procedure successfully completedExecuted in 0.625 secondsSQL> SELECT * FROM TEST_TT;ADDTIME A B C
----------- ---------- ---------- ----------
8/1/2008 20 30 40
8/2/2008 10 30 40
8/3/2008 13 39 56
8/4/2008 25 56 43
8/5/2008 36 30 25
8/6/2008 20 36 67
8/7/2008 20 90 40
7/8/2009 10 50 50
8/10/2009 30 20 20
10/20/2008 33 44 5510 rows selectedExecuted in 0.969 seconds
rt number := 1;
begin
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE) VALUES('21','公告','维护3','维护3','1','takako', sysdate);
dbms_output.put_line(rt);
exception
when others then
rt := 0;
dbms_output.put_line(rt);
end f;如果没有插入成功该输出0.在命令行下
set serveroutput on;
exec f();
成功输出1,错误是输出0;