ORACLE(国家中级)练习题

Oracle数据库编程：

SET SERVEROUTPUT ON --显示输出 --匿名块

DECLARE --定义：变量 变量名 变量类型 %type表示和该字段数据类型一致 %rowtype表示和类表结构一致 MYSAL SCOTT.EMP.SAL%TYPE;

CURSOR MYCUR IS SELECT * FROM SCOTT.EMP WHERE SAL > MYSAL; --游标 1：定义2：打开3：取值4：关闭

RECORD1 MYCUR%ROWTYPE;

BEGIN --执行部分 MYSAL:=500; OPEN MYCUR;

FETCH MYCUR INTO RECORD1;

DBMS_OUTPUT.PUT_LINE('工作证件：'||RECORD1.EMPNO); DBMS_OUTPUT.PUT_LINE('姓名：'||RECORD1.ENAME); DBMS_OUTPUT.PUT_LINE('职业：'||RECORD1.JOB); DBMS_OUTPUT.PUT_LINE('销售额：'||RECORD1.SAL); END;

create or replace procedure showavgsal(p_deptno number) --标题部分 --过程内参 in out inout 默认为in

as --命名块 as 代替 declare --声明部分 v_sal number(6,2);

begin --执行部分

select avg(sal) into v_sal from scott.emp where deptno=p_deptno; --在程序设计中 select 后往往跟 into

dbms_output.put_line(v_sal); --在显示表结构时 select后跟 from end;

execute showavgsal(10);

--输出单行的用变量+select into 的用法 多行输出 用游标+for循环

--select into 只能查询一个记录的信息 如果没有查询到 no_data_found异常 如果查询到多个 too_many_rows异常

--select into后的变量个数，顺序必须的与查找字段相匹配

declare

v_emp emp%rowtype;

v_ename emp.ename%type; v_sal emp.sal%type;

begin

select ename,sal into v_ename,v_sal from emp where deptno=9090; dbms_output.put_line(v_ename||' '||v_sal);

exception

when no_data_found then dbms_output.put_line('lalalla'); end;

declare

v_deptno.scott.emp.deptno%type; v_increment number(4);

v_empno scott.emp.empno%type; begin

v_empno:=&x;

select deptno into v_deptno from scott.emp where empno=v_empno; if v_deptno=10 then v_increment:=100; elsif v_deptno=20 then v_increment:=150; elsif v_deptno=30 then v_increment:=200; else v_increment:=300; end if;

update scott.emp set sal=sal+v_increment where empno=v_empno; end;

提取数据 declare

cursor mycur is select ename,sal from scott.emp where sal<3000;

begin

for abc in mycur loop

dbms_output.put_line(abc.ename||' '||abc.sal);

end loop;

exception when others then null; end;

算平均工资

declare

cursor mycur is select deptno,avg(sal) avsal from scott.emp group by deptno;

begin

for abc in mycur loop

dbms_output.put_line(abc.deptno||' '||abc.avsal); end loop;

exception when others then null; end;

create or replace procedure procedure1 as tem scott.emp.sal%type; begin

select sal into tem from scott.emp where empno=7369; dbms_output.put_line(to_char(tem)); end;

exec procedure1

create or replace procedure procedure2( tempno in scott.emp.empno%type, name1 out scott.emp.ename%type, job1 in out scott.emp.job%type) as

myjob scott.emp.job%type;

myname scott.emp.ename%type; begin

select job into myjob from scott.emp where empno=tempno; select ename into myname from scott.emp where empno=tempno; name1:='姓名'||myname; job1:='职业'||myjob; end;

set serveroutput on declare

myno scott.emp.empno%type; myname1 scott.emp.ename%type; myjob1 scott.emp.job%type; begin

myno:=7369; myname1:=''; myjob1:='';

procedure2(myno,myname1,myjob1); dbms_output.put_line(myno); dbms_output.put_line(myname1); dbms_output.put_line(myjob1);

end;

--创建一个过程，要求以部门号为参数，输出该部门的平均工资格式为 --如果输入的部门号不存在，输出不存在的提示

create or replace procedure p1(v_deptno emp.deptno%type) as

avgsal emp.sal%type; begin

select avg(sal) into avgsal from emp where deptno=v_deptno;

dbms_output.put_line(v_deptno||'department''s average salary is' ||avgsal);

for i in (select empno,ename from emp where sal>avgsal and deptno=v_deptno)loop dbms_output.put_line(i.empno||' '||i.ename); end loop;

exception

when no_data_found then dbms_output.put_line('the department doesn''t exists');

end; exec

--创建过程，要求输入员工编号，姓名工作吗，工资，部门号，为表插入数据 --create or replace procedure p2(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type,v_sal emp.sal%type,v_deptno emp.deptno%type)

--as --begin --insert emp(empno,ename,job,sal,deptno)values(v_empno,v_ename,v_job,v_sal,v_deptno);

--end;

--exec p2(111,'mike','clerk',100,10);

--select * from emp;

declare

v_deptno emp.deptno%type;

cursor mycur is select ename,hiredate from emp where deptno=v_deptno; begin

v_deptno:=&x;

dbms_output.put_line('序号 姓名 雇佣日期'); for i in mycur loop

dbms_output.put_line(mycur%rowcount||' '||i.ename||' '||i.hiredate); end loop; end;

delete from emp where empno='111';

into

create or replace function return_maxsal(p_deptno scott.emp.deptno%type) return scott.emp.sal%type as

v_maxsal scott.emp.sal%type; begin

select max(sal) into v_maxsal from scott.emp where deptno=p_deptno; return v_maxsal; exception

when no_data_found then

dbms_output.put_line('the deptno is invalid!'); end;