Oracle/PLSQL常用操作语句

SQL
–创建表
create table 表名(字段名 字段类型);
create table user(
id number,
name varcahr2(20),
sex vrachr2(1)
);

–删除表
drop table 表名;
drop table user;

–添加主键
alter table 表名 add constraint 约束名 primary key(id);
alter table user add constraint pk_user primary key(id);

–删除主键
alter table 表名 drop constraint 约束名;
alter table user drop constraint pk_user;

–创建一般索引
create index 索引名 on user(创建索引的字段);
create index in_user on user(name);

–创建唯一索引（主键oracle默认为其创建唯一索引）
create unique 索引名 on user(创建索引的字段);
create unique index in_user_o on user(id);

–删除索引
drop index 索引名;
drop index in_user_o;

–表名注释
comment on table user is ‘用户’;

–字段注释
comment on column user.name is ‘用户名’；

–插入数据
insert into user(id,name) values(1,‘Jack’);
insert into user values(2,‘Jack’,‘M’);

–更新数据
update user set name = ‘Jet’ where id = 1;

–删除数据

delete from user where name = ‘Jet’;

–删除所有数据
truncate table user;
delete from user;

–增加字段
alter table user add (createdate date);

–修改字段类型
alter table user modify(createdate varchar2(20));

–删除字段
alter table user drop column createdate;

–修改字段名称
alter table user rename column name to username;

–创建序列
create sequence s_user_id start with 1 increment by 1 nomaxvalue nominvalue nocycle nocache;–自增序列

–删除序列
drop sequence s_user_id;

–查询序列
—直接用会报为在当前会话中定义
select s_user_id.currval from dual;
/*
currval 表示序列的当前值，新序列必须使用一次nextval 才能获取到值，否则会报错
nextval 表示序列的下一个值。新序列首次使用时获取的是该序列的初始值，从第二次使用时开始按照设置的步进递增
*/
–truncate(数据特多情况)
truncate table user;
—不可回滚
–DML(如update，delete，insert等)语句在为commit前可以回滚，DDL(create,drop,truncate等)语句不可回滚，因在执行后数据库自动为其提交一个commit;
create table ad(num1 int);
insert into ad values(1);
select * from ad;
savepoint s_1;–创建回滚点1
insert into ad values(2);
savepoint s_2;–创建回滚点1
select * from ad;
insert into ad values(3);
rollback to s_2;–回滚
select * from ad;
commit;
–排序函数dense_rank() over (partition by xxxx(以某个字段作为分组) order by xxxx(以某个字段作为排序))
select h_sc.student_no,
h_sc.course_no,
h_sc.core,
dense_rank() over(partition by h_sc.course_no order by h_sc.core desc) rk from hand_student_core h_sc
select trunc(add_months(sysdate,-1),‘month’)from dual 查询上月第一天
Select trunc(last_day(add_months(sysdate,-1))) from dual
查询上月最后一天

PLSQL
–创建游标
CURSOR emp_ded IS
SELECT e.department_id
FROM employees e;

–ref coursor 定义变量为游标类型
–不可用for rec in c_cursor 遍历，可以用loop … end loop;
type c_cursor is ref cursor;

–循环遍历游标
FOR rec IN emp_ded
LOOP
emp_d := rec.department_id;
emp_test(emp_d,
nup,
isalary,
asalary);
IF nup = 0
OR nup IS NULL THEN
RAISE ex;
ELSE
dbms_output.put_line(emp_d || ’ num is ’ || nup);
END IF;
END LOOP;

–Group BY 分组函数
SELECT AVG(salary)
,department_id
FROM employees
GROUP BY department_id
having AVG(salary) > 10000;

having 后面跟限定条件

–创建存储过程
PROCEDURE emp_test(emp_did IN employees.department_id%TYPE,
np OUT NUMBER,
misalary OUT employees.salary%TYPE,
masalary OUT employees.salary%TYPE) IS
BEGIN
SELECT COUNT(*) AS nup,
MIN(e.salary),
MAX(e.salary)
INTO np,
misalary,
masalary
FROM employees e
WHERE e.department_id = emp_did;
END emp_test;

–创建FUNCTION
FUNCTION area(side_one IN INTEGER,
side_two IN INTEGER,
side_three IN INTEGER) RETURN INTEGER IS
z INTEGER;
m INTEGER;
BEGIN
z := 0.5 * (side_one + side_two + side_three);
m := sqrt(z * (z - side_one) * (z - side_two) * (z - side_three));
RETURN m;
END area;

–创建自定义异常处理
PROCEDURE in_emp IS
ex EXCEPTION;
begin
EXCEPTION
WHEN ex THEN
dbms_output.put_line(emp_d);
END in_emp;
–数据库自带异常处理

–常用函数