Oracle自学笔记2

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 视图名