oracle function¿ª·¢
ORACLE º¯Êý¿ª·¢
1¡¢½¨Á¢º¯ÊýµÄÓï·¨£º
CREATE [OR REPLACE] FUNCTION º¯ÊýÃû
[ (arg1 [mode] datatype[,........])] --µ±Ö¸¶¨²ÎÊýÊý¾ÝÀàÐÍʱ£¬²»ÄÜÖ¸¶¨Æ䳤¶È¡£modeÖµ£ºIN¡¢OUT¡¢IN OUT£¬µ±mode δָ¶¨Ê±£¬±íʾΪIN
RETURN datatype --ÓÃÓÚÖ¸¶¨º¯Êý·µ»ØÖµµÄÊý¾ÝÀàÐÍ£¬²»ÄÜÖ¸¶¨Æ䳤¶È
IS|AS
pl/sql block; --µ±½¨Á¢º¯Êýʱ£¬ÔÚº¯ÊýÍ·²¿±ØÐëÒª´øÓÐRETURN×Ӿ䣬ÔÚº¯ÊýÌåÄÚÖÁÉÙÒª°üº¬Ò»ÌõRETURNÓï¾ä¡£
Àý1¡¢Î޲κ¯Êý
SQL> CREATE OR REPLACE function HX_01 --´´½¨HX_01º¯Êý
2 RETURN VARCHAR2
3 IS
4 v_name varchar2(20);
5 begin
6 select ename into v_name from emp where empno=7369;
7 return v_name;
8 end;
9 /
Function created
SQL> select hx_01 from dual; --Ö´Ðк¯Êý
HX_01
--------------------------------------------------------------------------------
SMITH
Àý2¡¢´øÓÐÒ»¸öIN²ÎÊý
SQL> CREATE OR REPLACE function HX_02 --´´½¨HX_02º¯Êý
2 (v_no in emp.empno%type)
3 RETURN VARCHAR2
4 IS
5 v_name varchar2(20);
6 v_sal emp.sal%type;
7 v_dname dept.dname%type;
8 v_return varchar2(100);
9 begin
10 select a.ename,a.sal,b.dname into v_name,v_sal,v_dname from emp a,dept b where a.deptno=b.deptno and empno=v_no;
11 v_return:='ÐÕÃû£º'||rpad(v_name,8)||LPAD('¹¤×Ê£º',6)||v_sal||lpad('²¿ÃÅ',6)||v_dname;
12 return v_return;
13 end;
14 /
Function created
SQL> begin
2 dbms_output.put_line(hx_02(7566)); --Ö´Ðк¯Êý
3 end;
4
5 /
PL
Ïà¹ØÎĵµ£º
ÔÚoracleÖе±ÎÒÃÇн¨Á˶à¸öÊý¾Ý¿âºó£¬ÔٴεǼʱ£¬ÏµÍ³Ä¬ÈϵÄÊÇ×îºó´´½¨µÄÄǸöÊý¾Ý¿â¡£
ÈçºÎÉèÖÃÎÒÃÇ×Ô¼ºÏëÒªµÄĬÈÏÊý¾Ý¿âÄØ£¿
·½·¨ºÜ¼òµ¥£¬ÎÒÃÇÖ»ÐèÔÚÔËÐд°¿ÚÀïÊäÈëregedit£¬¼´¿É½øÈë×¢²á±í±à¼Æ÷£¬ÔÚHKEY_LOCAL_MACHI ......
ÔÚ Oracle ÖУ¬¶ÔÓÚÒѾ½¨ºÃµÄÖ÷¼ü£¬ÊÇ×Ô¶¯´´½¨Ë÷ÒýµÄ£¬²¢ÇÒË÷ÒýÊÇ´´½¨µ½µ±Ç°µÄ±í¿Õ¼äÖеģ¬Èç¹ûÒª½«Ë÷Òý¸ü»»µ½ÆäËü±í¿Õ¼ä£¬¿ÉÒÔʹÓÃÈçϵķ½·¨£¬³¬¼òµ¥£º
alter index index_aaa rebuild tablespace newtablespace;
newtablespace ÊÇÐÂÖ¸¶¨µÄ±í¿Õ¼ä¡£ ......
/******************************************************************************/
/*
Ö÷Á÷Êý¾Ý¿âMYSQL/MSSQL/ORACLE²âÊÔÊý¾Ý¿â½Å±¾´úÂë
½Å±¾ÈÎÎñ:½¨Á¢4¸ö±í,Ìí¼ÓÖ÷¼ü,Íâ¼ü£¬²åÈëÊý¾Ý,½¨Á¢ÊÓͼ
ÔËÐл·¾³1:microsoft sqlserver 2000 ²éѯ·ÖÎöÆ÷
ÔËÐл·¾³2:mysql5.0 phpMyAdminÍøÒ³½çÃæ
ÔËÐл·¾³3:oracle 9i SQL*PLU ......
ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖظ´¼Ç¼£¬µ«Ö»ÄÜÈ¡Ò»¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......