自写的非常不错的oracle语句 精选
发布时间:2020-12-11 23:15:58 所属栏目:MySql教程 来源:网络整理
导读:今天PHP站长网 52php.cn把收集自互联网的代码分享给大家,仅供参考。 --在sqlplus中用system/svse连接 然后授权(grant dba to scott) 再将权限授予svse用户(grant connect,resource to svse)----建立表空间svsespace并建
以下代码由PHP站长网 52php.cn收集自互联网 现在PHP站长网小编把它分享给大家,仅供参考 --在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,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,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 以上内容由PHP站长网【52php.cn】收集整理供大家参考研究 如果以上内容对您有帮助,欢迎收藏、点赞、推荐、分享。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |