17,377
社区成员
发帖
与我相关
我的任务
分享
begin
insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE)
VALUES('8','公告','维护','维护','1','takako', sysdate)
return 1
end
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;
-- TRY IT ..
-- 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 20
9 rows selected
Executed in 1.062 seconds
SQL> 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: 1
PL/SQL procedure successfully completed
Executed in 0.625 seconds
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 20
10/20/2008 33 44 55
10 rows selected
Executed in 0.969 seconds
[Quote=引用楼主 takako_mu 的帖子:]--检验字符是否符合日期格式,如果符合返回1,否则返回0.本函数可常用于对数据仓库的ETL数据清洗。
SQL> CREATE OR REPLACE FUNCTION FU_TEST(SJ VARCHAR2)
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 created
SQL> select fu_test('20081010') RV from dual;
RV
----------
1
SQL> select fu_test('20081033') RV from dual; --20081033是不符合日期格式的字符串
RV
----------
0
SQL>
/--使用函数,使用前先编译一下
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;
create or replace function f return number is
rt number := 1;
begin
insert into t VALUES(1,1,'111111111111111111111');
exception
when others then
rt := 0;
return rt;
end f;
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 /
0
PL/SQL procedure successfully completed
SQL>
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 /
1
PL/SQL procedure successfully completed