Oracle汇总笔记

http://gzf_PC:1158/em
数据库配置文件已经安装到 D:\WorkC,同时其他选定的安装组件也已经安装到 D:\WorkC\oracle。
iSQL*Plus URL 为:
http://gzf_PC:5560/isqlplus
iSQL*Plus DBA URL 为:
http://gzf_PC:5560/isqlplus/dba
-- 创建表
drop table test;
create table test(
Id int,
UName varchar2(20),
constraint ucon unique(Id)
);
-- 创建的同时 指定主键值；
create table cdpt(
id number(6),
name varchar2(30),
constraint pk_id primary key(id)
);
insert into test values(1,‘郭泽峰‘);
select * from test;
-- 复制表和数据(不复制表约束)
create table testback as select * from test;
-- 复制表结构
drop table testback;
create table testback as select * from test where 1=2;
-- 创建索引
create index i_1 on test(uname asc);
-- 给表或视图取一个别名
create synonym testother for test;
-- 添加列
alter table test add (other int)
-- 删除列
alter table test modify (other varchar2(50))
alter table test drop (other)
-- 修改表名
rename test11 to test
-- 添加外键约束
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);
--
/*
5 .对已经存在的表添加约束
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
示例：
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);

6 .对表里的约束禁用;
ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;

7 .对表里的约束重新启用;
ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;

8 .删除表中约束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
示例:
ALTER TABLE emp drop CONSTRAINT <Primary key>;

*/
-- 删除
drop table aa
drop index aa
drop synonym aa -- 删除别名
-- 删除
SELECT [DISTINCT] <column1 [as new name] ,columns2,...>
FROM <table1>
[WHERE <条件>]
[GROUP BY <column_list>]
[HAVING <条件>]
[ORDER BY <column_list> [ASC|DESC]]
--
select * from emp where not deptno=10;
select * from emp where empno in(7788,7369,7499);
select * from emp where sal between 1000 and 3000;
select * from emp where ename like ‘_C%‘;
--
select * from emp where sal=(select max(sal) from emp);
--找出比deptno=30的员工最低工资高的其他部门的员工
select * from emp where sal>ANY(select sal from emp where deptno=30) and deptno<>30;
-- 找出比deptno=30的员工最高工资高的其他部门的员工
select * from emp where sal>ALL(select sal from emp where deptno=30) and deptno<>30;
-- 两个表链接
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
--
select * from emp where rownum<=10;--查询前10行记录
--注意ROWNUM只能为1 因此不能写 select * from emp where rownum between 20 and 30;
--
select * from emp where rownum<=3 and empno not in (select empno from emp where rownum<=3);
---结果可以返回整个数据的3-6行;
-- oracle分页查询：效率最低
select * from (select t.*,row_number() over(order by Id desc) rk from test t) where rk<5 and rk>2;
--另一种分页效率稍微高[必须先小于后大于]
select * from(select t.*,rownum rn from(select * from test order by Id asc) t where rownum<7) where rn>5;
-- 效率比较低
SELECT b.* FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM test order by Id) A ) b WHERE b.RN BETWEEN 4 AND 5
--

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM test) A
WHERE ROWNUM <= 5
)
WHERE RN >= 3
-- 创建视图
drop view testView
create view testView
as
select Id from test
-- 创建存储过程 (修改 or replace)
create procedure guo(
a int,
)
as
begin
select a;
end

/*
Create 创建
Sequence 序列 seqEmop 序列名称
Increment by 步长
Stat with 1 开始值
Maxvalue 最大值
Minvalue 最小值
*/
-- 创建序列：

Create sequence seq_cdpt
Increment by 1
Start with 1
Maxvalue 999999
Minvalue 1
Nocycle
nocache;

-- 修改序列
Alter sequence seqEmp maxvalue 5;
-- 查询序列
Select seq_cdpt.nextval from dual;
Select seq_cdpt.currval from dual;

-- 使用序列
insert into test(Id) values(444);
insert into test(Id) values(seq_cdpt.nextVal);
commit;-- 必须有commit;
select * from user_indexes where table=‘test‘ ;

-- 查看数据及log
select name,log_mode from v$database;
-- 查看实例名字
select instance_name,status from v$instance;
--
select tablespace_name,status,extent_management from dba_tablespaces;
--查看控制文件位置
select name from v$controlfile;
-- 查看数据文件的位置和大小
select name,bytes from v$datafile;
--
SELECT VIEW_NAME FROM USER_VIEWS;
--
SELECT * FROM all_views t where view_name=‘testview‘
--与
select object_name from user_objects where object_type=‘VIEW‘
-- 查看视图内容
select text from user_views where view_name=upper(‘testview‘);
-- 变量声明：
declare
guo nvarchar2(50);
begin
guo:=‘234‘;
dbms_output.put_line(guo);
end;
-- 查看oracle安装平台：
select * from v$version;
-- 查看控制文件：
select name from v$controlfile;
-- 查看日志文件
select member from v$logfile;
-- 查看数据库对象：
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
-- 查看数据库版本：
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=‘Oracle‘;
-- 查看数据库日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
-- 创建用户：
CREATE USER <user_name> [profile "DEFAULT"]
identified by "<password>" [default tablespace "USERS"]
-- 删除用户：
DROP USER <user_name> CASCADE
-- 创建角色：
CREATE ROLE <role_name>
identified by "<password>"
-- 删除角色
DROP ROLE <role_name>
--
select * from user_tables
--
--修改密码
alter user gzf identified by gzf;
-- 查询查看当前用户拥有的角色权限信息
select * from role_sys_privs;
-- 查询当前用户
select * from user_users;
-- 查看当前用户的角色信息
select * from user_role_privs;
-- 删除权限：
revoke resource from gzf;
--授予用户权限：
grant connect,resource to gzf;
grant select on scott.Test to gzf;
--删除权限：
revoke select on scott.Test from gzf;
--查询当前时间
select sysdate from dual
/*
boolean:true,false,null;用number(1)来标示；
*/
-- 变量声明：如果错误的话，将用exception输出；
declare guo int:=123123123;
res varchar2(50);
begin
--输出：
guo:=2*2+3/0;
-- set serveroutput on;
res:=To_Char(guo);
dbms_output.put_line(guo);
exception
when others then
dbms_output.put_line(‘error‘);
-- commit;
end;
-- 类型在转换
select to_char(12) from dual;
select to_date(‘2005-01-01 13:14:20‘,‘yyyy-MM-dd HH24:mi:ss‘) from dual;
select to_date(‘2005-01-03‘,‘yyyy-dd-MM‘) from dual;
select to_number(‘123‘) as cc from dual;
-- 打印变量 -- number也存储小数，所以整形，建议用binary_integer;
declare a number:=123.12;
b nvarchar2(10);
c boolean:=true;
begin
b:=to_char(a);
dbms_output.put_line(b);
exception
-- 啥意思？
when others then
dbms_output.put_line(‘error‘);
end;
--
--number:如果第二个参数是负数的话，说明四舍五入整数位，123.123 （5,-2）结果：100
declare a number(5,-5):=123.23;
begin
dbms_output.put_line(a);
end;
--多变量声明
declare a int;b int;
begin
a:=123;b:=234;
dbms_output.put_line(a);
end;
-- record类型：v与v1值可以互换；
declare
type guo_type is record(
u_Id int,
u_Name varchar2(50)
);
v guo_type;
v1 guo_type;
begin
v.u_Id:=123;
v.u_Name:=‘sdfsdf‘;
dbms_output.put_line(v.u_Name);
end;
-- table类型：相当于数组，可以存储record类型
declare
type ta is table of varchar2(50) index by binary_integer;
va ta;
begin
va(100):=‘gzf0‘;
va(101):=‘gzf1‘;
va(102):=‘gzf2‘;
dbms_output.put_line(‘索引为100的数据记录va(100)：‘||va(100));
dbms_output.put_line(‘第一条记记录va.first：‘||va(va.first));
dbms_output.put_line(‘最后一条记录va.last：‘||va(va.last));
dbms_output.put_line(‘第二条记录的前一条记录va.prior：‘||va(va.prior(101)));-- 101是索引
dbms_output.put_line(‘第二条记录的后一条记录‘||va(va.next(101)));
dbms_output.put_line(‘总记录va.count：‘||to_char(va.count));
end;
-- 存储record类型：
declare
type re is record(id varchar2(50));
type ta is table of re index by binary_integer;
va ta;
begin
va(100).id:=‘gzf123‘;
dbms_output.put_line(va(100).id);
end;
-- 定义多维度表变量:好像只能填充一条数据：
declare
type tt is table of test%rowtype index by binary_integer;
t tt;
begin
select * BULK COLLECT into t from test where id>0;
for aa in 1..t.count loop
dbms_output.put_line(t(aa).uname);
end loop;
end;
select * from test;
-- 设置类型为表中某个字段类型,这样一旦表类型变化，变量类型也跟着变化；
declare
guo test.ID%type;
begin
guo:=123;
dbms_output.put_line(guo);
end;
-- 行类型：
declare
guo test%rowtype;
begin
select * into guo from test where id=1;
dbms_output.put_line(guo.UNAME||‘sfsdf‘);
end;
--控制语句：if elsif else end if;
declare
a int:=25;
begin
if a>0 and a<10 then
dbms_output.put_line(‘1‘);
elsif a>20 and a<40 then
dbms_output.put_line(‘2‘);
else
dbms_output.put_line(‘3‘);
end if;
end;
--loop: if then exit;也可以，exit when ;也可以
declare
guo int:=0;
begin
loop
guo:=guo+1;
dbms_output.put_line(guo);
if guo=10 then
exit;
end if;
end loop;
end;
-- 或
declare
guo int:=0;
begin
loop
guo:=guo+1;
dbms_output.put_line(guo);
exit when guo=5;
end loop;
end;
-- while循环
declare
guo int:=0;
begin
while guo<10 loop
guo:=guo+1;
dbms_output.put_line(guo);
end loop;
end;
-- for循环:
declare
guo int:=0;
begin
for a in 1..10 loop
guo:=guo+1;
dbms_output.put_line(guo);
end loop;
end;
--异常
begin
dbms_output.put_line(1/0);
exception
when others then
dbms_output.put_line(‘error‘);
end;
-- 游标
declare
t test%rowtype;
cursor c is select * from test where id>0;
begin
open c;
loop
fetch c into t;
dbms_output.put_line(t.UName);
exit when c%notfound;
end loop;
close c;
end;
-- 存储过程：out在类型前面；---像varchar2千万不要加长度否则会报错...好变态啊...
create or replace procedure guo11(
iid int,
uname out varchar2
) is
begin
uname:=‘123rrrr‘;
end guo11;
--
create or replace procedure guo12(
iid int
)
is
begin
dbms_output.put_line(iid);
end guo12;
--
create or replace procedure guo13
is
begin
dbms_output.put_line(123);
end;
--创建包：--------一些函数或存储过程的包头；

--创建包头

create or replace package package_demo is

function Getage(birthst varchar,birthend varchar) return integer;

function Getsalary(VFpsncode varchar) return number;

end package_demo;

--创建包体

create or replace package body package_demo is

function Getage(birthst varchar,birthend varchar) return integer --得到年龄函数

is

V_birth integer;

ToDateEnd Date;

Toyear number(4);

Tomonth number(4);

Fromyear number(4);

Frommonth number(4);

begin

if (birthend=‘‘) or (birthend is null) then

select sysdate into ToDateEnd from dual; --得到系统时间

end if;

Toyear := to_number(to_char(ToDateEnd,‘YYYY‘)); --得到最后年月

Tomonth := to_number(to_char(ToDateEnd,‘MM‘));

Fromyear := to_number(substr(birthst,1,4));--计算的年月

Frommonth := to_number(substr(birthst,6,2));

if Tomonth-Frommonth>0 then V_birth:=Toyear-fromyear;

else V_birth:=Toyear-fromyear-1;

end if;

return(V_birth);

end Getage;

function getSalary(VFpsncode varchar) return number--返回工资情况
is
V_psnSalary number(8,2);
begin
Select 2 into V_psnSalary from dual;

return(V_psnSalary);

end getSalary;

end package_demo;

-- 调用包体：
select package_demo.getsalary(‘C001‘) from dual;
-- 存储过程返回结果集；********************************************************存储过程**************************
----------------------声明一个Package--------------
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype); --Package中声明名为get 的Procedure(只有接口没内容)
END pkg_test;
---声明包体
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT * FROM test;
ELSE
sqlstr :=
‘select *
from test where id=:w_id‘; --w_id是个参数，
--以下 p_rc是个REF CURSOR游标类型，而且是OUT型参数，即可返回一个记录集了。USING p_id就是替换上面SQL中:w_id值拉:)
OPEN p_rc FOR sqlstr USING p_id;　
END IF;
END get;
END pkg_test;
--简单存储过程。。。。。。。。。。。。。。
CREATE OR REPLACE PROCEDURE getcity (
citycode IN int,
ref_cursor OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */
)
AS
BEGIN
OPEN ref_cursor FOR
SELECT *
FROM test
WHERE id != citycode;
END;

-- 调用存储过程
declare
t test%rowtype;
cur sys_refcursor;
begin
getcity(1,cur);
-- open cur; --这里游标已经打开，所以不用继续打开
loop
fetch cur into t;
dbms_output.put_line(t.UNAME);
exit when cur%notfound;
end loop;
close cur;
end;



-- 存储过程：参数可以缺省;
/*
create procedure guo(
iid int,
uname varchar,
)
--执行存储过程（默认缺省）
begin
guo();
end
制定一个参数：
begin
guo(uname=>‘cheng‘);
end
*/
begin
guo11();
end;
-- 执行：
declare
uname varchar2(20);
iid int:=123;
begin
guo11(iid,uname);
dbms_output.put_line(uname);
end;
--执行
-- 授予权限...
GRANT SELECT ANY TABLE TO scott WITH ADMIN OPTION;
-- 函数：创建函数：
create or replace function fun(vaName in varchar2)
return varchar2
is
begin
return ‘111‘||vaName;
end;

-- 调用函数：
declare
myresult varchar2(20);
begin
myresult:=fun(‘hhhh‘);
dbms_output.put_line(myresult);
end;
-- 打印带out参数的结果：
create or replace function fun1(vaName in varchar2,vaout out varchar2)
return varchar2
is
begin
vaout:=‘gzf000‘;
return ‘111‘||vaName;
end;
-- 查询结果：
declare
one varchar2(20);
two varchar2(20);
begin
one:=fun1(‘fff‘,two);
dbms_output.put_line(one);
dbms_output.put_line(two);
end;
-- 创建自增列...
Oracle没有自增字段这样的功能，但是通过触发器(trigger)和序列(sequence)可以实现。

先建一个测试表了：

create table userlogin
(

id number(6) not null,

name varchar2(30) not null primary key

)
tablespace users
/
第一步：创建SEQUENCE
create sequence userlogin_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;
第二步：创建一个基于该表的before insert 触发器，在触发器中使用刚创建的SEQUENCE
create or replace trigger userlogin_trigger
before insert on userlogin
for each row
begin
select userlogin_seq.nextval into:new.id from sys.dual ;
end;

-- 创建触发器：
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
BEFORE DELETE --指定触发时机为删除操作前触发
ON scott.emp
FOR EACH ROW --说明创建的是行级触发器
BEGIN
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
IF (TO_CHAR(sysdate,‘DAY‘) IN (‘星期六‘, ‘星期日‘)) OR (TO_CHAR(sysdate, ‘HH24:MI‘) NOT BETWEEN ‘08:30‘ AND ‘18:00‘) THEN
RAISE_APPLICATION_ERROR(-20001, ‘不是上班时间，不能修改departments表‘);
END IF;
END;

例3：限定只对部门号为80的记录进行行触发器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
CASE
WHEN UPDATING (‘salary‘) THEN
IF :NEW.salary < :old.salary THEN

RAISE_APPLICATION_ERROR(-20001, ‘部门80的人员的工资不能降‘);
END IF;
WHEN UPDATING (‘commission_pct‘) THEN

IF :NEW.commission_pct < :old.commission_pct THEN
RAISE_APPLICATION_ERROR(-20002, ‘部门80的人员的奖金不能降‘);
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, ‘不能删除部门80的人员记录‘);
END CASE;
END;

/*
实例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
*/

例4：利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、
自动的更新子表countries表中原来在该地区的国家的region_id。


CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘旧的region_id值是‘||:old.region_id
||‘、新的region_id值是‘||:new.region_id);
UPDATE countries SET region_id = :new.region_id
WHERE region_id = :old.region_id;
END;
例5：在触发器中调用过程。

CREATE OR REPLACE PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

--创建触发器调用存储过程...
CREATE OR REPLACE TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;

8.2.3 创建替代(INSTEAD OF)触发器

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;
--- 变量还可以既取值又赋值...
/*
create or replace function fun1(vaName in out varchar2)
*/
-- 存储过程可以参数缺省值：为参数指定默认参数...
-- 表复制：

-- 子查询...
--select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
-- 外联
-- select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
--
-- delete from info where not exists(select * from infobz where info.infid=infobz.infid )