oracle笔记

create table kkk(myid number,myname var2char(50),mydept number);


insert into kkk(myid,myname,mydept) select myid,myname,mydept from emp where deptno='10';


update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='john';


commit;


savepoint a1;
 
savepoint a2;


rollback to a1;


commit;


rollback;


//sql创建函数
create function sp2(sPname) return number is yearsal number(7,2)
begin 
--执行部分
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename =sPname;
return yearsal;
end


调用
var abc number;
call sp2('Scott')into:abc;


declare 
C-tax-rate number(3,2):=0.03
V-ename var2char(7);
V-esal number(7,2);
V-tax-sal number(7,2);
begin
select ename,sal into V-ename,V-esal where empno=&no;
V-tax-sal=V-esal*C-tax-rate;
dbms_output.putline('姓名是：'||V-name||'工资是’||V-tax-sal);
end;


也可以这样定义ename，
ename emp.ename%type;


declare
type sep_cursor is ref cursor;
test_cursor  sep_cursor;
begin 
open test_cursor for select ename ,sal from emp where empno=&no;
loop
fetch ename, sal into V-name,V-sal;
dbms_output.putline(''姓名是：'||V-name||'工资是’||V-sal);
exit when test_cursor%notfound
endloop
close test_cursor;
end


create or replace procedure sprol is 
begin
insert into kkk values('王子','1001');
end


show error;


调用过程
exec sprol(参数）；
call sprol(参数）；


set severoutput on




declare
begin 
exception 
end


exception when nodatafound
dmbs_output.putline("您输入的有误！")




create or replace procedure sp03(Spname var2char(7),Vsal number(7))
begin
update emp set sal =Vsal where ename=Spname;
end;


call sp03('Scott',4678);


自连接


select *from emp where (deptno,job)=select deptno,job from emp where ename='smith';


给表加别名不要加as
给列加别名可以加as


rownum分页
select a1.*,rowmum rn from (select * from emp) a1 where rownum<=10;


union 
union all
intersect
minus


select ename,job,sal from emp where sal>2500 union select (取并集)
union all(不会取消重复行)
intersect(取交集)
minus(取差集)


clear 清屏
desc dept查看表的结构
set timing on 显示操作时间的开关
select distinct
oracle 数据库区分大小写


create or replace procedure sp06(spName varchar2) is
declare
v_sal emp.sal%type
begin
select sal into v_sal from emp  where e_name=spName;
if v_sal<2000 then 
update emp set sal=1.1*sal where e_name=spName;
endif
end


if v_comm<>0 then update emp set sal=sal+100;


if
elsif 
else
endif


v_num number:=1;
loop
insert into users values(v_num,sPname);
exit when v_num=10
v_num:=v_num+1;
endloop 


while...
loop
endloop


begin
for i in reverse 1......10 loop
insert into users values(i,"");
endloop
end


declare
i int:=1;


if....
else null


编写分页过程
create or replace procedure sp_pr07(spBookId in number, ,) is
begin 
insert into book values();
end


create or replace procedure sp_pro8(sPno in number,sPname out varchar) is 
begin 
...
end


有返回值的存储过程（列表[结果集])
create or replace package testpackage As 
Type test_cursor is ref cursor;
end testpackage


create or replace procedure sp_pro9(SpNo in Number, pcursor out testpackage.test_cursor)
begin
open pcursor for select * from emp where depno=&SpNo;
end


oracle的分页
select t1.* rownum rn from（select * from emp） t1 where rownum<=10;
select * from (select t1.* rownum rn from（select * from emp） t1 where rownum<=10) where rn>=6;




--开始编写分页过程
1.create or replace package testpackage As 
Type test_cursor is ref cursor
end package
2.create or replace procedure fenye
(tableName in varchar2,
 pageSize in number,
 pageNow in number,
 myrows out number,
 myPageCount out number,
 pCursor out testpackage.test_cursor) is 
declare
V_sql varchar2(100);
V_begin number:=(pageNow-1)*pageSize+1;
V_end number:=pageNow*pageSize;
begin 
V_sql:='select * from (select t1.* rownum rn from（select * from emp） t1 where rownum<='||V_end||') where rn>='V_begin;
open p_cursor for V_sql;
V_sql:='select count(*) from '||tableName;
execute immediate V_sql into myrows;
if (mod(myrows,pageSize)==0) then 
mypageCount=myrows/pageSize;
else
mypageCount=myrows/pageSize+1;
endif
close p_cursor;
end


分页按薪水由高到低，再取第二页
在内层sql语句中加order by


exception when no_data_found then 
dbms_output.put_line();
 
case not found


cursor already open


dup_val_on_index


invalid_cursor


invalid number


too many rows


zero_divide


value_error 变量不足容纳实际数据


create or replace procedure test(SPNO number) is 
begin
update emp set sal=sal+100 where empno=SPNO;
if sql%notfound then
raise mytex;
endif
exception 
when mytex then


视图
create view myview as select * from emp where sal<1000[with read only];
drop view myview


create user xiaoming identified by m123;
conn system/manager;
show user;
password xiaoming;
在删除用户时，如果用户已经建表，则需要在删除时加上一个参数[cascade]
当新建用户时，若未赋权限，则不能登录到数据库
grant connect to xiaoming //添加权限后才能登陆数据库
grant resource to xiaoming
grant select on emp to xiaoming
select * from scott.emp


set linesize 120;
grant all on emp to xiaoming
收回权限
revoke select on emp from xiaoming
end


如果是对象权限
grant select on emp to xiaoming with grant option
grant select on Scott.emp to xiaoming with grant option 
如果是系统权限
grant select on emp to xiaoming with admin option


revoke select on emp from xiaoming
账户命令：
create profile myprofile failed_login_error 3 password_lock_time 2;
alter user scott profile myprofile;加上规则名称
alter user scott account unlock;
create profile myprofile limit password_life_time 10 password_grace_time 2;
alter user scott profile myprofile;
create profile myprofile limit  password_life_time 10 password_grace_time 2 password_reuse_time 10;
drop profile myprofile[cascade]