±±´óÇàÄñoracleѧϰ±Ê¼Ç17
Êý¾ÝÀàÐÍʹÓÃ
1.ÉùÃ÷£º
a.¸ñʽ£ºVariable_name [constant] databyte [not null] [:=default expression]
b.±äÁ¿Óë³£Á¿ÉùÃ÷»ù±¾Ò»Ö£¬Ê¹ÓÃconstantÉùÃ÷µÄΪ³£Á¿£¬²»Ê¹ÓÃΪ±äÁ¿£¬²¢ÇÒ¸³ÖµÖ»ÄÜÔÚDECLAREÇøÓò¸³Öµ£»
c.ʹÓÃ%TYPEºÍ%ROWTYPEÉùÃ÷¿ÉÒÔʹ±äÁ¿µÄÀàÐÍÓë±íÖÐ×Ö¶ÎÀàÐÍ»òÕû¸ö¼Ç¼ÀàÐͱ£³ÖÒ»Ö£»
2.¸³Öµ£º
a.ʹÓÓ:=”¸³Öµ£»
b.ʹÓÓselect into” »ò “fetch into”¸³Öµ£»
c.ʹÓÓ&str”´Ó¼üÅÌÊäÈ븳ֵ£»
DECLARE
eno VARCHAR(10):='0';
ename VARCHAR(20);
isValid BOOLEAN := TRUE;
empName CONSTANT VARCHAR(10):='pf';
BEGIN
eno:='1';
ename:='name1';
dbms_output.put_line('eno:'||eno);
dbms_output.put_line('ename:'||ename);
-- dbms_output.put_line('isValid:'||to_char(isValid));
dbms_output.put_line('empName:'||empName);
select stu_id, stu_name into eno,ename from student where stu_id = '1';
dbms_output.put_line('eno:'||eno);
dbms_output.put_line('ename:'||ename);
END;
ÄäÃûpl/sql³ÌÐò¿é
ÎÞ·¨ÔÙoracleÖд洢£¬Ã¿´ÎÖ´Ðж¼Ðè±àÒë
ÊôÐÔ
Ϊpl/sql±äÁ¿ºÍ³£Á¿ÒýÓÃÊý¾Ý¿âÖеÄÊý¾ÝÀàÐͺͶÔÏó
±äÁ¿Ãû ±íÃû.×Ö¶ÎÃû%type ÒýÓÃÊý¾Ý¿âÁÐ
±äÁ¿Ãû ±íÃû%rowtype ´ú±í±íÖеÄÐÐ ·ÃÎÊʱ²ÉÓà ±äÁ¿Ãû.±íÖÐÁÐÃû
declare
stuid student.stu_id%type;
stuname student.stu_name%type;
sturow student%rowtype;
begin
select stu_id,stu_name into stuid,stuname from student where stu_id = '1';
select * into sturow from student where stu_id = '2';
dbms_output.put_line('stuid:'||stuid);
dbms_output.put_line('stuname:'||stuname);
dbms_output.put_line('sturow stu_id:'||sturow.stu_id);
dbms_output.put_line('sturow stu_name:'||sturow.stu_name);
end;
Âß¼±È½Ï
ÊýÖµ±È½Ï
ÔËËã·û
º¬Òå
ʾÀý
=
µÈÓÚ
a = 123
!=
²»µÈÓÚ
c != 123
<
СÓÚ
a < 1
>
´óÓÚ
b > 4
<=
СÓÚµÈÓÚ
a <= b
>=
´óÓÚµÈÓÚ
a >= c
×Ö·û±È½Ï
ÔËËã·û
º¬Òå
ʾÀý
=
µÈÓÚ
name = 'pf'
!=
²»µÈÓÚ
name != 'pf'
<
×Öĸ˳ÐòÅÅÔÚÆäǰ
n
Ïà¹ØÎĵµ£º
ÔÚOracleÖÐʹÓÃGuid
ÔÚOracleÖпÉÒÔÓÃSYS_GUID()À´Éú³ÉÒ»¸öguid,Ï൱ÓÚmsSqlÖеÄnewid()¡£
ÔÚOracle9iºÍOracle 10g ÀïSYS_GUID²úÉúµÃµ½µÄÊý¾ÝÊÇ32 λµÄ£¬È磺234E45F0077881AAE0430AA3034681AA
µ«OracleÖеÄguidÓëmsSqlÖеÄguid¸ñʽ²»ÍêÈ«Ïàͬ¡£
½«OracleÖеÄguid·Ö¸î³ÉΪ Windowsϵͳ ObjectId µÄ¸ñʽ£º{8-4-4-4-12} £ ......
oracle°²È«
Óû§¹ÜÀí
DBAÊÚÓ費ͬÓû§²»Í¬È¨Àû£¬Ã¿¸öÓû§¿ÉÒÔÔÚÊÚȨ·¶Î§Äڻ£¬Èκγ¬Ô½È¨ÏÞ·¶Î§µÄ²Ù×÷¶¼ÊÓΪ·Ç·¨¡£
sysÓû§ ÍøÂç¹ÜÀíÔ± ÓµÓÐ×î¸ßȨÏÞ
conn sys/¿ÚÁî as sysdba;
systemÓû§ ±¾µØ¹ÜÀíÔ±
scottÓû§ ʾÀýÊý¾Ý¿â
½¨Á¢Óû§£º£¨±ØÐëÓµÓÐdbaȨÏÞ£©
Create User Óû§Ãû Identified by ¿ÚÁî [E ......
ÔÚOracleÖеÄÊ÷ÐβÙ×÷
1.È¡×ӽڵ㼰·¾¶(ÕýÊ÷):
select t.id ,t.code, t.name ,t.pid
,SYS_CONNECT_BY_PATH(t.id,'.')||'.' as IdPath
from tas_catalog t
--where id!=110
start with id=110
connect by pid = prior id
order siblings by id
2.È¡¸÷¼¶¸¸½Úµã(µ¹Ê÷)£º
select t.id ,t.code, t.na ......
in/not inÔÚÅжÏNULLʱÓõÄÓë=/<>Ò»ÑùµÄ·½Ê½£¬¼´±ØÐëÓÃis nullÀ´Åжϣ¬·ñÔòʼÖÕΪʧ°Ü¡£
Óï¾ä
select 'true' from dual where (1,2) not in ((2,3),(2,null));
³É¹¦µÄÔÒòÔÚÓÚÅж϶þÔªÖµÊ±Ê ......
oracle Óαê
¹Ø¼ü×Ö: oracle Óαê
1. Óαê: ÈÝÆ÷£¬´æ´¢SQLÓï¾äÓ°ÏìÐÐÊý¡£
2. ÓαêÀàÐÍ: ÒþʽÓα꣬ÏÔʾÓα꣬REFÓαꡣÆäÖУ¬ÒþʽÓαêºÍÏÔʾÓαêÊôÓÚ¾²Ì¬Óα꣨ÔËÐÐǰ½«ÓαêÓëSQLÓï¾ä¹ØÁª£©,REFÓαêÊôÓÚ¶¯Ì¬Óαê(ÔËÐÐʱ½«ÓαêÓëSQLÓï¾ä¹ØÁª)¡ ......