--在system表空间创建用户 --其中,jinanuser是用户名 jinanuser是密码 create user jinanuser identified by jinanuser; --将dba角色赋给jinanuser这个用户 grant dba to jinanuser; --撤销jinanuser用户的dba角色 revoke dba from jinanuser; --在自己创建
--在system表空间创建用户
--其中,jinanuser是用户名 jinanuser是密码
create user jinanuser identified by jinanuser;
--将dba角色赋给jinanuser这个用户
grant dba to jinanuser;
--撤销jinanuser用户的dba角色
revoke dba from jinanuser;
--在自己创建的用户下创建jinantest
create user jinantest identified by jinantest;
--给jinantest权限connect
grant connect to jinantest;
revoke connect from jinantest;
1、关于主键：在建表时指定primary key字句即可：
create table test(
id number(6) primary key,
name varchar2(30)
);
如果是对于已经建好的表，想增加主键约束，则类似语法：
alter table test add constraint pk_id primary key(id);
--给jinantest权限resource
grant resource to jinantest;
revoke resource from jinantest;
--使用jinanuser创建创建tb_user表
create table tb_user(
user_id int primary key,
user_name varchar2(50) not null,
user_desc varchar2(2000)
)
--删除表
drop table tb_user;
select * from tb_user;
--添加列
alter table tb_user add user_pwd varchar2(50);
--修改列
alter table tb_user modify user_pwd long;
select * from tb_user;
--删除列
alter table tb_user drop column user_desc;
--用于产生主键数值的方法,序列:sequence
create sequence seq_test
start with 1
increment by 1;
--选取序列的当前值 seq_xxx.currval
--dual 虚表
select seq_test.currval from dual;
--sysdate表示oracle数据库当前系统时间
select sysdate from dual;
--选取序列的下一个值:seq_xxx.nextval
select seq_test.nextval from dual;
insert into tb_user values(seq_test.nextval,'张三','123456');
--查询数据
select * from tb_user;
select seq_test.nextval from dual;
insert into tb_user values(seq_test.nextval,'李四','654321');
select * from tb_user;
--插入指定的字段
insert into tb_user(user_id,user_name) values (seq_test.nextval,'王五');
insert into tb_user(user_name,user_id) values ('小明',seq_test.nextval);
--删除表的主键
alter table tb_user drop primary key;
--给表添加主键
alter table tb_user add constraint pk_tb_user primary key (user_name);
alter table tb_user add primary key (user_name);
--
select * from tb_user;
--添加唯一性约束
alter table tb_user add constraint uk_tb_user unique (user_id);
--非空约束
alter table tb_user modify user_id not null;
--添加列
alter table tb_user add user_grade varchar2(10);
--查询
select * from tb_user;
--填充user_grade字段
update tb_user set user_grade='sk';
--添加非空约束:user_grade
alter table tb_user modify user_grade not null;
--创建表
create table tb_user1 (
user_id int primary key,
user_name varchar2(20) default('小王')
)
--创建序列
create sequence seq_test1
start with 1
increment by 1;
--添加数据
insert into tb_user1(user_id) values(seq_test1.nextval);
--dual 虚表
select seq_test1.currval from dual;
select * from tb_user1;
--外键约束
--创建经理表
create table tb_manager(
mgr_id int primary key,--经理表的主键
mgr_name varchar2(10) not null--经理姓名
)
create table tb_employee(
epe_id int primary key, --雇员的主键
epe_name varchar2(10) not null, --雇员的姓名
mgr_id int not null --所属经理的id
)
--外键
alter table tb_employee add constraint fk_epe foreign key (mgr_id)
references tb_manager (mgr_id);
select * from tb_manager;
select * from tb_employee;
--向经理表添加记录
insert into tb_manager values(1,'老兵');
--向雇员表添加记录
insert into tb_employee values(seq_test1.nextval,'小兵',1);
create table tb_employee2(
epe_id int primary key, --雇员的主键
epe_name varchar2(10) not null, --雇员的姓名
mgr_id int --所属经理的id
)
--外键
alter table tb_employee2 add constraint fk_epe2 foreign key (mgr_id)
references tb_manager (mgr_id);
insert into tb_employee2(epe_id,epe_name) values(seq_test1.nextval,'小兵');
commit;
select * from tb_employee2;
--外键在添加记录的时候,可以为空
select * from tb_user1;
delete from tb_user1;
--check约束
alter table tb_user1
add constraint ck_tb_user1
check(user_id>10);
insert into tb_user1 values(11,'小王');
select * from tb_user1;
commit;
--数学函数
--abs取绝对值
select abs(-10) from dual;
--ceil 向上取整
select ceil(-3.1) from dual;
--floor 向下取整
select floor(-3.1) from dual;
--power 幂次方
select power(3,2) from dual;
--round四舍五入
select round(2.4) from dual;
--sqrt开方
select sqrt(2) from dual;
--trunc数据截断
select trunc(15.79,1) from dual;
select trunc(15.79,0) from dual;
select trunc(15.79,-1) from dual;
select trunc(15.79,-2) from dual;
--向第一个参数表示要截取的数字,第二个参数,表示从第几位截取
--当第二个参数为正数的时候,表示保留几位小数
--当第二个参数为负数的时候,表示去掉几位整数部分
--相当于一把小刀,向右数几位砍掉,负数表示向左移动几位砍掉,0的时候表示砍掉小数部分
--mod整数取余操作
select mod(10,3) from dual;
select mod(10,3) mod from dual;
--转换函数
--tochar
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--to_date函数 将字符转换为时间格式数据
select to_date('2014-12-10 17:21:47','yyyy-mm-dd hh24:mi:ss') from dual;
--to_number
select '3' + '1' from dual;
select to_number('123456') - 23456 from dual;
--to_timestamp
select to_timestamp('2013-12-2 12:22:32','yyyy-mm-dd hh24:mi:ss') from dual;
select to_date('2013-12-2 12:22:32','yyyy-mm-dd hh24:mi:ss') from dual;
--to_timestamp_tz
select to_timestamp_tz('2013-12-2 12:22:32 8:00','yyyy-mm-dd hh24:mi:ss tzh:tzm') from dual;
--sysdate获取oracle系统当前时间
select sysdate from dual;
--extract提取日期中指定单位的数值
select extract (month from sysdate) from dual;
select extract (year from sysdate) from dual;
select extract (day from sysdate) from dual;
select extract (hour from to_timestamp('2012-2-12 23:32:21','yyyy-mm-dd hh24:mi:ss')) from dual;
select extract (minute from to_timestamp('2012-2-12 23:32:21','yyyy-mm-dd hh24:mi:ss')) from dual;
--months_between
select months_between(
to_date('2013-3-12 23:32:21','yyyy-mm-dd hh24:mi:ss'),
to_date('2014-12-12 23:32:21','yyyy-mm-dd hh24:mi:ss')
)from dual;
--add_months 添加月份
select add_months(
to_date('2013-3-12 23:32:21','yyyy-mm-dd hh24:mi:ss'),
10
)from dual;
--next_day 下一个星期数
select next_day(
'2013-3-12','yyyy-mm-dd'
)from dual;
--round 对日期四舍五入
select round(sysdate),sysdate from dual;
select round(to_date('2013-3-12 23:32:21','yyyy-mm-dd hh24:mi:ss')),
to_date('2013-3-12 23:32:21','yyyy-mm-dd hh24:mi:ss')
from dual;
--last_day 当月的最后一天
select last_day(
sysdate
)from dual;
--teunc 获取待定时间
select trunc(to_date('2013-3-12','yyyy-mm-dd'),'day') from dual;
select trunc(to_date('2013-3-12','yyyy-mm-dd'),'month') from dual;
select trunc(to_date('2013-3-12','yyyy-mm-dd'),'year') from dual;
--upper转化成大写
select upper('sdsda') from dual;
--lower转换成小写
select lower('sdsda') from dual;
--initcap首字母大写
select initcap('wqeqe') from dual;
--concat 字符串连接
select concat('wqeqe','asda') from dual;
--length获取字符数
select length('wqeqeasda') from dual;
--lpad左填充
select lpad('qweq',5,'fgrty') from dual;
--rpad右填充
select rpad('qweq',5,'fgrty') from dual;
--ltrim去除左空格
select ltrim(' qweq') from dual;
--rtrim去除右空格
select rtrim('qweq ') from dual;
--instr获取查询字符串的索引
select instr('corporate floor','or', 3, 2)
instring from dual;
select instr('zxcvbnm','m', 1, 1)
instring from dual;
--substr截取字符串
select substr('abcdefg',3,4) substring
from dual;/*从第三个开始截取一共截取4个*/
--replace替换字符串
select replace('jack and jue','j','bl') changes
from dual;
/*把j替换成bl*/
--loop循环
declare
myindex int:=0;
begin
loop
--输出结果到控制台,字符串拼接使用||,也可使用concat函数
dbms_output.put_line('myindex = ' || myindex);
-- dbms_output.put_line(concat());
myindex := myindex + 1;
if myindex > 10 then
exit;
end if;
end loop;
end;
--while循环
declare
myindex int :=0;
begin
while myindex loop
dbms_output.put_line(concat('myindex = ',myindex));
myindex :=myindex + 1;
end loop;
end;
--自增序列
create sequence
start with 1
increment by 1;
--for loop 循环
begin
for i in 0..10
loop
dbms_output.put_line('index = '||i);
end loop;
end;
--反转reverse
begin
for i in reverse 0..10
loop
dbms_output.put_line('index = '||i);
end loop;
end;
--动态sql语句
create table tb_test(
t_id int primary key,
t_name varchar2(10)
)
declare
mysql varchar2(500);
mydate date;
begin
execute immediate 'select sysdate from dual' into mydate;
dbms_output.put_line(to_char(mydate,'yyyy-mm-dd'));
end;
declare
mysql varchar2(500) : ='';
mytext varchar2(10) := '小名的名字';
begin
execute immediate 'insert into tb_test values(3,:x)' using '小明';
end;
declare
mysql varchar2(500) :='insert into tb_test(t_id,t_name) values(3,:x)';
mytext varchar2(10) := '小明';
begin
execute immediate mysql using mytext;
end;
select * from tb_test;
--给tb_test创建一个序列,用来生成主键的值
create sequence seq_123
start with 5
increment by 1;
--添加列
alter table tb_test add t_mony varchar2(50);
declare
mysql varchar2(500) :='insert into tb_test values(:n,:x,:y)';
myname varchar2(10) := '李四';
mydesc varchar2(100) :='这是李四的描述';
myindex int;
begin
select seq_123.nextval into myindex from dual;
execute immediate mysql using myindex,myname,mydesc;
end;
--异常的处理
declare
var1 int :=87;
var2 int :=0;
begin
var1 := var1/var2;
dbms_output.put_line('已经执行了,');
--异常处理语句
exception
--系统定义异常,zero_divide
when zero_divide then
dbms_output.put_line('不能被0除,异常');
end;
--自定义异常
begin
raise_application_error(-20001,'我测试用的自定义异常');
end;
select * from tb_user;
select * from scott.emp;
select * from emp;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
--游标的使用
declare
cursor mycur is select * from emp;
myrow emp%rowtype;
begin
open mycur;
loop
fetch mycur into myrow;
if myrow.sal if myrow.sal + 500 > 2000 then
update emp set sal =2000 where empno = myrow.empno;
else
update emp set sal = myrow.sal + 500 where empno=myrow.empno;
end if;
end if;
if mycur%notfound then
exit;
end if;
end loop;
close mycur;
commit;
end;
--
declare
myvar int := &mynumber;
mystr varchar2(50) := &mytext;
begin
dbms_output.put_line(mystr||'='||myvar);
end;
--计算器--
declare
myvar int:=&mynumber;
mystr varchar2(50):=&mytext;
myvar1 int:=&mynum;
mysum int;
myjian int;
mycheng int;
mychu int;
begin
mysum:=myvar+myvar1;
myjian:=myvar-myvar1;
mycheng:=myvar*myvar1;
mychu:=myvar/myvar1;
if mystr='+' then
dbms_output.put_line(mysum||'='||myvar||'+'||myvar1);
elsif mystr='-' then
dbms_output.put_line(myjian||'='||myvar||'-'||myvar1);
elsif mystr='*' then
dbms_output.put_line(mycheng||'='||myvar||'*'||myvar1);
elsif mystr='/' then
dbms_output.put_line(mychu||'='||myvar||'/'||myvar1);
end if;
-- dbms_output.put_line('结果='||mysum);
end;
create table dept as select * from scott.dept;
select * from dept;
declare
type mydept is record (mydeptno dept.deptno%type, mydeptname dept.dname%type);
v_mydept mydept;
begin
select deptno,dname into v_mydept from dept where deptno=10;
dbms_output.put_line('部门编号:'||v_mydept.mydeptno||'----'||'部门名称:'||v_mydept.mydeptname);
end;
select * from dept;
----savepoint的使用,rollback的使用
declare
myrow dept%rowtype;
begin
savepoint x;--设置回滚点
update dept set dname='it' where deptno=20;
select * into myrow from dept where deptno=20;
dbms_output.put_line(myrow.deptno||'--'||myrow.dname||'--'||myrow.loc);
dbms_output.put_line('update已经被执行');
rollback to x;--事务回滚到x点
--也可以直接回滚;
--rollback;
dbms_output.put_line('回滚到x点');
commit;
end;
commit;
declare
mynum int;
mydname varchar2(14);
begin
savepoint x;
update dept set dname='it' where deptno=20;
select dname into mydname from dept where deptno=20;
dbms_output.put_line('update之后,提交事务之前:dname='||mydname);
mynum :=10/0;
commit;
dbms_output.put_line('进行顺利,已经提交');
exception
when zero_divide then
dbms_output.put_line('发生异常,回滚');
rollback to x;
commit;
select dname into mydname from dept where deptno=20;
dbms_output.put_line('rollback之后:dname='||mydname);
end;
create table myacount(
acc_id int primary key,
owner_name varchar2(50),
balance number (10,3)
)
insert into myacount values(1,'张三',1000.00);
insert into myacount values(2,'李四',2000.00);
select * from myacount;
declare
myindex int :=&myindex;
mynum int;
begin
savepoint x1;
update myacount set balance=balance-50 where acc_id=1;
update myacount set balance=balance+50 where acc_id=2;
if myindex=1 then
mynum :=10/0;
end if;
commit;
exception
when zero_divide then
dbms_output.put_line('发生异常,事务回滚');
rollback to x1;
commit;
end;
--存储过程
create or replace procedure pro_test
as
begin
dbms_output.put_line('此存储过程已执行完毕!');
end;
--存储过程调用方法一
call pro_test();
--存储过程调用方法二 在pl/sql语句块中调用
begin
pro_test();
end;
--带参数的存储过程
create or replace procedure pro_test_params(v_num1 in number,v_str in varchar2,v_return out varchar2)
as
begin
v_return :=(v_num1+1)||v_str;
dbms_output.put_line('v_return'||v_return);
end;
--带输入输出参数的调用
declare
v_display varchar(100);
begin
pro_test_params(9,'结构',v_display);
dbms_output.put_line('v_display='||v_display);
end;
--创建一个带参数的存储过程,输入参数同事又是输出参数
--第一个参数为number,输入参数.第二个为varchar2类型的,是输入输出参数
--在参数中,就写varchar2,number,不需要指定长度等
create or replace procedure pro_test_params_inout(v_num in number,v_str in out varchar2)
as
--此处用来声明变量
begin
v_str :=(v_num+1)||v_str;
end;
--call pro_test_params_inout(99,);
--一个ascii字符在oracle中占用一个字节,一个汉字在oracle中占据两个字节
declare
v_str varchar2(20);
begin
v_str :='我是字符串';
pro_test_params_inout(99,v_str);
-- pro_test_params_inout(99); 报错:参数个数
dbms_output.put_line(v_str);
end;
------------------函数的创建---------------------
create or replace function func_test(v_num number,v_str varchar2)
--声明返回值类型
return varchar2
as
v_return varchar2(100);
begin
v_return :=v_str||(v_num+1);
--返回 返回值
return v_return;
end;
select func_test(99,'我是字符串') from dual;
--模拟字符串拼接函数 concat
select concat('aaa','ccc') from dual;
--func_concat
create or replace function func_concat(v_str1 varchar2,v_str2 varchar2)
--声明返回值类型
return varchar2
as
v_return varchar2(50);
begin
--给返回变量赋值
v_return := v_str1||v_str2;
--将处理加过返回
return v_return;
end;
select func_concat('a','b') from dual;
select * from dept;
select * from emp;
--游标在存储过程和函数中的使用
--存储过程中的使用
--根据部门的名称查找部门所有的员工
--对于存储过程来讲,如果参数不写明in或者out,系统默认为输入参数
--输出参数为有表的时候,类型是:sys_refcursor
create or replace procedure pro_allemps(v_deptname in varchar2,v_emps out sys_refcursor)
as
v_deptno int;
begin
--根据用户输入的部门查询对应的部门标号
select deptno into v_deptno from dept where dept.dname=v_deptname;
--使用游标接收查询到的结果集
open v_emps for select * from emp where emp.deptno=v_deptno;
end;
declare
--声明一个属性行变量,用来接收遍历游标的时候,去除的一条结果
v_temp emp%rowtype;
--声明一个sys_refcursor类型的游标来接收过程的输出参数
v_cursor sys_refcursor;
begin
--使用声明的游标作为参数,接收存储过程的输出结果:v_cursor
pro_allemps('research',v_cursor);
loop
fetch v_cursor into v_temp;
exit when v_cursor%notfound;
dbms_output.put_line(v_temp.empno||'-'||v_temp.ename||'-'||v_temp.job||'-'||v_temp.sal||'-'||v_temp.deptno);
end loop;
end;
--在函数中使用游标
--根据部门名称查找该部门的所有员工
create or replace function func_allemps(v_deptname varchar2)
return sys_refcursor
as
v_cursor sys_refcursor;
v_deptno int;
begin
select deptno into v_deptno from dept where dept.dname=v_deptname;
open v_cursor for select * from emp where emp.deptno=v_deptno;
return v_cursor;
end;
select func_allemps('sales') from dual;
create table emp as select * from scott.emp;
alter user scott account unlock;
alter user scott account lock;
declare
--声明一个游标用来接收函数的返回结果
v_cursorme sys_refcursor;
--声明一个属性行变量来接收游标返回的每一条记录
v_temprow emp%rowtype;
begin
--函数的调用，由于函数是有返回值的，所以我们使用声明的游标接收
v_cursorme := func_allemps('sales');
loop
fetch v_cursorme into v_temprow;
exit when v_cursorme%notfound;
dbms_output.put_line('员工姓名'||v_temprow.ename);
end loop;
end;
------触发器的操作
create or replace trigger tri_before_test_emp
before insert on emp for each row
begin
dbms_output.put_line('正在向emp表插入数据');
end;
select * from emp;
begin
insert into emp values(104,'王五','会计',0,sysdate,100,0,10);
commit;
end;
create or replace trigger tri_before_test_emp
before insert on emp for each row
begin
dbms_output.put_line('正在向emp表插入数据');
dbms_output.put_line('新值:'||:new.empno||'--'||:new.ename||'--'||:new.job||'--'||:new.mgr||'--'||:new.hiredate);
end;
---对于after的一个trigger
create or replace trigger tri_after_test_emp
before insert on emp for each row
begin
dbms_output.put_line('已经向emp表插入数据');
dbms_output.put_line('新值:'||:new.empno||'--'||:new.ename||'--'||:new.job||'--'||:new.mgr||'--'||:new.hiredate);
end;
select * from emp;
delete from emp where empno=104;
--设置主键
alter table emp add constraint pk_emp primary key (empno);
--在插入数据的时候,生成主键的值
create or replace trigger tri_before_test_emp
before insert on emp for each row
declare
myindex int;
begin
dbms_output.put_line('正在向emp表插入数据');
select max(empno)into myindex from emp;
:new.empno:=myindex +1;
end;
--执行插入数据的sql语句
begin
insert into emp values(1,'测试','测试人',0,sysdate,1000,0,10);
--提交数据
commit;
end;
select max(empno) from emp;
select *from emp order by empno desc;
--将insert换为:update delete
--update
create or replace trigger tri_before_test_emp
before update on emp for each row
begin
dbms_output.put_line('将要执行的update操作');
dbms_output.put_line('新值为:'||'--'||:new.ename||'--'||:new.job||'--'||:new.mgr||'--'||:new.sal);
dbms_output.put_line('旧值为:'||'--'||:old.ename||'--'||:old.job||'--'||:old.mgr||'--'||:old.sal);
end;
select * from emp;
begin
update emp set ename='小李',job='测试人',mgr=1000,sal=50 where empno=100;
commit;
end;
--after update
create or replace trigger tri_before_test_emp
before update on emp for each row
begin
dbms_output.put_line('己执行的update操作');
dbms_output.put_line('新值为:'||'--'||:new.ename||'--'||:new.job||'--'||:new.mgr||'--'||:new.sal);
dbms_output.put_line('旧值为:'||'--'||:old.ename||'--'||:old.job||'--'||:old.mgr||'--'||:old.sal);
end;
select * from emp;
begin
update emp set ename='小张',job='经理人',mgr=2000,sal=520 where empno=102;
commit;
end;
--oracle的分页
select t1.*,rownum rn from (select * from emp) t1
--取出前5条
select t1.*,rownum rn from (select * from emp) t1 where rownum--取出10条之内大于等于6的
select * from(select t1.*,rownum rn from (select * from emp) t1 where rownum=6;
--开发一个包
--创建一个包,在该包中,我定义类型test_cursor,是个游标
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
---创建包体
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal = newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;
--开始编写分页的过程
create or replace procedure fenye
(tablename in varchar2,
pagesize in number, --一页记录数
pagenow in number,
myrows out number, --总记录数
mypagecount out number, --总页数
p_cursor out tespackage.test_cursor --返回记录集
) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:= (pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
--执行部分
v_sql:='select * from select * from(select t1.*,rownum rn from (select * from '|| tablename
||') t1 where rownum='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和mypagecount
--组织一个sql
v_sql:='select count(*) from '||tablename;
--执行sql,并把返回的值,赋给myrows
execute immediate v_sql into myrows;
--计算mypagecount
if mod(myrows,pagesize)=0 then
mypagecount := myrows/pagesize;
else
mypagecount := myrows/pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;