- --在sqlplus中用system/svse连接 然后授权(grant dba to scott) 再将权限授予svse用户(grant connect,resource to svse)
- ----建立表空间svsespace并建立svse用户,建立如下表格 employee
- --建立表空间svsespace
- create tablespace svsespace
- datafile 'd:\\svse.dbf'
- size 5m;
- --建立svse用户
- create user svse
- identified by svse123
- default tablespace svsespace;
- --建立如下表格 employee
- create table employee
- (
- empno number(8) primary key,
- ename varchar2(20),
- job varchar2(20),
- sal number(8),
- deptno number(4) references dept(deptno)
- );
- --建立如下表格Dept表
- create table dept
- (
- deptno number(4) primary key,
- dname varchar2(20),
- location varchar2(20)
- );
- --为Dept表添加数据
- insert into dept values(10,'ACCOUNTING','武汉');
- insert into dept values(20,'NEW YORK','北京');
- insert into dept values(30,'BOSTON','上海');
- --为employee表添加数据
- insert into employee values(10001,'史密斯','职员',1000,10);
- insert into employee values(10002,'琼斯','分析员',3000,20);
- insert into employee values(10003,'爱德华','经理',5000,10);
- insert into employee values(10004,'福特','职员',1200,10);
- insert into employee values(10005,'艾伦','销售员',10500,20);
- insert into employee values(10006,'凯文','职员',1250,30);
- insert into employee values(10007,'鲍勃','分析员',3200,30);
- insert into employee values(10008,'贝克','经理',11500,30);
- insert into employee values(10009,'斯蒂文','会计师',6000,10);
- insert into employee values(10010,'苏珊','职员',600,20);
- select * from employee;
- select * from dept;
- --为employee表的empno字段创建序列
- create sequence seq_no
- start with 10011
- increment by 1;
- ----1.1 使用PLSQL实现数据的添加,要求接受输入,然后将数据加到数据库
- declare
- myename employee.ename%type;
- myjob employee.job%type;
- mysal employee.sal%type;
- mydeptno employee.deptno%type;
- begin
- myename := '&请输入员工姓名';
- myjob := '&请输入员工工作';
- mysal := &请输入员工工资;
- mydeptno := &请输入部门编号;
- insert into employee values(seq_no.nextval,myename,myjob,mysal,mydeptno);
- dbms_output.put_line('添加成功');
- end;
- --1.2 添加数据时,要求如果工资高于10000或低于800,则抛出异常,并打印异常信息
- declare
- myename employee.ename%type;
- myjob employee.job%type;
- mysal employee.sal%type;
- mydeptno employee.deptno%type;
- errorsal exception;
- begin
- myename := '&请输入员工姓名';
- myjob := '&请输入员工工作';
- mysal := &请输入员工工资;
- mydeptno := &请输入部门编号;
- --判断输入的工资是否大于10000或者小于800,如果是则抛出异常
- if mysal < 800 then
- raise errorsal;
- elsif mysal > 10000 then
- raise errorsal;
- else
- insert into employee values(seq_no.nextval,myename,myjob,mysal,mydeptno);
- dbms_output.put_line('添加成功');
- end if;
- exception
- when errorsal then
- dbms_output.put_line('您输入的工资范围必须在800到10000之间');
- end;
- --1.3 在该模式下,创建一个序列SEQ_ORDER,该序列从1开始,到9999为止,且不能循环计数
- create sequence seq_order
- start with 1
- increment by 1
- maxvalue 9999
- nocycle;
- --1.4 设计一个视图,能显示所有员工编号、名称、工作、薪水、部门姓名,部门所在地。
- --给svse用户创建视图的权限(在cmd中输入grant create view to svse 赋予用户svse创建视图的权限)
- create or replace view myview
- as
- select a.*,location from employee a,dept b where a.deptno = b.deptno;
- --查看视图
- select * from myview;
- --2. 用存储过程接受两个数相除并且显示结果,如果第二个数为0,则显示消息“除数不能为0”。
- create or replace procedure getNum(num1 number,num2 number)
- as
- num number;
- begin
- num := num1 / num2;
- end;
- --测试
- declare
- num1 number;
- num2 number;
- num number := num1 / num2;
- begin
- num1 := &请输入第一个数;
- num2 := &请输入第二个数;
- if num2 = 0 then
- dbms_output.put_line('除数不能为0');
- else
- getNum(num1,num2);
- dbms_output.put_line('所得值为;' || num1/num2);
- end if;
- end;
- --3. 编写一个存储过程,接受一个员工名,从emp表中显示该雇员的工作岗位与薪水,若输入的雇员名不存在,显示“该雇员不存在”信息。
- create or replace procedure getInfo(myno emp.empno%type,myjob out emp.job%type,mysal out emp.sal%type)
- as
- begin
- select job,sal into myjob,mysal from emp where empno = myno;
- dbms_output.put_line('获得成功!');
- end;
- --测试
- declare
- mysal emp.sal%type;
- myjob emp.job%type;
- inputempno emp.empno%type;
- begin
- inputempno := &请输入员工编号;
- getInfo(inputempno,myjob,mysal);
- dbms_output.put_line('员工工作:'||myjob ||' 员工工资:'||mysal);
- exception
- when no_data_found then
- dbms_output.put_line('您输入的员工编号有误,请核对后重新输入...');
- end;
- --4. 使用游标,接受一个部门号,从emp表中显示该部门的所有雇员的姓名,工作和薪水(带参数的游标)
- declare
- type mycurtype is record(myename emp.ename%type,myjob emp.job%type,mysal emp.sal%type);
- cursor mycur(mydeptno emp.deptno%type) is
- select ename,job,sal from emp where deptno = mydeptno;
- emprow mycurtype;
- theno emp.deptno%type;
- begin
- theno := &请输入部门编号;
- dbms_output.put_line('输出部门员工信息如下...');
- open mycur(theno);
- loop
- fetch mycur into emprow;
- exit when mycur%notfound;
- dbms_output.put_line('姓名:'||emprow.myename||' 工作:'||emprow.myjob||' 薪水'||emprow.mysal);
- end loop;
- close mycur;
- end;
- --5. 编写一个程序块,从emp表中对名字以”A”或”S”开头的所有雇员按他们基本薪水的10%给他们补贴,
- --如果该雇员的总工资(工资+补贴)超过1500元,按总工资的5%扣除个人所得税,并输出员工的应得工资。
- declare
- cursor mycur is select * from emp for update;
- mysal emp.sal%type;
- begin
- update emp set sal = sal * 1.1 where ename in(
- select ename from emp where ename like 'A%' or ename like 'S%');
- for emprow in mycur
- loop
- if emprow.sal + emprow.comm > 1500 then
- mysal := (emprow.sal + emprow.comm) * 0.95;
- dbms_output.put_line('该员工的姓名为:'||emprow.ename||' 该员工的实际工资为:'||emprow.sal||' 该员工应得工资为:'||mysal);
- end if;
- end loop;
- end;
- ----查询emp表中ename以A或者S开头的名字
- select ename,sal from emp where ename like 'A%' or ename like 'S%';
- /*****************************************************************/
- --6. 当更新emp表中的comm字段的值为空时,自动修改comm字段的值为’0’。
- create or replace trigger tri_update
- after update on emp
- for each row
- begin
- end;
- select comm,ename,sal from emp;
- /*****************************************************************/
- --7. 创建触发器,实现当某个部门被删除时,就把相应员工部门的名改为NULL。
- create or replace trigger tri_delete
- after delete on dept
- for each row
- declare
- cursor mycur is select * from emp where deptno = :old.deptno for update;
- begin
- for emprow in mycur
- loop
- update emp set deptno = null where deptno = emprow.deptno;
- dbms_output.put_line('修改成功!');
- end loop;
- end;
- --测试
- delete from dept where deptno = 10
- select * from dept;
- select * from emp;
- --该片段来自于http://www.codesnippet.cn/detail/260220132229.html
来源: http://www.codesnippet.cn/detail/260220132229.html