sssssssss

视图的分类：
如何判断复杂视图？
可以从第一从几张表中查询或者是否有组函数等 两者满足其一就可以
从几张表中查询 是否有组函数，GROUPBY语句 能否执行DML
简单视图： 1 没有 可以执行
复杂视图： 1或者多张 有 不能执行

4、查看视图：
select view_name,text from user_views where lower(view_name) like 'yinxd%'
select view_name,text from user_views where lower(view_name) like 'yinxd%'


create view temp_view
2 as select id,name from yin_temp;


> insert into temp_view values(1,'as'); 通过视图插入数据

第十五章:索引
作用： 加快检索速度，降低操作系统的io操作。
1、index:
是一种数据库中的一种对象，数据库使用索引来加快检索速度，降低操作系统的io操作。
如果一个列上加了索引，当数据库使用加了索引的列进行检索时，数据库自动使用索引。

rowid:不可能有两个一模一样的rowid，一个加了索引的列中每个元素中所对应的硬盘的位置。
SQL> select id,first_name,rowid from s_emp;

1 Carmen AAADVRAABAAAKeiAAA
2 LaDoris AAADVRAABAAAKeiAAB
2、创建索引：
create index index_name on table_name(column,...)
当一个列上有主键约束或者是唯一约束的时候，oracle会自动在该列上创建一个索引。
索引的算法：
B－Tree,位图，反序，函数
3、什么时候加上索引
一个列或多个列经常出现在where子句或者查询子句
一个列取值范围比较广
一个列没有包含大量的空值
如果表很大并且返回的记录少于2％－4％时
表濒繁更新的时候不见意加上索引

第十六章：权限控制
1、数据库安全的控制
数据库通过用户名和密码来控制用户的访问。如果新创建的用户在没有授权之前是没有任何权限的。
包括连接数据库的权限也没有。
2、创建一个新的用户
create USER user_name identified by user_passwd;

SQL> create user yinxd identified by yin;
SQL> grant create session to yinxd; //授权连接
grant resource connct to yinxd //角色，包括连接数据库
3、修改密码：
alter USER user_name identified by user_passwd;
4、授权
系统权限：
grant privileges/ role TO user/role;
对象权限：
grant privileges on object_name to user/role;

grant select,update,delete ON s_emp TO zs;(可以查询所有的列，想要控制通过视图）
SQL> grant select on s_emp to yinxd;

5、创建同义词：synonym
create synonym syn_name for object_name;

L> grant create synonym to yinxd; //必须用dba
SQL> create synonym s_emp for briup.s_emp; //创建了别名，被授权的用户自己创建
如果在创建同义词的时候 加上 PUBLIC 表示该同义词任何一个用户都可以使用。
例如：dual：是dba创建的一个表同时，它是一个别名，而且任何的人都可以用说明是public 类型。

6、回收权限
系统权限：
REVOKE privileges/ role FROM user/role;
对象权限：
REVOKE privileges FROM object_name TO user/role;
revoke select,update,delete FROM s_emp TO zs;
7、删除用户：
drop user user_name[cascade];
8、解锁用户
alter user user_name account unlock;


PL/SQL程序设计
API：application programe interface
SQLJ：可以把独立的sql语句写入java代码中，比jdbc相对较新
第一章
1、什么是pl/sql?
PL/SQL是"ORACLE"公司在标准SQL的基础上增加了过程化处理的语言
2特点：
结构化模块化编程
良好的可维护性 保存在数据库中。
良好的可移植性 在同是oracle的系统中都可以用。
提升系统性能 直接从数据库中拿出来就可以用省去了

缺点： 不便于向异构数据库系统移植应用程序（sql,mysql中就可以使用）

第二章:
1、pl/sql的结构
declare
－－－－＞声明部分（可以没有）
begin
－－－－＞执行部分（主体）
exception
－－－－＞异常处理
end
SQL> set serverout on size 30000;
--把程序的结果显示出来

declare
var varchar2(20);
begin
var := 'hello world!';
dbms_output.put_line(var); --相当于system.out.println();
end;
/

2、PL/SQL中注释
1、单行注释
--注释内容
2、多行注释
/*
注释内容
*/
3、声明变量
Var_name [constant] type [not null] [:=value];
Var_name：变量的名字
[constant]：常 量
declaration of a constant 'VAR' must contain an initialization
assignment 在初始化的时候如果包含了constant 但是没有赋值的情况
不能再给变量赋值
type： 变量的类型
[not null]：变量必须有值
: a variable declared NOT NULL must have an initialization assignment
当声明为NOT NULL并且没有初始化什值的时候 报的错，可以在后面继续赋值
[:=value]：初始值

a := b 表示将b的值赋给a，
a = b 表示判断a与b的值是否相同
注意：如果是constant或者是not null变量必须有初始值
如果是constant，则变量的值是不可以

declare
var varchar2(20) not null := 'what can i do for u';
begin
--var := 'hello world!';
dbms_output.put_line(var);
end;
/
第三章：
1、类型:
1）标量型：
2）组合型：
3）参考型：
2、声明变量
1）％type：表示所声明的变量与数据库中某张表的某个列具有相同的类型。
var s_emp.id%type 与s_emp 的 id列有相同的类型。
begin
for i in 1..50 loop
insert into yinxd_tbl values(i,'briup'||i);
end loop;
end;

for循环的步长始终是1，不能改变它，但是可以对指定的数据不做处理。

declare
v_id yinxd_tbl.id%type;
v_name yinxd_tbl.name%type;
begin
select id,name into v_id, v_name /此时不用关心id,name是什么类型
from yinxd_tbl where id=20;
dbms_output.put_line('ID='||v_id);
dbms_output.put_line('NAME='||v_name);
end;
/

2）record 自定义类型，可以包含其它类型。
type t_rec 给声明的类型起名字,多个类型用","隔开。
type t_rec is
record(
v_id number，
v_name varchar2（20）
)
例：
declare
type t_rec1 is
record(
v_id number,
v_name varchar2(20)
);

/*type t_rec2 is
record(
v_id number,
v_name varchar2(20)
);*/
v_rec1 t_rec1;
--v_rec2 t_rec2;
v_rec2 t_rec1;
begin
select id,name into v_rec from yinxd_tbl
where id=30;
v_rec2 := v_rec1;// t_rec1 和t_rec2是不同类型。
dbms_output.put_line('ID='||' '||v_rec2.v_id);
dbms_output.put_line('NAME='||v_rec2.v_name);
end;
/
3)%rowtype:所声明的变量与数据库中某个表的类型相同。
var s_emp%rowtype;
declare
var yinxd_tbl%rowtype;
begin
select * into var
from yinxd_tbl where id=20;

dbms_output.put_line('ID='||' '||var.id);
dbms_output.put_line('NAME='||var.name);

end;
/

4)table:类似于java中的数组(数组可以是自已定义的类型）
type t_tab is table of number index by binary_integer;

declare
type t_tab is table of number index by binary_integer;
v_tab t_tab;
begin
select id into v_tab(10)
from yinxd_tbl where id=20;
select id into v_id(0) from s_emp where id=10;
select id into v_id(-1) from s_emp where id=15;
dbms_output.put_line('V_ID = '||v_id(-1));
dbms_output.put_line('V_ID = '||v_id(0));
dbms_output.put_line('V_ID = '||v_id(1));
dbms_output.put_line('ID='||' '||v_tab(10));
end;
/
3、变量的作用域
declare
begin
declare
begin
end;
end;
/

dbms_output.put_line(sql%rowcount ||'条记录被影响');
其中sql是指刚刚执行的sql语句，而rowcoutn指这条语句被影响的行


变量的作用域：内部块可以使用外部块声明的变量，但是外部块不可以使用内部块声明的变量。

第四章：
1、分支语句
declare
v_num number :=0;
begin
select count(*) into v_num from yinxd_tbl;
if v_num =0 then 1>没有小括号，后面必须有 then 一起写
dbms_output.put_line('there is no recored !');
elsif v_num >0 and v_num <100 then 2>elsif 而不是 else if
dbms_output.put_line('the recored is:'||v_num);
else
dbms_output.put_line('the recored is more than 100 !!');
end if; 3>就算只有一个分支也要有end if
end;
/
2、循环语句 loop
loop
.....;
exit;
end loop;


declare
var varchar2(20) :='hello';
v_num number :=1;
begin
loop
dbms_output.put_line(var||v_num);
v_num := v_num+1;
if v_num > 5 then
exit;
end if;
end loop;
end;
/

----------------------------
while 条件 loop
循环体;
end loop;

declare
var varchar2(20) :='hello';
v_num number :=1;
begin
while v_num <=5
loop
dbms_output.put_line(var||v_num);
v_num := v_num+1;
end loop;
end;
/
----------------------------
for var_name in min_val..max_val loop
循环体；
end loop;

declare
v_sum number := 0;
begin
for newNum in 1..100
loop
if mod(newNum,2)=0 then
v_sum := v_sum + newNum;
end if;
end loop;
dbms_output.put_line('total Number:'||v_sum);
end;
/
3、goto语句：
declare
v_sum number := 0;
begin
for newNum in 1..100
loop
if mod(newNum,2)=0 then
v_sum := v_sum + newNum;
else
null;
end if;
if newNum>50 then
goto myLab;
--只能由内部跳到外面，不能由外部跳到内部避免死循环
end if;
end loop;
<<myLab>>
dbms_output.put_line('total Number:'||v_sum);
end;
/


1、游标：
作用：用来提取多行的数据集
使用步骤
1)声明游标
2）打开游标
3）提取数据
4）处理数据
5）关闭游标

declare
--声明游标
cursor v_cur is select id,name from yinxd_tbl;

--clare the variable

v_id number;
v_name varchar2(20);
begin
--open the cursor
open v_cur;

--obtain database
fetch v_cur into v_id,v_name;

--access the database
dbms_output.put_line('ID=:'||v_id);
dbms_output.put_line('NAME=:'||v_name);

--close the cursor
close v_cur;
end;
/
2)游标的属性
1)%found:是否提取到数据
2)%notfound：
3)%isopen:游标是否打开
4)%rowcount：提取到哪一行记录
使用：游标的名字＋游标的属性
例： v_cur%found 至少要在使用了fetch语句之后。


declare
cursor v_cur is select id,name from yinxd_tbl;
v_id number;
v_name varchar2(20);
begin
open v_cur;
fetch v_cur into v_id,v_name;
while v_cur%found
loop
dbms_output.put_line('ID=:'||v_id);
dbms_output.put_line('NAME=:'||v_name);
fetch v_cur into v_id,v_name;
end loop;
close v_cur;
end;
/


declare
cursor v_cur(var number) is select id,name from yinxd_tbl where id<var;
v_id number;
v_name varchar2(20);
begin
open v_cur(20); --指定的参数值
fetch v_cur into v_id,v_name;
loop
fetch v_cur into v_id,v_name;
if v_cur%found then
dbms_output.put_line('ID=:'||v_id);
dbms_output.put_line('NAME=:'||v_name);
else
exit;
end if;
end loop;
close v_cur;
end;
/

for 循环游标的使用：
begin
for v_cur in (select id,name,title from test)
loop
dbms_output.put_line('ID='||v_cur.id);
dbms_output.put_line('NAME='|| v_cur.name);
dbms_output.put_line('TITLE='|| v_cur.title);
end loop;
end;
/

游标可以带参数：
参数只能用于条件中，不能是select 或者是from 子句中
要查的是哪张表不能作为参数传入；
参数只能有类型 不能有长度限制，包括varchar2 类型；
在打开游标的时候给参数传入值。

第五章：
异常处理：
1）声明一个异常
2）抛出异常
3）处理异常
declare
v_temp number;
begin
select id into v_temp from test where title='he';
exception
when too_many_rows then
dbms_output.put_line('too many data');
when no_data_found then
dbms_output.put_line('no date found');
when others then
dbms_output.put_line('error');
end;
/


declare
v_id test.id%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from test where id=v_id;
commit;
exception
when others then
rollback;
v_errorcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval, v_errcode,v_errmsg,sysdate);
commit;
end;
/
第六章：
create [or replace] procedure proc_name
[(arg_name [{in | out| in out}]] type,
{is | as}
in:传入值， 只能位于赋值符号的右边
out：传出值， 只能伴于赋值符号的左边
in out: in 和 out 的结合。
注：有参数的procedure，在声明参数的的时候 参数只能有类型不能有长度限制

//没有参数的例子，默认的是 in 模式。
create or replace procedure proc_yinxd
is -- is take the place of declare then blow this began to declare
var varchar2(30) :='hello world';
begin
dbms_output.put_line(var);
end;
/

下面的开始调用。
begin
proc_yinxd;
end;
/

有参数的procedure，在声明参数的的时候 参数只能有类型不能有长度限制
create or replace procedure pro_yin(
v1 in number,
v2 out date,
v3 in out varchar2 --如果有默认值可以把在传参的时候不指定值。在不是指定参数的情况见意放在最后，
)
is
v11 number;
v22 date := sysdate;
v33 varchar2(20) := 'yinxd';
begin
v11 := v1;
v2 := v22;
v3 := v33;

dbms_output.put_line('v11='||v11);
dbms_output.put_line('v22='||v22);
dbms_output.put_line('v33='||v33);
end;
/

declare
-- used to input some value into the var
v1 number := 888;
v2 date; --it's a out type so we won't to initialize it;
v3 varchar2(20) := 'hello everyone!!';
begin
pro_yin(v1,v2,v3);
end;
/

//如果是 in，out模式的话，参数的位置 不能改变
in 模式，如果把参数写在左边，语法编译不能通过
out 模式，则可以编译通过，但是显不出来数值
create or replace procedure pro_yin(
v1 number
--v1 out number,
)
is
v11 number := 666;
begin
--v11 := v1;
v1 := v11;
dbms_output.put_line('v11='||v11);
end;
/

declare
-- used to input some value into the var
v1 number := 888;
begin
pro_yin(v1);
end;
/

作业
创建两张结构相同的表
创建一个procedure.
1)通过循环语句一次性插入test1 50 条记录
2）将test1 表中的前30 条记录插入到test2中
3）插入test2之前将test2中的记录清空


create or replace procedure proc_yinxd(
v1 number, --需要插入test1中的记录数
v2 number --从test1中插入到test2中的数
) as
v_id number;
v_name varchar2(20);
cursor v_cur is select id,name from test1; --声明一个游标
begin
for i in 1..v1 loop
insert into test1 values(i,'briup'||i);
end loop;

delete from test2; --把test2中的数据清空
open v_cur; --打开游标

loop
fetch v_cur into v_id,v_name;
if v_cur%rowcount > v2 then --判断当前的行号是否超过了给定的数
exit;
end if;
insert into test2 values(v_id,v_name);
end loop;

close v_cur;
end;
/


2、function
1)function与procedure 的区别
a）funciton是有返回值的，procedure 不有
b） function 只是pl/sql语句的一部分，但是procedure是一个完整的语句
c）
create or replace function func_yinxd
return varchar2
as
result varchar2(30);
v_sum number;
begin
select count(*) into v_sum from user_constraints;
if v_sum <= 0 then
result := 'no record';
elsif v_sum > 0 and v_sum <100 then
result := v_sum;
else
result := 'more than 100';
end if;
return result;
end;
/
declare
v_result varchar2(30);
begin
v_result := func_yinxd;
dbms_output.put_line(v_result);
end;
/


包

1、包头
create or replace package pac_yinxd
as
var varchar2(30);
procedure pro_yinxd; －－对procedure,function的声明
function func_yinxd return varchar2;
cursor v_cur is select id,name from yin_temp;
end;
/
-- 包的体部，如果没有包含procedure 或者 function 则不需要包的体部
create or replace package body pac_yinxd
is
procedure pro_yinxd
is
var varchar2(30) := 'hello';
begin
dbms_output.put_line(var);
end;

function func_yinxd return varchar2
is
v varchar2(30) := 'briup';
begin
var :=v;
return var;
end;

end;
/

－－调用情况
begin
pac_yinxd.pro_yinxd;
end;
/

2、包中子程序的重载
1）不能取决于参数的模式，只能取决于参数的类型与数量。
2）不能根据函数的返回类型来判断是否重载（不同的类型族，如char与varchar属于同一类型族，但是
number 与 char是不同的类型族）。
3）
触发器：
create or replace trigger trig_yinxd
after update on yin_temp
for each row
declare
v varchar2(100);
begin
v := 'insert one row!';
dbms_output.put_line(v);
end;
/

种类：
１）触发的时间：after/before
2）触发事件：insert/update/delete
3)触发级别：statement/row

:new 新修改的数据
:old 修改完以后的数据
id name
-------------
5 briup
update yin_temp set name='zs' where id=5;
:new.id = 5
:new.name ='zs'
:old.id=5
:old.name='briup'


create or replace package pack_yinxd
as
procedure pro_yinxd;
cursor v_cur is select classid,count(*),count(english),avg(math) from score group by classid;
end;
/

create or replace package body pack_yinxd
is
procedure pro_yinxd
is
v_classid number(2),
classmates number(2),
sum_english number(5),
avg_math number(3)
begin
open v_cur;
while v_cur%found
loop
fetch v_cur into v_classid,classmates,sum_english,avg_math;
insert into class values(test_seq.nextval,v_classid,classmates,sum_english,avg_math);
end loop;
close v_cur;
end;
end;
/

//可以用的
begin
for v_cur in (select classid, count(*) total,
sum(english) sum_eng, avg(math) avg_math from score group by classid)
loop
insert into class values(test_seq.nextval,v_cur.classid,
v_cur.total,v_cur.sum_eng,v_cur.avg_math);
end loop;
end;
/

ORACLE中的定时调用
dbms_job.submit(任务号,要执行的任务，什么时间开始执行，每隔多少时间执行一次)
//执行的任务也可以是一个procedure的一个语句，直接写其名字就可以,

declare
code number;
begin
dbms_job.submit(code,'insert into test_date values(sysdate);
',sysdate,'sysdate+1/(24*60)');
commit;
end;
/
此时的sysdate是上一次执行的时间而并非系统的当前的时间


可以从 user_jobs ，看到

dbms_job.remove();


begin
dbms_job.remove(21);
end;