2006年10月12日
create user test1 identified by test1;
grant connect,create table to test1;
conn cyts_cc/cyts_cc@orcl2000;
create table(
id int)
tablespace user;
ERROR 位于第 1 行:
ORA-01950: 表空間'USERS'中無權(quán)限
conn cyts_cc/cyts_cc@orcl2000;
alter user test1 quota 1M on users;
create tab
(id int);
success
alter user test1 account lock;
conn test1/test1@orcl2000;
ERROR:
ORA-28000: the account is locked
1 Database Schema
?a schema is a named collection of objects
?b user is created and a corresponding schema is created
?c user can be associated only with one schema
?d username and schema are often userd interchangely.
2 Checklist for creating users
? a idntfigy tablespaces in which the usr nedds to store objects
? b decide on quotas for each tablespace
? c assign a default tablespace and temporary tablespace.if you do not specify at the time of create user,system tablespace will be the defalut tablespace and temporary.it will affect the performance of the oralce.
? d create user
? e grant privileges and roles to user
? desc dba_users;
? select * from dba_users;
3 Creating a new user:
Database Authentiacation
?set the initial password
? create user aaron
? identified by soccer
? default tablespace data
? temporary tablespace temp
? guota 15m on data
? password expire;
? alter database default temporary tablespace temp;
4 Creating a new user operating System Authentication
? os_authent_prefix initialllization parameter specifies the format of the username
? defauts to ops$
??? create user arron
??? identified externally
??? default tablespace users
??? temporary tablespace temp
??? quota 15m on data
??? password expire;
??? conn /
??? show parameter os
??? os_authent_prefix??????????????????? string?????????????????????????? OPS$
??? create user ops$test3
????? identified externally
????? default tablespace us
????? temporary tablespace
????? quota 10m on users
??? thee test2 is an user of os ,which the oracle is installed.
5 Changing user quota on tablespace
?alter user test3 quota 4m on users;
?you cann't grant quota on temp and undotbs.
?
?alter quota 0 on uers -- means that no new table space can be allocated and cannot change the exist object in the tablespaces
6 drop user
?you cannot drop user who has connected to oracle
? drop user (cascade)
7 Obtaining User information
?information about uers can be obtained by qerying the data dictionary
? dba_users
? 名稱??????????????????????????
-----------------------------
USERNAME??????????????????????
USER_ID???????????????????????
PASSWORD??????????????????????
ACCOUNT_STATUS????????????????
LOCK_DATE?????????????????????
EXPIRY_DATE???????????????????
DEFAULT_TABLESPACE????????????
TEMPORARY_TABLESPACE??????????
CREATED???????????????????????
PROFILE???????????????????????
INITIAL_RSRC_CONSUMER_GROUP???
EXTERNAL_NAME?????????????????
? dba_ts_quotas
?? 名稱??????????
?---------------
?TABLESPACE_NAME
?USERNAME??????
?BYTES?????????
?MAX_BYTES?????
?BLOCKS????????
?MAX_BLOCKS????
2006年10月11日
1 The Union Operator
?The union operator returns results form both queries after eliminating duplications.\
?select employee_id,job_id
?from employees
?uniion
?select employ_id ,job_id
?from job_history;
2 the all operator
?The union all opertor reutrn result from both queries,including all duplications
3 interset
?select ster_id,qty from sales where qty>20;
?intersect
?select ster_id,qty from sales where ster_id like '7%';
4 minus
?select ster_id,qty from sales where qty>20
?minus
?select ster_id from sales where ster_id like '7%'
5 set operator guidelines
?. teh expressions in the select list must match in number and data type;
?. Parentheses can be used to alter the sequence of the execution
?.The order by clause:
??? can appear only at the very end of the statement
??? will accept the column name,aliases from thee firest select statement ,or thee positional notation
?.Duplicate row are atuomatically eliminated except in union all.
?.Column names from the first query appear in the result
?.The output is sorted in ascending order by default except in union all
6 matching the select statement
?select department_id,to_number(null),location,hire_date
?from employees
?union
?select department_id,location_id,to_date(null)
?from departments;
?select employee_id,job_id,salary
?from employees
?union
?select employee_id,job_id,0
?from job_history;
7 Controlling the order of the rows
?select 'sing' as "my dream" ,3,a_dummy
?from dual
?union
?select 'like''d like to teach',1
?from dual
?union
?select 'the world to',2
?from dual
?order by 2;
?1? 開發(fā)過程
?1) 建立過程：不帶參數(shù)
? create or replace procedure out_time
? is
? begin
??? dbms_output.put_line(systimestamp);
? end;
? a 使用execute 命令調(diào)用過程
?? set? serveroutput on
?? exec out_time;
? b 使用call 命令調(diào)用過程
?? set serveroutput on
?? call out_time();
?2) 建立過程：帶有in參數(shù) 默認(rèn)為輸入?yún)?shù)，另外也可以使用in 關(guān)鍵子顯示的定義
?? create or replace procedure add_employee
?? (eno number,name varchar2,sal number,
??? job varchar default 'clerk',dno number)
?? is
?? e_integrity exception;
?? pragma exception_init(e_intgegrity,-2291);
?? begin
??? insert into emp(empno,ename,sal,job.deptno)
????? values(eno.name,sal,job,dno);
??? exception
????? when dup_val_on_index then
??????? raise_application_error(-20000,'雇員號不能重復(fù)');
????? when e_integrity then
??????? raise_application_error(-20001,'部門號不存在');
???
調(diào)用
?? exec add_employee(111,'clark',200,'manager',10);
?? 3 建立過程，帶有out 參數(shù)
??? create or replcace procedure query_employee
??? (eno number,name out varchar2,salary out number)
??? is
??? begin
????? select ename,sal,into name,salary from emp where empno=eno;
??? exception
????? when no_data_found then
??????? raise_application_error(-20000,'G該雇員不存在');
??? end;
???
??? 調(diào)用
??? var name varchar2(10)
??? var salary number
??? exec query_employee(7788,:name,:salary);
??? print name,salary;
??? 4 建立過程，帶有in out 參數(shù)
??? create or replace procedure compute
??? (num1,in out number,num2 in out number)
??? is
?????? v1 number;
?????? v2 number;
??? begin
????? v1:=num1/num2;
????? v2:=mod(num1,num2);
????? num1:=v1;
????? num2:=v2;
??? end;?
???
??? 調(diào)用
??? var n1 number
??? var n2 number
??? exec :n1:=100;
??? exec :n2:=30
??? exec compute(:n1,:n2)
??? print n1 n2
??? 5) 為參數(shù)傳遞變量和數(shù)據(jù) 位置傳遞，名稱傳遞，組合傳遞
??? create or replace procedure add_dept
??? (dno number,dname varchar2 default null,loc varchar default null)
??? is
??? begin
????? insert into dept values(dno.dname,loc);
??? end;
??? -- 位置傳遞
???? exec add_dept(50,'sales','new york');
???? exec add_dept(60);
???? exec add_dept(70,'admin');
??? -- 名稱傳遞
???? exec add_dept(50,loc=>'new york');
???? exec add_dept(60,dname=>'sales',loc=>'new york');
?? 6) 顯示當(dāng)前用戶的所有程序及其源代碼
???? select text from user_source where name='add_dept';
?? 7) 刪除過程
???? drop procedure add_dept;
2 開發(fā)函數(shù)
? 可以在sql語句和其他子過程中執(zhí)行。
? 1 建立函數(shù)，不帶任何參數(shù)
? create or replace function get_user
? return varchar2
? is
??? v_user varchar2(100);
? begin
??? select username into v_user from user_users;
??? return v_user;
??? end;
? --使用變量接收函數(shù)返回值
?? var v2 varchar2(100)
?? exec :v1:=get_user
?? print v1;
?-- 在sql 語句中直接調(diào)用函數(shù)
?? select get_user from dual;
?-- 使用dbms_output 調(diào)用函數(shù)
?? set serveroutput on
?? exec dbms_output.put_line('get_user');
?2) 建立函數(shù)，帶有in 參數(shù)
?? create or replace function get_sal(name in varchar2)
?? reutnr number
?? as
?? v_sal emp.sal%type;
?? begin
???? select sal into v_sal from emp where upper(ename)=upper(name);
???? return v_sal;
?? exception
???? when no_data_found then
?????? raise_application_error(-20000,'employee does not exist');
?? end;
?3) 建立函數(shù)，帶有out 參數(shù)
?? create or replace function get_info
?? (name varchar2,title out varchar2)
?? return varchar2
?? as
???? deptname dept.dname%type;
?? begin
???? select a,job,b,dname into title,deptname from emp a,dept b
???? where a.deptno=b.deptno
???? and upper(a.ename)=uppder(name);
???? return deptname;
?? end;
?? var job varchar2(20)
?? var dname varchar2(20)
?? exec :dname:=get_info('scott',job);
?? print dname job
?4) 帶有 in out 參數(shù)
? create or replace function result
? (num1,number,num2 in out nu8mber)
? return number
? as
??? v_result number(6);
??? v_remainder number;
? begin
??? v_result:=num1/num2;
??? v_remainder:=mod(num1,num2);
??? num2:=v_remainder;
??? retrun v_result;
? exception
??? when zero_divide then
?????? raise_application_error(-20000,'zero divied');
? end;
?5) 函數(shù)調(diào)用的限制
? -- 在sql 語句中只能調(diào)用存儲函數(shù)
? -- 在sql 語句中只能調(diào)用帶有輸入?yún)?shù)in ，而不能有輸出參數(shù)out 和in out 參數(shù)的函數(shù)
? -- 在sql 語句中調(diào)用的函數(shù)的參數(shù)必須是標(biāo)準(zhǔn)的sql 數(shù)據(jù)類型，不能是pl/sql 所特有的數(shù)據(jù)類型
? -- 在sql 語句中調(diào)用的函數(shù)不能包含insert，update 和delete
?6) 查看源代碼
? set pagesize 40
? select text form user_source where name='result';
?7) 刪除函數(shù)
? drop function result;
?3 管理子程序
? 1) 列出當(dāng)前用戶的子程序
?? select object_name,created,status form user_objects
?? where object_type in ('procedure','function');
? 2)列出子程序源代碼
?? select text from user_sorce where name='raise_salary'
? 3)類出子程序編譯錯誤
??? -- show errors
??? --使用數(shù)據(jù)字典user_errors? 確定錯誤原因和位置
???? select line||'/'||position as "line/col",text error
???? from user_errors where name='raise_salary';
?? 4) 列出對象的依賴關(guān)系
??? -- 使用 user_dependenciess 確定直接依賴關(guān)系
??? select name,type from user_dependencies
??? where referenced_name='emp'
??? -- 使用 deptree 和 ideptree 確定依賴和見解依賴關(guān)系
??? select nested_level,name,type from deptree;
?? 5) 重新編譯子程序
???? 當(dāng)被應(yīng)用對象的結(jié)構(gòu)被修改以后，就會將相關(guān)依賴對象轉(zhuǎn)變?yōu)闊o效invalid ，需要重新編譯
???? 1
???? alter table emp add remark varchar2(100);
???? 2
???? select object_name,object_type from user_objects
???? where status='invalid'
???? 3
???? alter procedure add_employee compile;
???? alter view dept10 compile;
???? alter function get_info compile;
?????
??
1例外簡介
?1） 例外分類
? 預(yù)定義分類，非預(yù)定義分類，自定義例外。
2 處理預(yù)定義例外
? 1) 常用預(yù)定義例外
?? a access_into_null;
???? create type emp_type as object
???? (name varchar2(2),sal number(6,2));
????
???? declare
?????? emp emp_type;
???? begin
?????? emp.name:='scott';
???? exception
?????? when access_into_null then
????????? dbms_output.put_line('首先初始化對象emp');
??? b case_not_found
???? undef no
???? declare
?????? v_val emp.sal%type;
???? begin
?????? select sal into v_sal from emp where empno=&no;
?????? case
???????? when v_sal<1000 then
?????????? update emp set sal=sal+100 where empno=&no;
???????? when v_sal<2000 then
?????????? update emp set sal=sal+150 where empno=&no;
???????? when v_sal<3000 then
????????? update emp set sal=sal+200 where empno=&no;
?????? end case;
?????? exception
???????? when case_not_found then
?????????? dbms_output.put_line();
????? end;
??? c collection is null
??? 在給集合元素（嵌套表或array類型）賦值前，必須首先初始化集合元素
??? declare type ename_table_type is table of emp.eanme%type;
??? ename_table ename_table_type;
??? begin
????? select e_name into ename_talbe(2) from emp where empno=$no;
??? exception
????? when collection_is_null then
??????? dbms_output.put_lilne('必須使用構(gòu)造方法初始化集合元素');
??? end;
??? d currsor_already_open
??? reopen curosr 如果用戶已經(jīng)使用了open 命令打開了顯示游標(biāo)，或執(zhí)行for循環(huán)(隱式的打開游標(biāo))
?????? delcare
???????? cursor emp_surosr is select ename,sal,from emp;
?????? begin
???????? open emp_curosr;
???????? for emp_record in emp_cursor loop
????????????? dbms_output.put_line(emp_record.eanme);
???????? end loop;
?????? exception
????????? when cursor_already_open then
??????????? dbms_output.put_line("游標(biāo)已經(jīng)打開");
?????? end;
???? e dup_val_on_index
???? begin
???? exception
??????? when dup_val_on_index then
??????? dbms_output.put_line("列上不能出現(xiàn)重復(fù)值");
???? end;
???? d invalid_curosr
???? delcare
?????? curosr emp_cursor is select ename,sla from emp;
?????? emp_record emp_crusor%rowtype;
???? begin
?????? fetch emp_cursor into emp_record;
?????? close emp_crusro;
???? exception
?????? dbms_output.put_line("游標(biāo)沒有打開");
???? end;
???? f invalid_number? can not convert char to nmuber successfully
?????? begin
???????? update mep set sal=sal+1oo;
?????? exception
???????? when invalid_number then
???? g no_data_found? when select into is executed ,no row is returned?????
?????? declare
???????? v_sal emp.sal%type;
?????? begin
????????? select sal into v_cal form emp where lower(ename)=lower('&name');
?????? exception
???????? when no_data_found then
???????????? dbms_output.put_line('沒有返回結(jié)果');
?????? end;
????? h too_many_row? ora -01422 there are too many are return when "select into" is executed
????? i zero_divide ora-01476
????? g subscript_beyond_count ora-065533
????? declare
??????? type emp_array_type is varray(20) of varchar2(10);
??????? emp_array emp_array_type;
????? begin
???????? emp_array:=emp_array_type('scott','mary');
???????? dbms_output.put_line('emp_array(3)');
????? exception
???????? when subscript_beyone_count then
????????? dbms_out.put_line('超出下標(biāo)范圍');
????? end;
????? k subscript_outside_limit
?????? emp_array(-1);
????? l value_error the length of variable cannot contain the actual value;
????? declare
????? begin
????? end;
3 處理非預(yù)定義例外
? delcare
??? e_integrity exception;
??? pragma exception_init(e_integrity,-2291);
? begin
??? update emp set deptno=dno where empno=&no;
? exception
??? when a_integrity then
? end;
4 處理自定義例外
? 與oracle 錯誤沒有任何聯(lián)系，為業(yè)務(wù)邏輯所定義的例外
? delcare
??? e_no_employee exception;
? begin
??? update emp set deptno=&dno where empno=&eno;
? if sql%notfound then
??? raise e_no_employee;
? end if;
? exception
??? when e_no_emplyee then
???? dbms_output.put_line('該雇員不存在');
5 使用錯誤例外函數(shù)
? 使用例外函數(shù)可以取得錯誤號以及相關(guān)的錯誤消息，sqlcode 用于取得oracle 的錯誤號，而sqlerrm
則用于取得與之相關(guān)的錯誤信息。
? 1 sqlcode 和 sqlerrm
? 為了在pl/sql 應(yīng)用程序中處理其他為預(yù)料的到的oracle 錯誤，用戶可以在例外處理部分的
when others 自句后，引用兩個函數(shù)
? declare
??? v_ename emp.ename%type;
? begin
??? select ename into v_ename form emp where sal='&v_sal';
? exception
??? when no_data_found then
??? when others then
?????? dbms_output.put_line(sqlcode);
?????? dbms_output.put_line(sqlerrm);
? end;
? 2 raise_aaplicaition_error
?? 只能在子程序中使用（過程，函數(shù)，包，觸發(fā)器）
?? raise_application_error(error_number,message,[true|false]);
?? error_number 錯誤號，在－20000到－20999 之間
?? message 指定錯誤消息，不能超過2048?
?? if v_comm is null then
?????? raise_application_error(-20001,'該雇員無補助');
?? end if;
1建立包
?1) 建立包規(guī)范： 用于定義包的公共組建，包括常量，變量，游標(biāo)，過程和函數(shù)等
?create or replace package emp_package is
? g_deptno number(3):=30;
? procedure add_employee(eno number,name varchar2,salary number dno number default g_deptno);
? procedure fire_empoyee(eno number);
? function get_sal(eno number) return number;
?end emp_package;
? 2) 建立包體
?? 用于實現(xiàn)包規(guī)范所定義的過程和函數(shù)。在包體中也可以單獨定義私有組件，包括變量，常量，過程和函數(shù)等。但在包體中所定義的組件只能在包內(nèi)使用，而不能由其他子程序引用。
?? create or replace package body emp_package is
??? function validate_deptno(v_deptno number)
????? return boolean;
??? is
????? v_temp int;
??? begin
????? select 1 into v_temp from dept where deptno=v_deptno;
????? return true;
??? exception
????? when no_data_found then
?????? return false;
??? end;
??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
??? is
??? begin
????? if validate_deptno(dno) then
??????? insert into emp(empno,ename,sal,deptno)
???????? values(eno,name,salary,dno);
????? esle
??????? raise_application_error(-20011,'部門不存在');
????? end if;
??? exception
????? when dup_val_on_index then
??????? raise_application_error(-20011,'該雇員已存在')
??? end;
??? procedure fire_employee(eno number) is
??? begin
????? select from emp where empno=eno;
????? if sql%notfound then
???????? raise application_error(-20012,'');
????? end if;
??? end;
?? function get_sal(eno number) return number
?? is
???? v_sal emp.sal%type;
?? begin
???? select sal into v_sal from emp where empno=eno;
???? return v_sal;
?? exception
???? when no_data_found then
?????? raise_application_error(-200012,'');
?? end;
? end emp_package;
? 3) 調(diào)用包組建
??? －－ 在同一個包內(nèi)調(diào)用組建 不需要加包名前綴，直接調(diào)用
? create or replace paclage body emp_package is
??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
??? is
??? begin
????? validate_deptno(dno) then
??? exception
??? end;
?? -- 調(diào)用包公用變量
?? exec emp_package.g_deptno:=20
?? -- 調(diào)用公用過程
?? exec emp_package.add_employee(111,'mary,2000');
?? -- 調(diào)用遠(yuǎn)程數(shù)據(jù)庫包的公用組件
?? exec
emp_paclage.add_employee@orasrv(1116,'scott',1200);
?? 4) 查看包源代碼
?? select text from user_source where name='emp_package' and type='package';
?? 5) 刪除包
?? drop packagee emp_package;
2 使用包重載
? 指多個具有相同名字的子程序，在調(diào)用的時候使用不同的參數(shù)傳遞。
? 1) 建立包規(guī)范
?? 同名過程和函數(shù)必須具有不同的輸入?yún)?shù)，但同名函數(shù)返回值的類型數(shù)據(jù)必須相同
??? create or replace package overload is
??? function get_sal(eno number) return number;
??? function get_sal(name varchar2) return number;
??? procedure fire_employee(eno number);
??? procedure fire_employee(name varchar2);
?? 2) 建立包體
??? 。。。
3) 使用包構(gòu)造過程
?初始化全局變量
?1 建立包規(guī)范
?? create or replace packiage emp_package
?? is
?? minsal number(6,2);
?? maxsal number(6,2);
?? procedure upd_sal(eno number,salary number);
?? procedure upd_sal(name varhcar2,salary number);
?? end;-- 定義了兩全局變量和三個公用過程
?? 2 建立包體
??? create or replace package body emp_package is
????? procedure add_employee(cno number,name varchar2,salary number,dno number)
????? is
????? begin
??????? if salary between minsal and maxsal then
????????? insert into emp(empno,ename,sal,deptno)
???????? ........
??? -- 構(gòu)造過程，位于子程序尾部，已begin 開始已end 結(jié)束
???? begin
?????? select min(sal),max(sal) into minsal,maxsal from emp;
???? end;
????? end;
3 調(diào)用包公用組建
?? 在同一次會話中第一次調(diào)用包的公用組建時，會自動執(zhí)行其它構(gòu)造函數(shù)，而將來調(diào)用其他組建時則不會再調(diào)用其構(gòu)造過程。
4 使用純度級別
?1 家里包規(guī)范
? create or replcace package purity is
? minsal number(6,2);
? maxsal number(6,2);
? function max_sal return number;
? function min_sal return number;
? pragma restrict_references(max_sal,wnps);--wnps 不能修改包的變量（不能給包的變量賦值）??????????????????????????????????????????? --wnds 不能執(zhí)行dml
? pragma restrict_references(min_sal,wnps);--rnps 用于限制函數(shù)不能讀取包變量
? end;
1 What are Constrains
? 1) Constrains enforce on the table level
? 2) Constrains the deletion of a table if there are dependencies
2 Constrain Guidelines
? 1) Name a constraint or the oracle generate a name by the sys_cn format
? 2) Create a constraint either
???? --At the same time as the table is created.or
???? --After the table has been created
? 3)Define a constraint at the column or table level
? 4)view constraint in the data dictionary
3 Crete a constraint
? create table test2
? (id int not null,-- column level
?? lname varchar(20),
?? fname varchar(20),
?? constraint uk_test2_1 unique(lname,fname))--table level
4 The not null Constraint
? create table employees(
?? employee_id number(6),
?? last_name?? varchar2(25) not null? --system named
?? hire_date?? DATE
?????????????? constraint emp_hire_date not null --User named
5Foreign key
? create table test3
? (rid int,
?? name varchar(30),
?? constraint fk_test3_1 foreign key(rid) reference test2(id));
? froeign key constraint keywords
??? foreign key :Define the column in thee child table at the table constrain level.
??? references? :Identifies the table and column in the parent table.
??? on delete cascade: Delete the dependent rows in the child table when a row in the???? parent table is deleted
??? on delete set null:Convert the dependent foreign key values to null when a row in the
??? parent table is deleted.
??
??? --parent table referenced table
??? --child table refernce other table
6 The check Constraint
? Define a condition that each row must be satisfy
? alter table test3
? add constrain ch_test3 check(name like 's%')
7 Dropping a Constraint
? 1) Remove the manager constraint form the employee table
?? alter table test3
?? drop constriant test3_manager_fk
? 2) Remove the primary key constraint on the departments table and drop the associated
?? foreign key constraint on the employees.department_id column
?? alter table departments
?? drop primary key cascade
8 Disabling and enable Constraints
? 1)Execute the disable clause of the alter table statment to deactive an integrity constraint
? 2)Apply the cascade option to disable dependent integrity constrints
? alter table employees
? disable constraint emp_emp_id_pl cascade
? 3) enabling Constraints
? .Active an integrity constraint currently disabled in the table definition by using the enable clause.
?? alter table employees
?? enable constraint emp_emp_id_pk;
? a unique? or a primary? index is automatically created if you enable a unique key or a primary key constraint?
?8 View Constraints
? select constraint_name,constriant_type,serch_condition
? from user_constraints
? where table_name='employees'
?9 view the columns associated with constraints
?select constraint_name,column_name
?from user_cons_columns
?where table_name='employees'
1Why Use Views
? to restrict data access
? to make complex query easy
? to provide data independence
? to provide defferent view of the same data
2 Creating a View
? 1)create [or replace] [force|noforce] view view
? as subquery
? force : create view wether the referenced object existed or not
?
? desc view_name;
?2)create a view by using column aliases in the subquery
? create view salv50
? as select employee_idIO_NUMBER,last_name NAME,slaary*12 ANN_SALARY
? from employees
? where department_id=50;
3 Modigy a View
? 1) Modigy the emplvu80 view by using create or replace view clause.Add an alias for each column name;
?? create or replace view empvu80
?? (id_number,name,sal,department_id)
?? as select employee_id,first_name||" "||last_name,salary.department_id
?? from employees
?? where department_id=80;
?? column aliases in the create view clause are listed in the same order as the columns in the subquery
?? note : alter view_name is not a valid command.
4 Create a Complex View
? Create a complex view that contains group functions to display values from two tables
? create view dept_sum_vu
?? (name,minsal,maxsal,avgsal)
? as
?? select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
?? from employees e,departments d
?? where e.department_id=d.department_id
?? group by d.department_name;
5 Rules for performs DML operaton on a view
? 1) You can perform DML operation on simple views
? 2) You can not romove a row if the view contains the following:
??? --group functions
??? --a group by clause
??? --the distinct keyword
??? -- rownum keyword
??? -- column defined by expressions
6 Using the with check option Clause
? 1) you can ensure that dml operatons performed on the view stay within the domain of the view by using the with check option clause.
? create view test1
? as
? select * from emp where qty>10;
? with check option;
? update testview1 set qty=10
? where ster_id=6830;
? --when you doing the following update operation
? update testview1 set qty=5 where id=10;
? -- an error will report
? --you violate the where clause
? 2)Any attempt to change the department number for any row in the view fails because it violates the with check option constraint
?? create or replace view empvu20
?? as
?? select * where department_id=20
?? with check option constriant empvu20_ck;
7 Denying DML Operations
? 1 You can ensure that no dml operations occur by adding the with read only option to your view definition.
? 2)Any attempt to a DML on any row in the view resuls in an oralce server error.
8 remove veiw
? drop view_name
9 inline view
? 1) an inline view is a subquery with an alias that you can use within a sql statement.
? 2) a named subquery in the from clause of the main query is an exqmple of an inline view
? 3) an inline view is not a schema object.
10 Top-N Analysis
?1)Top_N querise ask for the n largest or smallest values of a column.
?2)Both largest values and smallest values sets considered Top-N queries
? select * from (select ster_id,qty from sales);
?example
? To display the top three earner names and salaries from the employees
? select rownum as rank,last_name,salary
? from (select last_anme,slary from employee
??????? order by slary desc)
? where rownum<=3;
?
1 觸發(fā)器簡介
? 1) 觸發(fā)事件
? 2) 觸發(fā)條件
? 3) 觸發(fā)操作
???? . 觸發(fā)器代碼的大小不能超過32k，如果使用大量代碼建立觸發(fā)器，應(yīng)該先建立存儲過程，然后再觸發(fā)器中使用call語句調(diào)用存儲過程。
???? . 觸發(fā)器中正能含有select ，insert，update 和delete 語句，而不能含有ddl 語句，和事物控制語句。
2 建立dml 觸發(fā)器
?1) 觸發(fā)時機
? before，after 表示在執(zhí)行dml操作之后觸發(fā)器
?2)觸發(fā)事件
? insert ，update 和delete 操作。也可以使用書法事件
?3) dml 觸發(fā)器是針對特定表進行的 因此必須制定dml 操作所對應(yīng)的表
?4) 觸發(fā)器類型 用于指定當(dāng)觸發(fā)器事件之后，需要執(zhí)行幾次觸發(fā)器操作。如果指定語句觸發(fā)器類型
那么會執(zhí)行一次觸發(fā)器代碼：如果指定行觸發(fā)器類型，則會在每個被作用行上執(zhí)行一次觸發(fā)器代碼。
?5) 觸發(fā)條件
?用于指定執(zhí)行行觸發(fā)器代碼的條件，只有為ture時，才會執(zhí)行行觸發(fā)器代碼。
? 6) 如果使用pl/sql 存儲過程，java 存儲過程，或外部處處過程需要在觸發(fā)器操作部分直接使用call
? 7) dml 觸發(fā)器觸發(fā)順序
?? (1)dml 觸發(fā)器在單行數(shù)據(jù)上的觸發(fā)順序。
??? 對于單行數(shù)據(jù)而言，無論是語句此觸發(fā)器，還是行觸發(fā)器，觸發(fā)器代碼實際只執(zhí)行一次，并且執(zhí)行
順序為before 語句觸發(fā)器，before 行觸發(fā)器，dml 操作，after 行觸發(fā)器，after 語句觸發(fā)器
?? (2) dml 觸發(fā)器在多行數(shù)據(jù)上的觸發(fā)順序
??? before 語句觸發(fā)器
??? before 行觸發(fā)器
??? after 行觸發(fā)器
??? before行觸發(fā)器
??? after 行觸發(fā)器
??? after語句觸發(fā)器
?? 語句觸發(fā)器只被執(zhí)行一次，而行觸發(fā)器在每個行上都執(zhí)行一次。
? 2) 語句觸發(fā)器
? 當(dāng)審計dml 操作，或確保dml操作安全執(zhí)行時，可以使用語句觸發(fā)器
? 1 建立before 語句觸發(fā)器
?? create or replace trigger tr_sec_emp
?? before insert or update or delete on emp
?? begin
???? if to_char(sysdate,'DY','nls_dtate_language=AMERICAN') in ('sat','sun') then
???? railse_application_error(-200001,'不能在休息日改變雇員信息');
???? end if;
?? end;?
?? 2 使用條件謂詞
?? inserting ,updating ,deleting
?? create or replace trigger tr_sec_emp
?? before insert or update or delete on emp
?? begin
???? if to_char(sysdate,'DY','nls_date_language=american')
????? in('sat','sun') then
???? case
?????? when inserting then
???????? raise_application('-20001','inserting');
?????? when updating then
???????? raise_application('-20002','updating');
?????? when deleting then
???????? raise_application('-20003','deleting');
???? end case;
??? end if;
?? end;
?? 3 建立after 語句觸發(fā)器
??? 為了dml 操作，或者dml 操作后執(zhí)行匯總運算
?? create table aduit_table(
???? name varchar2(20),ins int,upd int,del int,
???? starttime date,endtime date
?? );
?? create or replace trigger tr_aduit_emp
?? after insert or update or delete emp
?? declare
???? v_temp int;
?? begin
???? select count(*) into v_temp from aduit_table
?????? where name='emp';
???? if v_temp=0 then
?????? insert into audit_table values
?????? ('emp',0,0,0,sysdate,null);
???? end if;
???? case
?????? when? inserting then
???????? update aduit_table set ins=ins+1,endtime=sysdate where name='emp';
?????? when updating then
???????? update audit_table set upd=upd+1,endtime=sysdate where name='emp';
?????? when deleting then
???????? update aduit_table set del=del+1,endtime=sysdate where name='emp';
?? end;
? 3) 行觸發(fā)器
?? 審計數(shù)據(jù)變化可以使用行觸發(fā)器
?? 1 建立不before 行觸發(fā)器
??? 為了取保數(shù)據(jù)符合商業(yè)邏輯或企業(yè)規(guī)則，對輸入的數(shù)據(jù)進行復(fù)雜的約束，可以使用before行觸發(fā)器
???? create or replace trigger tr_emp_sal
???? before update of sal on emp
???? for each row
???? begin
?????? if :new.sal<:old.sla then
???????? raisse_application_error(-200010,'工資只漲不降');
?????? end if；
???? end;
???? 2) 建立after 行觸發(fā)器
???? 為了審計dml 操作，可以使用語句觸發(fā)器或oracle 系統(tǒng)提供的審計功能，而為了審計數(shù)據(jù)變化
，則應(yīng)該使用after 行觸發(fā)器
???? create table audit_emp_change(
?????? name varchar2(10),odl number(6,2),
?????? newsal number(6,2),time date);
??? create or replace trigger tr_sal_change
??? after update of sal on emp
??? for each row
??? declare
???? v_temp int;
??? begin
???? select count(*) into v_temp from audit_emp_change where name=:old.ename;
??? if v_temp =0 then
????? insert into audit_emp_change
??????? values(:old,ename,:old.sal,:new,sal,sysdate);
??? else
????? update audit_emp_change
??????? set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;?
??? end if;
??? end;
???? )
??? 3) 限制行觸發(fā)器
??? 當(dāng)使用行觸發(fā)器，默認(rèn)情況下會咱每個被作用行上七星一次觸發(fā)器代碼，為了時得再特定條件下執(zhí)行行觸發(fā)器代碼，需要使用when 子句
??? create or replace trigger tr_sal_change
??? after update of sal on emp
??? for each row
??? when(old.job='salesman')
??? declare
?????? v_temp int..
2 dml 觸發(fā)器使用注意事項
? 觸發(fā)器代碼不能從觸發(fā)器所對應(yīng)的基表中讀取數(shù)據(jù)
3 dml 觸發(fā)器
? 為了保證數(shù)據(jù)庫滿足特定的商業(yè)規(guī)則或企業(yè)邏輯，可以使用約束，觸發(fā)器和子程序。約束性能最好，實現(xiàn)最簡單，所以為售選，如果觸發(fā)器不盟實現(xiàn)，可以選擇觸發(fā)器。
? dml 觸發(fā)器可以用于實現(xiàn)數(shù)據(jù)安全保護，數(shù)據(jù)審計，數(shù)據(jù)完整性，參照完整性，數(shù)據(jù)復(fù)制等功能。
?1) 控制數(shù)據(jù)安全
? create or replace trigger tr_emp_time
? before insert or update or delete on emp
? begin
??? if to_char(sysdate,'hh24') not between '9' and '17' then
????? raise_application_error(-20101,'not work time');
???? end if;
? end;
? 2) 實現(xiàn)數(shù)據(jù)審計
? 使用數(shù)據(jù)審計只能審計sql 操作，而不會記載數(shù)據(jù)變化
? audit insert,update,delete on emp by access
? 3)實現(xiàn)數(shù)據(jù)完整性
? 首選約束 alter table emp add constraint ck_sal check (sal>=800),但是在有些情況下只能使用觸發(fā)器來實現(xiàn)數(shù)據(jù)完整性
?? create or replace trigger tr_check sal
?? before update of sal on emp
?? for each row
?? when (new.sla<old.sal or new.sal>1.2* old.sal)
?? begin
????? raise_application_error(,,,,,,)
?? end;
? 3) 使用引用完整性
? 采用 on delete cascade 可以進行集聯(lián)刪除，但是卻不能進行集聯(lián)更新。采用觸發(fā)器實現(xiàn)集聯(lián)更新
?? create or replace trigger tr_update
?? after update of sal on emp
?? for each row
?? begin
???? update emp set depno=:new.deptno where dentno=:old.deptno;
?? end;
4 建立instead of 觸發(fā)器
? 對于簡單視圖可以直接進行insert update 和delete 等操作，但是對于復(fù)雜視圖不允許進行insert，update 和delete 操作。
? 滿足一下條件的為復(fù)雜視圖
??? 具有操作集合符 union，union all ，intersect，minus
??? 具有分組函數(shù) min，max，avg，sum，count
??? 具有g(shù)roup by connect 編譯 或start with
??? 具有distinct
??? 具有連接
? 為了在復(fù)雜視圖上執(zhí)行dml 操作，必須要基于instead-of 觸發(fā)器，建立instead－of 觸發(fā)器后，就可以基于復(fù)雜視圖執(zhí)行insert，update和delete 語句。
?? instead of 選項只使用于視圖
?? 基于視圖建立觸發(fā)器時，不能定義before 和 after
?? 在建立視圖時不能指定 with check option
?? 當(dāng)建立instead of 觸發(fā)器時，必須指定for each row 選項
? 1) 建立復(fù)雜視圖dept_emp
?? create or replace view dept_emp as
?? select a.deptno,a.dname,b,empno,b,ename
?? from dept a,emp b
?? where a,deptno=b.deptno;
? 2) 建立 instead－of 觸發(fā)器
?? create of replacee trigger tr_instead_of_dept_emp
?? instead of insert on dept_emp
?? for each row
?? declare
???? v_temp int;
?? beegin
????? select count(*) into v_temp from dept where deptno=:new.deptno;
????? if v_temp=0 then
??????? insert into dept(deptno,dname) values(:new.deptno,:new.dname);
????? end if;
????? select count(*)into v_temp from emp where empno=:new.empno;
????? if v_temp=0 then
???????? insert into emp(empno,ename,deptno)
?????????? values(:new.deptno,:new.ename,:new.deptno);
???????? end if;
?? end;
??
?? 可以對視圖執(zhí)行insert 操作了
??? insert into dept_emp values(50,'admin','1223','mary')
5 管理觸發(fā)器
? 1) 顯示觸發(fā)器信息
??? select trigger_name,status from user_triggers
??? where table_name='emp';
?? 2)禁止觸發(fā)器
??? alter trigger tr_check_sal disable;
?? 3) 激活觸發(fā)器
??? alter trigger tr_check_sal enable;
?? 4) 禁止或激活表上的所有觸發(fā)器
??? alter table emp disable all triggers;
??? alter table emo eanble all triggers;?
?? 5)重新編譯觸發(fā)器
??? alter trigger tr_check_sal compile;
?? 6) 刪除觸發(fā)器
??? drop trigger tr_check_sal;
1 pl/sql 集合 處理單列多行數(shù)據(jù)庫，使用的類型為標(biāo)量類型
?1）索引表
? type ename_table_type is table of emp.ename%type
??? index by binary_integer;
? ename_table ename_table_type;
? begin
??? select ename into ename_table(-1) from emp
????? where empno=&no;
??? dbms_output.put_line('雇員名：'||ename_table(-1));
? end;
?
?? set serveroutput no
?? declare
???? type area_table_type is table of number
??????? index by varchar2(10);
???? rea_table area_table_type;
??? begin
??????? area_table('beijing'):=1;
??????? area_table('shanghai'):=2;
??????? area_table('guangzhou'):=3;
??????? dbms_output.put_line(area_table.first);
??????? dbms_output.put_line(area_table.last);
??? end;
???? 2) 嵌套表
????? 索引表類型不能作為累得數(shù)據(jù)類型使用，但是嵌套表可以作為表類的數(shù)據(jù)類型使用。
當(dāng)使用嵌套表元素時，必須先用其構(gòu)造方法初始化其嵌套表：
?????? a? 在pl/sql 塊中使用嵌套表
??????? declare
????????? type ename_table_type is table of emp.ename%type;
????????? ename_table ename_table_type;
??????? begin
?????????? ename_table:=eanme_table_type('2','2','3');
?????????? select ename into ename table(2) from emp where empno=&no;
?????????? dbms_ouput.put_line(ename_table(2));
??????? end;
????? b 在表中使用嵌套表
??????? create type phone_type is table of varchar2(20);
??????? create table employee(
????????? id number (4),name varchar2(10),sal number(6,2),
????????? phone phone_type
??????? )nested table phone store as phone_table;
?????? -- 為嵌套表插入數(shù)據(jù)
??????? insert into employee values(2,'scott',200,phone_type('2222','333333'));
?????? －－檢索嵌套表累得數(shù)據(jù)
??????? set serveroutput on
??????? declare
????????? phone_table phone_type;
??????? begin
????????? select phone into phone_table
????????? from employee where id=1;
????????? for i in 1..phone_table.count loop
??????????? dbms_output.put_line(phone_table(i));
????????? end loop;
??????? end;
?????? -- 更新嵌套表列的數(shù)據(jù)
???????? delcare
??????????? phone_table phone_type:=('44444','555555');
???????? begin
??????????? update employee set phone=phone_table
??????????? where id=1;
???????? end;
??? 3) 變長數(shù)組
????? 在使用varray 時必須指定最大個數(shù)，和數(shù)據(jù)類型,在使用其元素時必須進行初始化
????? type ename_table_type is varray(20) of emp.ename%type;
????? ename_table ename_table_type:=ename_table_type('1','2');
?????
????? -- 在快中使用varray
????? declare
???????? type ename_table_type is varray(20) of emp.ename％type；
???????? ename_table ename_table_type:=ename_table_type('mary');
???????? begin
??????????? select ename into ename_table(1) form emp
?????????????? where empno=&no;
????????? end;
????? --在表列中使用varray
?????? create type phone type is varray(20) of varchar2(20);
?????? create table employee(
???????? id number(4),name varchar2(10),
???????? sal number(6,2),phone phone_type);
??????
???? 3)記錄表
????? 記錄表結(jié)合了記錄和集合的優(yōu)點
??????? declare
????????? type emp_table_type is table of emp%rowtype
????????? index by binary_integer;
??????? emp_table emp_table_type;
??????? begin
????????? select * from into emp_table(1) from emp
????????? where empno=&no;
????????? dbms_output.put_line(emp_table(1).ename);
??????? end;
????? 4)多維集合
?????? 1 多級varray
?????? declare
??????? --define 一維集合
????????? type al_array_type is varray(10) of int;
??????? --定義二維集合
????????? type nal_varray_type is varray(10) of a1_varray_type;
??????? --初始化二維集合
????????? nvl nal_varray_type:=nal_varray_type(
??????? ???? a1_varray_type(1,2),
??????????? a1_varray_type(2,3)
????????? )
???????? beign
?????????? for i in 1..nal_varray_type.count loop
????????????? for j in 1..a1_array_type.count loop
??????????????? dbms_out.putline(nvl(i)(j));
????????????? end loop;
?????????? end loop;
??????? end;
?????? 2 使用多級嵌套表
??????? table a1_table_type is table of int;
??????? table nvl_table_type is table of a1_table_type;
??????? nvl nvl_table_type:=nvl_table_type(
????????? a1_table_type(1,2),
????????? a1_table_type(2,3)
??????? );
2 集合方法
? 1) exist
?? if ename_table.exists(1) then
??? ename_table(1):='scott';
?? 2) count 返回當(dāng)前集合變量中的元素總個數(shù)
??? ename_table.count
?? 3) limit 返回集合元素的最大個數(shù)? 只有varray 有
?? 4)first and last
?????? ename_table.first
?????? ename_table.last
?? 5) prior 和next
??? ename_table.prior(5); －－返回元素5的前一個
??? ename_table.next(5);? --? 后一個
?? 6) extend
??? 使用于varray 和 嵌套表。
??? extend add a null value
??? extend (n) add n null value
??? extend (n,i)add n i value
??? declare
????? type ename_table_type is varray(20) of varchar2(20);
????? ename_table ename_table_type;
??? begin
????? ename_table:=ename_table_type('mary');
????? ename_table.extend(5,1);
????? dbms_output.put_line(ename_table.count);
??? end;
?? 7) trim
?? trim remove one element from the tail of the collection.
?? trim(n) remove n element from the tail of the colleciton.
?? 8)delete
??? delete: delete all the elements
??? delete(n) :delete the nth elements
??? delete(m,n): delete the elements from m to n
3 集合賦值
? 1)將一個集合的數(shù)據(jù)賦值給另一個集合.clear the destination collectins and set the original collection
?? delcare
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array1 name_varray_type;
??? name_array2 name_varray_type;
?? begin
???? name_array1:=name_varray_type('scott','smith');
???? name_array2:=name_array_type('a','b','c');
???? name_array1:=name_array2;??
?? end;
??
?
? type name_array1_type is varray(4) of varchar2(10);
? type name_array2_type is varray(4) of varchar2(10);
? name_array1 name_array1_type;
? name_array2 name_array2_type;
? 具有相同的數(shù)據(jù)類型，單具有不同的集合類型不能構(gòu)賦值
? 2) 給集合賦城null 值
??? 可以使用delete 或 trim
??? 也可以使用 空集合賦給目表集合
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array name_varray_type;
??? name_empty name_varray_type;
???
??? name_array:=name_varray_type('1','2');
??? name_array:=name_empty;
? 3) 使用集合操作賦和比較集合都是10g 的內(nèi)容，p176 先略過。
4 批量綁定
? 執(zhí)行單詞sql 操作能傳遞所有集合元素的數(shù)據(jù)。
? 1 forall 語句
? 用于insert update 和delete操作。在oracle9i 中forall 語句必須具有連續(xù)的元素
??? 1) using forall on insert
???? declare
??????? type id_table_type is table of number(6)
??????? index by binary_integer;
??????? type name_table_type is table of varchar2(2)
??????? index by binary integer;
??????? id_table id_table_type;
??????? name_table name_table_type;
????? begin
???????? for i in 1..10 loop
?????????? id_table(i):=i;
?????????? name_table(i):='Name'||to_char(i);
???????? end loop;
???????? forall i in 1..id_table.count
?????????? insert into demo values(id_table(i),name_table(i));
????? end;
???? 2)using forall on using update
?????? forall i in 1..id_table.count
?????????? upate demo set name:=name_table(i)
????????????? where id:=id_table(i);
???? 3)using forall on using delete
??????? forall i in 1..id_table.count
??????????? delete from demo where id:=id_table(i);
???? 4) using forall on part of the collection
??????? for i in1..10 loop
????????? id_table(i):=i;
????????? name_table(i):="name"||to_char(i);
??????? end loop;
??????? forall i in 8..10 l
?????????? insert into demo values(id_table(i),name_table(i));
?? 2 bulk collect
???? is fit for select into ,fetch into and dml clause
???? 1) using bulk collect
????? declares??
??????? type emp_table_type is table of emp%rowtype
???????? index by binary_integer;
??????? emp_table emp_table_type;
????? begin
???????? select * bulk collect into emp_table
????????? from emp where deptno=&no;
???????? for i in 1..emp_tablee.count loop
??????????? dbms_output.put_line(emp_table(i).ename);
???????? end loop;
????? 2) 在dml 的返回字句使用bulk collect 字句
???????? declare
????????? type ename_table_type is table of emp.ename%type;
?????????? ename_table ename_table_type;
????????? begin
???????????? deletee from emp where deptno=&no
???????????? returning ename bulk_collect into ename_table;
????????? for i in 1..ename_table.count loop
??????????? dbms_output.put(ename_table(i));
????????? end loop;
??????? end;
????????? end;
????? end;
1 subquery: is a select statement embedded in other sql statement.
? .the subquery execute (inner query) once before the main query
? .the result of the subquery is used by the main query.
2 pairwise comarison subquery
?select * from employee
?where (manager_id,department_id) in
????????? (select manager_id,department_id
?????????? from employees
?????????? where employee_id in (178,174))
?and employee_id not in (178,174);
?nonpairwise comparison subquery
?select employee_id,manager_id,department_id
?from employee
?where manager_id in
????????????????? (select manager_id
?????????????????? from employees
?????????????????? where employee id in (174,141))
?and department_id in
?????????????????? (select department_id
??????????????????? from employees
??????????????????? where employee_id in (174,141))
?and employ_id not in (174,141);
????????????????? )
3 using a subquery in the from clause
? select a.last_name,a,salary,b.slaavg
? from employees a ,(select department_id,
????????????????????? avg(salary) salavg
????????????????????? from?? employees
????????????????????? group by department_id) b
?? where a.department_id=b.department_id
?? and a.salary>b.salavg;
4 scalar subquery expressions
? . a scalar subquery expression is a subquery that return exactly on column value from one row
? . in oracle8i scalar subqueries can be used in condition and expression part and all clause.
?1) sclaar subqueries in casse expression
?? select employee_id ,last_name,
?? (case
??? when department_id=
????????? (select department_id from departments
????????? where location_id=1800)
??? then 'canada'
??? else 'usa'
?? end) location
?from employees
?? 2)scalar subqueries in order by clasue
?? select employee_id,last_name
?? from employees e
?? order by (select department_name
???????????? from departments d
???????????? where e.department_id=d.department);
4 correlated subqueries
?? the wubquery references a column form a table in the parment query
?? select column1,solumn2....
?? from table1 outer
?? where column1 operator
???????????????????????? (select column1,column2
????????????????????????? from table2
????????????????????????? where expr1=out.expr2);
? e.g 1
?? select last_name,salary,department_id
?? from employees outer
?? where salary>
?????????????? (select avg(salary)
??????????????? from employees
??????????????? where department_id=
?????????????????? outer.department_id);
?????????????? )
?? e.g 2
??? display details of those employees who have switched jobs at lease twice
??? select e.employee_id,last_name,e.job_id
??? from employees e
??? where 2<=(select count(*)
????????????? from job_history
????????????? where employee_id=e.employee_id);
6 using the exists operator
? . the exists operator tests for existencee of rows in the results set of the subquery
? . if a subquery row value id found:
???? the search does not continue in the inner query
???? the condition is flagged true
? .if a subquery row value is not fount
???? the condition is flagged fasle
???? the search continues in the inner query
?e.g
??? find employees who have at least one person reporting to them
??? select employee_id,last_name,job_id,department_id
??? from employees outer
??? where exists (select count(*)
???????????????? from employees
???????????????? where manager_id=outer.employee_id);
? not exist.
7 corelated update
??? use a correlated subquery to update rows in on table based on rows from another table
?? e.g
??? --denormalize the employees table by adding a column to store the department name
??? alter table employees
??? add(department_name varchar2(14));
?? --populate the table by using a correlated update
??? update employees e
??? set department_name=
?????????????????????? (select department_name
??????????????????????? from departments d
??????????????????????? where e.departmentid=d.department);
8 correlated delete
??? delete test1 t1
??? where ster_id in(select ster_id form sales t2 where t.ster_id=t2.ster_id);?
9 using the with clause ,you can use the same query block in a a select statement when it cocurs more than once within a complex query
? the with clause retrieves the results of a query block and stores it in the user's the user's templary tablespace
? the with clause improves performance.
?e.g
?? with
?? dept_costs as(
??? select d.department_name,sum(e.salary) as dept_total
??? from employee e,departments d
??? where e,department_id=d.department_id
??? group by d.department_name),
?? avg_cost as(
???? select sum(dept_total)/count(*) as dept_avg
???? from dept_cost)
? select *
? from dept_costs
? where dept_total>(select dept_avg
??????????????????? from afb_cost)
? order by department_name;