欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle常用Sql操作讲解

程序员文章站 2022-10-31 15:17:40
创建表空间与用户 /* 说明:若已经存在相应的用户和表空间,则需要先删除相应的用户和表空间 然后再全部重新建立 */ --删除用户 drop user te...

创建表空间与用户

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

--删除用户
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;