Oracle自学笔记2

灰太狼 2022-09-26 02:51 207阅读 0赞

connect conn 可以切换数据库链接  
disconnect disc 可以断开  
passw  可以修改密码  
start 路径+脚本名  
edit  路径+ 脚本名可以修改脚本  
create user 用户名 identified by 密码  创建用户  
dorp user 用户名 删除用户  
grant 权限 to 用户名 可以授权 eg： grant connect to xiaoming;  grant resource to xiaoming;  
  
  
权限： select update insert create delete ==all  
grant select on emp to xiaoming;  
select\* from scott.emp;  
revoke 收回权限  
revoke select on emp from xiaoming;  
/\* 对权限的维护 \*/  
\--如果是用户权限  
grant select on emp to xiaoming with grant option;  
\--如果是系统权限  
grant connect to xiaoming with admin option  
  
  
  
  
alter table 表名  add（字段 类型);  
eg:alter table student add(classId number(2));  
设置保存点 savepoint aa;  
回滚        rollback aa;  
  
  
set timing on；可以查看所需时间  
nvl() 处理null  
  
  
  
  
  
  
\-------------------------------------------------  
创建一个表  
create table mytest(name varchar2(30),passwd varchar2(30));  
创建过程：  
create or replace procedure jh\_pro1 is  
begin  
 insert into mytest values('苏继海','sujihai');  
end;  
  
  
如何查看错误信息  
show error;  
  
  
如何调用该过程：  
1 exec 过程名（参数值1，参数值2。。。）  
  
  
2 call 过程名（参数值1，参数值2。。。）  
  
  
create procedure jh\_pro3 (jhName varchar2,newSal number) is  
begin  
update emp set sal = newSal where ename=jhName;  
end;  
  
  
exec jh\_pro3('SMITH',7689);  
  
  
\--创建函数  
  
  
create or replace function jh\_fun2(jhName varchar2) return number is YearSal number(7,2);  
begin  
 select sal\*12+nvl(comm,0)\*12 into YearSal from emp where ename=jhName;  
 return YearSal;  
end;  
  
  
var abc number;  
call jh\_fun2('SCOTT') into:abc;  
  
  
\--创建包  
  
  
  
  
create package jh\_page is  
  procedure update\_sal(name varchar2,newSal number);  
  function annual\_income(name varchar) return number;  
end;  
  
  
\--创建包体  
create pakeage body jh\_page 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;  
  
  
\--调用包  
exec jh\_package.update\_sal('SCOTT'120);  
  
  
\-----------------------------------------------------------------------------------------  
  
  
declare  
v\_tax\_rate number(3,2):=0.03;  
v\_ename varchar2(5);  
v\_sal number(7,2);  
v\_tax\_sal number(7,2);  
begin  
select ename,sal into v\_ename,v\_sal from emp where empno=&no;  
v\_tax\_sal:=v\_sal\*v\_tax\_rate;  
dbms\_output.put\_line('姓名:'||v\_ename||'工资'||v\_sal||'交税'||v\_tax\_sal);  
end;  
  
  
  
  
\---------------------------------  
\--游标  
declare  
type jh\_emp\_cursor is ref cursor;  
test\_cursor jh\_emp\_cursor;  
v\_ename emp.ename%type;  
v\_sal emp.sal%type;  
begin  
open test\_cursor for select ename,sal from emp where deptno =&no;  
loop  
fetch test\_cursor into v\_ename,v\_sal;  
exit when test\_cursor%notfound;  
dbms\_output.put\_line('名字'||v\_ename||'工资'||v\_sal);  
end loop;  
end;  
  
  
\-------------------------------------------------------------------  
create or replace procedure jh\_pro6(jh\_Name varchar2) is   
v\_sal emp.sal%type;  
begin  
select sal into v\_sal from emp where ename=jh\_Name;  
if v\_sal<2000 then   
update emp set sal=sal+sal\*1.1 where ename=jh\_Name;  
end if;  
end;  
  
  
\-----------------------------------------------------------  
create or replace procedure jh\_pro7(jhName varchar2) is  
  
  
v\_comm emp.comm%type;  
begin  
select comm into v\_comm from emp where ename=jhName;  
if v\_comm<>0 then  
update emp set comm=comm+100 where enmae=jhName;  
else  
update emp set comm=comm+200 where ename =jhName;  
end if;  
end;  
\----------------------------------------------------------------  
create or replace procedure jh\_pro6(jhNo number) is  
v\_job emp.job%type;  
begin  
select job into v\_job from emp where empno=jhNo;  
if v\_job='PREDIENT' then  
update emp set sal=sal+1000 where empno=jhNo;  
elsif v\_job ='MANAGER' then  
update emp set sal = sal+500 where empno=jhNo;  
else  
update emp set sal = sal+100 where empno=jhNo;  
end if;  
end;  
\---------------------------  
  
  
\--自定义例外  
create or replace procedure ex\_test(jhNo number)  
is   
\--定义例外  
myex exception;  
begin  
update emp set sal=sal+1000 where empno = jhNo;  
if sql%notfound then  
raise myex;  
end if;  
exception   
when myex then  
dbms\_output.put\_line('没有更新任何数据');  
end;  
  
  
\--------------------------------------  
system  
grant create any view to scott;  
  
  
  
  
scott  
  
  
create view myview as select\* from emp where sal<1000;  
  
  
drop view 视图名