oracle ±Ê¼Ç
1. SQL Óï¾äÓëSQL*Plus ÃüÁî
SQL
SQL*Plus
Ò»ÖÖÓïÑÔ
Ò»ÖÖ»·¾³
ANSI±ê×¼
Oracle µÄÌØÐÔÖ®Ò»
¹Ø¼ü×Ö²»ÄÜËõд
¹Ø¼ü×Ö¿ÉÒÔËõд
ʹÓÃÓï¾ä¿ØÖÆÊý¾Ý¿âÖеıíµÄ¶¨ÒåÐÅÏ¢
ºÍ±íÖеÄÊý¾Ý
ÃüÁî²»ÄܸıäÊý¾Ý¿âÖеÄÊý¾ÝµÄÖµ
¼¯ÖÐÔËÐÐ
2.ÒÔϼ¸¸ö²éѯºÜÖØÒª£¬×ÐϸÌå»á
1 (ÓÐÔ±¹¤µÄ³ÇÊÐ)¸÷¸ö³ÇÊеÄƽ¾ù¹¤×Ê
select city,avg(salary) from employees e,departments d locations l
where e.department_id = d.department_id and d.location_id = l.location_id group by city;
2 ²éѯÄÄЩ³ÇÊÐÓÐÔ±¹¤£¬ÄÄЩ³ÇÊÐûÓÐÔ±¹¤
select city from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
group by city having count(employee_id) > 0
1)×麯Êý²»ÄÜ·ÅÔÚ where×Ó¾äÖУ¬ ÐèÒª·ÅÔÚ having ×Ó¾äÖÐ
2) Ðè°´ city ½øÐзÖ×é
3 manager ÖÐ×î¸ß¹¤×ÊÊÇË manager ÖÐ×îµÍ¹¤×ÊÊǶàÉÙ manager ÖÐƽ¾ù¹¤×ÊÊǶàÉÙ
Ê×ÏÈҪȷ¶¨ÄÄЩÈËÊÇmanager
¹«Ë¾ÓÐÄÄЩÈËÊÇ manager :
·½°¸1
select distinct m.employee_id,m.last_name from employees e,employees m
where e.manager_id = m_employee_id order by m.employee_id
·½°¸2
select distinct manager_id from employees where manager_id is not null order by manager_id
ƽ¾ù¹¤×Ê:
select avg(m.salary) from employees e,employees m
where e.manager_id = m.employee_id order by m.employee_id
×î¸ß¹¤×Ê:
/*
select max(m.salary) from employees e,employees m
where e.manager_id= m.employee_id order by m.employee_id
*/
¿ÉÒÔÓÃ×Ó²éѯ
select last_name,salary from employees where salary =
(select max(m.salary) from employees e,employees m where e.manager_id = m.employee_id )
order by employee_id
5. Ñ¡ÔñËùÓÐÔ±¹¤µÄÐÕÃû£¬Ô±¹¤ºÅ£¬ÒÔ¼°ËûµÄ¹ÜÀíÕßµÄÐÕÃûºÍÔ±¹¤ºÅ£¬½á¹ûÀàËÆÓÚÏÂÃæµÄ¸ñʽ
--------------------
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
¹ØÓÚ´æ´¢¹ý³ÌºÍº¯ÊýµÄ¶¨ÒåÍøÉÏÒ»ËÑÒ»´ó°Ñ£¬ÕâÀï¾Í²»ÌØÊâ½éÉÜÁË£¬ÕâÀï¾ÍÖ»¶ÔÎÒ×Ô¼ºÐ´µÄ¼¸ÖÖ¸ñʽµÄ´æ´¢¹ý³ÌºÍº¯Êý×öһЩ×ܽᣬϣÍû¶Ô´ó¼ÒÓеã°ïÖú¡£
Ò»£º´æ´¢¹ý³Ì
1£º×îÆÕͨµÄÒ»ÖÖ¡££¨´«²Î£¬²éѯÓαִ꣬ÐУ¬Ñ»·Óαê×ö²åÈ붯×÷£©¡£
create or replace procedure zy2040_sirole(rolekey in varchar2) ......
½çÃ濪·¢ÈËÔ±±¨ÓкܶàÖظ´Êý¾ÝÔÚÓû§È¨ÏÞ±í¡£È»ºóÎÒɾ³ýÁ˱íÊý¾Ýdelete ·½Ê½£¬ÐÞ¸ÄÁ˶ÔÓ¦µÄ´æ´¢¹ý³Ìʹ֮²»Öظ´£¡
ºóÀ´·¢ÏÖ ÖØÐÂÀ»ØµÄÊý¾ÝûȨÏÞ¡£ Ö»ºÃÉÁ»Øµ½½ñÌìÁ賿ÁË£¡
SQL> ALTER TABLE BA.T_POWER_ADMIN ENABLE ROW MOVEMENT;
Table altered
SQL> flashback table ba.t_Power_Admin to tim ......
Èý¡¢Ç¶Ì×±íµÄʹÓ÷½·¨
1¡¢½«Ç¶Ì×±í¶¨ÒåΪPL/SQLµÄ³ÌÐò¹¹Ôì¿é
TYPE type_name IS TABLE OF element_type[NOT NULL];
ÈçÏÂÀýËùʾ£º
DECLARE
-- Define a nested table of variable length strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
-- Declare and initialize a n ......
ORACLE 10GµÄ¼¯ºÏAPI
¼¯ºÏAPI·½·¨×îÔçÊÇÔÚORACLE 8IÖÐÒýÈëµÄ¡£¼¯ºÏAPIÌṩÁ˼ò»¯·ÃÎʼ¯ºÏµÄ·½·¨¡£ÔÚORACLE 10GÒÔÇ°£¬ÕâЩ·½·¨È·Êµ¼ò»¯Á˶Լ¯ºÏµÄ·ÃÎÊ¡£µ«ÊÇ£¬²¢²»ÊÇ·ÇÒªÇóÕÆÎÕÕâЩ·½·¨²»¿É¡£´ÓORACLE 9IµÄINDEX-BY±íµ½ORACLE 10GµÄÁªºÏÊý×éµÄת»»£¬Ê¹ËüÃdzÉΪ±ØÐëÕÆÎյķ½·¨¡£
ÏÂÃæÊÇÎÒ´ÓÊéÉÏժ¼ÏµÄһР......