基础语法2 总结-CSDN博客

本文链接：https://blog.csdn.net/iteye_133/article/details/82104234


--  一章节

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('先生...');
	elsif v_sex='女' then
	  dbms_output.put_line('女士...');
	else
	  dbms_output.put_line('人妖...');
	end if;
end;

--CASE语句
begin
	case '&性别'
	  when '男' then dbms_output.put_line('先生...');
	  when '女' then dbms_output.put_line('女士....');
	  else dbms_output.put_line('人妖...');
	end case;
end;

declare 
  v_sex varchar2(2);
begin
	v_sex := '&sex';  --从屏幕接受输入
	case 
	  when v_sex='男' then dbms_output.put_line('先生...');
	  when v_sex='女' then dbms_output.put_line('女士....');
	  else dbms_output.put_line('人妖...');
	end case;
end;

--LOOP、
declare
  v_num number:=1;
begin
	loop 
		/*if (v_num>5) then
		   exit;
		end if;	 
		*/
		exit when v_num>5;  --退出
	  dbms_output.put_line('loop: '||v_num);
	  v_num:=v_num+1;
	end loop;
end;
/

--while
declare
  v_num number:=1;
begin
	while v_num<=5
	loop
	  dbms_output.put_line('loop: '||v_num);
	  v_num:=v_num+1;
	end loop;
end;


--for
begin
	for v_num in 1..5 loop
	  dbms_output.put_line('loop: '||v_num);
	end loop;	
end;

--动态sql
declare
 v_sql varchar2(200);
 v_a number:=1;
 v_b varchar2(20):='hello';
begin
	v_sql:='create table test(a number(2),b varchar2(20))';
	--执行
	execute immediate v_sql;
	v_sql:='insert into test values(:a,:b)';
	execute immediate v_sql using v_a,v_b;
end;

--异常
declare 
  v_name emp.ename%type;
  v_i number;
begin
	select ename into v_name from emp;
	v_i:=5/0;
exception
  when TOO_MANY_ROWS then
    dbms_output.put_line('肉丝太多....');
  when ZERO_DIVIDE then
    dbms_output.put_line('除数不能为0');
  when others then
    dbms_output.put_line('未知异常');
end;
/

--自定义异常
declare
  v_sex varchar2(2);
  e_sex exception;--自定义异常
begin
	v_sex:='&sex';
	if v_sex!='男' and v_sex!='女' then
	  raise  e_sex;  --抛异常
	end if;
	exception
	  when e_sex then
	    dbms_output.put_line('性别搞错哒》。。。');
end;


--三章节
1.pl/sql支持的语句
  dml
  tcl
  游标
  函数和运算符
2.pl/sql的数据类型
  所有sql类型
  null
  boolean
  属性类型
    %type：
    %rowtype
3.为变量赋值
  a:=10;
  select ename,sal into a,s from emp where ...;

1.游标
  1.概念：在pl/sql中，用来查询，并指向集合的指针
  2.作用：逐行处理查询结果，以编程的方式访问数据
  3.类型：隐式游标、显示游标、ref游标
2.隐式游标
  1.在Pl/sql指向DML语句时自动创建
  2.名称：SQL
  3.属性
    1.%FOUND:有影响的行数返回true
    2.%NOTFOUNT:没有影响的行数返回true
    3.%ROWCOUNT:返回影响的行数
    4.%ISOPEN:是否打开
3.显示游标   
  1.作用：处理返回多行的结果集
  2.步骤：
    1.声明：CURSOR cur_name IS SELECT ....;
    2.打开：OPEN cur_name;
    3.提取记录：FETCH cur_name INTO var1,var2...;
    4.关闭：CLOSE cur_name;
4.REF游标
  1.执行动态指定SQL语句
  2.强ref游标:有return,约束了查询的结果集结构
    1.%rowtype
    2.自定义Record类型
  3.弱ref游标：没有return

--隐式游标
begin
	delete from emp where deptno=10;
	--判段执行的情况
	if sql%FOUND then
	   dbms_output.put_line('删除了'||sql%ROWCOUNT||'行记录');
	end if;
	if sql%notfound then
	   dbms_output.put_line('没有删除人...');
	end if;
end;

--显示游标
declare
  --声明
  cursor cur_name is 
     select empno,ename from emp;
  v_no emp.empno%type;
  v_name emp.ename%type;
begin
  --打开
  open cur_name;
  loop
    --提取数据
    fetch cur_name into v_no,v_name;
    --退出
    exit when cur_name%notfound;
    dbms_output.put_line(v_no||'  '||v_name);
  end loop;
  close cur_name; --关闭
end;

--带参数的游标
declare
  --声明
  cursor cur_name(no number) is 
     select empno,ename from emp where deptno=no;
  v_no emp.empno%type;
  v_name emp.ename%type;
begin
  --打开
  open cur_name(&no);
  loop
    --提取数据
    fetch cur_name into v_no,v_name;
    --退出
    exit when cur_name%notfound;
    dbms_output.put_line(v_no||'  '||v_name);
  end loop;
  close cur_name; --关闭
end;


--修改记录
declare
  --声明
  cursor cur_name is 
     select deptno,ename,sal from emp for update;
  v_no emp.deptno%type;
  v_name emp.ename%type;
  v_sal emp.sal%type;
begin
  --打开
  open cur_name;
  loop
    --提取数据
    fetch cur_name into v_no,v_name,v_sal;
    --退出
    exit when cur_name%notfound;
    if(v_no=10) then
      update emp set sal=sal+1000 where current of cur_name;
      v_sal:=v_sal+1000;
    end if;
    dbms_output.put_line(v_no||'  '||v_name||'  '||v_sal);
  end loop;
  close cur_name; --关闭
end;

--循环游标
declare
  cursor cur_name(no number)
   is select ename,deptno from emp where deptno=no;
begin
	for rec in cur_name(&no)
	loop
		dbms_output.put_line(rec.ename||'  '||rec.deptno);
	end loop;
end;

--弱类型ref游标
declare
  TYPE ref_cur_name IS REF CURSOR;--游标类型
  cur_name ref_cur_name;--游标变量
  v_name emp.ename%type;
  v_no emp.deptno%type;
begin
	--打开
	open cur_name for select ename,deptno from emp;
	loop
	  fetch cur_name into v_name,v_no;
	  exit when cur_name%notfound;
	  dbms_output.put_line(v_name||'  '||v_no);
	end loop; 
	close cur_name;
end;

--强ref游标
declare
  TYPE ref_cur_name IS REF CURSOR return emp%rowtype;--游标类型
  cur_name ref_cur_name;--游标变量
  v_emp emp%rowtype;
begin
	--打开
	open cur_name for select * from emp;--ename,deptno
	loop
	  fetch cur_name into v_emp;
	  exit when cur_name%notfound;
	  dbms_output.put_line(v_emp.ename||'  '||v_emp.deptno);
	end loop; 
	close cur_name;
end;

--自定义Record类型
declare
  TYPE rec_type IS RECORD(
    name emp.ename%type,
    deptno emp.deptno%type
  );--类型
  v_rev rec_type; --Record类型变量
  TYPE ref_cur_type IS REF CURSOR return rec_type;
  cur_name ref_cur_type;
begin
	open cur_name for select ename,deptno from emp where deptno=&no;
	loop
	  fetch cur_name into v_rev;
	  exit when cur_name%notfound;
	  dbms_output.put_line(v_rev.name||'  '||v_rev.deptno);
	end loop;
end;
/


--四章节

1.什么是游标
2.游标的分类
3.游标的四个属性
4.显示游标的操作步骤
5.如何定义和使用显示游标修改数据

1.过程
2.函数
3.程序包  

--最简单的存储过程
create or replace procedure p1
is
begin
	dbms_output.put_line('你好,.....');
end;

--调用
exec[ute] p1;

--IN 参数
create or replace procedure p2
(eno number)
as
  v_name emp.ename%type;
begin
	select ename into v_name from emp where empno=eno;
	dbms_output.put_line(v_name);
end;
/

--执行
execute p2(7788);  --按位置传参
execute p2(eno=>7788);  --按名称传参
call p2(7788);

--pl/sql调用
begin
	p2(&no);
end;

--OUT 参数
create or replace procedure p3
(eno IN number,name OUT varchar2)
as
begin
	select ename into name from emp where empno=eno;
end;
/

--调用
declare 
  v_name varchar2(20);
begin
  --p3(name=>v_name,eno=>7788);
  p3(7788,name=>v_name);
  dbms_output.put_line(v_name);
end;

--IN OUT 参数
create or replace procedure p4
(num1 IN OUT  number,num2 IN OUT number)
is
  num3 number;
begin
	num3 := num2; 
	num2 := num1;
	num1 := num3;
end;
/

--调用
declare
  v_1 number:=10;
  v_2 number:=20;
begin
	p4(v_1,v_2);
	dbms_output.put_line(v_1||'  '||v_2);
end;

--授予执行权限 
grant execute on p1 to aniu;

--简单的函数
create or replace function fun1
return varchar2
is
begin
	return '你好,朋友....';
end;

--调用
select fun1 from dual;
--pl/sql调用

declare 
 v_n varchar(20);
begin
	dbms_output.put_line(fun1);
	v_n := fun1;
	dbms_output.put_line(v_n);
end;

--调用
VAR v varchar2(20)  --声明全局变量
exec :v:= fun1;
print :v;


--带参数的函数
create or replace function fun2
(eno number)
return varchar2
is
  v_name varchar2(20);
begin
	select ename into v_name from emp where empno = eno;
	return v_name;
end;

--调用
declare
   v_name varchar2(20);
begin
	v_name:= fun2(&no);
	dbms_output.put_Line(v_name);
end;

--授权
grant execute on fun1 to scott;

--程序包规范
create or replace package pack1
is
CURSOR cur_fenye(page number,line number) return emp%rowtype;--声明游标
function fun2(eno number) return varchar2;  --声明函数
procedure p1(eno number);  --声明过程
procedure proc_fenye(page number,line number);--分页的存储过程
end pack1;


--程序包主题
create or replace package body pack1
is
 --实现游标
  CURSOR cur_fenye(page number,line number)
    return emp%rowtype IS
    select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO  from(
    select rownum r,e.* from emp e where rownum<=page*line
    ) a where a.r>(page-1)*line;

  --实现函数
	function fun2
	(eno number)
	return varchar2
	is
	  v_name varchar2(20);
	begin
		select ename into v_name from emp where empno = eno;
		return v_name;
	end;

	--实现过程
	procedure  p1(eno number)
	is
	  v_name varchar2(20);
	begin
	  select ename into v_name from emp where empno=eno;
	  dbms_output.put_Line(v_name);
	end;

	--实现分页的存储过程
	procedure proc_fenye(page number,line number)
	is
	  v_emp emp%rowtype;
	begin
	  open cur_fenye(page,line);--打开
	  loop
	  	fetch cur_fenye into v_emp;
	  	exit when cur_fenye%notfound;
	  	dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' ' ||v_emp.sal);
	  end loop;
	  close cur_fenye;
	end;
end pack1;

--调用
select pack1.fun2(7788) from dual;
exec pack1.p1(7788);

--调用分页
exec pack1.proc_fenye(4,4);

--格式化列的现实
COLUMN LINE FORMAT 999
COLUMN TEXT FORMAT A70
SELECT line,text FROM USER_SOURCE WHERE NAME=UPPER('P2');