PL/SQL基础代码总结（1）

--创建一张和emp一样的表
create talble emp as select * from scott.emp;
--创建用户
create user hehe identified by 123;
--给hehe创建会话的权限
grant create session to hehe;
--给hehe查询所有表的权限
grant select any table to hehe;
--给hehe创建表的权限
grant create table to hehe;
--给hehe资源权限
grant resource to hehe;
--收回权限
revoke create session,create table,resource,select any table from hehe;
--删除表中的列
alter table student drop column hobby;
--添加student表的haha列类型为varchar2
alter table student add haha varchar2(100);
--添加数值
insert into student(age,sex) values (19,女);
--修改数据
update student set name='刘',age=22 where name='李';
--删除数据
delete from student where name='刘';
--增删改小结

insert into student(id,name) values(3,'Linder');
update student set name='Black' where id=3;
delete from student where name='Jerry';
alter table student drop column gender;
（2）
--创建序列sq_106序列
create sequence sq_106
minvalue 10
maxvalue 10000
start with 10
increment by 2
cache 20 --缓存大小
--创建视图v_106
create view v_106 as select e.ename,d.name,e.sal,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by sal desc;
--查询视图
select from v_106;
--滤空
select e.,nvl(sal,0) from emp e order by sal desc;
--分页
select from (select e.,rownum r form emp e) where r>3 and r<=6;