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'中無權限
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? 開發過程
?1) 建立過程：不帶參數
? create or replace procedure out_time
? is
? begin
??? dbms_output.put_line(systimestamp);
? end;
? a 使用execute 命令調用過程
?? set? serveroutput on
?? exec out_time;
? b 使用call 命令調用過程
?? set serveroutput on
?? call out_time();
?2) 建立過程：帶有in參數 默認為輸入參數，另外也可以使用in 關鍵子顯示的定義
?? 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,'雇員號不能重復');
????? when e_integrity then
??????? raise_application_error(-20001,'部門號不存在');
???
調用
?? exec add_employee(111,'clark',200,'manager',10);
?? 3 建立過程，帶有out 參數
??? 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;
???
??? 調用
??? var name varchar2(10)
??? var salary number
??? exec query_employee(7788,:name,:salary);
??? print name,salary;
??? 4 建立過程，帶有in out 參數
??? 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;?
???
??? 調用
??? var n1 number
??? var n2 number
??? exec :n1:=100;
??? exec :n2:=30
??? exec compute(:n1,:n2)
??? print n1 n2
??? 5) 為參數傳遞變量和數據 位置傳遞，名稱傳遞，組合傳遞
??? 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) 顯示當前用戶的所有程序及其源代碼
???? select text from user_source where name='add_dept';
?? 7) 刪除過程
???? drop procedure add_dept;
2 開發函數
? 可以在sql語句和其他子過程中執行。
? 1 建立函數，不帶任何參數
? 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;
? --使用變量接收函數返回值
?? var v2 varchar2(100)
?? exec :v1:=get_user
?? print v1;
?-- 在sql 語句中直接調用函數
?? select get_user from dual;
?-- 使用dbms_output 調用函數
?? set serveroutput on
?? exec dbms_output.put_line('get_user');
?2) 建立函數，帶有in 參數
?? 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) 建立函數，帶有out 參數
?? 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 參數
? 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) 函數調用的限制
? -- 在sql 語句中只能調用存儲函數
? -- 在sql 語句中只能調用帶有輸入參數in ，而不能有輸出參數out 和in out 參數的函數
? -- 在sql 語句中調用的函數的參數必須是標準的sql 數據類型，不能是pl/sql 所特有的數據類型
? -- 在sql 語句中調用的函數不能包含insert，update 和delete
?6) 查看源代碼
? set pagesize 40
? select text form user_source where name='result';
?7) 刪除函數
? drop function result;
?3 管理子程序
? 1) 列出當前用戶的子程序
?? 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
??? --使用數據字典user_errors? 確定錯誤原因和位置
???? select line||'/'||position as "line/col",text error
???? from user_errors where name='raise_salary';
?? 4) 列出對象的依賴關系
??? -- 使用 user_dependenciess 確定直接依賴關系
??? select name,type from user_dependencies
??? where referenced_name='emp'
??? -- 使用 deptree 和 ideptree 確定依賴和見解依賴關系
??? select nested_level,name,type from deptree;
?? 5) 重新編譯子程序
???? 當被應用對象的結構被修改以后，就會將相關依賴對象轉變為無效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） 例外分類
? 預定義分類，非預定義分類，自定義例外。
2 處理預定義例外
? 1) 常用預定義例外
?? 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('必須使用構造方法初始化集合元素');
??? end;
??? d currsor_already_open
??? reopen curosr 如果用戶已經使用了open 命令打開了顯示游標，或執行for循環(隱式的打開游標)
?????? 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("游標已經打開");
?????? end;
???? e dup_val_on_index
???? begin
???? exception
??????? when dup_val_on_index then
??????? dbms_output.put_line("列上不能出現重復值");
???? 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("游標沒有打開");
???? 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('沒有返回結果');
?????? 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('超出下標范圍');
????? end;
????? k subscript_outside_limit
?????? emp_array(-1);
????? l value_error the length of variable cannot contain the actual value;
????? declare
????? begin
????? end;
3 處理非預定義例外
? 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 錯誤沒有任何聯系，為業務邏輯所定義的例外
? 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 使用錯誤例外函數
? 使用例外函數可以取得錯誤號以及相關的錯誤消息，sqlcode 用于取得oracle 的錯誤號，而sqlerrm
則用于取得與之相關的錯誤信息。
? 1 sqlcode 和 sqlerrm
? 為了在pl/sql 應用程序中處理其他為預料的到的oracle 錯誤，用戶可以在例外處理部分的
when others 自句后，引用兩個函數
? 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
?? 只能在子程序中使用（過程，函數，包，觸發器）
?? 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) 建立包規范： 用于定義包的公共組建，包括常量，變量，游標，過程和函數等
?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) 建立包體
?? 用于實現包規范所定義的過程和函數。在包體中也可以單獨定義私有組件，包括變量，常量，過程和函數等。但在包體中所定義的組件只能在包內使用，而不能由其他子程序引用。
?? 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) 調用包組建
??? －－ 在同一個包內調用組建 不需要加包名前綴，直接調用
? 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;
?? -- 調用包公用變量
?? exec emp_package.g_deptno:=20
?? -- 調用公用過程
?? exec emp_package.add_employee(111,'mary,2000');
?? -- 調用遠程數據庫包的公用組件
?? 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 使用包重載
? 指多個具有相同名字的子程序，在調用的時候使用不同的參數傳遞。
? 1) 建立包規范
?? 同名過程和函數必須具有不同的輸入參數，但同名函數返回值的類型數據必須相同
??? 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) 使用包構造過程
?初始化全局變量
?1 建立包規范
?? 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)
???????? ........
??? -- 構造過程，位于子程序尾部，已begin 開始已end 結束
???? begin
?????? select min(sal),max(sal) into minsal,maxsal from emp;
???? end;
????? end;
3 調用包公用組建
?? 在同一次會話中第一次調用包的公用組建時，會自動執行其它構造函數，而將來調用其他組建時則不會再調用其構造過程。
4 使用純度級別
?1 家里包規范
? 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 不能執行dml
? pragma restrict_references(min_sal,wnps);--rnps 用于限制函數不能讀取包變量
? 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 觸發器簡介
? 1) 觸發事件
? 2) 觸發條件
? 3) 觸發操作
???? . 觸發器代碼的大小不能超過32k，如果使用大量代碼建立觸發器，應該先建立存儲過程，然后再觸發器中使用call語句調用存儲過程。
???? . 觸發器中正能含有select ，insert，update 和delete 語句，而不能含有ddl 語句，和事物控制語句。
2 建立dml 觸發器
?1) 觸發時機
? before，after 表示在執行dml操作之后觸發器
?2)觸發事件
? insert ，update 和delete 操作。也可以使用書法事件
?3) dml 觸發器是針對特定表進行的 因此必須制定dml 操作所對應的表
?4) 觸發器類型 用于指定當觸發器事件之后，需要執行幾次觸發器操作。如果指定語句觸發器類型
那么會執行一次觸發器代碼：如果指定行觸發器類型，則會在每個被作用行上執行一次觸發器代碼。
?5) 觸發條件
?用于指定執行行觸發器代碼的條件，只有為ture時，才會執行行觸發器代碼。
? 6) 如果使用pl/sql 存儲過程，java 存儲過程，或外部處處過程需要在觸發器操作部分直接使用call
? 7) dml 觸發器觸發順序
?? (1)dml 觸發器在單行數據上的觸發順序。
??? 對于單行數據而言，無論是語句此觸發器，還是行觸發器，觸發器代碼實際只執行一次，并且執行
順序為before 語句觸發器，before 行觸發器，dml 操作，after 行觸發器，after 語句觸發器
?? (2) dml 觸發器在多行數據上的觸發順序
??? before 語句觸發器
??? before 行觸發器
??? after 行觸發器
??? before行觸發器
??? after 行觸發器
??? after語句觸發器
?? 語句觸發器只被執行一次，而行觸發器在每個行上都執行一次。
? 2) 語句觸發器
? 當審計dml 操作，或確保dml操作安全執行時，可以使用語句觸發器
? 1 建立before 語句觸發器
?? 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 語句觸發器
??? 為了dml 操作，或者dml 操作后執行匯總運算
?? 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) 行觸發器
?? 審計數據變化可以使用行觸發器
?? 1 建立不before 行觸發器
??? 為了取保數據符合商業邏輯或企業規則，對輸入的數據進行復雜的約束，可以使用before行觸發器
???? 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 行觸發器
???? 為了審計dml 操作，可以使用語句觸發器或oracle 系統提供的審計功能，而為了審計數據變化
，則應該使用after 行觸發器
???? 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) 限制行觸發器
??? 當使用行觸發器，默認情況下會咱每個被作用行上七星一次觸發器代碼，為了時得再特定條件下執行行觸發器代碼，需要使用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 觸發器使用注意事項
? 觸發器代碼不能從觸發器所對應的基表中讀取數據
3 dml 觸發器
? 為了保證數據庫滿足特定的商業規則或企業邏輯，可以使用約束，觸發器和子程序。約束性能最好，實現最簡單，所以為售選，如果觸發器不盟實現，可以選擇觸發器。
? dml 觸發器可以用于實現數據安全保護，數據審計，數據完整性，參照完整性，數據復制等功能。
?1) 控制數據安全
? 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) 實現數據審計
? 使用數據審計只能審計sql 操作，而不會記載數據變化
? audit insert,update,delete on emp by access
? 3)實現數據完整性
? 首選約束 alter table emp add constraint ck_sal check (sal>=800),但是在有些情況下只能使用觸發器來實現數據完整性
?? 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 可以進行集聯刪除，但是卻不能進行集聯更新。采用觸發器實現集聯更新
?? 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 觸發器
? 對于簡單視圖可以直接進行insert update 和delete 等操作，但是對于復雜視圖不允許進行insert，update 和delete 操作。
? 滿足一下條件的為復雜視圖
??? 具有操作集合符 union，union all ，intersect，minus
??? 具有分組函數 min，max，avg，sum，count
??? 具有group by connect 編譯 或start with
??? 具有distinct
??? 具有連接
? 為了在復雜視圖上執行dml 操作，必須要基于instead-of 觸發器，建立instead－of 觸發器后，就可以基于復雜視圖執行insert，update和delete 語句。
?? instead of 選項只使用于視圖
?? 基于視圖建立觸發器時，不能定義before 和 after
?? 在建立視圖時不能指定 with check option
?? 當建立instead of 觸發器時，必須指定for each row 選項
? 1) 建立復雜視圖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 觸發器
?? 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;
??
?? 可以對視圖執行insert 操作了
??? insert into dept_emp values(50,'admin','1223','mary')
5 管理觸發器
? 1) 顯示觸發器信息
??? select trigger_name,status from user_triggers
??? where table_name='emp';
?? 2)禁止觸發器
??? alter trigger tr_check_sal disable;
?? 3) 激活觸發器
??? alter trigger tr_check_sal enable;
?? 4) 禁止或激活表上的所有觸發器
??? alter table emp disable all triggers;
??? alter table emo eanble all triggers;?
?? 5)重新編譯觸發器
??? alter trigger tr_check_sal compile;
?? 6) 刪除觸發器
??? drop trigger tr_check_sal;
1 pl/sql 集合 處理單列多行數據庫，使用的類型為標量類型
?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) 嵌套表
????? 索引表類型不能作為累得數據類型使用，但是嵌套表可以作為表類的數據類型使用。
當使用嵌套表元素時，必須先用其構造方法初始化其嵌套表：
?????? 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;
?????? -- 為嵌套表插入數據
??????? insert into employee values(2,'scott',200,phone_type('2222','333333'));
?????? －－檢索嵌套表累得數據
??????? 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;
?????? -- 更新嵌套表列的數據
???????? delcare
??????????? phone_table phone_type:=('44444','555555');
???????? begin
??????????? update employee set phone=phone_table
??????????? where id=1;
???????? end;
??? 3) 變長數組
????? 在使用varray 時必須指定最大個數，和數據類型,在使用其元素時必須進行初始化
????? 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)記錄表
????? 記錄表結合了記錄和集合的優點
??????? 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 返回當前集合變量中的元素總個數
??? ename_table.count
?? 3) limit 返回集合元素的最大個數? 只有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)將一個集合的數據賦值給另一個集合.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;
? 具有相同的數據類型，單具有不同的集合類型不能構賦值
? 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 的內容，p176 先略過。
4 批量綁定
? 執行單詞sql 操作能傳遞所有集合元素的數據。
? 1 forall 語句
? 用于insert update 和delete操作。在oracle9i 中forall 語句必須具有連續的元素
??? 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;