Oracle常用Sql操作讲解

创建表空间与用户

/*
    说明:若已经存在相应的用户和表空间，则需要先删除相应的用户和表空间
        然后再全部重新建立
*/

--删除用户
drop user test cascade;

--删除表空间
drop tablespace test_data_temp including contents and datafiles;
drop tablespace test_data      including contents and datafiles;


--创建临时表空间
create temporary tablespace test_data_temp tempfile 'test_data_temp.dbf' 
       size 100m autoextend on;

--创建表空间
create tablespace test_data logging datafile 'test_data.dbf' 
       size 100m autoextend on;

--创建用户并指定表空间
create user test 
       identified by test 
     default tablespace test_data 
     temporary tablespace test_data_temp
     profile default;


--给用户授予角色权限
grant connect to test;
grant resource to test;
--给用户授予系统权限
grant unlimited tablespace to test;
--给用户授予管理员权限
grant dba to test;

exit;

创建表及修改表相关信息

--删除表
drop table t_persion;
--创建表
create table t_persion(
 pid varchar2(32) not null,
 pname varchar(32) not null,
 age number,
 sex char(1)
);
--修改表名
alter table t_persion rename to t_persion2;
--修改列名
alter table t_persion rename column pid to ppid;
--增改删表字段
alter table t_persion add (asd number);
alter table t_persion modify (asd char(1) default '0' not null);
alter table t_persion drop (asd);
--增删主键约束
alter table t_persion add constraint pk_t_persion primary key(pid);
alter table t_persion drop constraint pk_t_persion
--增删外键约束
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid);
alter table t_persion add constraint fk_t_persion foreign key (sex) references t_sex(sid) on delete cascade;--外键约束,级联删除
alter table t_persion drop constraint fk_t_persion;
--添加表注释
comment on table t_persion is '个人信息表';
--添加字段注释
comment on column t_persion.pid is 'id';
comment on column t_persion.pname is '姓名';
comment on column t_persion.age is '年龄';
comment on column t_persion.sex is '性别';

存储过程

无参+游标

--无参+游标
create or replace procedure demo1  
 as
 cursor cur_data is -- //(游标:一个可以遍历的结果集) 
       select * from t_persion t;
 begin 
   for cur_row in cur_data loop
     dbms_output.put_line(cur_row.pid||':'||cur_row.pname);
   end loop;
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 

insert into t_persion values('1','张三',18,1);
insert into t_persion values('2','李四',17,0);
call demo1();

入参

--入参
create or replace procedure demo2(
       p_pid in test.t_persion.pid%type,
       p_pname in varchar2,
       p_age in number,
       p_sex in char
       )
 is      
 begin 
    insert into t_persion(pid,pname,age,sex) values(p_pid,p_pname,p_age,p_sex);
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 
call demo2('3','王五',18,0);

入参+出参

--入参+出参
create or replace procedure demo3(
       p_pid in test.t_persion.pid%type,
       p_pname out varchar2
       )
 is      
 begin 
    select pname into p_pname from t_persion t where t.pid = p_pid;
    delete t_persion t where t.pid = p_pid;
 exception 
    when others then 
      dbms_output.put_line(sqlcode);
      dbms_output.put_line(sqlerrm);
 end; 
--在sqlplus中调用
var pname varchar2(32);
call demo3(3,:pname);
print :pname;