oracle基本操作语句

oracle基本操作语句 收藏
/*
*数据定义语句(只操作表的结构)
*/

--创建表
--创建部门表
create table department_13(
department_id number(6),
department_name varchar(25),
manager_id number(6),
location_id number(4)
);
--使用子查询创建表
create table department_13_temp as select department_id, department_name from department_13;

--修改表
--增加字段
alter table department_13_temp add(manager_id number(6));
--删除字段
alter table department_13_temp drop column manager_id;
--修改字段名称
alter table 表名 rename column 原列名 to 新列名;
--修改字段类型
alter table department_13_temp modify(manager_id varchar(6));
--修改字段大小
alter table department_13_temp modify(manager_id number(4));

--删除表
drop table department_13_temp;

--数据字典表
select table_name from dba_tables;
select table_name from user_tables;
--察看用户拥有的数据库对象类型
select distinct object_type from user_objects;


--约束
--创建非空约束(同时也是列级约束)
create table department_13(department_id number(6) constraint dept_13_id not null, department_name

varchar(25),
manager_id number(6), location_id number(4));
--创建唯一性约束(同时也是表级约束)
create table department_13(department_id number(6), department_name varchar(25),
manager_id number(6), location_id number(4),
constraint dep_id_13_uni unique(department_id));
--创建主键约束
create table department_13(department_id number(6), department_name varchar(25),
manager_id number(6), location_id number(4),
constraint dep_id_13_pri primary key(department_id));

--创建外键约束
create table employee_13(employee_id number(6), employee_name varchar(25), email varchar(28), hire_date

date,
job_id varchar(20), salary number(8,2),commission_pct number(2,2),
manager_id number(6), department_id number(6),
constraint emp_13_foreign foreign key(department_id)
references department_13(department_id));
--check约束
create table employee_13_temp(employee_id number(6), employee_name varchar(25), email varchar(28),

hire_date date,
job_id varchar(20), salary number(8,2),commission_pct number(2,2),
manager_id number(6), department_id number(6),
constraint emp_sal_min check(salary > 8888));
--增加约束
alter table employee_13_temp add constraint emp_13_pri primary key(employee_id);
alter table employee_13_temp modify(salary not null);

--删除约束
alter table employee_13_temp drop constaint emp_13_pri;
--删除被外键参照的主键约束
alter table department_13 drop primary key cascade;

--手工创建索引
create I

ndex emp_13_sal on employee_13(salary);

--删除索引
drop index emp_13_sal;

--创建序列
create sequence hospital_id
minvalue 1
maxvalue 999999999999
start with 11
increment by 1
cache 10;


--创建视图
create or replace view emp_13_11 as select employee_id, employee_name, salary, job_id from employee_13

where department_id = 11;
create or replace view emp_13_dept as select d.department_name, d.manager_id, e.employee_name, e.salary

from employee_13 e, department_13 d
where e.department_id = d.department_id and e.department_id = 11;

create or replace view emp_13_dept_temp as select d.department_name, d.manager_id, e.employee_name,

e.salary from employee_13 e, department_13 d
where e.department_id = d.department_id;

--删除视图
drop view emp_13_11;

--查找出薪水最高的三个员工的信息(Top-N分析法):使用到了行内视图
select rownum, employee_name, salary from (select employee_name, salary from employee_13 order by

salary desc) where rownum <=3;

select * from (select employee_name, salary from employee_13 order by salary desc) where rownum <=3;

--创建一个同义词
create synonym ct from System.emp_13_dept_temp;
--删除同义词
drop synonym ct


/*
*数据操作语句(操作表的数据)
*/

--Insert语句
insert into department_13 values(13,'测试部',120,119);
insert into department_13 values(28,null,null,113);
insert into department_13 values(&department_id, '&department_name',&manager_id, &location_id);

--Update语句
update employee_13 set salary=66566 where employee_id = 3;

--merge语句(数据合并语句)
merge into depat_13_temp a
using department_13 b
on(a.department_id = b.department_id)
when matched then
update set
a.department_name = b.department_name,
a.manager_id = b.manager_id,
a.location_id = b.location_id
when not matched then
insert(a.department_id, a.department_name, a.manager_id, a.location_id)
values(b.department_id, b.department_name, b.manager_id, b.location_id);

--提交事务
update department_13 set manager_id = 120 where department_id = 14;
commit;

--察看自动提交环境变量
show autocommit;
--打开自动提交
set autocommit on;

--savepoint

update department_13 set manager_id=130 where department_id > 14;

savepoint undo1;

delete from department_13 where department_id > 14;

savepoint undo2;

rollback to undo1;

--SELECT语句
--带算书表达式的select语句
select employee_id, employee_name, salary, salary*12 from employee_13;
--带连接表达式的select语句
select employee_name|| '的年薪是:'|| salary*12 ||'美元'from employee_13;

--对空值的引用
select employee_name, salary, salary*(1+commission_pct) "奖金" from employee_13;
--字段别名
select employee_name "姓名", salary "薪水", salary*(1+commission_pct) "奖金" from employee_13;

--去掉重复值
select distinct salary from employee_13;

--带条件

的查询
select employee_id, employee_name, salary from employee_13 where department_id = 10;
--得到当前日期格式字符串
select * from v$nls_parameters;
--得到系统当前日期
select sysdate from dual;

--比较操作符
--between..and
select employee_name, job_id, hire_date from employee_13 where salary between 4000 and 7000;

--in
select employee_name, job_id, hire_date from employee_13 where salary in(6111,4111,7222);

--like
select employee_name, job_id, hire_date from employee_13 where employee_name like '李%';

--is null
select employee_name, job_id, hire_date from employee_13 where commission_pct is null;

--比较操作的逻辑运算符
-AND
select employee_name, job_id, hire_date from employee_13 where salary between 4000 and 7000 and job_id

= '软件架构师';
-- and .. or
select employee_name, salary from employee_13 where (job_name = '软件工程师' or job_name = '软件架构师

') and salary > 4000;

--排序显示
--单字段排序
select employee_name, salary from employee_13 order by salary desc;
--组合字段排序(主排序字段相同时，按照辅助排序字段排序)
select employee_name, salary, hire_date from employee_13 order by salary desc,hire_date desc;


/**
* SQL函数
*/
--单行函数
--字符函数
--大小写转换函数
select employee_id, salary from employee_13 where lower(employee_name) = 'draglong';
select employee_id, salary from employee_13 where upper(employee_name) = 'DRAGLONG';
select employee_id, salary from employee_13 where Initcap(employee_name) like 'D%';
--字符处理函数
select replace('db2', 'oracle') from dual;
select employee_name, concat(employee_name, job_name) name, length(employee_name) len, instr

(employee_name,'g') ins
from employee_13 where substr(employee_name,1,5) = 'dragl';

--日期函数
select employee_name, job_name, (sysdate-hire_date)/7 weeks from employee_13;
select hire_date, months_between(sysdate,hire_date) week, add_months(hire_date,6) week2, next_day

(sysdate,'星期六') nextday,
last_day(hire_date) from employee_13;
--round函数
select employee_name, hire_date, round(hire_date,'MONTH') from employee_13;
--trunc函数
select trunc(sysdate,'D'), trunc(sysdate,'MM'),trunc(sysdate,'MONTH'), trunc(sysdate,'DD') from dual;

--转换函数
--日期转换为字符TO_CHAR(字段名,'格式字符串')
select employee_name, to_char(hire_date, 'MM/YY') from employee_13;
select employee_name, to_char(hire_date, 'YEAR"年"MM"月"DD"日"') from employee_13;

select employee_name, to_char(hire_date,'"北京时间"YYYY"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM

employee_13;
select employee_name, to_char(hire_date,'YYYYspth"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM employee_13;

--数字转换为字符TO_CHAR(字段名,'格式字符串')
select employee_name, to_char(salary,'$99,999.99') from employee_13;
select employee_name, to_char(salary,'L99,999.99')

from employee_13;
select employee_name, to_char(salary,'$00,000.00') from employee_13;
--字符型日期转换为日期型日期to_date('日期字段值','格式字符串')
insert into employee_13 values(213,null,null,to_date('2007年04月28

日','YYYY"年"MM"月"DD"日"'),null,null,null,null,null);

--第五类函数
/**
*为空处理函数
*/
--NVL函数
select employee_name "姓名", salary "薪水", salary*(1+nvl(commission_pct,0)) "奖金" from employee_13;
--NVL2
select employee_name "姓名", salary "薪水", nvl2(commission_pct,'架构师','工程师') "级别" from

employee_13;

--NULLIF
select employee_name,length(employee_name) a, job_name, length(job_name) b, nullif(length

(employee_name),length(job_name)) result from employee_13;

--COALESCE(取得列表中的第一个非空值)
select employee_name, coalesce(commission_pct,salary) from employee_13;


/**
*CASE语句
*/
select employee_name, job_name, salary,
case job_name when '软件工程师' then 0.40*salary
when '软件架构师' then 0.30*salary
when '系统架构师' then 0.20*salary
else salary end "加薪幅度"
from employee_13;

/**
*DECODE语句
*/
select employee_name, salary, job_name,
decode(job_name, '软件工程师',0.40*salary,
'软件架构师',0.30*salary,
'系统架构师',0.20*salary,
salary) "工资涨幅"
from employee_13;

/**
* 分组函数(多行函数)
*/
select employee_name, salary,avg(salary),count(salary),max(salary),min(salary),sum(salary) from

employee_13;

--Group by语句
select department_id, avg(salary) from employee_13 group by department_id;

/**
*多表连接和子查询
*/
--等值连接
select d.department_name, e.employee_name, d.department_id, e.salary
from employee_13 e, department_13 d
where e.department_id = d.department_id
and d.department_name = '开发部';
--非等值连接
select d.department_name, e.employee_name, d.department_id, e.salary
from employee_13 e, department_13 d
where e.department_id = d.department_id
and e.salary between 4000 and 7000;
--左外连接
select d.department_name, e.employee_name, d.department_id, e.salary
from employee_13 e, department_13 d
where e.department_id(+) = d.department_id;
--右外连接
select d.department_name, e.employee_name, d.department_id, e.salary
from employee_13 e, department_13 d
where e.department_id = d.department_id(+);

--自连接
select worker.employee_name || ' work for ' || manager.employee_name from employee_13 worker,

employee_13 manager
where worker.manager_id = manager.employee_id;

--sql 1999
--cross join
select employee_name, department_name from employee_13 cross join department_13;
--natural join
select department_name from department_13 natural join employee_13;
https://www.sodocs.net/doc/6011682795.html,ing(使用指定的字段进行等值连接)
se

lect department_name from department_13 join employee_13 using(department_id);

--join..on(不同名称的字段上的等值连接)
select department_name from department_13 d join employee_13 e on(d.department_id = e.employee_id);
--左外连接
select d.department_name, e.employee_name, d.department_id, e.salary
from employee_13 e right outer join department_13 d
on d.department_id = e.department_id;
--全连接
select e.employee_name, e.department_id, d.department_name from employee_13 e full outer join

department_13 d
on(d.department_id = e.department_id);


/**
*子查询
*/
select employee_name, salary from employee_13 where salary>(
select salary from employee_13 where employee_name = '高伟祥') order by salary;

--单行比较操作
select employee_name, job_name, salary from employee_13 where job_name =(select job_name from

employee_13 where employee_id = 2);
select employee_name, job_name, salary from employee_13 where salary =(select min(salary) from

employee_13);

select department_id, min(salary) from employee_13 group by department_id having min(salary) > (select

min(salary) from employee_13 where department_id = 10);

--多行比较操作
select employee_id, employee_name, job_name, salary from employee_13 where department_id in(
select department_id from employee_13 where job_name = '软件工程师') AND
job_name <>'软件工程师';

select employee_name, salary, job_name from employee_13 where salary < ANY(
select salary from employee_13 where job_name = '软件工程师') and
job_name <>'软件工程师';

select employee_name, salary, job_name from employee_13 where salary < ALL(
select salary from employee_13 where job_name = '软件工程师') and
job_name <>'软件工程师';

select employee_name,job_name,salary from employee_13 where department_id in (select department_id from

employee_13 where job_name='软件工程师' and job_name<>"软件工程师');

select replace('Oracle Sql','Oracle','DB2') from dual;

/**
*PL/SQL开发
*/
--变量定义
set serveroutput on
declare
id number(6,4):=0;
hire_date date:=sysdate+7;
v_tax_rate constant number(3,2):=8.25;
v_valid boolean not null:=true;
begin
dbms_output.put_line('编号是: ' ||id);
dbms_output.put_line('入职时间: ' ||hire_date);
dbms_output.put_line('税率: ' ||v_tax_rate);
if v_valid then
dbms_output.put_line('这是真的');
else
dbms_output.put_line('这是假的');
end if;
end;
/

--表类型复合变量的定义
set serveroutput on
declare
type name_table_type is table of varchar(26) index by binary_integer;
t_name name_table_type;
begin
t_name(1):='陈龙';
t_name(2):='李林波';
t_name(3):='阿猫';
dbms_output.put_line('第一个数据为: '||t_name(1)

);
dbms_output.put_line('第二个数据为: '||t_name(2));
dbms_output.put_line('第三个数据为: '||t_name(3));
end;
/

--记录类型变量的定义
set serveroutput on;
declare
type dept_record_type is record(
department_id number(6),
department_name varchar(20),
manager_id number(6),
location_id number(6));
d_dept dept_record_type;
begin
d_dept.department_id :=111;
d_dept.department_name := '开发部';
d_dept.manager_id := 110;
d_dept.location_id := 119;
dbms_output.put_line(d_dept.department_id);
dbms_output.put_line(d_dept.department_name );
dbms_output.put_line(d_dept.manager_id);
dbms_output.put_line(d_dept.location_id);
end;
/

//查找数据库中的记录存放在记录类型变量中
set serveroutput on;
declare
type dept_record_type is record(
department_id number(6),
department_name varchar(20),
manager_id number(6),
location_id number(6));
d_dept dept_record_type;
begin
select department_id, department_name, manager_id, location_id into
d_dept.department_id, d_dept.department_name, d_dept.manager_id, d_dept.location_id from

department_13 where department_id = 11;
dbms_output.put_line(d_dept.department_id);
dbms_output.put_line(d_dept.department_name );
dbms_output.put_line(d_dept.manager_id);
dbms_output.put_line(d_dept.location_id);
end;
/

--使用%type属性定义变量
set serveroutput on
declare
e_name employee_13.employee_name%type;
e_sal number(8,2);
e_min_sal e_sal%type:= 1888;
begin
e_name:='jack';
e_sal := 1899;
e_min_sal:=e_sal/3;
dbms_output.put_line(e_name);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_min_sal);
end;
/

//查找数据库中的记录存放在%type属性定义的变量中
set serveroutput on
declare
e_name employee_13.employee_name%type;
e_sal number(8,2);
e_min_sal e_sal%type:= 1888;
begin
select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13 where employee_id

= 2;
dbms_output.put_line(e_name);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_min_sal);
end;
/

--%rowtype属性定义的变量
set serveroutput on
declare
r_dept department_13%rowtype;
begin
r_dept.department_id := 115;
r_dept.department_name := 'temp';
r_dept.manager_id := 111;
r_dept.location_id:=112;
dbms_output.put_line(r_dept.department_id);
dbms_output.put_line(r_dept.department_name);
dbms_output.put_line(r_dept.manager_id);
dbms_output.put_line(r_dept.location_id);
end;
/

--pl/sql程序块
set serveroutput on
DECLARE
v_weight NUMBER(3) := 100;
v_message VARCHAR2(255) := 'Outer Value';
BEGIN
DECLARE
v_weight NUMBER(3) := 1;
v_message VARCHAR2(255) := 'Inner value';
BEGIN
v_weight := v_weight + 1;
v_message := 'Put' || v_message;
dbms_output.put_line(v_weight);
dbms_output.put_line(v_message);

END;
v_weight := v_weight + 1;
v_message := 'Put'|| v_message;
dbms_output.put_line(v_weight);
dbms_output.put_line(v_message);
END;
/

--PL/SQL中的select语句
set serveroutput on
declare
e_name employee_13.employee_name%type;
e_sal number(8,2);
e_min_sal e_sal%type:= 1888;
begin
select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13;
dbms_output.put_line(e_name);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_min_sal);
end;
/

--修改当前会话的语言环境
alter session set nls_language=american;
--pl/sql中的insert ,update, delete
begin
insert into department_13 values(321,'test',111,null);
update department_13 set manager_id = 112 where department_id =12;
delete department_13 where department_id = 15;
commit;
end;
/

--pl/sql中的条件分支语句
--if...then
set serveroutput on
declare
v_sal number;
begin
select salary into v_sal from employee_13 where employee_id = 2;
IF v_sal < 3000 THEN
dbms_output.put_line('薪水较低');
ELSIF v_sal < 10000 THEN
dbms_output.put_line('中等薪水');
ELSE
dbms_output.put_line('薪水很高');
END IF;
end;
/

set serveroutput on
declare
v_sal number;
begin
select salary into v_sal from employee_13 where employee_id = 2;
IF v_sal < 3000 THEN
update employee_13 set job_name='软件工程师';
commit;
END IF;
IF v_sal >6000 THEN
update employee_13 set job_name='软件架构师';
commit;
END IF;
end;

--简单循环
declare
v_number number:=1;
begin
loop
insert into test_table values('姓名'||v_number,v_number*10);
v_number:= v_number+1;
exit when v_number>10;
end loop;
end;
/

--for循环
set serveroutput on
declare
type t_number is table of number index by binary_integer;
v_1 t_number;
v_total number;
begin
for v_count IN 1..10 LOOP
v_1(v_count):=v_count;
dbms_output.put_line(v_1(v_count));
end loop;
v_total := v_1.COUNT;
dbms_output.put_line(v_total);
end;
/

--for循环和if的结合使用
begin
for v_1 in 1..10 loop
insert into test_table values('陈龙'||v_1, v_1*10);
if v_1 =5 then
exit;
end if;
end loop;
end;
/

--while循环
declare
v_1 number:=1;
begin
while v_1 <=10 loop
insert into test_table values('draglong'||v_1, v_1+10);
v_1:= v_1+1;
end loop;
end;
/

--游标
--使用游标取得记录
set serveroutput on
declare
e_name employee_13.employee_name%type;
e_sal employee_13.salary%type;
e_job employee_13.job_name%type;
cursor cl is
select employee_name, salary, job_name from employee_13 where job_name='超人';
begin
open cl;
if cl%isopen then
loop
fetch cl into e_name, e_sal,e_job;
dbms_output.put_line(e_name);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_job);

exit when cl%not

found;
end loop;
dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);
close cl;
end if;
end;
/

--游标的复合类型变量的使用
set serveroutput on
declare
cursor cl is
select employee_name, salary, job_name from employee_13;
emp_record cl%rowtype;
begin
open cl;
loop
fetch cl into emp_record;
dbms_output.put_line(emp_record.employee_name);
dbms_output.put_line(emp_record.salary);
dbms_output.put_line(emp_record.job_name);
exit when cl%notfound;
end loop;
dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);
close cl;
end;
/
--for循环取得游标记录
set serveroutput on
declare
v_number number;
cursor cl is
select employee_name, salary, job_name from employee_13;
begin
for v1 in cl loop
dbms_output.put_line(v1.employee_name);
dbms_output.put_line(v1.salary);
dbms_output.put_line(v1.job_name);

v_number:=v1.COUNT;
end loop;
--dbms_output.put_line('取得的游标记录数为: '||v_number);
end;
/


/**
*异常处理部分
*/
--预定义异常

set serveroutput on
declare
e_name employee_13.employee_name%type;
e_sal number(8,2);
e_min_sal e_sal%type:= 1888;
begin
select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13 where salary >

2000;
dbms_output.put_line(e_name);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_min_sal);
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('没有符合条件的数据');
when TOO_MANY_ROWS THEN
dbms_output.put_line('数据库中存在多条记录，不符合查找的要求');
when others then
dbms_output.put_line('其他错误');
end;
/

--非预定义异常
set serveroutput on
declare
EX EXCEPTION;
pragma EXCEPTION_INIT(EX, -01400);
begin
insert into department_13(department_id)values(null);
exception
when EX then
dbms_output.put_line('ora-1400 occurred 必须插入有效的部门编号');
end;
/


set serveroutput on
declare
EX EXCEPTION;
pragma EXCEPTION_INIT(EX, -2292);
begin
delete from department_13 where department_id = 10;
exception
when EX then
dbms_output.put_line('ora-2292 occurred 该条记录已经被其他字表参照');
end;
/

--用户自定义异常
set serveroutput on
declare
e_employee EXCEPTION;
v1 number;
begin
select count(*) into v1 from employee_13 where department_id = 12;
dbms_output.put_line(v1);
if v1 > 0 then
raise e_employee;
else
delete from department_13 where department_id = 12;
end if;
exception
when e_employee then
dbms_output.put_line('部门不能删除，因为部门存在员工');
end;
/

--创建错误日志表
create table log_table_error(code number(30), message varchar(200), info varchar(200));
--when others子句

declare
v_ErrorCode number;
v_ErrorMessage varchar

(200);
v_CurrentUser varchar(8);
v_Information varchar(100);
v_name varchar(30);
begin
select employee_name into v_name from employee_13;
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMessage:= SQLERRM;
v_CurrentUser:=USER;
v_information:='Error encountered on ' || to_char(sysdate) || 'by database user ' || v_CurrentUser;
insert into log_table_error values(v_ErrorCode, v_ErrorMessage,v_Information);
end;
/

/**
*存储过程
*/
--创建修改数据的存储过程
CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary(
v_emp_id IN NUMBER, v_new_salary IN NUMBER)
IS
BEGIN
UPDATE employee_13
SET salary=v_new_salary
WHERE employee_id = v_emp_id;
COMMIT;
END;
/

--创建追加数据的存储过程
CREATE OR REPLACE PROCEDURE AddDepartment(
dept_id department_13.department_id%type,
dept_name department_13.department_name%type,
manag_id department_13.manager_id%type,
location_id department_13.location_id%type)
IS
BEGIN
INSERT INTO department_13 VALUES(dept_id, dept_name, manag_id, location_id);
COMMIT;
END;
/


--存储过程调用存储过程
CREATE OR REPLACE PROCEDURE process_updateSalary(v_emp_id IN NUMBER, v_new_salary IN NUMBER)
IS
BEGIN
UpdateEmployeeSalary(v_emp_id, v_new_salary);
END;

--创建函数
CREATE OR REPLACE FUNCTION tax_rate(v_value IN NUMBER) RETURN NUMBER
IS
BEGIN
IF v_value >1600 THEN
RETURN(v_value*0.11);
ELSE
RETURN(0);
END IF;
END tax_rate;
/

CREATE OR REPLACE FUNCTION rick_tax(r_test IN NUMBER)
return NUMBER
IS
temp NUMBER;
begin
select salary into temp from employee_13_rick where employee_id = r_test;

IF temp > 1600 THEN
RETURN (temp*0.11);
ELSE
RETURN (0);
END IF;
END rick_tax;

CREATE OR REPLACE FUNCTION Select_salary(id IN NUMBER) RETURN NUMBER
IS
v_salary NUMBER;
cursor cl is
SELECT SALARY FROM employee_13 WHERE employee_id=id;
BEGIN
open cl;
loop
fetch cl into v_salary;
exit when cl%notfound;
end loop;
close cl;
return(v_salary*0.11);
END Select_salary;
/

--创建包
--创建包头
CREATE OR REPLACE PACKAGE dml_dept
IS
PROCEDURE insert_dept(p_id number, p_name varchar, m_id number, l_id number);
PROCEDURE delete_dept(p_id number);
PROCEDURE update_dept(p_id number, m_id number);
FUNCTION select_manager(p_id number) RETURN NUMBER;
END dml_dept;
/
--创建包体
CREATE OR REPLACE PACKAGE BODY dml_dept
IS
--插入数据的存储过程
PROCEDURE insert_dept(p_id number,p_name varchar,m_id number,l_id number)
IS
v_1 number;
BEGIN
select count(*) into v_1 from department_13 where department_id = p_id;
if v_1 > 0 then
dbms_output.put_line('这个部门已经存在,不需要加入');
else
insert into department_13 values(p_id,p_name, m_id, l_id);

end if;
end;
--删除数据的存储过程
procedure delete_dept(p_id number)
is
v1 number;
begin
select count(*) into v1 from department_13 where department_id = p_id;
if v1 > 0 then
delete department_13 where department_id = p_id;
end if;
end;
--修改数据的存储过程
procedure update_dept(p_id number, m_id number)
is
v1 number;
begin
update department_13 set manager_id = m_id where department_id = p_id;
end;

--查找数据的函数
function select_manager(p_id number) return number
is
v1 number;
begin
select department_id into v1 from department_13 where manager_id = p_id;
dbms_output.put_line('部门编号是：'|| v1);
return(v1);
end;
end dml_dept;
/


--在Oracle中建一个编号会自动增加的字段,以利于查询
1、建立序列：

CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2;
2、建立触发器：

CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual;
--use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END;