Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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
È·¶¨×Ó´®ÔÚ×Ö·û´®ÖеÄλÖ㬸ñʽ


Ïà¹ØÎĵµ£º

jdbcÁ¬½ÓOracle

      ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
    ½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
    ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
    Ê×ÏÈ£¬Ô ......

Éú³ÉSQL ServerÊý¾Ý¿â½Å±¾ËÄ·¨

¡¡¡¡Êý¾Ý¿â¿ª·¢ÈËÔ±»òÊý¾Ý¿â¹ÜÀíÔ±(DBA)ΪÁË·¢²¼Êý¾Ý¿â»ò±¸·ÝÊý¾Ý¿â¶ÔÏ󣬳£ÐèÒªÉú³ÉT-SQL½Å±¾¡£±ÊÕßÔÚÕâÀï¶Ô³£Ó÷½·¨½øÐÐÁË×ܽᣬ¹©ÅóÓÑÃDzο¼¡£
¡¡¡¡·½·¨Ò»£ºÊ¹ÓÃÆóÒµ¹ÜÀíÆ÷
¡¡¡¡½øÈë“ÆóÒµ¹ÜÀíÆ÷”£¬ÓÒ»÷Êý¾Ý¿â£¬Ñ¡Ôñ“ËùÓÐÈÎÎñ→Éú³ÉSQL½Å±¾”¼´¿É¡£
¡¡¡¡·½·¨ÆÀ¼Û£ºÓŵãÊÇ·½±ã£¬ÇÒ²Ù×÷¼òµ¥¡ ......

sql ²éѯÏȽøÏȳö

declare @tb3 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int,³ö¿âÊýÁ¿ int)
declare @tb1 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int)
insert into @tb1 select '0001','090801',200
      union all  select '0001','090501',50
  &n ......

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
......

sql ÅжÏÊý¾Ý¿âÊÇ·ñÒÑ´æÔÚ


if exists(select * from master.dbo.sysdatabases where name = 's2723103005')
 begin
  drop database s2723103005
  print 'ÒÑɾ³ýÊý¾Ý¿âs2723103005'
 end
create database s2723103005
on primary
(name=His_data,
 filename = 'd:\database\his_data.mdf',
 siz ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ