3-1创建用户,为用户分配表空间(表空间和临时的表空间) 4
3-4创建用户 (没有表空间 ,使用默认的表空间system) 5
5-1-1传递系统权限用 with admin option 9
5-1-2传递用户权限用 with grant option 9
14-2 PL/SQL 块中不能单独的执行select 语句 35
第一,系统用户sysdba
1,创建表空间
--创建表空间语法:create tablespace 表空间名称 datafile 'dbf文件路径' size 大小 autoextend on;
create tablespace kusy
datafile 'kusy' size 1000m autoextend on;
--创建表空间
create tablespace myspace
datafile 'D:\app\Administrator\oradata\orcl\myspace.dbf'
size 100m
autoextend on;
2,创建临时表空间
2-1创建临时表空间
创建临时表空间语法:create temporary tablespace 表名 tempfile '临时表空间存放的dbf文件路径' size 大小 autoextend on;
create temporary tablespace mytemp
tempfile 'D:\app\Administrator\oradata\orcl\mytemp.dbf' size 10m autoextend on ;
2-2删除表空间
--假如表空间为空了就用 drop tablespace 表空间名;
--假如表空间不为空就级联删除 drop tablespace 表空间名 including contents and datafiles
--但是现在被删除的数据库 dbf文件还是存在的 直接删除dbf文件
drop tablespace db1pwd including contents and datafiles;
3,创建用户
3-1创建用户,为用户分配表空间(表空间和临时的表空间)
--创建用户zhangsan
create user zhangsan --用户名
identified by zhangsan --密码
default tablespace myspace --指定表空间
temporary tablespace mytemp --指定临时表空间
quota 5M on myspace;
--------------------------------
--创建用户yjh
create user yjh --用户名
identified by yjh --密码
default tablespace myspace --指定表空间
temporary tablespace mytemp --指定临时表空间
3-2修改密码,锁定/解锁用户
--修改密码
alter user 用户名 identified by 密码 ;
--锁定用户
alter user 用户名 account lock;
--解锁用户
alter user 用户名 account unlock;
3-3删除用户
--新创建的用户,假如没有表了就用 drop user 用户名;
--假如有表了就级联删除 drop user 用户名 cascade;
drop user zhangsan cascade;
3-4创建用户 (没有表空间 ,使用默认的表空间system)
--用户尽量避免使用系统表空间,所以我们在创建用户的时候 去指定用户自己创建的表空间和临时表空间
create user zhangsan identified by zhangsan;
create user lisi identified by lisi;
-创建一个表空间-->临时表空间-->创建用户-->分配表空间和临时表空间
-->给新用户创建会权限,创建表权限,查询表的权限
4,给用户权限
给用户权限语法:grant 权限名 to 用户名
收回用户的权限:revoke 权限名 from 用户名
4-1关于序列的权限
1. Create sequence 允许被授权者在他们自己的模式中创建新的序列
2. CREATE ANY SEQUENCE允许被授权者在任意一个模式中创建新的序列
3. ALTER ANY SEQUENCE允许被授权者修改数据库中任意一个序列的属性
4. DROP ANY SEQUENCE允许从数据库内的任意一个模式中删除任意一个序列
5. SELECT ANY SEQUENCE
4-2关于会话的权限
1. CREATE SESSION允许被授权者连接到数据库。该特权对用户账户是必需的，但对软件账户可能是不受欢迎的。
2. ALTER SESSION允许被授权者执行ALTER SESSIONS语句
3. ALTER RESOURCE COST允许被授权者修改ORACLE为一个概况中的资源约束计算资源成本的方式。
4. RESTRICTED SESSION允许数据库在RESTRICTED SESSION模式时连接到数据库，一般是为了管理性目的。
4-3关于表空间的权限
1. CREATE TABLESPACE允许创建新的表空间
2. ALTER TABLESPACE允许被授权者更改现有表空间
3. DROP TABLESPACE允许删除表空间
4. MANAGE TABLESPACE允许更改表空间。例如ONLINE、OFFILE、BEGIN BACKUP或END BACKUP
5. UNLIMITED TABLESPACE允许消耗任意一个表空间中的磁盘限额。相当于给指定授权者每个表空间中的无限限额。以上介绍Oracle系统特权。
4-4关表于的权限
1. CREATE TABLE允许在自己的对象模式中创建表
2. CREATE ANY TABLE允许在任意一个对象模式中创建表
3. ALTER ANY TABLE允许更改任意一个对象模式中的表
4. DROP ANY TABLE允许从任意一个对象模式中删除表
5. COMMENT ANY TABLE允许给任意一个对象模式中的任意一个表或列注释
6. SELECT ANY TABLE允许查询任意表
7. INSERT ANY TABLE允许插入新行到任意表
8. UPDATE ANY TABLE允许更新任意表
9. DELETE ANY TABLE允许删除任意表中的行
10. LOCK ANY TABLE允许执行一条LOCK TABLE来明确锁定任意一个表
11. FLASHBACK ANY TABLE允许使用AS OF 语法对任意一个对象模式的任意一个表或视图执行一个SQL回闪查询。
4-5关于同义词的权限
1. CREATE SYNONYM允许在自己的对象模式中创建同义词
2. CREATE ANY SYNONYM允许在任意对象模式中创建新的同义词
3. CREATE PUBLIC SYNONYM允许被授权者创建新的公用同义词。这些同义词对数据库中的所有用户都是可访问的。
4. DROP ANY SYNONYM允许从任意对象模式中删除任意一个同义词
5. DROP PUBLIC SYNONYM允许被授权者从数据库中删除任意一个公用同义词
4-6关于表对象的权限
1. select允许查询指定表
2. INSERT允许在指定表创建新行
3. UPDATE允许修改指定表的现有行
4. DELETE允许删除指定表的行
5. ALTER允许添加、修改或重命名指定表中的列，转移该表到另一个表空间，乃至重命名指定表。
6. DEBUG允许被授权者借助于一个调度程序访问指定表上的任意触发器中的PL/SQL代码
7. INDEX允许被授权者在指定表上创建新的索引
8. REFERENCES允许创建参考指定表的外部键约束
4-7关于视图对象的权限
1. SELECT查询指定视图
2. INSERT允许在指定视图创建新行
3. UPDATE允许修改指定视图的现有行
4. DELETE允许删除指定视图的行
5. DEBUG允许被授权者借助于一个调度程序访问指定视图上的任意触发器中的PL/SQL代码
6. REFERENCES允许创建参考指定视图的外部键约束
4-8关于序列的权限
1. SELECT允许访问当前值和下一个值(即CURRVAL和NEXTVAL)
2. ALTER允许修改指定序列的属性
4-9举例
例如:新创建的用户连基本的登陆权限就没有,必须以管理员的身份赋予权限
1--给予用户 会话和创建表的权限
可以进行的操作为:连接数据库,创建表,查询表内容容
grant create session to yjh;
grant create table to yjh;
grant select on tb_user to yjh;
无限使用表空间
grant unlimited tablespace to role_test;
revoke select on tb_user from yjh;
revoke unlimited tablespace from yjh;
select * from user_role_privs;
select * from tb_user;
2--赋予张三创建会话的权限
grant create session to zhangsan;
--无限使用表空间的权限
grant unlimited tablespace to zhangsan;
--允许数据库的权限
grant connect to zhangsan;
--创建序列的权限
grant create sequence to zhangsan;
--赋予用户创建表的权限
grant create table to zhangsan;
--赋予用户查询某张表的权限
grant select on tb_user to zhangsan;
--赋予用户某张表的所有权限
grant all on tb_user to zhangsan;
-----------------------------------
3-回收权限
--语法:revoke 权限名 from 用户名 ;
revoke all on tb_user from zhangsan;
revoke connect from zhangsan;
revoke create session from zhangsan;
5,角色和权限传递
5-1权限传递
系统权限：grant create session to zhangsan with admin option;
(表示把系统权限授予给zhangsan，并允许其授予给其他用户)
用户权限：grant update on mytab to lisi with grant option;
(表示把用户权限授予给lisi，并允许其授予给其他用户)
5-1-1传递系统权限用 with admin option
grant create session to zhangsan with admin option;
5-1-2传递用户权限用 with grant option
grant select on scott.emp to zhangsan with grant option;
---回收了zhangsan的用户权限,那么经过zhangsan传递的权限全部失效
---回收了zhangsan的系统权限,那么经过zhangsan传递的权限不会失效
revoke select on scott.emp from zhangsan;
5-2角色管理：
创建角色：create role roleName;
给角色授予权限：grant 权限 to roleName;
将角色授予给用户：grant roleName to userName;
用户查询拥有的角色：select * from user_role_privs;
删除角色：drop role roleName;
当给角色授予权限的时候，拥有此角色的用户也同时增加了权限；
当撤销角色权限的时候，拥有此角色的用户的对应权限也被撤销；
当角色被删除，拥有此角色的用户将丧失之前角色所有的所有权限。
5-2-1创建角色
创建角色role_test-拥有创建session和创建table的权限
create role role_test;
5-2-2给角色赋予权限
grant create session to role_test;
grant create table to role_test;
5-2-3把角色给softeem
grant role_test to softeem;
5-2-4收回角色,那么这个角色所拥有的权限全部收回
revoke role_test from softeem;
5-2-5删除角色,拥有此角色的用户对应的权限也失效
drop role role_test;
5-2-6授予角色 connect resource
grant connect to li;
grant resource to li;
5-2-7查询当前用户拥有的角色
select * from user_role_privs;
6,数据字典
6-1、用户
--查看当前用户的缺省表空间
select username,default_tablespace from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
--显示当前会话所具有的权限
select * from session_privs;
--显示指定用户所具有的系统权限
select * from dba_sys_privs where grantee='GAME';
--显示特权用户
select * from v$pwfile_users;
--查看用户下所有的表
select * from user_tables;
--查看用户下所有的表的列属性
select * from USER_TAB_COLUMNS where table_name=:table_Name;
--显示用户信息(所属表空间,用户名区分大小写)
select default_tablespace,temporary_tablespace
from dba_users where username='SCOTT';
--显示用户的PROFILE
select profile from dba_users where username='SCOTT';
6-2,表
--查看用户下所有的表
select * from user_tables;
--查看名称包含log字符的表
select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;
--查看某表的创建时间
select object_name,created from user_objects where object_name=upper('&table_name');
--查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
--查看放在Oracle的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;
6-3,索引
--查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;
--查看索引被索引的字段
select * from user_ind_columns where index_name=upper('&index_name');
--查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
6-4,序列号
--查看序列号，last_number是当前值
select * from user_sequences;
6-5,视图
--查看视图的名称
select view_name from user_views;
--查看创建视图的select语句
set view_name,text_length from user_views;
set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
select text from user_views where view_name=upper('&view_name');
6-6,同义词
--查看同义词的名称
select * from user_synonyms;
6-7,约束条件
--查看某表的约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
6-8,存储函数和过程
--查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
--查看函数和过程的源代码
select text from all_source where owner=user and name=upper('&plsql_name');
第二部分 实例用户中
1,实例中的用户
--Nomal 普通的用户角色
--sysdba 超级用户管理员
--syspoer 系统管理员
--sys 网络管理员用户,具有最高的数据库管理权限,可以创建数据库
--system 本地管理员角色,具有权限仅次于sys的最高的数据库管理权限,不可以创建数据库
--scott 普通用户,默认的权限是锁住的,需要自己打开
---------------------------------
---sys system scott 都是默认的用户,我们也可以自己创建用户去连接数据库,但是没有任何权限,需要dba给予权限
---表空间:包含 表 视图 索引
---段:包含 数据段 索引段
---数据块: 数据块是最小的逻辑存储单元
2创建表
2.1创建表
create table tb_user(
stu_num int primary key,
stu_name varchar2(20) not null,
stu_sex char(4) default('男') check (stu_sex in('男','女')),
stu_birthday date
);
2.2查询表
--查看表的存储信息
select * from user_tables where table_name='TB_USER';
--查看表的相关信息(属性类型和长度)
select * from user_tab_columns where table_name='TB_USER';
2.3添加信息
insert into tb_user(stu_num,stu_name) values('1001','a');
insert into tb_user(stu_num,stu_name,stu_sex) values('1002','b','女');
insert into tb_user(stu_num,stu_name,stu_sex,stu_birthday)values(to_number('1003'),'c','男',to_date('1997-02-03','yyyy-mm-dd'));
select * from tb_user;
2.4删除表
drop table tb_user;
3数据类型
--标量类型
--数值型 : number(integer,float)
--字符型 : char(2000) varchar(4000) long(2G)
--日期型 : date timestamp(不推荐)
--大字段LOB : BLOB CLOB
--属性类型
--%type
--%rowtype
4创建视图
视图: (1)查询不会影响基表
(2)增删改视图的时候,更新的相应信息同时也会更新基表的对应信息
(3)删除视图 基表还存在不会影响
select * from tb_user;
4-1创建视图
create view v_user as select * from tb_user;
4-2查询视图
select * from v_user;
drop view v_user;
insert into v_user(stu_num,stu_name,stu_sex,STU_BIRTHDAY) values(1005,'甘小娟','女',to_date('1993-5-6','yyyy-mm-dd'));
insert into v_user(stu_num,stu_name,stu_sex,STU_BIRTHDAY) values(1009,'甘娟','女',sysdate);
4-5删除视图内容
delete from v_user where stu_num=100;
4-6修改视图内容
update v_user set stu_name='大话单' where stu_num=1002;
5索引
--创建索引 针对表中的某一个经常用的字段
---在查询的时间会自动调用,会使查询的速度加快
5-1创建索引 index
语法:create index 索引名 on 表名(索引字段)
create index index_emp_sal on emp(sal);
---根据索引字段查询,在查询时索引是自动调用的
select * from emp where sal between 1000 and 2000;
5-2删除索引
drop index index_emp_sal;
6同义词synonym
--创建同义词:对同义词的操作会影响到原表 synonym
--给emp创建同义词e ,e 就可以代表emp
create synonym e for emp;
--e=emp 意思相同
select * from e;
update e set sal=801 where ename='SMITH';
--删除同义词
drop synonym e;
7连接
7-1等值连接
select * from emp,dept where emp.deptno=dept.deptno order by empno;
select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno order by empno;
7-2右连接 --(+)在左边
打印了没有员工的部门 (+)在左边的为右连接,在右边的为左连接
select emp.*,dname,loc from emp,dept where emp.deptno(+)=dept.deptno order by empno;
7-3左连接 --(+)在右边
select emp.*,dname,loc from dept, emp where emp.deptno=dept.deptno(+) order by empno;
7-4用union连接
--结果集的操作 union的左边和右边查询的结果在一个表中,但是两边显示的结果的个数和类型要一样
select empno,ename from emp union select deptno,dname from dept;
8 PL/SQL代码快
代码块:描述一个过程 里面包含一个语句
语法:
declare
声明部分
begin
业务逻辑处理
exception
异常处理部分
end
注释:在声明部分定义我们需要使用的变量,不用在每个变量前使用'declare'来声明,变量名不用使用'@'符号
8-1,块的举例
declare
num number(5);--定义的两个变量
str varchar2(50);
m number:=10;--在变量声明的部分给变量初始值
tsal number;
begin
--给变量赋值
str:='王向新是个大逗比.....';
--通过select语句给多个变量赋值
select count(*),sum(sal) into num,tsal from emp;
dbms_output.put_line(num ||'人的总工资为:'||tsal);
--into 是给变量赋值
-- select count(*) into num from emp;
--把值打印出来,在上面的Output看结果
--在PL/SQL中字符串拼接用 '||'
num:=num+m;
dbms_output.put_line(num || str || m ||'--->' || tsal);
end;
--查询员工的数量,如果数量大于5就打印 '哈哈'否则打印'呵呵'
declare
num number;
begin
select count(*)into num from emp where sal>3000;
if(num>5)then
dbms_output.put_line('员工的数量为:'|| num);
dbms_output.put_line('哈哈');
else
dbms_output.put_line('员工的数量为:'|| num);
dbms_output.put_line('呵呵');
end if;
end;
--------------------------
declare
num number;
begin
select count(*)into num from emp where sal>3000;
if(num>5)then
dbms_output.put_line('@@@@@员工的数量为:'|| num);
dbms_output.put_line('哈哈');
else
if(num>3)then
dbms_output.put_line('%%%%%%%%%%员工的数量为:'|| num);
dbms_output.put_line('呵呵');
else
dbms_output.put_line('&&&&&&&&&&&员工的数量为:'|| num);
dbms_output.put_line('嘻嘻');
end if;
end if;
end;
8-2连续if选择
declare
esal number;
begin
--查询SMITM的工资赋值给esal
select sal into esal from emp where ename='SMITH';
if(esal<1000)then
dbms_output.put_line('小伙子,要努力啊');
end if;
if(esal>1000 and esal<=2000)then
dbms_output.put_line('小伙子,继续努力啊');
end if;
if(esal>2000 and esal<=3000)then
dbms_output.put_line('小伙子,还不错');
end if;
if(esal>3000 and esal<=4000)then
dbms_output.put_line('小伙子,很牛啊');
end if;
if(esal>4000 and esal<=5000)then
dbms_output.put_line('土豪啊,我们做朋友吧');
end if;
if(esal>5000)then
dbms_output.put_line('有钱的任性,没钱的认命');
end if;
end;
--------------------------------------------
8-3 if....elsif...选择
declare
esal number;
begin
--查询SMITM的工资赋值给esal*************
select sal into esal from emp where ename='SMITH';
if(esal>2000)then
dbms_output.put_line('小伙子,要努力啊');
elsif(esal>1000 and esal<=2000)then
dbms_output.put_line('小伙子,继续努力啊');
elsif( esal<=1000)then
dbms_output.put_line('小伙子,要努力啊');
end if;
end;
----------------------------------
8-4 case ....when选择
declare
esal number;
str varchar2(40);
n int;
m number;
name varchar2(20);
begin
name:='&请输入员工姓名';--弹出窗口输入
--将Scott的工资给esal
select sal into esal from emp where ename=name;
--变量赋值
m:=esal/1000;
n:=esal/1000;
-- esal:=esal/1000;
if(n>m)then
n:=n-1;
end if;
case n
when 0 then
str:='不及格';
when 1 then
str:='有进步';
when 2 then
str:='还不错';
when 3 then
str:='很牛';
when 4 then
str:='盆友';
when 5 then
str:='巅峰';
end case;
dbms_output.put_line(n||'-->'||str);
end;
----------------------------------
8-5LOOP 循环
declare
m number;
begin
m:=1;
loop
dbms_output.put_line('aaa-->'||m);
m:=m+1;
exit when m=10;--当 m=10 的时候就退出,终止循环
end loop;
end;
---------------------------------
8-6 while 当什么的时候
declare
m number;
begin
m:=1;
while m<10
loop
dbms_output.put_line('aaa-->'||m);
m:=m+1;
end loop;
end;
8-7 for 相当于foreach
declare
r emp%rowtype;--属性类型
begin
for r in(select * from emp)
loop
dbms_output.put_line(r.ename ||'-->'||r.empno);
end loop;
end;
9异常类
--oracle中的异常分为:系统异常(编号 0~20000) 和 用户异常
--用户异常(编号可用 20001 到 20999)
--系统异常:直接由oracle数据库进行管理,当出现了系统异常,数据库会自动提示
--用户异常:
--1 在声明部分声明异常
--2 在代码部分出现异常通过 raise 关键字抛出异常
--3 在exception部分通过when 来处理异常
--4 在exception部分我们可以通过raise_application_error(错误编号,'错误提示');
--也就是说将异常以系统提示的形式弹出,告知用户
--以后的oracle系统中抛出异常以后,可以通过 'ORA-编号' 搜索解决方案
--对异常的处理
declare
me01 exception;
me02 exception;
score number;
begin
score:='&请输入成绩';
if(score<0)then
--抛出第一种异常,提示分数小于0 不合法
raise me01;
else if(score>100)then
--抛出第二种异常,提示分数大于100 不合法
raise me02;
else
dbms_output.put_line('成绩合法'||score);
end if;
end if;
exception
when me01 then
dbms_output.put_line('分数小于0 不合法,输入的为:'||score);
--系统异常,会弹出error框提示
--raise_application_error(-20001,'数据错误');
when me02 then
dbms_output.put_line('分数大于100 不合法,输入的为:'||score);
end;
10存储过程procedure
--一个任务需要多个步骤完成 通常一个select语句只能完成一个步骤,而单个的select语句是没有特定的业务逻辑关系的,所以我们把多个select语句放在一个PL/SQL代码块中
--这样以来代码快就可以完成特定的任务
语法:
create procedure<proName>[(参数一,参数二....)]
as
begin
exception
end;
10-1存储过程的类型
10-1-1,无输入参数无输出参数
create or replace procedure test1
as
r emp%rowtype;
begin
dbms_output.put_line('编号 姓名 工作');
for r in (select * from emp)
loop
dbms_output.put_line(r.empno||' '||r.ename||' '||r.job);
end loop;
end;
--调用存储过程
call test1();
------------------------
10-1--2,有输入参数无输出参数
create or replace procedure test2(name varchar2)
as
esal emp.sal%type;
begin
select sal into esal from emp where ename=name;
dbms_output.put_line(name||' 的工资为: '||esal);
end;
--调用
call test2('SCOTT');
------------------------
10-1-3,无输入参数有输出参数
--用 out 来声明参数为输出参数
--out 放在输出参数名和类型之间
create or replace procedure test3(tsal out number)
as
begin
select sum(sal) into tsal from emp;
end;
--在PL/SQL块中间调用存储过程,不需要写'call'调用
--调用存储过程时,输出参数无需使用out声明
declare
ts number;
begin
test3(ts);
dbms_output.put_line('总的工资为'||ts);
end;
------------------------
10-1-4,有输入参数有输出参数
create or replace procedure test4(name in varchar2,salary out number)
as
begin
select sal into salary from emp where ename=name;
end;
--调用
declare
salary number;
name varchar2(20);
begin
name:='&员工的姓名';
test4(name,salary);
dbms_output.put_line(name||' 的工资为: '||salary);
end;
10-1-5输入/输出参数
--定义存储过程可以给 存储过程指定参数(输入参数/输出参数)
--通过 'in' 来声明一个参数为输入参数(可以省略)
--通过 'out' 来声明一个参数为输出参数(不可以省略)
--通过 'in out' 来声明一个参数既是输入参数又是输出参数
create or replace procedure test5(param in out varchar2)
as
begin
select job into param from emp where ename=param;
end;
--调用
declare
p varchar2(20):='KING';
begin
test5(p);
dbms_output.put_line(p);
end;
--把系统时间转换成指定的格式
--dual 为oracle的伪表
select to_char(sysdate,'yyyy-mm-dd') from dual;
11触发器trigger
触发器: 触发器就是一种无需调用(在发生了某个事件时) 就自动调用执行的存储过程
触发时间
--before : 在触发的事件执行之前执行触发器
--after : 在触发事件执行之后再执行触发器
11-1创建触发器after update
--创建触发器 after 修改之后执行触发器
create or replace trigger t_test1 after update on tb_user
declare
logid number:=0;
c number;
d date;
begin
--查询tb_log表 总记录条数给 c
select count(*) into c from tb_log;
if(c>0)then
--查询tb_log表 把最大的log_id给logid
select max(log_id) into logid from tb_log;
end if;
logid:=logid+1;
select sysdate into d from dual;
insertintotb_log(log_id,log_date,log_oper)values(logid,d,'update');
end;
触发:update tb_user set stu_name='李氏' where stu_num='1001';
11-2创建触发器after delete
--创建触发器 after 删除之后执行触发器
create or replace trigger t_test2 after delete on tb_user
declare
logid number:=0;
c number;
d date;
begin
--查询tb_log表 总记录条数给 c
select count(*) into c from tb_log;
if(c>0)then
--查询tb_log表 把最大的log_id给logid
select max(log_id) into logid from tb_log;
end if;
logid:=logid+1;
select sysdate into d from dual;
insert into tb_log (log_id,log_date,log_oper)values(logid,d,'delete');
end;
11-3 触发器before update
--触发器 befor在执行事件之前进行执行
create or replace trigger t_test3 before update on emp
declare
r emp%rowtype;
begin
for r in (select * from emp)
loop
dbms_output.put_line(r.ename||'-->'||r.sal);
end loop;
end;
11-4语句级触发器 : 表中的所有列
--当前的触发器只是普通的触发器,监听表中的所有的列
--只要对emp表 进行修改就会触发
--语句级触发器不管影响多少条语句都执行一次
--行级触发器 影响几次 就执行几次
create or replace trigger t_test4 before update on emp
declare
r emp%rowtype;//多条记录
begin
dbms_output.put_line('***********');
end;
------------------------------------------------------
--修改触发
update emp set job='softeem' where ename='SCOTT';
------------------------------------------------------
11-5 行级触发器 : 会执行多次
create or replace trigger t_test5 before update on emp for each row
declare
begin
dbms_output.put_line('<><><><<<><><><><');
end;
------------------------------------------------------
触发条件
update emp set sal=sal+20 where deptmo=20;
11-6当对 emp 表进行修改或者删除的时候
create or replace trigger t_test6 before update or delete on emp
declare
begin
dbms_output.put_line('&&&&&&&&&&&&');
end;
11-7列触发器
--列触发器 : 在操作后面通过 'of 列名' 来指定列触发器(update insert)
--只监听一列数据 当列进行修改的时候 执行该触发器
--update job列时执行该触发器
update emp set job='softeem' where ename='SCOTT';
-----触发器
create or replace trigger t_test7 before update of job on emp
declare
begin
dbma_output.put_line('%%%%%%%%');
end;
-------------------------------------------------
select * from tb_user;
insert into tb_user(stu_num,stu_name,stu_sex,stu_birthday)
values (1010,'张三','男',to_date('2013-2-2','yyyy-mm-dd'));
--:new 用在insert中表示新添加的这条数据
create trigger t_test8 before insert on tb_user for each row
declare
begin
if(:new.stu_sex='男' or :new.stu_sex='女')then
dbms_output.put_line('数据正确');
else
dbms_output.put_line('数据错误');
end if;
end;
-----------------------------
--:old 用在delete语句中 表示删除的语句
delete from tb_user where stu_num=1010;
--触发器
create or replace t_test9 after delete on tb_user for each row
declare
begin
dbms_output.put_line(:old.stu_num||'--->'||:old.stu_name);
end;
-----------------------------------------------------------------
--禁用触发器
alter trigger t_test9 disable;
--解除禁用
alter trigger t_test9 enable;
12函数 和 游标
12-1函数 function
函数 : 是一个特殊的存储过程 相当于java中的方法
--存储过程只有参数(输入/输出参数)
--函数总有输入参数 但是有返回值
--函数 的定义:有参数 function
create or replace function f_test1(m number,n number)
return number
as
a number;
begin
a:=m+n;
return a;
end;
-------------------------------------------
12-1-1无参数的函数
create or replace function f_test2
return varchar2
as
name varchar2(20);
begin
--into 一种给变量的赋值方式 ,把值给name
select dname into name from dept where deptno=20;
return name;
end;
12-1-2调用函数
--1,通过dual 系统表(数据字典)调用
select f_test1(3,2) from dual;
select f_test2() from dual;
--2,在 PL/SQL 块中调用
declare
s number;
begin
--调用方法 并把返回值赋给 s
s:=f_test1(3,9);
dbms_output.put_line(s);
end;
---------------------------------------------
12-2常见的系统函数
1,instr
instr(str1,str2):indexof
--返回第二个字符串在第一个字符串中第一次出现的位置
--如果第一个字符串中不包含第二个字符串返回 0
declare
num number;
begin
num:=instr('HELLOWORD','H');
dbms_output.put_line(num);
end;
2,lpad和rpad
lpad(str1,len,str2) : 将 str2 拼接到 str1 的左边,直到str1的长度等于 len
rpad(str1,len,str3) : 将 str2 拼接到 str1 的右边,直到str1的长度等于len
declare
str varchar2(20);
begin
str:=lpad('abcd',7,'1234');
dbms_output.put_line(str);
str:=rpad('abcd',7,'1234');
dbms_output.put_line(str);
end;
3,trim
--trim:去除字符串首尾的空格
--ltrim:去除字符串左边(首)的空格
--rtrim:去除字符串右边(尾)的空格
declare
str varchar2(20);
begin
str:=rtrim(' abc bbb ');
dbms_output.put_line('****'||str||'******');
end;
4,ceil
--ceil(num):返回大于等于num 的最小整数(只入不舍)
begin
dbms_output.put_line(ceil(15.1));
end;
5,floor
--floor(num):返回小于等于num 的最小整数(只舍不入)
begin
dbms_output.put_line(floor(15.999));
end;
6,round
--round(num) : 四舍五入
begin
dbms_output.put_line(round(15.999));
end;
7,power
--power(num1,num2) : 返回 num1 的 num2 次方的值
select power(10,3) from dual;
8,trunc
--trunc(num1,num2)保留num1的num2位小数
select trunc(3.1415926,4) from dual;
-------------------------------------------
13游标 cursor
--游标概念:游标相当于一个结果集,用来存储和遍历
--一个查询SQL的结果集---> ResultSet
13-1图解游标
cursor my_cur <---select * from emp
r emp%rowtype;
loop
my_cur----->r
exit when my_cur;
end loop;
13-2游标的分类 :
--静态游标 :在定义静态游标的时候要指定其结果集
--隐式游标:(系统游标)无需定义其初始化,直接使用
--显示游标:(自定义游标)用户自己定义的游标变量,并手动将结果集赋值给游标
--ref 游标:动态赋予结果集
--隐式游标:是由oracle系统提供,我们可以直接使用的
--特性:自动创建打开和关闭
--隐式游标的属性:
--SQL%FOUND--如果SQL游标有结果集返回true
--SQL%NOTFOUND--如果SQL游标没有结果集返回true
--SQL%ROWCOUNT--返回SQL游标中的记录条数
--SQL%ISOPEN--始终返回false.
begin
update tb_user set stu_name='哈哈' where stu_num=1002;
dbms_output.put_line(SQL%ROWCOUNT);
end;
select * from tb_user;
13-2-2显式游标
--显式游标: 用户自己定义的游标,在定义的时候同时需要通过'IS'指定结果集
--特性:
--1,在定义的时候就需要指定结果集
--2,遍历的时候
--打开游标--->取值---->关闭游标
declare
cursor my_cur IS select * from emp;
e emp%rowtype;
begin
--打开游标
open my_cur;
loop
--对游标进行抓取
fetch my_cur into e;
exit when my_cur%NOTFOUND;
dbms_output.put_line(e.empno);
end loop;
--关闭游标
close my_cur;
end;
---for 循环变量游标,无需使用手动打开和关闭游标
declare
--把查询emp表记录的值 给游标
cursor my_cur is select * from emp;
begin
--对游标进行循环打印
for e in my_cur loop
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
end;
13-2-3 ref游标
--ref 游标:动态结果集游标 ,在声明的时候无需指定结果集
declare
--声明一个ref游标 : 1声明一个类型为 ref cursor
type my_type is ref cursor;
--2,声明一个变量
res my_type; -->studernt---stu
sqlStr varchar2(200);--存放sql查询语句
e emp%rowtype;--结果对象(存放查询出的某一条记录)
begin
sqlStr:='select * from emp';
--打开游标 通过for来指定结果集
open res for sqlStr;
loop
fetch res into e;
exit when res%notfound;--当游标没有值的时候就退出
dbms_output.put_line(e.empno||'-->'||e.sal);
end loop;
close res;
end;
13-3在带有参数的存储过程中使用带有参数的游标
create or replace procedure emp1(sal1 number,sal2 number)
as
s number:=sal1+sal2;
--声明一个游标的时候可以带参数,在is后面的select中可以使用参数
cursor my_cur(p1 number,p2 number)is
select * from emp where sal between p1 and p2;
e emp%rowtype;
begin
dbms_output.put_line(s);
--当打开游标的时候将参数传递给游标 先是SQL语句的加载参数 然后
--执行SQL语句,将执行的结果赋值给游标
open my_cur(sal1,sal2);
loop
fetch my_cur into e;
exit when my_cur%notfound;
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
close my_cur;--关闭游标
end;
--调用
call emp1(2000,3000);
13-4利用游标对数据进行修改
select * from emp where ename like '%M%';
update emp set sal=sal+10 where ename like '%M%';
---创建游标
declare
--for update 和下面的 current of 进行对应
cursor my_cur is select * from emp where ename like '%M%'for update;
e emp%rowtype;
begin
open my_cur;
loop
fetch my_cur into e;
exit when my_cur%notfound;
--游标遍历出一个符合条件的用户,我们就修改一个
update emp set sal=sal+10 where current of my_cur;
end loop;
close my_cur;
commit;
end;
14 程序包package
--用来存放存储过程 函数 游标 等
--程序包有两部分组成:包的声明部分(package),包的实现部分(package body).
--package : 只包含存储过程 触发器 游标 函数 等的声明（接口）
--package body : 存储过程 触发器 函数 等的实现部分
create or replace package pack_test1
is
procedure proc_test1(num1 number,num2 number);
--函数 没有参数的时候函数名后面不带括号
function func_test1 return number;
type mytype is ref cursor;--定义游标 类型为 ref cursor
end pack_test1;
---------------------------------------------------------
create or replace package body pack_test1
is
--对存储过程的实现
procedure proc_test1(num1 number,num2 number)
as
begin
for e in (select * from emp where sal between num1 and num2)
loop
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
end;
--对函数的实现
function func_test1 return number
as
num number;
begin
select count(*)into num from emp;
return num;
end;
end pack_test1;
14-1调用存储过程 函数
--包中的存储过程和函数的调用与普通的调用方式相同
--只是在存储过程/函数前面加上'包名'
call pack_test1.proc_test1(1000,2000);
select pack_test1.func_test1 from dual;
14-2 PL/SQL 块中不能单独的执行select 语句
--如果有select语句 那么就在PL/SQL块中就是赋值语句
declare
num number;
begin
select empno into num from emp where empno =122;
end;
---------------------------------------------
create or replace procedure emp2(sal1 number,sal2 number,cs out pack_test1.mytype)
as
sqlStr varchar2(200);
begin
sqlStr:='select * from emp where sal between 1000 and 2000';
open cs for sqlstr;
end;
-----------------------------------------------
--调用带存储过程的游标
declare
cur pack_test1.mytype;--包里面的游标 ref cursor
e emp%rowtype;
begin
emp2(1000,2000,cur);
loop
fetch cur into e;
exit when cur%notfound;
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
end;
15 设置自动增长
--序列(sequence) : MySQL : auto_increment SQLServer:identity by (1,1)
--oracle 中的列不能实现自动增长 我们可以使用序列来实现
--创建序列
--序列名: seq_test1
--序列的初始值: 1
--序列的增量: 1
--序列的最大值: 9999
----获取序列当前值: currentVal
----下一个值 : nextVal
---获取到下一个值,并把下一个值设置为当前值
--第一次nextVal返回的是初始值,随后nextVal会自动增长你定义的 increment by
--然后返回的是增加后的值
--currentval 返回的是当前sequence 的值 但是在第一次使用nextVal初始之后才能使用currentval 否则回报错
--如果指定cache 值(缓存): oracle就会预先在内存里放一些sequence 这样在获取的时候,就更快一点,cache里面取完之后 oracle会再存放一组到cache中,使用cache的时候或许会跳号 必须数据库正常的关闭 ,否则这个时候cache中的sequence会丢失,所以在创建序列的时候尽量nocache防止这种情况.
--创建序列
create sequence seq_test1
start with 1 --序列从 1 开始
increment by 1 --每次增长 1
maxvalue 9999 --最大值 nomaxvalue(不设置最大值)
nocache --缓冲区 默认20 ,nocache(不建立缓冲区)
order;//结束
create table users(
user_id int primary key,
user_name varchar2(20) unique not null,
user_pwd varchar2(16)
);
insert into users values(seq_test1.nextval,'aa','123');
insert into users values(seq_test1.nextVal,'bb','124');
select * from users;
select * from roles;
--清空表
truncate table users;
truncate table roles;
drop table users;
--------------------------------------------------------------
--创建一张角色表
create table roles(
role_id int primary key,
role_name varchar2(32)not null,
role_des varchar2(100)
);
--共用一个序列
insert into roles values(seq_test1.nextval,'系统管理员','最高权限');
insert into roles values(seq_test1.nextval,'游客','普通权限');
------------------------------------------------------------------
--创建班级表
create table tb_class(
class_no char(10) primary key,--主键
class_name varchar2(32) not null unique,
class_num number not null
);
--多种数据表存储结构.常用的三种:
--1,堆表(heap table)
--2,索引组织表(index organization table)
--3,聚簇表(cluster table)
--创建学生表
create table tb_student(
stu_no char(10) primary key ,
stu_name varchar2(20),
stu_sex varchar2(2) not null check(stu_sex in('男','女')),
class_no char(10) ,
--将tb_class表的class_no 字段设置成外键,级联删除
foreign key(class_no) references tb_class(class_no)on delete cascade
)organization index
--按索引组织的表,如果要将一张表设置成按索引的表,这张表就一定要有主键
storage(--设置存储的参数
initial 4k ---初始区间的大小
next 2k --下一个区间的大小
minextents 6 --最小区间数
maxextents 99 --最大区间数
pctincrease 0 --下一个区间相对与上一个区间的增量,建议为0,减少系统开销
);
select * from tb_class;
select * from tb_student;
----添加数据
insert into tb_class values('1班','java班',33);
insert into tb_class values('2班','安卓班',30);
insert into tb_student values('1001','钟竹','男','1班');
insert into tb_student values('1002','李氏','男','2班');
--设置了级联删除,直接删除主键,会把外键一起删除
delete from tb_class where class_no='1班';
-------------------------------------------------------------
--伪列
select rownum,ename ,sal from emp where sal>1500;
select rownum,deptno ,dname ,loc from dept;
--伪列 rownum 是oracle 自带的,对查询的结果自动排序
select empno from emp where rownum <=5;
select * from emp where empno not in (select empno from emp where rownum <=5) and rownum <=5 ;
---------------------------------------分-页-----------------------------------------
---创建存储过程分页
create or replace procedure split01(
tableName varchar2,
pk varchar2,
pageSize number,
pageNum number,
cs out sys_refcursor
)as
sqlStr varchar2(200);
sqlStr2 varchar2(100);
excCount number:=pageSize*(pageNum-1);
begin
sqlStr2:=' select ' ||pk|| ' from ' ||tableName|| ' where rownum<=' ||excCount ;
sqlStr:='select * from ' ||tableName|| ' where ' ||pk|| ' not in (' ||sqlStr2|| ') and rownum<=' ||pageSize ;
dbms_output.put_line(sqlStr);
open cs for sqlStr;
end;
注释:分页SQL语句
Select * from 表名 where pk(分页的排序主键) not in (select pk from 表名 where rownum <= (当前页-1)*每页显示的个数 ) and rownum<=每页显示的个数
---------------------------------
--调用
declare
cs sys_refcursor;
e emp%rowtype;
begin
split01('emp','empno',5,1,cs);
loop
fetch cs into e;
exit when cs%notfound;
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
end;
16 分页存储过程
create or replace procedure proc_split2(
pageNum in number,--页数
pageSize in number,--每页的记录数
tableName in varchar2,--表
priColum in varchar2,--主键
condition in varchar2,--分页条件
--输出参数
pageCount out number,--总页数
recordCount out number,--总记录数
resultCur out sys_refcursor--游标:存放查询的数据
)as
vsql varchar2(200);--分页sql语句
vsql2 varchar2(100);
vsql3 varchar2(100);--统计总记录数
pNum number;
begin
pNum:=pageNum;--页码
vsql3:='select count(*) from '||tableName||' where 1=1 '||condition;
execute immediate vsql3 into recordCount;
--计算总页数
pageCount:=ceil(recordCount/pageSize);
--判断pageNum的合法性
if pageNum<=0 then
pNum:=1;
elsif pageNum>=pageCount then
pNum:=pageCount;
end if;
--拼接分页sql语句
vsql2:='select '||priColum||' from '||tableName||' where rownum<= '||pageSize*(pNum-1);
vsql:='select * from '||tableName||' where '||priColum||' not in ( '||vsql2||' ) and rownum<= '||pageSize||' '||condition;
dbms_output.put_line(vsql);
--将vsql执行结果赋值给游标
open resultCur for vsql;
end;
-------------------------
--调用带判断的存储过程分页
declare
pc number;
rc number;
rcur pack_test1.mytype;--包里游标
e emp%rowtype;
begin
proc_split2(3,3,'emp','empno','and sal>1500',pc,rc,rcur);
dbms_output.put_line('总记录数:'||rc);
dbms_output.put_line('总页数:'||pc);
loop
fetch rcur into e;
exit when rcur%notfound;
dbms_output.put_line(e.ename||'-->'||e.sal);
end loop;
end;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
