| Oracle日期函数 |
oracle日期函数 |
|
--得到当前时间
select sysdate from dual
create table reg(
name varchar(20),
pwd varchar(20),
regdate date
)
insert into reg values('guolijuan','123456',sysdate)
select * from reg
--转换函数 字符串转换为日期
select to_date('2008-10-15 20:15:10','yyyy-MM-dd HH24:MI:SS') from dual
--to_char
select to_char(sysdate,'yyyy"年"MM"月"dd"日"') from dual
--9i
select to_date('2008-10-15 10:15:10','yyyy-MM-dd HH:mm:SS') from dual
--add_months 给月份加2
select add_months(sysdate,2) from dual
create table huiyuan(
name varchar(20),
jiarudate date,
daoqidate date
)
insert into huiyuan values('zhangsan',to_date('2008-12-15','yyyy-MM-dd'),
to_date('2010-12-15','yyyy-MM-dd'))
select * from huiyuan
--修改到期时间
update huiyuan set daoqidate=add_months(daoqidate,24) where name='zhangsan'
--两个日期之间的月份的差值
select months_between(sysdate,to_date('2008-11-15','yyyy-MM-dd')) from dual
select months_between(sysdate,daoqidate) from huiyuan
--求某个日期的最后一天
select last_day(sysdate) from dual
--next_day 当前日期的下一个星期几 1——7 表周日到周六
select next_day(sysdate,1) from dual
select next_day(to_date('2010-5-5','yyyy-MM-dd'),1) from dual
--round
select round(to_date('2010-8-5','yyyy-MM-dd'),'yyyy') from dual
select trunc(sysdate,'yyyy') from dual --截取
--首字母变大写
select initcap('aaaa') from dual
insert into huiyuan values(initcap('gggg'),sysdate,add_months(sysdate,24))
select * from huiyuan
select upper(name) from huiyuan
--ltrim 去除左边空格 rtrim 去除右边空格 length去长度
select length(rtrim(ltrim(' ssss '))) from dual
select length(trim(' sssss ')) from dual
select rtrim(ltrim('abcguolijuanabcd','abc'),'abcd') from dual
select * from huiyuan where name=trim(' zhangsan')
select translate('abcta','t','r') from dual
select replace('guoliutttjuaneee','ttt','99') from dual --字符串替换
select replace(' guoliujuan egetete',' ','') from dual
select instr('ggeabggab','ab',5) from dual --某个字符在字符串中出现的位置
select substr('guolijuanee',5,4) from dual --截取字符串
select concat('tttt','rrrr') from dual
select 'tttt'||'tgggg' from dual
select * from huiyuan where name='gggg'
select chr(65) from dual
select ascii('a') from dual
select lpad('ttteetddd',2) from dual
select decode('good', 'good', 'yes','no') from dual;
select * from huiyuan
select decode(name,'zhangsan','张三','ddd') from huiyuan
insert into huiyuan(name) values(null)
select decode(name,null,'',name) from huiyuan
select power(5,5) from dual
select ceil(-7878.5) from dual
select floor(7878.5) from dual
select round(7878.489,2) from dual
select mod(0,3) from dual
select trunc(7878.578,2) from dual
select to_number('7878.55')+10 from dual
--nvl 但第一个表达式为null的时候 ，用第二个表达式来替换,表达式不为空返回本身
select h.*,h.rowid from huiyuan h
select nvl(name,'t') from huiyuan
--nvl2 当第一个表达式不为null的时候，显示第二个表达式的值，当第一个表达式为空的时候，返回第三个表达式的值
select jiarudate,daoqidate,nvl2(name,'one','two') from huiyuan
--nullif 当第一个表达式和第二个表达式相等的时候返回null
select nullif(name,'zhangsan') from huiyuan
create table stu_5(
stuname varchar(20),
stuno varchar(20),
stusex char(3),
stuage int,
stuseat int,
stuaddress varchar(50)
)
insert into stu_5 values('张秋丽','s25301','男',18,1,'文化路')
insert into stu_5 values('李文才','s25302','男',31,2,'金水路')
insert into stu_5 values('李斯文','s25303','女',22,3,'陇海路')
insert into stu_5 values('欧阳俊雄','s25304','男',28,4,'东风路')
insert into stu_5 values('梅超风','s25318','女',23,1,'地址不祥')
insert into stu_5 values('李斯文2','s25305','女',22,3,'陇海路')
select s.*,s.rowid from stu_5 s
--得到李斯文的年龄
select * from stu_5 where stuage>(
select stuage from stu_5 where stuname='李斯文'
)
--把除了李斯文之外的学员年龄+10
update stu_5 set stuage=stuage+10 where stuname in('李斯文' ,'李斯文1','梅超风')
--查询下，欧阳俊雄的左右学员是谁
select * from stu_5 where stuseat =(
select stuseat from stu_5 where stuname='欧阳俊雄')+1
or stuseat =(
select stuseat from stu_5 where stuname='欧阳俊雄'
)-1
select * from cj_2
select * from xueke
select * from students
select * from students where id=(
select sid from cj_2 where fenshu=60 and xid=(
select xid from xueke where xname='语文'
)
)
--表连接来做
select s.*,c.fenshu,x.xname from students s inner join cj_2 c on s.id=c.sid inner join xueke x on c.xid=x.xid
where c.fenshu=60 and x.xname='语文'
select * from cj_2
select * from students where id in(
select sid from cj_2 where fenshu>=80
)
exists
|
| Oracle索引，约束 |
|
|
创建oracle 用户
create user guo identified by guolijuan
给guo用户授予权限
grant dba to guo
select sysdate from dual;
创建一张表
create table students(
sname varchar2(20),
age int,
xuefei number(8,2) ,--定义带小数位的 第一个参数是精度 ，第二个参数是小数位数
add_date date
)
insert into students values('guolijuan9992',55,5555.05,sysdate)
select a.*,a.rowid,rownum from students a where age=65 and rownum<3
--删除表
drop table system.tt
drop table students
--增加一列
alter table students add sid int
--修改表的列
alter table students modify sname varchar(50)
--删除一列
alter table students drop column sname
--插入一条记录
insert into students (sname,age) values ('lisi1',30)
insert into students (sname,age,add_date,xuefei)
values('zhaotttt',20,to_date('2009-11-01','yyyy-MM-dd'),889.98)
--对日期类型的插入值的时候一定要进行转换
--事物提交
commit
--事物回滚
rollback
select * from students
--删除一行
delete from students where sname='zhangsan'
delete from students -- 删除表中的所有行，可以回滚
truncate table students --删除表中的所有行 不能回滚
--修改表中某一个某一个列的值
update students set xuefei=899.85 where sname='lisi1'
-- 加上where条件，先查询到满足条件的记录，在修改记录即可
update students set xuefei=990,add_date=to_date('2011-1-1','yyyy-MM-dd')
where age>=20
--所有行所有列
select * from students
--所有行某些列
select sname,age from students
--某些列 某些行 查询年龄=30岁的学生姓名和年龄，
select sname,age from students where age=30
--某些行所有列 查询年龄=30岁的学生的信息
select * from students where age=30
--查询add_date 在2001-1-1 到2009-12-1之间的学员信息
select * from students where add_date>=to_date('2001-1-1','yyyy-MM-dd')
and add_date <= to_date('2009-12-1','yyyy-MM-dd')
--查询姓zhao的学员信息
select * from students where sname like 'zhao%'
--创建表
create table students(
sid int primary key,--加上主键 非空 唯一
sname varchar(20),
idcard varchar(18) unique --唯一约束 唯一约束可以有null
)
--创建表2
create table students(
sid int ,
sname varchar(20) ,
idcard varchar(18) ,
constraints pk_sid primary key(sid),--加上主键 非空 唯一
constraints un_idcard unique(idcard) --唯一约束 唯一约束可以有null
)
--在表创建好之后添加一个约束
alter table students add constraints sname_unique unique(sname)
alter table students modify sname varchar(20) not null
insert into students values(5,null,'454545')
select * from students
create table stu(
sid int primary key,
sname varchar(20) not null unique,
sex char(3) default('男') check (sex='男' or sex='女') --default 默认约束 check检查约束
)
insert into stu values(3,'张三','男')
insert into stu (sid,sname)values(1,'王五')
select * from stu
create table stu2(
sid int primary key,
sname varchar(20),
ruxue date,
biye date,
constraints biye_check check(biye>ruxue)
)
insert into stu2
values(1,'zhangsan',to_date('2009-1-1','yyyy-MM-dd'),
to_date('2011-1-1','yyyy-MM-dd'))
select * from stu
create table cj(
sid int references stu(sid),
scj number(5,2)
)
insert into cj values(2,90)
select * from cj
delete from cj where sid=1
delete from stu where sid=1
create table stu3(
sid int ,
sname varchar(20) ,
age int,
constraints pk_stu3 primary key(sid,sname)
)
insert into stu3 values(2,'张三')
select * from stu3
-- 删除列
alter table stu3 drop column age
--删除约束
alter table stu3 drop constraints pk_stu3
--查看系统的数据字典
select * from user_constraints
select * from user_cons_columns
create table stu4(
sid int primary key,
sname varchar2(20)
)
--手动创建索引
create index stu4_sname on stu4(sname)
insert into stu4 values(2,'lisi')
select * from stu4 where sname='lisi'
--删除索引
drop index stu4_sname
--查看索引
select * from user_indexes
select * from user_ind_columns
--创建序列
create sequence t
--使用序列
select t.nextval from dual --获取下一个序列值
select t.currval from dual --获取当前序列值
create sequence tt
maxvalue 10
minvalue 1
cache 9
cycle --cycle 到最大值的时候从最小值开始 ， nocyle到最大值之后不从头来
select tt.nextval from dual
select * from stu4
delete from stu4
insert into stu4 values(t.nextval,'sss')
create sequence ttt
start with 10
increment by -1
maxvalue 10
select ttt.nextval from dual
drop sequence g --删除序列
alter sequence ttt increment by -3--修改序列
--别名
select * from stu4
select s.sid||sname as 学生信息 from stu4 s
select stu4.sid from stu4
select distinct sname from stu4 --去除select中重复行
select * from stu4
create table stu5(
sid int primary key,
sname varchar2(20),
age int
)
insert into stu5 values(5,'aaa',62)
select * from stu5
-- between ...and
select * from stu5 where age between 20 and 30
select * from stu5 where sname between 'l' and 'zzzz'
--like
select * from stu5 where sname like '_lis%' -- _表示任意单个字符 % 表示任意0-多个字符
update stu5 set sname='' where sid=3
--null
select * from stu5 where sname is null
insert into stu5 (sid,age) values(50,30)
--排序
select * from stu5 order by sname asc, age+50 desc
select s.*,s.rowid from stu5 s
|
| Oracle子程序(存储过程.函数，游标，包) |
oracle子程序 存储过程 函数 游标 包 |
|
--loop
declare
x int:=0;
begin
loop
dbms_output.put_line('tttt');
x:=x+1;
exit when x=10; --当x=10 的时候退出
end loop;
end;
--while
declare
i int:=0;
begin
while i<10 loop
dbms_output.put_line('*');
if i=5 then
exit;
end if;
i:=i+1;
end loop;
end;
--for
declare
i int:=0;
begin
for i in 1..5
loop
dbms_output.put_line(i);
end loop;
end;
select * from stu
--goto
declare
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1004;
if s_age <20 then
goto t; --跳转到指定的位置
dbms_output.put_line('xiaoyu20');
elsif s_age>20 and s_age<=50 then
goto f;
end if;
<<t>>
dbms_output.put_line('20');
<<f>>
null; --占用一行的位置，但是什么都不错
end;
--错误处理
declare
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1001;
dbms_output.put_line(s_age);
dbms_output.put_line(s_age/0);
exception
when TOO_MANY_ROWS then --返回的行数大于实际需要的行数
dbms_output.put_line('返回的行数太多');
when no_data_found then --没有查到信息
dbms_output.put_line('没有找到记录');
when others then
dbms_output.put_line('有错误');
end;
--自定义异常
declare
myexception exception;
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1001;
if s_age<18 then
raise myexception; --手动抛出一个异常
end if;
exception
when myexception then --捕获异常
dbms_output.put_line('年龄太小');
-- raise_application_error(-20888,'年龄太小');--抛出一个系统的异常信息
end;
--引发系统异常
declare
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1001;
if s_age<18 then
raise_application_error(-20888,'年龄太小');--抛出一个系统的异常信息
end if;
end;
--定义一个过程
create or replace procedure test1
as
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1001;
if s_age<18 then
raise_application_error(-20888,'年龄太小');--抛出一个系统的异常信息
end if;
end;
--调用存储过程
call test1()
--带参数的存储过程 根据输入的sid来查找学员的年龄
create or replace procedure stuage(s_id in stu.sid%type)
as
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=s_id;
dbms_output.put_line('年龄是'||s_age);
exception
when others then
dbms_output.put_line('错误');
end;
call stuage(1004)
--带参数的存储过程 根据输入的sid来查找学员的姓名
create or replace procedure getstusnamebysid(s_id in stu.sid%type,ssname out stu.sname%type)
as
s_name stu.sname%type;
begin
select sname into s_name from stu where sid=s_id;
ssname:=s_name;
exception
when others then
dbms_output.put_line('错误');
end;
select * from stu
--调用存储过程
declare
sid stu.sid%type;
s_name stu.sname%type;
begin
sid:=1004;
getstusnamebysid(sid,s_name);
dbms_output.put_line(s_name);
end;
--带参数的存储过程 根据输入的sid来查找学员的年龄
create or replace procedure getstusagebysid(s_id_age in out int) -- in out
as
s int;
begin
select sage into s from stu where sid=s_id_age;
s_id_age:=s;
exception
when others then
dbms_output.put_line('错误');
end;
declare
ss int;
begin
ss:=1005;
getstusagebysid(ss); --ss是in out参数 可以得到要返回来的值
dbms_output.put_line('得到的信息是：'||ss);
end;
create user ls identified by ls
grant connect to ls
grant execute on test1 to ls --授予在test1过程上的执行权限
grant execute any procedure to ls --授予ls执行任何过程的权限
drop procedure test1 --删除存储过程
---创建函数
create or replace function mulx_y
return int --表明函数返回值
as
x int;
y int;
begin
x:=3;
y:=5;
return x*y;
end;
--用select语句可以执行函数
select mulx_y() from dual
--创建一个带参数的函数
create or replace function getstuagebyid(id in int)
return int
as
s_age int;
begin
select sage into s_age from stu where sid=id;
id:=s_age;
return s_age;
end;
select getstuagebyid(1001) from dual
declare
s int;
x int;
begin
s:=1005;
x:=getstuagebyid(s);
dbms_output.put_line('eee'||x);
end;
---
create or replace function getsnamebyid(id int)
return varchar2 --返回值执行写类型，不能写具体的长度
as
ssname stu.sname%type;
begin
select sname into ssname from stu where sid=id;
return ssname;
end;
--隐式游标
declare
s varchar(100);
begin
select sname into s from stu where sid=1010;
if SQL%found then --查询游标中是否找到值
dbms_output.put_line('查询到了');
end if;
if SQL%notfound then
dbms_output.put_line('未查询到了');
end if;
if SQL%isopen then
dbms_output.put_line('打开');
else
dbms_output.put_line('关闭');
end if;
end;
begin
update stu set sage=sage+1 where sid=1001 ;
if SQL%notfound then --在执行DML语句的时候没有找到
dbms_output.put_line('没有更新值');
end if;
dbms_output.put_line(SQL%rowcount); --游标中有几行
end;
--显式游标
declare
cursor mycursor is select * from stu ;--声明游标
student stu%rowtype;
begin
open mycursor; --打开游标
loop
fetch mycursor into student; --提取游标
dbms_output.put_line(student.sid || ' '||student.sname);
exit when mycursor%notfound; --当到最后找不到值的退出
end loop;
close mycursor;--关闭游标
end;
--用游标来更新数据
declare
cursor mycursor1 is select * from stu where sid<=1003 for update;
student stu%rowtype;
begin
open mycursor1;
loop
fetch mycursor1 into student;
exit when mycursor1%notfound;
update stu set sname='dddd' where current of mycursor1; --更新的时候根据已经查询到的游标
--delete from stu where current of mycursor1; --删除的时候根据游标中已经查到的删除
end loop;
close mycursor1;
end;
--循环游标
declare
cursor mycursor2 is select * from stu;
begin
--循环游标
for s in mycursor2 --s变量可以不用声明
loop
dbms_output.put_line(s.sid|| ' '||s.sname);
end loop;
end;
|
| Oracle表分区 |
oracle表分区 |
|
--字符集转换的时候
select CONVERT('郑云飞' , 'WE8MSWIN1252', 'WE8MSWIN1252') from dual
--确认你的数据库支持该字符集
select * from v$nls_valid_values where parameter= 'CHARACTERSET'
--范围分区
create table stu(
sid int primary key,
sname varchar(20),
sage int
)
partition by range(sage)
(
partition p1 values less than (30),
partition p2 values less than (60),
partition p3 values less than (maxvalue)
)
insert into stu values(1005,'李四',20)
--查看
select * from stu partition (p1)
--删除某个分区中的值
delete from stu partition (p2) where sname='李四'
--散列分区
create table stu_1 (
sid int primary key,
sname varchar(20),
sage int
)
partition by hash(sname)
(
partition p1 ,
partition p2
)
insert into stu_1 values(1003,'eeee',20)
select * from stu_1 partition(p2)
-------
create table stu_2(
sid int primary key,
sname varchar(20),
sage int
)
partition by hash(sname)
partitions 3 --在这个表上划分3个分区
insert into stu_2 values(1003,'eeee',20)
select * from stu_2
-------列表分区
create table stu_3(
sid int primary key,
sname varchar(20),
sage int,
address varchar(50)
)partition by list(address)
(
partition p1 values ('河北','北京','黑龙江'),
partition p2 values ('广州','珠海','广西'),
partition p3 values (default) --除了上面定义的之外的值都进入p3
)
insert into stu_3 values(1005,'lisi',20,'山西')
select * from stu_3 partition(p3)
--复合分区
create table stu_4(
sid int primary key,
sname varchar(20),
sage int
)
partition by range(sage)
subpartition by hash(sname)
subpartitions 3 --表明每个大分区中包含3个小分区
(
partition p1 values less than(30),
partition p2 values less than(60),
partition p3 values less than(maxvalue)
)
insert into stu_4 values(1001,'dddd','25')
select * from stu_4 partition (p1)
create table stu_5(
sid int primary key,
sname varchar(20),
sage int
)
partition by range(sage)
subpartition by hash(sname)
(
partition p1 values less than(30)(
subpartition p11,
subpartition p12
),
partition p2 values less than(60)(
subpartition p21,
subpartition p22,
subpartition p23
),
partition p3 values less than(maxvalue)(
subpartition p31,
subpartition p32
)
)
----分区操作
--范围分区
create table stu_6(
sid int primary key,
sname varchar(20),
sage int
)
partition by range(sage)
(
partition p1 values less than (30),
partition p2 values less than (60)
)
--增加一个分区
alter table stu_6 add partition p5 values less than(maxvalue)
insert into stu_6 values(1002,'ddd',30)
select * from stu_6 partition(p2)
---删除分区
alter table stu_6 drop partition p3
--截断分区
alter table stu_6 truncate partition p5
--合并分区
alter table stu_6 merge partitions p1,p2 into partition p6
--拆分表分区
alter table stu_6 split partition p6 at(35) into (partition pp1,partition pp2)
select * from stu_6 partition(pp2)
续集：
---创建一个pl/sql块
begin
dbms_output.put_line('测试');
end;
--在sql*plus 显示dbms_output.put_line打印的值的话用
--set serveroutput on
select s.*,s.rowid from stu s
declare
s_age int;
s_name varchar(20);
s_id int;
ss_age int;
begin
select sage into s_age from stu where sname='李四';
dbms_output.put_line('李四的年龄'|| s_age);
select sid,sname,sage into s_id, s_name,ss_age from stu where sage>s_age;
dbms_output.put_line('sid='|| s_id ||' sname='||s_name||' sage='||ss_age);
end;
declare
s_age stu.sage%type; --表明此变量应该跟stu表中的sage是同一类型，
student stu%rowtype; --表明此变量时stu表的一行的类型
begin
select sage into s_age from stu where sname='李四';
dbms_output.put_line('李四的年龄'|| s_age);
select * into student from stu where sage>s_age;
dbms_output.put_line('sid='|| student.sid ||' sname='||student.sname||' sage='||student.sage);
exception
when others then
dbms_output.put_line('返回的行数大于1');
end;
declare
s_age stu.sage%type; --表明此变量应该跟stu表中的sage是同一类型，
student stu%rowtype; --表明此变量时stu表的一行的类型
s_name constant varchar(100):='李四'; --定义一个常量 对于常量的话要赋初始值
begin
select sage into s_age from stu where sname=s_name;
dbms_output.put_line('李四的年龄'|| s_age);
select * into student from stu where sage>s_age;
dbms_output.put_line('sid='|| student.sid ||' sname='||student.sname||' sage='||student.sage);
exception
when others then
dbms_output.put_line('返回的行数大于1');
end;
declare
f boolean:=true; --布尔类型
s_age int;
ss_age s_age%type;
begin
f:=false;
if f then
dbms_output.put_line('李四的年龄');
end if;
end;
declare
t date;
begin
t:=to_date('2009-1-1','yyyy-mm-dd');
dbms_output.put_line(t);
end;
create table books(
bid int primary key,
bname varchar(20),
bcontext Clob
)
insert into books values(1,'盗墓笔记','身上算所烧上的发挥快考试的发生的发生咖啡壶日日日日日柔柔弱弱上的方式付款后skf上的开发和思考')
select * from books
declare
contexts books.bcontext%type;
cc varchar(500);
ff int:=3;
begin
select bcontext into contexts from books;
dbms_lob.read(contexts,ff,9,cc);
dbms_output.put_line(cc);
end;
--条件if
select s.*,s.rowid from stu s
declare
s_age stu.sage%type;
begin
select sage into s_age from stu where sid=1001;
if s_age<20 then
dbms_output.put_line('您很年轻');
update stu set sname='ddd' where sid=1001 ;
elsif s_age<60 then
dbms_output.put_line('强壮');
else
dbms_output.put_line('老了');
end if;
end;
--case
declare
sage int:=35;
begin
case sage
when 30 then dbms_output.put_line('30岁');
when 40 then dbms_output.put_line('40岁');
else dbms_output.put_line('其他');
end case;
end;
begin
case '&sage'
when 30 then dbms_output.put_line('30岁');
when 40 then dbms_output.put_line('40岁');
else dbms_output.put_line('其他');
end case;
end;
|
| Oracle程序包和触发器练习 |
oracle程序包和触发器练习 |
|
一、创建程序包
问题
要求创建用于输入新职员的程序包，并确保新职员的数据存在于数据库中。
分析
编写在emp表中添加新职员的记录，须在程序包中创建过程，以便在emp表中插入新职员的信息。创建函数接受empid已检查职员是否已经存在，如果已存在，则显示消息“职员已存在”。
解决方案
(1) 启动sql*plus，已scott用户登录，输入以下代码。
(2) 输入以下代码以创建程序包主体。
要执行程序包中的过程，请输入以下命令。
要调用程序包中的函数，请输入以下命令。
二、
1、编写一个程序包，包含有两个过程和一个函数，第一个过程根据职员编号打印姓名，第二个过程根据编号打印职员薪水。函数根据职员编号返回职员的就职年份。
SQL> create or replace package emp_pack21 as
2 procedure printNameById(empid emp.empno%type);
3 procedure printSalById(empid emp.empno%type);
4 function printYearById(empid emp.empno%type) return date;
5 end emp_pack21;
6 end;
7 /
C创建包主体：
SQL> create or replace package body emp_pack21 as
2 procedure printNameById(empid varchar2) as
3 name emp.ename%type;
4 begin
5 select ename into name from emp where empno=empid;
6 dbms_output.put_line('员工姓名是'||name);
7 end;
8 procedure printSalById(empid varchar2) as
9 sarlary emp.sal%type;
10 begin
11 select sal into sarlary from emp where empno=empid;
12 dbms_output.put_line('员工薪水是'||sarlary);
13 end;
14 function printYearById(empid varchar2) as
15 year emp.hiredate%type;
16 begin
17 select hiredate into year from emp where empno=empid;
18 dbms_output.put_line('你的就职年份是'|| year);
19 end;
20 /
执行过程1：
执行过程2：
调用函数：
提示：使用scott用户的emp表。
2.编写一个程序，显示从2到100的素数。
提示：使用for循环
declare
i int :=2;
j int :=2;
t int :=1;
begin
for i in 2..100 loop
t:=1;
for j in 2..i/2 loop
if mod(i,j)=0 then
t:=0;
end if;
end loop;
if t=1 then
dbms_output.put_line(i||' ');
end if;
end loop;
end;
练习1：创建语句级触发器
问题：
要求对员工表进行的更新都要有记录，只记录所做的操作，不记录用户更新的具体内容。
解决方案
(1) 启动”SQL Plus”，以scott用户登录数据库。
(2) 输入以下命令，创建记录操作的表。
(3) 输入以下命令，创建语句级触发器。
(4) 测试触发器。
对emp表进行更新、插入、和删除操作，再查看emp_log表是否有记录。
创建行级触发器
问题：
创建一个触发器实现计算总金额的功能
解决方案
(1) 启动SQL Plus；
(2) 输入以下语句创建一个订单表。
(3) 输入以下代码创建触发器。
(4) 向表中插入数据测试触发器
执行以下语句的输出结果如下所示。
(5) 更新表中数据测试触发器。
任务：
1． 创建一个触发器，studentinfo表的记录被删除时激活，此触发器将所删除记录的学生信息级删除的时间插入到名为deleted_stu表中。
create or replace trigger studentinfo_trigger
after delete
on studentinfo for each row
begin
insert into delectd_stu(id,name,deletedate) values(:old.stu_id,:old.stu_name,sysdate);
end;
2.创建一个触发器，在用户修改数据库对象时激活，并显示错误信息“不能修改对象”。 提示：创建模式触发器，使用RAISE_APPLICATION_ERROR显示错误信息。
create or replace trigger tri
after drop or create or alter on schema
begin
raise_application_error(-20888,'不能修改对象');
end;
2． 创建一个视图，然后在视图上定义一个触发器，当用户更新视图时，更新和视图关联的表。
create or replace trigger tri_view
instead of update
on v_stuinfo
begin
update tt set id=:new.id,name=:new.name where id=:old.id;
3． end;
提示：使用INSTEAD OF 触发器。
4． 创建一个存储过程，能够修改某一个职员的薪金水平。
5． 创建一个trg_anti_alter 触发器，在用户修改数据库对象时激活。在激活触发器时，应显示错误信息“无法更改对象”
提示：创建模式触发器，使用Raise_Application_error 显示错误信息。
create or replace trigger trg_anti_alter
after drop or create or alter on schema
begin
raise_application_error(-20888,'不能修改对象');
end;
|
| Oracle程序包和触发器 |
oracle程序包和触发器 |
|
--定义程序包规范
create or replace package stupackage
as
procedure getnamebysid(s_id int,s_name out stu.sname%type);
function getagebysid(s_id int) return int; --函数有返回值
end stupackage; --end后边跟上程序包的名称
--程序包内容（主体）
create or replace package body stupackage
as
procedure getnamebysid(s_id int,s_name out stu.sname%type)as
begin
select sname into s_name from stu where sid=s_id;
end;
function getagebysid(s_id int) return int as
s_age int;
begin
select sage into s_age from stu where sid=s_id;
return s_age;
end getagebysid;
end;
select * from stu
--调用一下程序包中的函数
select stupackage.getagebysid(1001) from dual
--用pl/sql块来调用
declare
sid int;
sname varchar(100);
begin
sid:=1001;
stupackage.getnamebysid(sid,sname);
dbms_output.put_line(sname);
end;
--定义了一个
create or replace package test
as
function aa(s_id int) return int;
procedure aa(s_id int);
end;
create or replace package body test
as
procedure aa(s_id int)as
begin
dbms_output.put_line('过程');
end;
function aas(s_ids int) return int as
begin
dbms_output.put_line('函数');
return 0;
end;
end;
--创建一个触发器 当操作stu表的时候打印提示信息
create or replace trigger triger_stu
after insert or update or delete --当执行insert 、update、delete语句的时候来执行此触发器
on stu
begin
dbms_output.put_line('操作了stu表');
end;
select * from stu
update stu set sname='eee' where sid=1001
insert into stu values(1012,'uuuu',90)
create table stu_log(
caozuodate date,
neirong varchar(200)
)
create or replace trigger trigger_stu1 --触发器本身是一个事物
before insert --只对insert语句起作用
on stu
begin
dbms_output.put_line('tttttttt');
insert into stu_log values(sysdate,'插入了一条记录');
dbms_output.put_line('888888');
end;
select * from stu_log
--创建一个触发器当修改某一列的时候来触发
create or replace trigger tri_update_bname
after update of bname --指在修改bname列的时候来触发此触发器
on books
begin
dbms_output.put_line('修改');
end;
select * from books
select * from books_log
update books set bname='rrrrrr'
create table books_log(
cuozuodate date,
neirong varchar(200)
)
create or replace trigger tri_update_bname
after update or delete or insert
on books
for each row --逐行操作
begin
insert into books_log values(sysdate,'操作内容把'||:old.bname||'更新为'||:new.bname);
end;
select * from books_log
update books set bname='guoliua'
delete from books
insert into books values(8,'tttt','rrrrrrrrr')
create table students(
sid int primary key,
name varchar(20),
age int
)
insert into students values(1002,'lisi',50)
create or replace trigger students_tri
after update
on students
for each row --行级触发器
begin
if :new.age>=60 then
dbms_output.put_line('年龄太大');
end if;
end;
select * from students
update students set age=70
create or replace trigger triger_stu
after insert or update or delete --当执行insert 、update、delete语句的时候来执行此触发器
on stu
for each row
begin
if INSERTING then
dbms_output.put_line('插入了一条记录'||:new.sid||' '||:new.sname);
elsif UPDATING then
dbms_output.put_line('修改了信息'||:new.sid||' '||:new.sname);
else
dbms_output.put_line('删除了信息'||:old.sid||' '||:old.sname);
end if;
end;
INSERT into stu values(1090,'zhangsan',90)
update stu set sname='999999' where sid=1001
delete stu
create or replace view student_view
as
select * from stu where sage<=50
insert into student_view values(10,'ttt',80)
select * from stu
--在视图上建立触发器
create or replace trigger trigger_view
instead of update
on student_view
for each row
begin
dbms_output.put_line('测试视图上的触发器'||:new.sname);
end;
select * from student_view
update student_view set sname='yyyyyy'
--DDL模式触发器
create table user_log(
name varchar(20),
type varchar(20),
tdate date
)
select * from user_log
create or replace trigger tri_u
after drop or create or alter on schema --在DDL语句执行的触发器
begin
--dbms_output.put_line('用户执行了DDL');
insert into user_log values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
create table aa(
name varchar(20)
)
drop table aa
create or replace view vv
as
select * from stu where sname like '%t%'
alter trigger triger_stu disable --禁用触发器
alter trigger triger_stu enable --启用触发器
insert into stu values(1081,'rrr',30)
--
select * from user_triggers --触发器的数据字典
select * from user_trigger_cols
--创建一个数据库级别的触发器
create table user_session(
username varchar(20),
udate date
)
create or replace trigger tri_database
before logoff on database
begin
insert into user_session values(user,sysdate);
end;
select * from user_session
-- 数据库级触发器
CREATE TABLE session_info (
username VARCHAR2(30),
logontime DATE,
session_id VARCHAR2(30),
ip_addr VARCHAR2(30),
hostname VARCHAR2(30),
auth_type VARCHAR2(30)
);
select * from guo.session_info;
CREATE OR REPLACE TRIGGER trg_session_info
before LOGOFF ON DATABASE
DECLARE
session_id VARCHAR2(30);
ip_addr VARCHAR2(30);
hostname VARCHAR2(30);
auth_type VARCHAR2(30);
logontime date;
BEGIN
SELECT sys_context ('USERENV', 'SESSIONID') -- 会话编号
INTO session_id FROM dual;
-- 用户登录的客户端IP地址
SELECT sys_context ('USERENV', 'IP_ADDRESS')
INTO ip_addr FROM dual;
-- 用户登录的客户端主机名
SELECT sys_context ('USERENV', 'HOST')
INTO hostname FROM dual;
-- 登录认证方式，数据库认证或外部认证
SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')
INTO auth_type FROM dual;
INSERT INTO session_info
VALUES (user, sysdate, session_id,
ip_addr, hostname,auth_type);
END;
|
| Oracle 角色权限 练习 |
oracle 角色权限 练习 |
|
一、
创建一个表空间ibm 空间大小为50M
create tablespace ibm datafile 'D:/数据库/ibm.dbm' size 50m;
创建一个用户zhangsan，授予用户在的默认表空间是ibm 然后用户在ibm表空间中做占用的空间份额是10M
> create user zhangsan identified by a8888 default tablespace ibm quota 10m on ibm
创建角色，让这个角色拥有对创建表、删除表、修改表和创建触发器、创建存储过程的权限
把此角色授予给zhangsan
grant create any table,delete any table ,update any table ,create trigger,create procedure to myrole;
grant myrole to zhangsan;
二、使用SCOTT和HR用户下的所有对象导出到 scott_hr.dmp文件。
将导出的文件scctt_hr.dmp中HR的所有表导入SCOTT用户。
exp system/a8888@orcl file=d:\数据库\scott_hr.dmp owner=(scott,hr)
imp system/a8888@rocl file=d:/数据库/scott_hr.dmp
作业：
1． 在命令提示符下将scott用户的emp表导出到emp.dmp。
exp scott/a8888@orcl tables=(emp) file=d:/数据库/emp.dmp
2． 在命令提示符下将emp.dmp导入到另一个用户中。
imp system/a8888@orcl file=d:/数据库/emp.dmp fromuser=sco
tt touser=zyf03
3． 使用导入实用程序的命令行和参数文件的方法将emp.dmp文件导入到另一个用户，导入是忽略创建错误并在导入过程中自动提交。
imp system/a8888@orcl file=d:/数据库/emp.dmp fromuser=sco
tt touser=zyf03 ignore=y
4． 使用导出实用程序将整个数据库导出。
exp scott/a8888@orcl file=d:\exp.dmp full=y
|
| Oracle面向对象 type |
oracle 面向对象 自定义类型 |
|
create table dept(
dept_id int primary key,
dept_name varchar(20),
pid int references dept(dept_id) --创建自身的关联关系
)
insert into dept values(1001,'技术部',null)
insert into dept values(1002,'市场部',null)
insert into dept values(10011,'java部',1001)
insert into dept values(100111,'java部门1组',10011)
select * from dept where pid=1001
--创建一个自定义的类型
create type user_type as object(
name varchar(20),
age int
)
create table users(
u_id int primary key,
u user_type --创建表的时候来使用这个类型
)
select * from users
insert into users values(2,user_type('李四',20));
select * from users uu where uu.u.name='张三'
update users uu set uu.u.age=20 where uu.u.name='张三'
update users uu set uu.u=user_type('张三2',25) where uu.u_id=1
create type user_type2 as object(
name_age user_type, --用我们已经创建好的数据类型，来创建新的数据类型
pwd varchar(20)
)
create table users2(
id int primary key,
u user_type2
)
insert into users2 values(1001,user_type2(user_type('王五',50),'888'))
select * from users2
--查看自定义数据类型的数据字典
select * from user_type_attrs
--创建一个普通的数组
create type t_array as varray(5) of int
create table s_1(
id int primary key,
name varchar(20),
cj t_array
)
insert into s_1 values(1002,'lisi',t_array(10,20,30,50))
select ss.id,ss.name,s2.* from s_1 ss,
(select * from table(select s.cj from s_1 s where s.id=1001)) s2
--创建一个对象
create type ke_cj as object(
kemu varchar(20),
cj binary_float
)
--根据自定义的对象来创建一个可变数组
create type stu_cj_array as varray(5) of ke_cj
create table students(
id int primary key,
name varchar(20),
cj stu_cj_array
)
insert into students values(1001,'zhangsan',
stu_cj_array(
ke_cj('语文',30),
ke_cj('数学',90),
ke_cj('英语',40)
)
)
insert into students values(1002,'李四',
stu_cj_array(
ke_cj('语文',90),
ke_cj('数学',70),
ke_cj('英语',30)
)
)
select * from table(select stu.cj from students stu where id=1001)
select stu.id,stu.name,cj.* from students stu,
(select * from table(select stu.cj from students stu where id=1001)) cj
--数组的修改
update students set students.cj=stu_cj_array(
ke_cj('语文',80),
ke_cj('数学',70),
ke_cj('英语',40)
)
where id=1001
--嵌套表
create type stu_cj_tab as table of ke_cj
create table student(
id int primary key,
name varchar(200),
cj stu_cj_tab
)nested table cj store as stu_cj_table --做嵌套表的时候不能少
insert into student values(1001,'zhangsan',stu_cj_tab(
ke_cj('yuwen',100),
ke_cj('shuxue',90),
ke_cj('yingyu',10)
)
)
insert into student values(1002,'lisi',stu_cj_tab(
ke_cj('yuwen',100),
ke_cj('shuxue',90),
ke_cj('yingyu',10)
)
)
select * from table(select stu.cj from student stu where id=1001)
update table(select stu.cj from student stu) a set a.cj=40 where a.kemu='yuwen'
update table(select stu.cj from student stu where id=1001) a
set a.cj=123 where a.kemu='yuwen'
delete table (select stu.cj from student stu where id=1001) a where a.kemu='yuwen'
|
| Oracle 事务 |
oracle 事务 |
|
package oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//oracle中事务处理
public class OracleTrans
{
public static void main(String[] args)
{
//模拟oracle中的事务处理
try
{
//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e)
{
}
Connection conn=null;
try
{
//得到连接
conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.1:1521:orcl","zyf03","a8888");
//执行sql语句
//添加事物处理
conn.setAutoCommit(false);//让事物不自动提交
//让书号为006的图书价格减少10元
PreparedStatement ps=conn.prepareStatement("update books set price=price-10 where bno='006'");
int rs=ps.executeUpdate();
//在这里认为制造出一个异常,让增加钱数和减少钱数不同步
int i=7/0;
//让书号为008的图书价格增加10元
ps=conn.prepareStatement("update books set price =price +10 where bno='007'");
int result=ps.executeUpdate();
conn.commit();//所有的增删改(dml 数据操作语言)结束之后,再提交事物
}
catch (Exception e)
{
//如果在事物提交的过程中,发生了异常,再次将事物回滚
try
{
conn.rollback();
}
catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
|
| Oracle 角色权限 |
oracle 角色权限 |
|
如何查询一个角色 包括的权限
a.一个角色包含的系统权限
select * from dba_sys_privs where grantee='CONNECT';
另外也可以这样查看:
select * from role_sys_privs where role='CONNECT';
b.一个角色包含的对象权限
select * from dba_tab_privs where grantee ='CONNECT';
2.oracle 究竟有多少种角色
select * from dba_roles;
3.如何查看某个用户 ,具有什么样的角色？
select * from dba_role_privs where grantee ='SCOTT';
4 。显示当前数据库的全称
select * from global_name;
5.显示当前用户可以放我那的所有数据字典视图
select * from dict where comments like='%grant%';
create user zs identified by zhangsan
alter user zs identified by zs --修改
alter user zs identified by zs default tablespace aa quota 2M on aa --修改用户
grant create session to zs --创建一个会话的权限（登陆权限）
grant connect to zs --connect 是连接角色
grant create any table to zs
grant create any view to zs
--创建一个角色
create role myrole
create user uu identified by uu
--把myrole角色授权给uu用户
grant myrole to uu
--给myrole角色授予权限
grant create any table to myrole --把一个系统权限给myrole
grant connect to myrole --把一个角色给myrole
grant select on guo.stu to myrole -- 把一个对象权限给了myrole 角色
create user gg identified by gg
grant myrole to gg
alter user gg quota 2M on users --对用户的表空间的份额的修改用alter user
create role role2
grant update on guo.stu to role2
grant role2 to myrole --把一个角色给myrole 角色
--授予gg用户下删除stu表的权限
grant delete on guo.stu to gg
--授予role3用户下students 表的查看、修改和删除的权限
create role role3
grant select,update,delete on guo.students to role3
grant execute on stuage to role3 --把stuage存储过程的执行权限给role3
grant role3 to uu
grant role3 to gg
--回收执行权限
revoke execute on stuage from role3
grant update(bname) on books to gg --细化到列上面的权限
select * from books
grant all on books to uu -- 把books这个对象上的所有权限给uu
--可以查看某个系统角色所拥有的权限
select * from sys.role_role_privs order by role
select * from sys.role_sys_privs
select * from sys.dba_sys_privs
--回收权限
revoke all on books from uu
set role role3 ; --启用
set role all except role3
revoke role3 from uu
grant role3 to uu
create role role4 identified by role4
grant execute on stuage to role4
create user aa identified by aa
grant myrole to aa
grant role4 to aa
grant role3 to aa
alter user aa default role myrole,role3 --给某个用户授予默认角色
--非默认的角色在使用的时候要启用
set role role4--启用某个角色
|
| Oracle排名次函数 |
oracle 排名次函数 |
|
select dense_rank() over(order by state desc) as 名次,state from users u order by state desc;
|
| Tomcat下部署项目 |
tomcat |
|
如果一个项目不发布到Tomcat下的webapp下，那么怎么在Tomcat下配置才能使这个项目能在Tomcat下运行？
首先，在conf文件夹下找到server.xml文件，打开，
在最下边的 </Host>前加上<Context path="/项目名" docBase="项目的绝对路径" debug="0" reloadable="true" />
注意：项目名和绝对路径必须用英文，Tomcat不支持中文。
例如：
<Host name="localhost" appBase="webapps"
unpackWARs="true" autoDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">
<Context path="/web" docBase="I:\web" debug="0" reloadable="true" />
</Host>
<Context docBase="/hrsw" path="/hrsw" debug="5" reloadable="true">
path="/hrsw"代表项目名称，即http://ip/hrsw;
docBase="hrsw"代表站台的目录位置(绝对路径)，即{Tomcat_install}\webapps\hrsw;
debug则是设定debug level,0表示提供最少的信息，9表示提供最多的信息
reloadable则表示Tomcat在执行时，当class,web.xml被更新过时，都会自动重新加载，不需要重新启动Tomcat.
|
| Oracle动态创建自动增长的序列 |
oracle 主键自动增长 序列 |
|
1.动态创建自动增长的序列:
declare
v_sql varchar2(1000);
v_num number(20):=1;
begin
v_sql:='create sequence sqbs ';
select max(sqb.id)+1 into v_num from sqb;
if v_num is null then v_num :=1;
v_sql:=v_sql||' maxvalue 999999 start with '||v_num ||' increment by 1 nocache';
end if;
execute immediate v_sql;
end;
2--创建序列
create sequence t
--使用序列
select t.nextval from dual --获取下一个序列值
select t.currval from dual --获取当前序列值
drop sequence g --删除序列
alter sequence ttt increment by -3--修改序列
|
