我有一条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我是这么写的,不行
解决方案 »
- 求批量插入sql优化方法
- sqlplus能登陆,但是使用pl/sql developer却不能
- 请问这样的语句查询出来为什么很慢,有没有优化的方案
- 就一sql,急。。。
- 查询优化方面的书籍
- 拼接Sql语句问题
- 如何调用存储过程(返回记录集)
- 动态sql语句建表问题!
- 一个sql遇到的挠头问题!!!!!!!!急!!!!
- 如何判断一个字段内容是否存在汉字?
- 为什么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;