Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle PL\SQL ²Ù×÷£¨Èý£©Oracleº¯Êý


1.ϵͳ±äÁ¿º¯Êý
£¨1£©SYSDATE
¸Ãº¯Êý·µ»Øµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØµÄÊÇOracle·þÎñÆ÷µÄµ±Ç°ÈÕÆÚºÍʱ¼ä¡£
select sysdate from dual;
insert into purchase values
(‘Small Widget’,’SH’,sysdate, 10);
insert into purchase values

(‘Meduem Wodget’,’SH’,sysdate-15, 15);
²é¿´×î½ü30ÌìµÄËùÓÐÏúÊۼǼ£¬Ê¹ÓÃÈçÏÂÃüÁ
select * from purchase
where purchase_date between (sysdate-30) and
sysdate;
£¨2£©USER
²é¿´Óû§Ãû¡£
select user from
dual;
£¨3£©USERENV
²é¿´Óû§»·¾³µÄ¸÷ÖÖ×ÊÁÏ¡£
select userenv(‘TERMINAL’) from
dual;
2.ÊýÖµº¯Êý
£¨1£©ROUND ËÄÉáÎåÈ뺯Êý
ROUND(ÊýÖµ£¬±£ÁôλÊý£©
select round(3.1415,3) from deul;
select product_name,round(product_price,0) price
from
product;
£¨2£©TRUNC ´ÓÊýÖнØȥСÊý²¿·Ö
TRUNC(ÊýÖµ£¬½Ø¶ÏСÊýµãnλºóµÄÊý£©
select trunc(3.145159,3) from dual;
select trunc(123456.45,-1) from dual;
select trunc(123456.45) from dual;
select product_name,trunc(product_price) price
from
product;
3.Îı¾º¯Êý
£¨1£©UPPER¡¢LOWERºÍINITCAP
ÕâÈý¸öº¯Êý¸ü¸ÄÌṩ¸øËüÃǵÄÎÄÌåµÄ´óСд¡£
select upper(product_name) from product;
select lower(product_name) from product;
select initcap(product_name) from
product;
º¯ÊýINITCAPÄܹ»ÕûÀíÔÓÂÒµÄÎı¾£¬ÈçÏ£º
select initcap(‘this TEXT hAd UNpredictABLE caSE’) from
dual;
£¨2£©LENGTH
ÇóÊý¾Ý¿âÁÐÖеÄÊý¾ÝËùÕ¼µÄ³¤¶È¡£
select product_name,length(product_name) name_length
from product
order by
product_name;
£¨3£©SUBSTR
È¡×Ó´®£¬¸ñʽΪ£º
SUBSTR£¨Ô´×Ö·û´®£¬ÆðʼλÖã¬×Ó´®³¤¶È£©£»
create table item_test(item_id char(20),item_desc char(25));
insert into item_test values(‘LA-101’,’Can, Small’);
insert into item_test values(‘LA-102’,’Bottle, Small’);
insert into item_test values
(‘LA-103’,’Bottle, Large’);
È¡±àºÅ£º
select substr(item_id,4,3) item_num,item_desc
from
item_test;
£¨4£©INSTR
È·¶¨×Ó´®ÔÚ×Ö·û´®ÖеÄλÖ㬸ñʽ


Ïà¹ØÎĵµ£º

oracle ±í¿Õ¼ä²Ù×÷

oracle±í¿Õ¼ä²Ù×÷Ïê½â
  1
  2
  3×÷Õߣº   À´Ô´£º    ¸üÐÂÈÕÆÚ£º2006-01-04 
  5
  6 
  7½¨Á¢±í¿Õ¼ä
  8
  9CREATE TABLESPACE data01
 10DATAFILE '/ora ......

oracle¸´Ï°£¨Ò»£© Ö®OracleÌåϵ½á¹¹

      ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬Ô­À´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·­Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
      µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......

sql ÅúÁ¿É¾³ýÊý¾Ý¿âÖÐµÄ±í £¨º¬ÓÐÍâ¼üÔ¼Êø£©

 Ð´·¨Ò»£º
set xact_abort on
begin tran
DECLARE @SQL VARCHAR(99)
DECLARE CUR_FK CURSOR LOCAL FOR
SELECT 'alter table '+ OBJECT_NAME(FKEYID) + ' drop constraint ' + OBJECT_NAME(CONSTID) from SYSREFERENCES
--ɾ³ýËùÓÐÍâ¼ü
OPEN CUR_FK
FETCH CUR_FK INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ