oracle ±Ê¼Ç III Ö®´æ´¢¹ý³ÌÓ뺯Êý
DML(Manipulation):Êý¾Ý²Ù×÷ÓïÑÔ
CRUD
DDL(Definition): Êý¾Ý¶¨ÒåÓïÑÔ,Óë±í£¬Ë÷Òý£¬Í¬Òå´ÊÓйØ
create,alter,drop,rename,truncate(Çå¿Õ)
DCL(Control): Êý¾Ý¿ØÖÆÓïÑÔ£¬ÓëȨÏÞÓйØ
grant,revoke
TCL(Transaction Control): ÊÂÎñ¿ØÖÆÓïÑÔ,ÓëÊÂÎñÓйØ
commit,rollback,savepoint
==========================
´æ´¢¹ý³ÌºÍ´æ´¢º¯Êý£¬Ï൱ÖØÒª£¬ÔÚjavaÐèÒªµ÷Óô洢¹ý³ÌºÍº¯Êý
дһ¸öÀý×Ó£ºÐ´Ò»¸öº¯ÊýÍê³ÉÈçϹ¦ÄÜ£¬ÊäÈëÁ½¸ödepartment_id,dept_1,dept_2,·µ»ØËûÃÇƽ¾ù¹¤×ʽϸߵÄÄǸö²¿ÃÅÖÐ manager µÄsalary
º¯Êý°æ:
create or replace function get_mgr(
dept_id_1 employess.department_id%type,
dept_id_2 employees.department_id%type,
)
return number
is
max_sal_1 employees.salary%type;
max_sal_2 employees.salary%type;
dept_id employees.department_id%type;
mgr_id employees.manager_id%type;
sal employees.salary%type;
begin
select max(salary) into max_sal_1 from employees
where department_id = dept_id_1;
select max(salary) into max_sal_2 from employees
where department_id = dept_id_2;
if max_sal_1 > max_sal_2 then
dept_id := dept_id_1;
else
dept_id := dept_id_2;
end if;
select manager_id into mgr_id from departments where department_id = dept_id;
select salary into sal from employees where employee_id = mgr_id;
return sal;
end;
sql°æ:
µÚÒ»²½ ²éѯ¸ø¶¨µÄÁ½¸ö²¿ÃÅÖиߵÄƽ¾ù¹¤×Ê:30,80
select max(avg_sal)
from (select avg(salary) avg_sal from emplo
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
°´ÕÕOracleDocumentÖеÄÃèÊö£¬v$sysstat´æ´¢×ÔÊý¾Ý¿âʵÀýÔËÐÐÄÇ¿ÌÆð¾Í¿ªÊ¼ÀÛ¼ÆȫʵÀý(instance-wide)µÄ×ÊԴʹÓÃÇé¿ö¡£
ÀàËÆÓÚv$sesstat£¬¸ÃÊÓͼ´æ´¢ÏÂÁеÄͳ¼ÆÐÅÏ¢£º
1>.ʼþ·¢Éú´ÎÊýµÄͳ¼Æ(È磺user commits)
2>.Êý¾Ý²úÉú£¬´æÈ¡»òÕß²Ù×÷µÄtotalÁÐ(È磺redo size)
3>.Èç¹ûTIMED_STATISTICSֵΪtrue,Ôòͳ¼Æ»¨·Ñ ......
DML:Data Manipulation Language Êý¾Ý²Ù×÷ÓïÑÔ
°üÀ¨£ºCRUD
1. insertÓï¾ä
(1) ´ÓÆäËü±íÖи´ÖÆÊý¾Ý,ʵÏÖ·½·¨:ÔÚinsert Óï¾äÖмÓÈë²éѯÓï¾ä
insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';
(2) up ......
ÓÐÁ½ÖÖº¬ÒåµÄ±í´óС¡£Ò»ÖÖÊÇ·ÖÅä¸øÒ»¸ö±íµÄÎïÀí¿Õ¼äÊýÁ¿£¬¶ø²»¹Ü¿Õ¼äÊÇ·ñ±»Ê¹Ó᣿ÉÒÔÕâÑù²éѯ»ñµÃ×Ö½ÚÊý£º
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
»òÕß
Select Segment_Name,Sum(bytes)/1024/1024 from User_Extents Group By Segment_Name
ÁíÒ»ÖÖ±íÊ ......
Oracle OFS FS10755 ±¨´íÎÊÌâµÄ½â¾ö·½·¨
ÔÚijЩÇé¿öÏ£¬ÖØ×°»òÖØÅä OFS ʱ£¬±ÈÈçÑé֤Ⱥ¼¯£¬»òÒƶ¯Èº¼¯×éʱ£¬»á±¨ FS10755 ´í£¬ ´íÎóÐÅÏ¢ÈçÏ£º
Oracle OFS error - FS10755: Error Creating the OracleFsWorker COM object After Oracle OFS installed, the verification could not pa ......