oracle中getnum,oracle 常用SQL语句之二

oracle 常用SQL语句之二

SQL

#oracle2012-05-05 00:22

--在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://my.oschina.net/58685474

相关文章