oracle高级用法
设置read committed 隔离(oracle 默认隔离级别)
set transaction isolation level read commiitted;
设置 serialezable 隔离(串行读)
set transaction isolation level serialezable;
设置 read only 隔离 (只读)
set transaction isolation level read only;
事务是包含一条或多条sql语句的一个逻辑单位。一个事务是一个原子体。
创建序列:<create sequence 序列名（默认创建以一为增量起始数为一的长度为int的序列的值）>
create sequence test_seq increment by 1 start with 1 maxvalue 100000 minvalue 1 cache 10 ;
create sequence 序列名(_seq)
increment by 序列增量
start with 序列的起始数
maxvalue 序列的最大生成数
minvalue 序列的最小生成数
cache 预先生成几个序列数发在缓存中
nocycle 用于指定序列执行完后是否再次循环
create sequence 序列名 increment by 序列间隔 start with 序列开始数;
查询序列：
查询当前序列值：select 序列名.nextval from dual;
查询下一个序列值：select 序列名.currval from dual;
序列的修改：alter sequence 序列名 increment by 序列增量 maxvalue 序列生成最大数 nocycle nocache;
删除序列：drop sequence 序列名;
创建同义词:
创建公共同义词 ：create public synonym synonym_同义词名 for 对象名;
例：create public synonym synonym_emp for emp;
2) 创建私有同义词：create synonym synonym_同义词名 for 对象名;
创建视图：
create view 视图名 as 查询语句;<当视图的select中包含函数或表达式的时候，必须为其定义别名>
例：create view v_emp as select * from emp;
创建索引<再利用索引进行查询时，不可以使用aum,avg,max,min等函数>：
create index 索引名字 on 表名(列名);
例：create index a_emp on emp(empno);
查询时通过索引查询：select * from emp where empno = 7369;
查看索引信息：select index_name,inde_type,tablespace_name uniqueness,logging form user_indexes where table_name='emp';
其中index_name 索引名字 index_type 索引类型 tablespace_name 存储索引的表空间 uniqueness 索引的唯一性 logging 表示是否将索引的变化记录到重做日志文件中
重命名索引 alter index idx_emp_ename rename to new_emp_ename;
oracle函数(PL/SQL语法)
--预执行在PL/SQL oracle 页面打印数据 需要在该事务执行最开是的时候执行下面代码，整个会话中只需执行一次
set serveroutput on
--打印值
dbms_output.put_line('输出内容');<换行>
dbms_output.put('输出内容');<不换行>
语法一：
declare（可省略）
--声明部分，用于定义各种变量
begin
--执行部分，用于执行各种sql语句
exception(可省略)
--异常处理部分，用于处理sql语句的各种异常
end;
/
语法二：<变量定义>
变量名 变量类型 【:=值】;
例：
v_name int :=12;
v_ename int;
v_ename emp.ename%type;<加上%type表示与该类型相同(前面的那个表示为与emp表中的ename列的属性相同)>
语法三：<控制台输入数据>
变量名 :='&说明性文字';(默认显示时再说明性文字前加入了 ‘输入值于 ’)
v_empno := '&员工编号';
若要将查询出来的值赋给我自己定义的变量，使用into进行赋值
例如：
dcelare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
end;
/
语法四（if函数）：
begin
if 条件块一 then
执行块一
else
执行块二
end if;
end;
/
begin
if 条件块一 then
执行块一
elsif 条件块二 then
执行块二
else
执行块三
end if;
end;
/
例如：
declare
v_num int;
begin
v_num :='&整数数值';
if v_num>0 then
dbms_output.put_line('a > 0');
end;
/
语法五：(case 函数)
case 定义变量
when 变量值一 then
执行块一
when 变量值二 then
执行块二
else
执行块三
end case;
【例如：
declare
a int;
begin
a := '&a';
case a
when 1 then
dbms_output.put_line('a == 11');
when 1 then
dbms_output.put_line('a == 12');
else
dbms_output.put_line('a != 1 并且 a != 2');
end case;
end;
/】
case
when 条件块一 then
执行块一
when 条件块二 then
执行块二
else
执行块三
end case;
语法六：<循环(基本循环,while,for)>
--基本循环
loop
执行块
exit when 条件;<循环退出条件>
end loop;
【例如：
declare
i int := 0;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when i > 9;
end loop;
end;
/】
--while循环
while 条件块<进入循环的条件> loop
执行块
end loop;
【例如：declare
i int := 0;
begin
while i < 10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
/】
--for循环
for 变量名 in 循环条件(起始值 ..结束值) loop
执行块
end loop;
【例如：begin
for i in 0..9 loop
dbms_output.put_line(i);
end loop;
end;
/】
在循环中的in后面加上 reverse 表示倒序打印 ,循环条件还是从小到大排列
【游标】
隐式游标
示例：
BEGIN
UPDATE emp SET sal = sal + 100 WHERE deptno = 20;
--查看隐式游标的属性，rowcount：即受影响的行数
dbms_output.put_line(SQL%ROWCOUNT);
END;
/
--使用显式游标
DECLARE
--1.定义游标,即定义所指向的结果集
CURSOR emp_cursor IS SELECT * FROM emp;
emp_record emp%ROWTYPE;
BEGIN
--2.打开游标
OPEN emp_cursor;
LOOP
--3.提取数据
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND; --指定循环退出的条件
--在这个位置做事情
dbms_output.put_line('第'|| emp_cursor%ROWCOUNT || '个员工：'||'姓名：' || emp_record.ename || ' 工资： '|| emp_record.sal);
END LOOP;
--4.关闭游标
CLOSE emp_cursor;
END;
/
--while循环使用游标
DECLARE
--定义游标
CURSOR emp_cursor IS SELECT * FROM emp;
emp_record emp%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO emp_record;
WHILE emp_cursor%FOUND LOOP
--在这里做事情
dbms_output.put_line(emp_record.ename);
FETCH emp_cursor INTO emp_record;
END LOOP;
CLOSE emp_cursor;
END;
/
--for循环使用游标, 不用打开和关闭游标，自动提取数据
DECLARE
CURSOR emp_cursor IS SELECT * FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.put_line(emp_record.ename);
END LOOP;
END;
/
--for循环直接打开结果集
BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
dbms_output.put_line(emp_record.ename || ':' || emp_record.sal);
END LOOP;
END;
/
--批量提取游标数据
DECLARE
CURSOR emp_cursor IS SELECT * FROM emp;
TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
OPEN emp_cursor;
--批量提取
FETCH emp_cursor BULK COLLECT INTO emp_table;
FOR i IN 1..emp_table.count LOOP
dbms_output.put_line(emp_table(i).ename || emp_table(i).sal);
END LOOP;
CLOSE emp_cursor;
END;
/
--游标参数
DECLARE
CURSOR emp_cusrsor(v_deptno NUMBER) IS SELECT * FROM emp WHERE deptno = v_deptno;
BEGIN
FOR emp_record IN emp_cusrsor('&deptno') LOOP
dbms_output.put_line(emp_record.ename);
END LOOP;
END;
/
--将20号部门所有员工工资增加20%
DECLARE
CURSOR emp_cursor IS SELECT * FROM emp FOR UPDATE NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.deptno = 20 THEN
UPDATE emp SET sal = sal * 1.2 WHERE CURRENT OF emp_cursor; --确定更改当前行
END IF;
END LOOP;
COMMIT;
END;
/
--使用游标变量
DECLARE
--定义游标类型
TYPE emp_cursor_type IS REF CURSOR;
--定义游标变量
emp_cursor emp_cursor_type;
emp_record emp%ROWTYPE;
BEGIN
OPEN emp_cursor FOR SELECT * FROM emp;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
/
--存储过程
<in类型>
create or replace procedure 存储过程名
--参数区域 in 为输入参数 out为输出参数 in out 为输入输出<此区域可省略>
(
v_empno in emp.empno%type,
v_sal in emp.sal%type
)
is或者as
--此区域用于声明变量(可不写，但需要保留空间)
begin
update emp set sal = v_sal where empno = v_empno;
end;
/
--调用方法(注意传值顺序<按照参数区域的定义顺序传值>)
方式一：
execute 存储过程名(7369,1000);
方式二：
begin
存储过程名(7369,1000);
end;
/
完整示例：
【
CREATE OR REPLACE PROCEDURE update_empsal
(
v_empno IN emp.empno%TYPE,
v_sal IN emp.sal%TYPE
)
IS
BEGIN
UPDATE emp SET sal = v_sal WHERE empno = v_empno;
COMMIT;
END;
/
--调用
--EXECUTE update_empsal(7369,1000);--注意传值顺序
--BEGIN
-- update_empsal(7369,1000);
--END;
--/
】
<out类型>
create or replace procedure query_emp (
v_empno in number,
v_ename out emp.ename%type
)
is
begin
select ename into v_ename from emp where empno = (select mgr from emp where empno = v_empno);
dbms_output.put_line(v_ename);
end;
/
--调用
方式一：
var v_empno number;
var v_ename varchar2;
execute :v_empno :7369;
execute query_emp(:v_empno,:v_ename);
方式二：
declare
v_empno number;
v_ename varchar2(20);
begin
v_empno :=7369;
query_emp(v_empno,v_ename);
end;
/
函数
语法：
create or replace function 函数名
--参数区域<包括in类型 out类型 in out类型>
（
）
return 返回类型
is 或者 as
<可省略，但要保留空间>
--变量定义区
begin
--执行区域
return 与上定义的类型要一致;
end;
/
调用
可直接将其放入一个查询语句中
完整示例：【
CREATE OR REPLACE FUNCTION get_ename
(
v_empno IN NUMBER
)
RETURN VARCHAR2
IS
v_ename varchar2(20);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno = v_empno;
RETURN v_ename;
END;
/
--select ename, get_ename(mgr) from emp;
】
触发器:
【语句级触发器：】<sql执行一次，触发器执行一次>
--当执行修改emp表中信息时触发该事件
create or replace trigger emp_befor_trigger before update on emp;
begin
dbms_output.put_line(' 触发器正在执行 ');
end;
/
--修改特定列时触发
create or replace trigger emp_sal_trigger before update of sal on emp;
修改emp表的sal列
--指定多个事件
create or replace trigger emp_sal_tigger before update or insert or delete on emp;
declare
begin
case
when updating then
--修改
when inserting then
--增加
when deleting then
--删除
end case ;
end;
/
【行级触发器】<sql影响一行，触发器执行一次>
--
create or replace trigger emp_sal_tigger before update on emp;
for each row
begin
--执行块
end;
/
示例：
CREATE OR REPLACE TRIGGER emp_before_trigger BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
IF :new.sal < :old.sal THEN -- 只能before行级触发器
raise_application_error(-20001,' 工资只能涨不能跌 ');
END IF;
END;
/
查看： select * from user_triggers;
禁用： SQL> alter trigger TR_EMP_SAL disable;
启用： SQL> alter trigger TR_EMP_SAL enable;
禁用表的所有触发器： SQL> alter table emp disable all triggers;
重新编译： SQL> alter trigger TR_EMP_SAL compile;
删除： SQL> drop trigger TR_CHECK_SAL;
【包】
包是一种数据对象，它是相对类型，子程序，游标，异常，变量和常量封装组成的。
--创建包头
create or replace package emp_pkg
is
procedure add_emp(
v_empno in number,
v_ename in varchar2,
v_sal in number,
v_deptno in number
);
....
function get_dname(
v_deptno in number
)return varchar2;
end emp_pkg;
/
--定义包体<包体名必须和包头名相同>
create or replace package body emp_pkg
is
procedure add_emp(
v_empno in number,
v_ename in varchar2,
v_sal in number,
v_deptno in number
)
is
begin
--执行区域
end;
....
end emp_pkg;
/
--命令窗口执行
--创建按
variable job number ;
begin
sys.dbms_job.submit(job => :job,
what => 'init_no_every_year;',
next_date => to_date( '01-04-2015 10:45:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1' );
commit;
end;
/
http://blog.itpub.net/27157/viewspace-425567/
描述 INTERVAL参数值
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'
－－－－－－－－－－－－－－－－－－－－－－－－－－
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
数据库分页查询语句:
select * from ( select c.*,rownum r from t_classes c where rownum < 60 ) cl where cl.r >50;
查询一个XX占用几个字节
select lengthb('a' )/length('a') from dual;
