--PL/SQL编程
drop table emp;
--创建emp 和 dept表
CREATE TABLE EMP(
empno number primary key,--设置主键
ename varchar2(20) not null,
job varchar2(30) not null,
mgr number,
hiredate date not null,
sal number(10,2) not null,
comm number(10,2),
deptno number not null
);
drop table dept;
CREATE TABLE DEPT
(
deptno number primary key,
dname varchar2(30) not null,
loc varchar2(50) not null
)
-- 建立主外键 emp 中的deptno 字段参照 dept表中的 deptno
--格式 ALTER TABLE 主需要建立外键的表 ADD CONSTRAINT 外键名 FOREING KEY(外键表需要建立的具体的字段名) REFERENCES 需要参照的表（需要参照的字段）
ALTER TABLE emp ADD constraint FK_EMP_DEPT_DEPTNO FOREIGN KEY(deptno) references dept(deptno);
--添加emp 表 不指定字段
insert into emp values(7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-MM-dd'),800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','yyyy-MM-dd'),1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,to_date('1980-12-22','yyyy-MM-dd'),1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,to_date('1980-4-2','yyyy-MM-dd'),2975,null,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,to_date('1980-09-28','yyyy-MM-dd'),1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,to_date('1981-05-1','yyyy-MM-dd'),2850,null,30);
insert into emp values(7782,'CLARK','MANAGER',7839,to_date('1981-06-09','yyyy-MM-dd'),2450,null,10);
insert into emp values(7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-MM-dd'),5000,null,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','yyyy-MM-dd'),1500,null,30);
insert into emp values(7900,'JAMES','CLERK',7698,to_date('1981-12-3','yyyy-MM-dd'),950,null,30);
insert into emp values(7902,'FORO','ANALYST',7566,to_date('1981-12-3','yyyy-MM-dd'),3000,null,20);
insert into emp values(7934,'MILLER','CLERK',7782,to_date('1982-1-23','yyyy-MM-dd'),1300,null,10);
insert into dept values(10,'ACCOUNTING','NEW YORK');
insert into dept values(20,'RESEARCH','DALLAS');
insert into dept values(30,'ACCOUNTING','CHICAGO');
insert into dept values(40,'ACCOUNTING','BOSTON');
select * from dept;
select * from emp;
-- 索引 存储过程 sql优化
declare --申明
username varchar2(20); --定义变量 变量名 类型
age int:=23;
begin --开始执行
username:='&请输入姓名'; --从控制台输入
age:='&请输入年龄';
DBMS_OUTPUT.put_line('我的名字:'||username||'年龄'||age); --打印信息 ||连接符号 类似JAVA中的 +加号
DBMS_OUTPUT.put_line('我的名字:'||username||'年龄'||age);
DBMS_OUTPUT.put_line('在后面');
end; --结束
99
DECLARE
score number(4,1);
begin
score:='&请输入成绩：';
if score>=0 and score<60 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：D');
ELSIF score>=60 and score<80 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：C');
ELSIF score>=80 and score<90 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：B');
ELSIF score>=90 and score<=100 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：A');
ELSE
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 不在范围之内');
end if;
end;
DECLARE
score number(4,1);
begin
score:='&请输入成绩：';
case
when score>=0 and score<60 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：D');
when score>=60 and score<80 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：C');
when score>=80 and score<90 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：B');
when score>=90 and score<=100 then
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 等级是：A');
ELSE
DBMS_OUTPUT.put_line('你的成绩是:'||score||' 不在范围之内');
END CASE;
END;
DECLARE
num number:=-3;
begin
loop
DBMS_OUTPUT.put_line('num:'||num);
num:=num+1;
exit when num>3;
end loop;
DBMS_OUTPUT.put_line('循环结束');
end;
--while
DECLARE
num number:=-3;
begin
while num<3 loop
DBMS_OUTPUT.put_line('num:'||num);
num:=num+1;
end loop;
DBMS_OUTPUT.put_line('循环结束');
end;
DECLARE
v_ename emp.ename%TYPE;
begin
select ename into v_ename from emp where empno = 7499;
DBMS_OUTPUT.put_line('这是名字：'||v_ename);
end;
select * from emp
-----属性类型 %TYPE %ROWTYPE
--1.%TYPE 表示和某个表中的某个字段的数据类型一致
declare
v_sal EMP.Sal%TYPE; --设置和EMP表中的type要一致
BEGIN
select sal into v_sal from emp where empno = 7782;
DBMS_OUTPUT.put_line('我的工资：'||v_sal);
end;
--2.%ROWTYPE 和表的行数据类型一致
declare
v_emp EMP%ROWTYPE;
BEGIN
select * into v_emp from emp where empno=7499;
DBMS_OUTPUT.put_line('姓名：'||v_emp.ename||' 工资：'||v_emp.sal);
end;
DECLARE
v_ename EMP.ENAME%TYPE;
v_empno EMP.EMPNO%TYPE;
begin
v_empno:='&请输入员工编号';
select ename into v_ename from emp where empno=v_empno;
DBMS_OUTPUT.put_line('员工编号：'||v_empno||' 对应的名称：'||v_ename);
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line('员工编号不存在'||v_empno);
when TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('反正值过多'||v_empno);
when others then
DBMS_OUTPUT.put_line('其他未知的错误');
end;
-- 案例：插入数据到部门表，如果存在提示‘部门已经存在，添加失败’
select * from dept;
DECLARE
v_deptno DEPT.DEPTNO%TYPE;
v_dname DEPT.DNAME%TYPE;
v_loc DEPT.LOC%TYPE;
v_num number;--用来存储计数，判断有多少条记录
v_errorCode varchar2(100);
DEPT_EXP EXCEPTION;
begin
v_deptno:='&请输入部门编号：';
v_dname:='&请输入部门名称：';
v_loc:='&请输入部门地址：';
--向表里面添加数据
insert into dept(deptno,dname,loc) values(v_deptno,v_dname,v_loc);
--通过计数来判断是否添加成功
select count(deptno) into v_num from dept where dname = v_dname;
--判断
if v_num>1 then --表示插入的部门名称 重复
v_errorCode:='部门名称已经存在，请确认！！！';
--回滚
rollback;
--抛出异常
RAISE DEPT_EXP;
ELSE
DBMS_OUTPUT.put_line('部门信息插入成功');
--提交
commit;
end if;
exception --处理异常
when DEPT_EXP then --自定义的异常
---oracle允许自定义的错误代码的范围为 -20000 -- -20999
RAISE_APPLICATION_ERROR(-20001,v_errorCode);--用系统的方式 弹出错误的信息
when DUP_VAL_ON_INDEX then --系统预定义的异常
RAISE_APPLICATION_ERROR(-20001,'主键重复xxx');
when others then
RAISE_APPLICATION_ERROR(-20002,'其他未知的错误类型');
end;
-- 存储过程：就是用来存储PL/SQL过程语句，方便下次使用
CREATE OR REPLACE PROCEDURE pro_hellWorld
as
begin
DBMS_OUTPUT.put_line('HelloWolrd--我是一个无参的存储过程');
exception
when others then
DBMS_OUTPUT.put_line('哇，出现异常了');
end;
call pro_hellWorld();
CREATE OR REPLACE PROCEDURE addMony(p_empno in number,p_sal in number )
AS
v_newSal number;
begin
update Emp set sal = sal+p_sal where empno=p_empno;
commit;
select sal into v_newSal from emp where empno=p_empno;
DBMS_OUTPUT.put_line(p_empno||'现在的工资'||v_newSal);
exception
when others then
DBMS_OUTPUT.put_line('出现异常');
end;
select * from emp;
call addMony(7499,20000);
CREATE OR REPLACE PROCEDURE getEname(p_empno in number,p_ename out varchar2,p_sal out number)
AS
begin
--通过empno查询对应的姓名 和工资 并且赋值给name 和 sal
select ename,sal into p_ename,p_sal from emp where empno=p_empno;
end;
declare
v_name varchar2(20);--定义变量 用来接收存储过程的输出参数
v_sal number(10,2);
begin
getEname(7782,v_name,v_sal);-- 调用存储过程
DBMS_OUTPUT.put_line(v_name);
DBMS_OUTPUT.put_line(v_sal);
end;
select * from sys.user_source where name='ADDMONY';
select * from EMP;
-- 练习：创建存储过程，给所有工资低于5000员工，加薪20% ，但是最高不能超过 5000（如果提薪后超过5000，按照5000计算）
563
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
