2006年10月11日
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
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;
1 动态sql 简介
2
1 使用execute immediate 处理ddl 操作
create or replacee procedure drop_table(table_name varchar2)
is
sql_statement varchar2(100);
begin
sql_statement:='drop table'||table_name;
execute immediate sql_statement;
调用
exec drop_table('worker');
end;
2) 使用 execute immediate 处理dcl 操作
create or replace procedure grant_sys_priv
(priv varchar2,username varchar2)
is
begin
sql_stat:='gruant'||priv||'to'||username;
execute immediate sql_stat;
end;
exec grant_sys_priv('create session','scott');
3 使用execute immediate 处理dml 操作
1) 处理无占位符和returning 子句的dml 语句
delcare
sql_stat varchar2(100);
begin
sql_stat:='update emp set sal=sal*1.1 where deptno=44';
execute immediate sql_stat;
end;
2) 处理包含占位符的dml语句
delare
sql_stat varchar2(100);
begin
sql_stat:='update emp set sql=sql*(1+:percent/100)'
||'where deptno=:dno';
execute immediate sql_stat using &1,&2;
end;
3) 处理包含returning 子句的dml语句
declare
salary number(6,2);
sql_stat varchar2(200);
begin
sql_stat:='update emp set sal=sal*(1:percent/100)'
||'where empno=:eno returning sal into :salary';
execute immediate sql_stat using &1,&2;
returning into salary;
end;
输入1的值 15
输入2的值 2222
新工资；2223
4) 使用execute immediate 处理单行查询
declare
sql_stat varcchar2(100);
emp_record emp%rowtype;
begin
sql_stat:='select * from emp where empno=:eno';
execute immediate sql_stat into emp_record using &1;
end;
3 处理多行查询语句
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
emp record emp%rowtype;
sql_stat varchar2(100);
begin
sql_stat:='select * from em where deptno=:dno';
open emp_cv for sql_stat using &dno;
loop
fetch emp_cu into emp_record;
exit when emp_cv%notfound;
end loop;
close emp_cv;
end;
4 在动态sql 中使用bulk语句
1) 在 execute immediate 语句中使用动态bulk 语句
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
type sal_table_type is table of emp.sal%type
index by binary_integer;
ename_table ename_table_type;
sa_table sal_table_type;
sal_stat varchar2(100);
begin
sql_stat:='update emp set sal=sal*(1+:percent/100)'
|| 'where deptno=:dno'
||'returning ename,sal into :name,:salary';
execut immediate sql_stat using &percent,&dno
returning bulk collect into ename_table,sal_table;
for i in 1..ename_table.count loop
....
end loop;
end;
2) 使用bulk 子句处理多行查询
sql_stat:='select ename from emp where deptno=:dno';
execute immediate sql_stat bulk collect into ename_table using &dno;
3) 在fetch 语句中使用bulk 子句
declare
type empcurtyp is ref cursor;
emp_cv empcurtyp;
type ename_table_type is table of emp.ename%type;
index by binary_integer;
ename_table ename_table_type;
sql_stat varchar2(100);
begin
sql_stat:='select ename from emp where job:=title';
open emp_cv for sql_stat using '&job';
fetch emp_cv bulk collect into ename_table;
4) 在forall 语句中使用bulk 子句
declare
type ename_table_type is table of emp.ename%type;
type sla_table_type is table of emp.sal%type;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat varchar2(100);
begin
ename_table:=ename_table_type('scott','smith','clark');
sql_stat:='update emp set sal=sal*1.1 where ename=:1'
||'returning sal into :2';
forall i in 1..ename_table.count
execite immediate sql_stat using ename_table(i)
returning bulk collect into sal_table;
end;
1
1)system parameter file -- spfile<sid>,spfile (oracle 9i or later) is a binary file ,it cann't been edited by the text editor. you can change it through "alter system XXX";
in oracle 9.2.0,spfile is stored in the directory of ora92\datatase\spfile<sid>.ora.
2)init parameter file(pfile)--init<sid>.ora (early version) is a text file.it is stored in admin\<sid>\pfile.
2
Entries are specific to the instance being accessed;
there are two kinds of parameters:
explicit: having an entry in the file
implicit: no entry within the file,but assuming the oracle default values;
Multiple files can be used for a single datasbase to optimize performance in different situation.
static parameter file,pfile (init.ora)
persistent parameter file,spfile(spfile<sid>.ora);
3 order being used
first spfile<sid> then int<sid>.ora
select name,value from v$system_parameter
4 spfile spfilesid.ora
1)Binary file with the abiility to make changes persistent across shutdown and startup
2)Maintained by oracle server
3)Record parameter value change made with the alter system command
4)Can specify wether the change being made is tempory or persistent
5)value can be delete or reset to allow an instance to revert to the default value;
e.g
alter system set timed_statistics=false scope=memory;
alter system set timed_statistics=false scope=spfile;
startup force-- restart oracle
select name,value from v$system_parameter where name like 'time%';
alter system set timed_statistics=false scope=both;
6) not all entries can be modified.to see more ,you can lool up v$system_parameter.in this table the isses_modifiable and issys_modifiable will tell which one can be modified in session,and which one can be modified in sys, and which one cann't be modified.
e.g
select name,isses_modifiable from v$system_parameter where isses_modifiable='true';
alter session set timed_statistics=false;
e.g
select name,issys_modifiable from v$system_parameter where issys_modifiable<>'false';
.issys_modifiable may have two values immediate or deffered.
immediate means alter this entry will take effect on the current session ,while derrered will take effect on a new session.
5 pfile initsid.ora
.the pfile is a text file than can be modigied with an operation system editor.
.modifications to the file are made manually.
.changes to the file take effect on the next startup
6 Creating an spfile
spfile can be created from initsid.ora file using thee create spfile command,whick can be executed before or after instance startup.
create spfile from pfile. -- you must shutdown the instance befofe or you can use the fellow statement:
create spfile='d:\oracle\oracle\ora92\database\spfile.ora' from pfile;
yo caan backup the
7 Oracle Managed files(OMF)
1) OMF are created and deleted by the oracle server as directed by sql commands
2) OMF are established by parameters:
DB_CREATE_DEST: set to give the default location for data files
DB__CREATE_OMLINE_LOG_DEST_N: set to gieve the default locations for online redo logs and control files,up to a mazimum of 5 locations.
e.g 1
show parameter db_create
e.g 2
create tablespace test1;--report error,Do not konw where create the data file
alter system set db_create_file_dest='d:\oracle\oradb';
create tablespace test1; -- is ok;
e.g 3
alter database add logilfe group 6;
-- if you do not specify the db_create_online_log_dest_N ,the log file will create at --the location which is specified by the db_create_file_dest.
e.g 4
drop table test1; -- oracle alse remove it's physicial file;
alter database drop logfile group 6-- also romove the physical file.
e.g 5
create tablespace test1
datafile 'd:\oracle\oradb\test1.dbf' size 2m;
drop tablespace test1 -- if you create tablespace or others object in this way ,you --will cann't remove the physical file.
drop table test1 including comtents and datafiles;
8 Starting up a database mount
startup nomomount :instance started for this instance;create db.create controlfule
1) create alert_sid.log startup log which is stored in <oracle_home>\admin\<sid>\bdump
2) start instance include allocating memory and start bpprocess
select * from v$instance;
select * from v$bgprocess;
select * from V$sga;
alter database mount;
open control files for the instance;
select* from v$database;
select* from v$tablespace;
select* from datafile;
select* from log;
alter database open;
all files opened as describled by the control file for this instance;
9 start up
start pfile=...\initsid.ora; (alter session set nls_language=american)
alter database open read only; you can not change database to read only after the database has been created.
startup database restrict;
alter system enable restricted session;-- only people with restricted privilege can --access the database;
alter restricted session to kkk;
alter disable restricted session
10 opening a database in read only mode
a databse can be opened as read only database alter database open read only;
a read-only database can be used to :
execute queries
execute disk sorts using locally managed
take data files offline and online ,not tableespaces
perform recovery of offline data files and tablespace;
11Shutting down teh database
normal: wait until current sessions end,wait until current trasactions end ,force a checkpoint and close files.
tansactional:wail until current transaction end.force a checkpoint and close files
immediate :force a checkpoint and close the files
abort : do nothing ,just close the files. when startup ,need recovery.
12 Managing an instance by monitoring Diagnostic Files
Diagnostic files contain information about significant events encounted while the instance is operational.
.user to resolve problem or to better manager the database.
.server types of dignostic files exist:
alertsid.log --which location is specified by the background_dump_dest --entry in the initsid.ora.tje default value is --<ora_home>\admin\sid\bdump
background trace files -- the same as above;
user_tace files
13 alert log file
the alertsid.log file recored the command and result of major event while the database is operational.
.it is userd for day-to-day operational information or dignosing database errors
.ench entry has a time stamp associated with it.
.the dba manager the alertsid.log file.
.its location is defined by background_dump_dest.
14 enabling or disabling user tracing
. sessin level using the alter session
alter session set sql_trace=true;
session level by execcuting dbms
1 hierachical Queries
select [level],colun,expr......
from table
[where condition(s)]
[start with condition(s)]
[connect by prior condition(s)]
where condition
exprcompparison_operator expr
2 Starting Point
.Specifies the condition that must be met
.Accepts any valid condition
3 Waling the Tree
connect by prior column1=column2
walk from the top donw ,using the employees table
top donw
column1=parentkey
column2=childkey
bottom up
column1=child key
column2=parent key
select level,last_name
from employees
start with last_name='king'
connect by prior employee_id=manager_id;
1 over of multitable insert statements
1)the insert...select statement can be userd to insert row into multiple table as part of a single dml statement.
2) multitable insert statements can be used in data warehousing systems to transfer data from one or more operational sources to source to a set of target table.
3) they providde significant performance improvement over
single dml versuls multiple insert...select statement
single dml versus a proceedduree to do mutiple inserts using if ,,, then syntax.
2
unconditional insert
insert all
into sal_history values (EMPID,HIREDATE,SAL)
into mgr_history values (EMPID,MGR,SAL)
select employee_id EMPID,hire_date JIREDATE,
salary SAL,manager_id MGR
from employees
where employee_id>200;
3 Conditional insert all
insert all
when sal>1000 then
into sal_history values(empid,hiredate,sal)
when mgr>200 then
into mgr_history values(empid,mgr,sal)
select emp_id empid,hire_date hiredate,salary sal,manager_id mgr,
from employees
where employee_id>200;
4 Conditional first insert
insert first
when sal >25000 then
into special_sal values(deptid,sal)
when hiredate like ('%00%') then
into hiredate_history_00 values(deptid,hiredate)
when hiredate like ('%99%') then
insert hiredate_history_99 values(ddeptid,hiredate)
else
into hiredate_history values(deptid,hiredate)
select ddepartmeent_id deptid,sum(salary) sal,
max(hire_date) hiredate
from employees
group by department_id;
5 Pivoting insert
insert all
into sales_info values (employee_id,week_id,sales_mon)
into sales_info values (employee_id,week_id,sales_tue)
into sales_info values (employee_id,week_id,sales_wed)
into sales_info values (employee_id,week_id,sales_thur)
into sales_info values (employee_id,week_id,sales_fri)
select employee_id,weekid,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri
from sales_source_data;
6 create index with create table statement
create table new_emp
(employee_id number(6)
primary key using index
(create index emp_id_idx on new_emp(employee_id)),
first_name varchar2(20),
last_name varchar2(25)
)
1 Profiles
1)a profile is a named set of password and resource limits
2)Profiles are assigned to user by the create user or alter user command
3)can be enable or disable
4)can relate to the default profile.
2 Password Management
Password history,account locking,password expiration and aging ,password verificcation.
3Enabling Password Management
1)set up passwordd management by using profiles and assign them to users
2)lock unlock and expire accounts usign the create user or alter user
3)alwarys enforcing
e.g
create user test identified by test;
alter user test account lock;
alter user test account unlock;
alteer user test password expire;
4 Passowrd Account locking
FAIKED_LOGIN_ATTEMPS: number of failed login attemts before lockout of the account
PASSWORD_LOCK_TIME : number of days the account is locked after the specified number of failed login attemps
e.g
create profile profile1 limit
password_lock_time 1/1440 -- one muinuts
failed_login_attempts 3;
alter user test
profile profile1;
alter profile profile1 limit
passowrd_lock_time 1/24 --one hour
5 passowrd expiration and aging
passwowd_life_time lifetime of the passowrd in days after which the password expires(有效期)
password_grace_time grace period in days for changing the password after the first successful login afteer the password has expired(锁定期)
e.g
alter profile profile1 limit
password_life_time 2
password_grace_time 3;
6 password history
password_reuse_time:number of days before a passowrd and be resued
password _reuse_max:maxum number of times password can bee reused
e.g
alter profile profile1 limit
password_reuse_time 10
password_reuse_max 3;
7passowrd Verification(study latter)
8drop a profile
drop profile profile1 (cascade);
the user will use the default profile.it will take effect on the new session.
9 Resource Management
Resource mangement limits can be enforced at the session level,the call level or both
limits can be defined by profiles using the create profile command
enable resource limints with the
.resource_limit initialization parameter
alter system command
e.g
alter system set resource_limit=true;
10 setting a Resdource limits at session level
cup_per_session : total cpu time measured in hundredths of seconds (百分之一秒)
sessions_per_user: number of coucurrent sessions allowed for each username
connect_time:elapsed connect time measured in minutes
idle_time :periods of inactive time measured in minutes
logical_reads_per_session: number of data blocks
private_sga :measure in reads
e.g
alter profile profile1 limit
cpu_per_session 100000
connect_time 60
idle_time 5;
alter user test profile profile1
11 Setting Resource limits at call level
e.g
alter profile profile1
cpu_per_call 1000 -- cup time per call in
logical_reads_per_call --number of data balock that can be read per call
create profile develper_prof limit
session_per_user2
cpu_per_session 10000
idle_time 60
connect_time 480
12 Managing Resources using database resource manager
1)Provides the oracle server with more control over resorce management decisions
2)elements of database resorcee manager
resource consumer group
resourcee plan
resource allocation method
resource plan directives
3)dbms_resource_manager package is uerd to create and maintain elements
4)requires administer_resource_manager privilege
desc dbms_resoource_manager
13 0btaining password and resource limits informaiton
information about password and resource limits can be obtained by querying the data dictonary
dba_users
select * from users;
dba_profiles
select * from dba_profiles where profile='PROFILE1'
the addBatch() method is basically nothing more than a tool fro assigning a bunch of sql statements to a jdbc statement object for execution together
PreparedStatement stmt=conn.prepareStatement(
"update account set balance=? where acct_id=?");
int[] rows;
for(int i=0;i<accts.length;i++){
stmt.setInt(1,i);
stmt.setLong(2,i);
stmt.addBatch();
}
rows=stemt.executeBatch();
1 oracle 的实现
语句一
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;
语句二：
SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;
select * from (select rownum as numrow from table_name where numrow>80 and numrow<100 )
不能直接使用 select * from rownum>100 and rownum<200;
in oracle return null;
2 sql server 的实现
3 mysql 的实现
select id from table_name where id in
select * from (select rownum as numrow ,id from tabl_name)
where numrow>80 and num<100;
1 prepared sql
oracle provide two kinds of prepared SQL prepared statements and store procedures.Prepared SQL provide an advantage over the simple sql statements you have convered so far.if you execute the same prepared sql more than once,the database remains ready for your sql without having to rebuild the query plan.
1) Prepared Statements
PreparedStatement statement=conn.preparedStatement(
"update account set balance=? where id=?");
for(int i=0;i<accounts.length;i++){
statement.setFloat(1,accounts[i].getBalance());
statement.setInt(2,i);
statement.execut();
stateement.clearParameters();
}
commit();
statement.close;
2) Stored Procedure
try {
CallableStatement statement;
int i;
statement = c.prepareCall("{call sp_interest[(?,?)]}");
statement.registerOutParameter(2, java.sql.Types.FLOAT);
for(i=1; i<accounts.length; i++) {
statement.setInt(1, accounts[i].getId( ));
statement.execute( );
System.out.println("New balance: " + statement.getFloat(2));
}
c.commit( );
statement.close( );
c.close( );
}
本文阐述了怎么使用DBMS存储过程。我阐述了使用存储过程的基本的和高级特性，比如返回ResultSet。本文假设你对DBMS和JDBC已经非常熟悉，也假设你能够毫无障碍地阅读其它语言写成的代码（即不是Java的语言），但是，并不要求你有任何存储过程的编程经历。
存储过程是指保存在数据库并在数据库端执行的程序。你可以使用特殊的语法在Java类中调用存储过程。在调用时，存储过程的名称及指定的参数通过JDBC连接发送给DBMS，执行存储过程并通过连接（如果有）返回结果。
使用存储过程拥有和使用基于EJB或CORBA这样的应用服务器一样的好处。区别是存储过程可以从很多流行的DBMS中免费使用，而应用服务器大都非常昂贵。这并不只是许可证费用的问题。使用应用服务器所需要花费的管理、编写代码的费用，以及客户程序所增加的复杂性，都可以通过DBMS中的存储过程所整个地替代。
你可以使用Java，Python，Perl或C编写存储过程，但是通常使用你的DBMS所指定的特定语言。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL。这些语言都非常相似。在它们之间移植存储过程并不比在Sun的EJB规范不同实现版本之间移植Session Bean困难。并且，存储过程是为嵌入SQL所设计，这使得它们比Java或C等语言更加友好地方式表达数据库的机制。
因为存储过程运行在DBMS自身，这可以帮助减少应用程序中的等待时间。不是在Java代码中执行4个或5个SQL语句，而只需要在服务器端执行1个存储过程。网络上的数据往返次数的减少可以戏剧性地优化性能。
使用存储过程
简单的老的JDBC通过CallableStatement类支持存储过程的调用。该类实际上是PreparedStatement的一个子类。假设我们有一个poets数据库。数据库中有一个设置诗人逝世年龄的存储过程。下面是对老酒鬼Dylan Thomas（old soak Dylan Thomas，不指定是否有关典故、文化，请批评指正。译注）进行调用的详细代码：
try{
int age = 39;
String poetName = "dylan thomas";
CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, poetName);
proc.setInt(2, age);
cs.execute();
}catch (SQLException e){ // ....}
传给prepareCall方法的字串是存储过程调用的书写规范。它指定了存储过程的名称，？代表了你需要指定的参数。
和JDBC集成是存储过程的一个很大的便利：为了从应用中调用存储过程，不需要存根（stub）类或者配置文件，除了你的DBMS的JDBC驱动程序外什么也不需要。
当这段代码执行时，数据库的存储过程就被调用。我们没有去获取结果，因为该存储过程并不返回结果。执行成功或失败将通过例外得知。失败可能意味着调用存储过程时的失败（比如提供的一个参数的类型不正确），或者一个应用程序的失败（比如抛出一个例外指示在poets数据库中并不存在“Dylan Thomas”）
结合SQL操作与存储过程
映射Java对象到SQL表中的行相当简单，但是通常需要执行几个SQL语句；可能是一个SELECT查找ID，然后一个INSERT插入指定ID的数据。在高度规格化（符合更高的范式，译注）的数据库模式中，可能需要多个表的更新，因此需要更多的语句。Java代码会很快地膨胀，每一个语句的网络开销也迅速增加。
将这些SQL语句转移到一个存储过程中将大大简化代码，仅涉及一次网络调用。所有关联的SQL操作都可以在数据库内部发生。并且，存储过程语言，例如PL/SQL，允许使用SQL语法，这比Java代码更加自然。下面是我们早期的存储过程，使用Oracle的PL/SQL语言编写：
create procedure set_death_age(poet VARCHAR2, poet_age NUMBER)
poet_id NUMBER;
begin SELECT id INTO poet_id FROM poets WHERE name = poet;
INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age);
end set_death_age;
很独特？不。我打赌你一定期待看到一个poets表上的UPDATE。这也暗示了使用存储过程实现是多么容易的一件事情。set_death_age几乎可以肯定是一个很烂的实现。我们应该在poets表中添加一列来存储逝世年龄。Java代码中并不关心数据库模式是怎么实现的，因为它仅调用存储过程。我们以后可以改变数据库模式以提高性能，但是我们不必修改我们代码。
下面是调用上面存储过程的Java代码：
public static void setDeathAge(Poet dyingBard, int age) throws SQLException{
Connection con = null;
CallableStatement proc = null;
try {
con = connectionPool.getConnection();
proc = con.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, dyingBard.getName());
proc.setInt(2, age);
proc.execute();
}
finally {
try { proc.close(); }
catch (SQLException e) {}
con.close();
}
}
为了确保可维护性，建议使用像这儿这样的static方法。这也使得调用存储过程的代码集中在一个简单的模版代码中。如果你用到许多存储过程，就会发现仅需要拷贝、粘贴就可以创建新的方法。因为代码的模版化，甚至也可以通过脚本自动生产调用存储过程的代码。
Functions
存储过程可以有返回值，所以CallableStatement类有类似getResultSet这样的方法来获取返回值。当存储过程返回一个值时，你必须使用registerOutParameter方法告诉JDBC驱动器该值的SQL类型是什么。你也必须调整存储过程调用来指示该过程返回一个值。
下面接着上面的例子。这次我们查询Dylan Thomas逝世时的年龄。这次的存储过程使用PostgreSQL的pl/pgsql：
create function snuffed_it_when (VARCHAR) returns integer ''declare
poet_id NUMBER;
poet_age NUMBER;
begin
--first get the id associated with the poet.
SELECT id INTO poet_id FROM poets WHERE name = $1;
--get and return the age.
SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id;
return age;
end;'' language ''pl/pgsql'';
另外，注意pl/pgsql参数名通过Unix和DOS脚本的$n语法引用。同时，也注意嵌入的注释，这是和Java代码相比的另一个优越性。在Java中写这样的注释当然是可以的，但是看起来很凌乱，并且和SQL语句脱节，必须嵌入到Java String中。
下面是调用这个存储过程的Java代码：
connection.setAutoCommit(false);
CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString(2, poetName);
cs.execute();
int age = proc.getInt(2);
如果指定了错误的返回值类型会怎样？那么，当调用存储过程时将抛出一个RuntimeException，正如你在ResultSet操作中使用了一个错误的类型所碰到的一样。
复杂的返回值
关于存储过程的知识，很多人好像就熟悉我们所讨论的这些。如果这是存储过程的全部功能，那么存储过程就不是其它远程执行机制的替换方案了。存储过程的功能比这强大得多。
当你执行一个SQL查询时，DBMS创建一个叫做cursor（游标）的数据库对象，用于在返回结果中迭代每一行。ResultSet是当前时间点的游标的一个表示。这就是为什么没有缓存或者特定数据库的支持，你只能在ResultSet中向前移动。
某些DBMS允许从存储过程中返回游标的一个引用。JDBC并不支持这个功能，但是Oracle、PostgreSQL和DB2的JDBC驱动器都支持在ResultSet上打开到游标的指针（pointer）。
设想列出所有没有活到退休年龄的诗人，下面是完成这个功能的存储过程，返回一个打开的游标，同样也使用PostgreSQL的pl/pgsql语言：
create procedure list_early_deaths () return refcursor as ''declare
toesup refcursor;
begin
open toesup for SELECT poets.name, deaths.age FROM poets, deaths -- all entries in deaths are for poets. -- but the table might become generic.
WHERE poets.id = deaths.mort_id AND deaths.age < 60;
return toesup;
end;'' language ''plpgsql'';
下面是调用该存储过程的Java方法，将结果输出到PrintWriter：
PrintWriter:
static void sendEarlyDeaths(PrintWriter out){
Connection con = null;
CallableStatement toesUp = null;
try {
con = ConnectionPool.getConnection();
// PostgreSQL needs a transaction to do this... con.
setAutoCommit(false); // Setup the call.
CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }");
toesUp.registerOutParameter(1, Types.OTHER);
toesUp.execute();
ResultSet rs = (ResultSet) toesUp.getObject(1);
while (rs.next()) {
String name = rs.getString(1);
int age = rs.getInt(2);
out.println(name + " was " + age + " years old.");
}
rs.close();
}
catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close();
}
}
因为JDBC并不直接支持从存储过程中返回游标，我们使用Types.OTHER来指示存储过程的返回类型，然后调用getObject()方法并对返回值进行强制类型转换。
这个调用存储过程的Java方法是mapping的一个好例子。Mapping是对一个集上的操作进行抽象的方法。不是在这个过程上返回一个集，我们可以把操作传送进去执行。本例中，操作就是把ResultSet打印到一个输出流。这是一个值得举例的很常用的例子，下面是调用同一个存储过程的另外一个方法实现：
public class ProcessPoetDeaths{
public abstract void sendDeath(String name, int age);
}
static void mapEarlyDeaths(ProcessPoetDeaths mapper){
Connection con = null;
CallableStatement toesUp = null;
try {
con = ConnectionPool.getConnection();
con.setAutoCommit(false);
CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }");
toesUp.registerOutParameter(1, Types.OTHER);
toesUp.execute();
ResultSet rs = (ResultSet) toesUp.getObject(1);
while (rs.next()) {
String name = rs.getString(1);
int age = rs.getInt(2);
mapper.sendDeath(name, age);
}
rs.close();
} catch (SQLException e) { // We should protect these calls. toesUp.close();
con.close();
}
}
这允许在ResultSet数据上执行任意的处理，而不需要改变或者复制获取ResultSet的方法：
static void sendEarlyDeaths(final PrintWriter out){
ProcessPoetDeaths myMapper = new ProcessPoetDeaths() {
public void sendDeath(String name, int age) {
out.println(name + " was " + age + " years old.");
}
};
mapEarlyDeaths(myMapper);
}
这个方法使用ProcessPoetDeaths的一个匿名实例调用mapEarlyDeaths。该实例拥有sendDeath方法的一个实现，和我们上面的例子一样的方式把结果写入到输出流。当然，这个技巧并不是存储过程特有的，但是和存储过程中返回的ResultSet结合使用，是一个非常强大的工具。
结论
存储过程可以帮助你在代码中分离逻辑，这基本上总是有益的。这个分离的好处有：
• 快速创建应用，使用和应用一起改变和改善的数据库模式。
• 数据库模式可以在以后改变而不影响Java对象，当我们完成应用后，可以重新设计更好的模式。
• 存储过程通过更好的SQL嵌入使得复杂的SQL更容易理解。
• 编写存储过程比在Java中编写嵌入的SQL拥有更好的工具－－大部分编辑器都提供语法高亮！
• 存储过程可以在任何SQL命令行中测试，这使得调试更加容易。
并不是所有的数据库都支持存储过程，但是存在许多很棒的实现，包括免费/开源的和非免费的，所以移植并不是一个问题。Oracle、PostgreSQL和DB2都有类似的存储过程语言，并且有在线的社区很好地支持。
存储过程工具很多，有像TOAD或TORA这样的编辑器、调试器和IDE，提供了编写、维护PL/SQL或pl/pgsql的强大的环境。
存储过程确实增加了你的代码的开销，但是它们和大多数的应用服务器相比，开销小得多。如果你的代码复杂到需要使用DBMS，我建议整个采用存储过程的方式。
1 不是使用了spring ，hibernate 等企业级产品的框架，我们就是企业级产品了。不是我们采用了新瓶装旧酒的web 2.0 我们就走在技术的前沿了。我门所需要的是一个高性能的，健壮的 产品，是一个可以降低我们实施成本，一个可以树立我们企业品牌的产品。在这里我不得不对我们产品的所谓的架构们产品疑问，Archetectures,what are you doing？
2 在实现框架代码的时候，当你对采用那种实现方式犹豫不决的时，换个角度，想一想如果你是程序员，喜欢怎么这些框架。在实现框架的时候一定要考虑程序员是否能够理解你写框架的思路，除非万不得已不要用一些自以为很高明很巧妙，然而却很晦涩难懂的方法，那样的框架，程序员至少合格的程序员是不愿意使用的。我想程序员和编码工人最大的区别就是程序员不仅要知其然，还要知其所以然。
3 只有在不断实践中，才能激发你不断的求知欲。只有把学到的知识不断的应用道实践中，你才能在学习中得到满足。不要为了学习而学习（学院派，不好听点就是纸上谈兵），而是要从实际问题出发，在解决问题的过程中不断深入，不断总结，所以说，当你离开了编程的第一线，你将失去学习编程知识的欲望。当然如果你愿意，在别的领域还有更广阔的天空，但是请不要总是说自己原来编程怎么怎么，其实你已经被三振出局了。
4 想外行一样思考，想专家一样实践，一本书的名字，虽然书没有看过，但她的名子就已经非常有意思了。这岂不就是我们作需求，和作架构时的座右铭吗？既能象“外行”一样的站在客户的角度思考问题，又能象“专家”一样参与到整个产品的开发和实施当中，在实践中不断提高自我。然而，不幸的是许许多多的所谓的架构师，系统分析员们却正向着相反的方向迈进。“真正”的做到了，象“专家”一样思考，象“外行”一样实践，可悲呀可悲。
5设计做到什么样才叫做到位呢。我想只有真正的开发者才有权利发言。只有有它们才是设计的真正使用者和受害者。因为就我所知和所见，绝大多数设计都是设计者自己的游戏（当然，我可能是井底之蛙了没有见过什么好的设计），程序员所开发往往还是对着原形自己再进行一遍设计，且不说额外增加了多少工作量，浪费了多少时间，就工作质量而言，也是差强人意。毕竟大多数情况下，设计者或称为架构师的在技术方面的经验都更为丰富，对业务的理解也更为深入，另外由一个人进行设计在功能复用，和整体性能方面的考虑也更完整一些。但怎么做才能熊掌和鱼兼得呢？下面我发表一下我个人的看法：
1 代码就是最好的设计，这句话不是我说的，是 xp开发届 中的一位大牛说的。之所以在这里引用别人的观点，并不是自己是一个xp 的fans，也并不时完全赞同xp 的理论，我只是觉得这句话得太对了，对程序员来说什么设计比代码读起来更亲切呢？。其实设计无非是向开发所着传达设计者的思想，告诉开发者系统需要开什么个对象，具有什么属性和行为，它们之间的调用关系又如何。我们在设计文档中经常使用的方法就是有class 图，协作图，和顺序图对上面所提到的进行描述。然而结果呢，面对这大量的令人畏惧的抽象图表，开发者可选择的也只有是“重整江河待后生了”。想想，这样的设计和代码能够同步吗，这样的设计文档还有什么用呢？所以说与其是这样还不如把设计变成代码，如对象属性可以这直接在代码中体现，方法可以只定义接口，实现方式可以作为代码的注释，向写需求分析用例似的来一步一步说明程序是需要怎样调用。当客户要求设文档的时候，只需要提出javadoc就可以了，而其保证和代码同步。而开发者呢，在开发前需要阅读用例，了解需求，然后在设计者已经搭好的代码框架中进行开发就可以了。如果需要修改的话，不用在去设计文档中更改，只需要修改一下代码注释就可以了，（程序员是比较懒的，不怎么愿意写写文档的）。当然了，让懒惰的程序员能够自觉地写好文档也不是一件容易事，下面也许能给你提供一个好的方法
2 交差开发能够帮助完成最好的设计文档。
3 设计者在开发阶段还作什么呢？
待续