±±´óÇàÄñ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°²È«
Óû§¹ÜÀí
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 ......
×ܽáÁËÒ»ÏÂɾ³ýÖØ¸´¼Ç¼µÄ·½·¨£¬ÒÔ¼°Ã¿ÖÖ·½·¨µÄÓÅȱµã¡£
¼ÙÉè±íÃûΪTbl£¬±íÖÐÓÐÈýÁÐcol1£¬col2£¬col3£¬ÆäÖÐcol1£¬col2ÊÇÖ÷¼ü£¬²¢ÇÒ£¬col1£¬col2ÉϼÓÁËË÷Òý¡£
1¡¢Í¨¹ý´´½¨ÁÙʱ±í
¿ÉÒÔ°ÑÊý¾ÝÏȵ¼Èëµ½Ò»¸öÁÙʱ±íÖУ¬È»ºóɾ³ýÔ±íµÄÊý¾Ý£¬ÔÙ°ÑÊý¾Ýµ¼»ØÔ±í£¬SQLÓï¾äÈçÏ£º
creat table tbl_tmp (select distinct* from tb ......
PL/SQL
¿é½á¹¹
DECLARE
ÉùÃ÷²¿·Ö
BEGIN
¿ÉÖ´Ðв¿·Ö
EXCEPTION
Òì³£´¦Àí²¿·Ö
END;
Àý£º
Ê×ÏÈÎÒÃÇ¿´Ò»¸ö¼òµ¥Ö®Àý×Ó,ÏÂÃæÕâ¸öÀý×ÓÊÇͳ¼Æ´Ó1 ......
in/not inÔÚÅжÏNULLʱÓõÄÓë=/<>Ò»ÑùµÄ·½Ê½£¬¼´±ØÐëÓÃis nullÀ´Åжϣ¬·ñÔòʼÖÕΪʧ°Ü¡£
Óï¾ä
select 'true' from dual where (1,2) not in ((2,3),(2,null));
³É¹¦µÄÔÒòÔÚÓÚÅж϶þÔªÖµÊ±Ê ......