Oracle ²Ù×÷º¯Êý
º¯Êý£º
1.ʹÓÃCreate Function Óï¾ä´´½¨
2.Óï
·¨£º
Create or replace Function º¯ÊýÃû[²ÎÊýÁбí]
Return Êý¾ÝÀàÐÍ
IS|AS
¾Ö²¿±äÁ¿
Begin
¿ÉÖ´ÐÐÓï¾ä
end£»
3.·ÃÎʺ¯Êý
-ÓÃÁ½ÖÖ·½Ê½½øÐзÃÎÊ
ʹÓÃPL/SQL¿é
ʹÓÃSQLÓï¾ä
-½ö½ÓÊÜIn²ÎÊý
ʾÀý£º
create or replace function
GetName(sNo varchar2)
return varchar Is --²»ÄÜÖ¸¶¨¾«¶È
name
varchar(12);
Begin
select ename into name from emp
where empno=sNo;
return name;
Exception
when
Too_many_rows then
DBMS_output.put_line('·µ»ØÖµ¶àÓàÒ»Ìõ');
when
Others then
DBMS_output.put_line('ÔÚÖ´Ðк¯ÊýGetNameʱ³öÏÖÒâÍâ´íÎó!');
End;
/
µ÷ÓÃ
declare
name varchar(12);
begin
name:=getname('7369');
DBMS_output.put_line('½á¹ûΪ:'||name);
end;
/
Êý¾Ý×ֵ䣺user_source
±àÒëʱ³ö´íµÄÊý¾Ý×Ö
µä£ºuser_errors;
»ò show errors£»
select * from user_source from where
name='GETNAME'; --×¢Òâ´óд
¹ý³ÌÓ뺯Êý
¹ý
³Ì º¯Êý
×÷Ϊ
PL/SQLÓï¾äÖ´ÐÐ &nb
Ïà¹ØÎĵµ£º
ORACLE Êý¾Ý¿â¶ÔÏó
——Ë÷Òý
q Ë÷ÒýÊÇÓë±íÏà¹ØµÄÒ»¸ö¿ÉÑ¡½á¹¹
q ÓÃÒÔÌá¸ß SQL Óï¾äÖ´ÐеÄÐÔÄÜ
q ¼õÉÙ´ÅÅÌI/O
q ʹÓà CREATE INDEX Óï¾ä´´½¨Ë÷Òý
q &n ......
³ÌÐò°ü
°üÖ÷Ìå/¹æ·¶Ãû×ÖÒ»Ñù
°üÖ÷Ìå/¹æ·¶ÖеĶÔÓ¦²ÎÊý±ØÐëÀàÐͼ°Ãû×ÖÒ»Ñù
Ö»ÄÜʹÓÃÇ¿ÀàÐ͵ÄREFÓαê
´´½¨³ÌÐò°ü¹æ·¶
create or replace package my_pack
is
procedure find_emp_proc(eno emp.empno%type);
function fin ......
¹ý³Ì¡¢º¯Êý
create or replace procedure p1
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=7788;
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
Procedure created
SQL> exec p1;
......
ÎÒÁгöÎÒÈ«²¿µÄ×ö·¨£º
table a ÓÐid1, str1, str2, str3
¿ªÊ¼µÄpkÊÇid1, str1, str2
Ï£Íû¸Ä³Éid1, str1, str3
--ÎÊÌâ
СµÜÏÈÓÐÈçÏÂÎÊÌ⣺
Ò»¸ö±íÔÀ´µÄPKÊÇ id1+str1+str2 ÁÐ
ÏÈÐ޸ijÉid1+str1+str3ÁÐ
¶øÕâÈýÁÐÏÖÔÚµ±Ç°Êý¾Ý¿âµÄÊý¾ÝÓÐÖØ¸´µÄÇé¿ö£¬ СµÜÏÖÔÚÓÃsql:
ALTER table a a ......
×î½ü·¢ÏÖÒ»¸öÎÊÌ⣬һ¸ö9iÅÜÔÚredhat5µÄ£¬¾ÓÈ» select sysdate from dual ³öÀ´µÄʱ¼äºÍʵ¼Êʱ¼ä²îÁ˼¸·ÖÖÓ£¬ÒòΪ»ùÓÚÕâ¸öoracleµÄϵͳʱ¼äÕýÈ·ÐÔ»¹ÊÇÓеãÒªÇóµÄ¡£ËùÒÔÖ»ÄÜ #date -s 2010-2-10 8:55:00 ÁË£¬ÔÙ#clock -w дÈëCOMS ×¢ÒâÐÞ¸Äʱ¼äµÃÔÚrootÏ¡£
ÔÚϵͳÆô¶¯Ê±£¬Linux²Ù×÷ϵͳ½«Ê±¼ä´ÓCMOSÖжÁµ½ÏµÍ³Ê ......