oracle之存储过程、视图、索引、表空间

---存储过程

------插入数据存储过程
create or replace procedure insert_emp_proc(myempno number,myename varchar2,myjob varchar2,mymgr number,mydate date,mysal number,mycomm number,mydeptno number)
as
begin
  insert into emp values(myempno,myename,myjob,mymgr,mydate,mysal,mycomm ,mydeptno);
  end;
   select * from emp

   call insert_emp_proc(999,'三九','胃病',7698,sysdate,1800,4000,30);--调用插入数据

-------更新数据存储过程
1.create or replace procedure update_emp_proc(myempno number,myename varchar2) as
mysql varchar2(500);
begin
  mysql:='update emp set ename=:1 where empno=:2';
  execute immediate mysql using myename,myempno;
  end;
 
                                                                                                  
 2. create or replace update_emp_proc(myempno in number,myename varchar2)as
  begin
    update emp set ename=myename where empno=myempno;
    end;
   
-------删除数据存储过程
create procedure del_emp_proc(myemp number)
as
begin
  delete  emp where empno=myemp;
  end;
 
-------查询数据存储过程
create procedure selname_emp_proc(myempno number,myname out varchar2)
as
begin
  select ename into myname from emp where empno=myempno;
  dbms_output.put_line(myname);
  end;

 
 
-----调用查询存储过程
declare
myname varchar2(50);
begin
  selname_emp_proc(7666,myname);
  end;

 

---视图

create or replace view empview
as
select empno,ename,job,sal from emp
with read only

---索引
create unique index uq_ename_idx on emp(ename)

---表空间

--1.创建
create tablespace myspace
datafile '表路径1' size 10M,'表路径2' size 5M
extent management local--表空间类型为本地管理表空间
uniform size 1M--指定每个分区的统一大小
--2.扩展表空间
alter tablespace mysapce add datafile '路径名1' size 10M
--3.为某一用户指定默认的表空间
create user acong identified by acong default tablespace myspace