--  一章节

1.日期函数
  add_months()
  extract()
  round()
  trunc()
  last_day()
  next_day()
2.转换函数
  to_date()
  to_char()
  to_number()
3.锁
  行级锁：
    1.自动上锁
      insert 
      update 
      delete 
      select...for update
  表级锁：
    1.手动锁
      lock table emp in share mode; 

1.同义词
 grant create synonym to aniu;
  create synonym emp for scott.emp;
  create synonym e for emp;
  
  grant create public synonym to aniu;
  create public synonym pe for scott.emp;
 --查看同义词
 select * from user_synonyms;--数据字典

2.序列
create sequence seq
start with 1
increment by 2
maxvalue 30
minvalue -5
nocycle  --不循环产生序列号
cache 5;
 
--修改
alter sequence seq cycle;

--序列位列
1.nextval:序列下一个值
select seq.nextval from dual;
2.currval:序列当前值
select seq.currval from dual;

create table test(i number(6));
insert into test values(seq.nextval);
--查看序列
desc user_sequences;
select SEQUENCE_NAME from user_sequences;

3.视图
--普通视图 
grant create any view to scott;--授权
create or replace view v1
as
select empno,ename from emp;
--带检查约束的视图:不能通过视图修改条件数据
create or replace view v2
as
select empno,ename,sal from emp where sal>3000
with check option;
--只读视图
create or replace view v3
as
select empno,ename,sal from emp where sal<3000
with read only;
--错误视图
create or replace force view v4
as
select empno,ename,sal from emp2 where sal<3000
with read only;
--给列取别名的试图
create or replace view v5
as
select empno 编号,ename,sal from emp where sal<3000
with read only;

create or replace view v5
(编号,姓名,工资)
as
select empno,ename,sal from emp where sal<3000
with read only;

create or replace view v6
as
select empno,ename,dname from emp,dept
where emp.deptno(+)=dept.deptno;
-- 有+的是从表

4.索引
  1.标准：
    create index ind_bz on student(stuno);
  2.唯一：不能有重复值
    create unique index ind_un on student(name);
  3.组合：基于多列查询时
    create index ind_zh on student(name,address); 
    10001   10001
  4.反向键：适合连续值的列
    create index ind_fx on student(stuno) reverse;
  5.位图：适合于低基数列
    create bitmap index ind_bm on student(address);
  6.函数索引：字符型数据列
    create index ind_hs on student(lower(name));  
  

--二章节


1.创建同义词的语法
  create synonym sy_name for scott.dept;
2.创建序列的语法
  create sequence seq_name
  start with 1
  increment by 2
  maxvalue 1000000
  minvalue -100
  cycle
  cache 10;
  
  seq_name.nextval
	seq_name.currval
	
3.创建视图的语法
create force view v8
as
select * from emp
with read only;
4.索引的种类及语法
 1.标准
 2.唯一
 3.组合
 4.函数
 5.反向键
 6.位图
 
1.PL/SQL内容
  1.DML
  2.TCL
  3.游标控制
  4.SQL函数和运算符
2.支持的数据类型
  1.所有的SQL类型
  2.支持NULL
  3.Boolean
  4.属性类型:%type  %rowtype
3.组成部分
  1.声明部分[可选的]
  2.可执行部分
  3.异常处理部分[可选的]
  DECLARE  --声明
    ...
  BEGIN    --可执行
    ....
  EXCEPTION --异常
    ....
  END;
4.变量和常量
  1. v_empno number(4);
  2. v_ename varchar2(10):='scott';
  3. v_pi constant number default 3.14;
5.为变量赋值
  1.v_empno:=7788;
  2.select ename into v_ename from emp where empno=7788;
  
6.条件控制
  1.IF ... THEN ... END IF;
  2.IF ... THEN ... ELSE ... END IF;
  3.IF ... THEN ... ELSIF ... THEN ... END IF;
7.循环语句
  1.LOOP ... END LOOP;  --无条件循环
  2.WHILE 条件  LOOP ... END LOOP;  
  3.FOR i IN 1..10 LOOP ... END LOOP;


--pl/sql测试
set serveroutput on  --打开输出
begin
  dbms_output.put_line('世界上最简单的PL/SQL');
end;

--变量和常量
declare
  v_empno number(4);
  v_ename varchar2(10);
begin
	v_empno:=7788;
	select ename into v_ename from emp where empno=v_empno;
	dbms_output.put_line('姓名：'||v_ename);
end; 
/

--属性类型
declare
  v_empno number(4);
  v_ename emp.ename%type;
begin
	v_empno:=7788;
	select ename into v_ename from emp where empno=v_empno;
	dbms_output.put_line('姓名：'||v_ename);
end; 
/

declare
  v_empno number(4);
  v_emp emp%rowtype;
begin
	v_empno:=7788;
	select ename,sal into v_emp.ename,v_emp.sal from emp where empno=v_empno;
	dbms_output.put_line('姓名：'||v_emp.ename||',工资'||v_emp.sal);
end; 
/

--IF语句
declare 
  v_sex varchar2(2);
begin
	v_sex := '&sex';  --从屏幕接受输入
	if v_sex='男' then
	  dbms_output.put_line('先生...'