http://topic.csdn.net/u/20081020/14/a8006bac-d661-4fdf-a121-6b74d0c60504.html
共150分求一个Oracle语句
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE) VALUES('21','公告','维护3','维护3','1','takako', sysdate);
成功返回1,失败返回0.
我新手初学Oracle,请各位达达不要自以为是随手写,请调试一下,谢谢!
共150分求一个Oracle语句
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE) VALUES('21','公告','维护3','维护3','1','takako', sysdate);
成功返回1,失败返回0.
我新手初学Oracle,请各位达达不要自以为是随手写,请调试一下,谢谢!
-- 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
他们只是给你例子,并没有真正地去向表写一个记录(你的insert),你要根据他们的例子去改的.
create or replace procedure f is
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;然后call f()看有没有输出。有没有插入。
SQL> select * from adm_blt;NOTICEID CLASS SUBJECT CONTENT STATUS CREATEBY CREATEDATE
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -----------
8 公告 维护 维护 1 takako 2008-10-20SQL>
SQL> CREATE OR REPLACE procedure insert_ny(fd1 in varchar2,fd2 in varchar2,fd3 in varchar2,fd4 in varchar2,fd5 in varchar2,fd6 in varchar2,fd7 date) as
2 begin
3 insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE)
4 VALUES(fd1,fd2,fd3,fd4,fd5,fd6,fd7);
5 --yn:=1;
6 --return (yn);
7 -- WHEN OTHERS THEN
8 -- DBMS_OUTPUT.PUT_LINE('Error ---------------' || SQLCODE || ' : ' || SQLERRM);
9 -- ROLLBACK;
10 -- yn:=0;
11 -- return (yn);
12 end;
13 /Procedure createdSQL> exec insert_ny('3','公告','维护','维护','1','takako', sysdate);PL/SQL procedure successfully completedSQL> select * from adm_blt;NOTICEID CLASS SUBJECT CONTENT STATUS CREATEBY CREATEDATE
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -----------
8 公告 维护 维护 1 takako 2008-10-20
3 公告 维护 维护 1 takako 2008-10-20SQL>
2.如果是在.net或其它应用程序中执行,则有相应的sql_err等方面的返回值;
3.因Oracle中使用function中不能执行DML操作,而Trigger没有返回值,仅有Procedure虽然能执行insert,但没见过返回值,所能楼主的这道题真是太难了!