文章标题

题目
Through the below execise, you can create the block,procedure,exeception.
–PL/SQL
2. Create a procedure called MY_PROCEDURE to output the phrase “My Procedure Works” to the screen.Then rewrite it in a function
3. Create a procedure to modify the salary to a 10% araise in EMP for a given empno.
4. Create and execute a procedure named MULTIPLIER that accepts two numbers through variables. The first number should be
divided by the second number and have the second number added to the result. The result should be written to a
PL/SQL variable and printed to the screen.Then rewite it in a function
5. Create a procedure to insert a new department into the DEPT table.
a. Use the DEPT_ID sequence generator for the department number.
b. Create a parameter for the department name.
c. Create a parameter for the location.
d. Execute the procedure.
6. Create a procedure to update the location for the department you created in execrise 5.
a. Create a parameter for the department number.
b. Create a parameter for the location number.
d. Test the procedure. What happens if you enter a location number that does not exist?
7. Create a procedure named EMP_MESSAGE that selects the employee last name,start date, and salary based on an employee number
provided at execution. Print a message to the screen based on any combination of one of the following criteria.
Criteria Message
Salary greater than 1200 Salary more than 1200
Name contains “R” Name contains “R”
Start date is in March March start date
None of the above None
8. Create a procedure ADD_COMMENTS to add a new columns in EMP
a. Add a new column named COMMENTS ;
b. According to the following criteria,update COMMENTS :
Criteria Comments
Have subordinate Is a manager
Have no subordinate is a clerk
c. Execuite the procedure
9. Write a procedure named SALARY_RANGE that prints the names of the employees that make plus or minus $100 of the salary value entered.

a. If there is no employee within that salary range, then print a message to the user indicating that is the case. Use an exception for this case.
b. If there are more than 3 employees within that range, then the message should indicate how many employees have that salary range.
Your results should look like the list below:
PL/SQL> SALARY_RANGE (1000);
Employees who make around 1000are:Biri,Schwartz,SmithPL/SQL>SALARYRANGE(900);4employeesalariesarewithin100 of 900PL/SQL>SALARYRANGE(2000);Exception:Norecordswithinthe2000 range.
10. Create a procedure EXCEPTION_DESIGN which use the predefined and user definded exception:
a. Create a parameter for empno ;
b. if the employee work over 5 years,the salary will increase 2000.Over 3 years, the salary will increate 1000, less than 3 years,
add the user exception to output the message: The employee is out the range of adding salary.
答案：
第二题：
create or replace procedure cux_practice_one is
begin
fnd_file.put_line(apps.fnd_file.output, ‘My Procedure Works’);
end cux_practice_one;
第三题：
create or replace procedure cux_procedure is

begin
update emp set sal=sal*1.1 where emp.empno =’7369’;
– dbms_output.put_line (fun_count(‘7369’));
end cux_procedure;
第四题：
declare
v_dividend float;
v_divisor float;
v_result float;

begin
v_dividend:=&被除数;
v_divisor:=&除数;
v_result:=v_dividend/ v_divisor;

  dbms_output.put_line(v_result ||'和'|| v_divisor);  end; 

第五题：
create or replace procedure cux_create_dept
(deptno in number,dname in varchar2,loc in varchar2)
is
begin
insert into dept values(deptno,dname,loc);

end cux_create_dept;
第六题：
–根据deptno 来更新
update dept set loc=’beijing’ where dept.deptno=20
–由于表中没有location number这个字段，所以我就向表中添加进入该字段，并加入相应的数据。
alter table dept add locnu varchar(20)；
update dept set locnu=’101’ where dept.deptno=10;
update dept set locnu=’102’ where dept.deptno=20;
update dept set locnu=’103’ where dept.deptno=30;
update dept set locnu=’104’ where dept.deptno=40;
–根据location number 来更新
update dept set loc=’shanghai’ where dept.locnu=’102’;
–如果写一个不存在的location number 来进行更新的话，该程序不会报错，并且进行编译，显示0行被更新。
update dept set loc=’us’ where dept.locnu=’108’;
第七题：
create or replace procedure cux_create_dept
(deptno in number,dname in varchar2,loc in varchar2)
is
begin
insert into dept values(deptno,dname,loc);

end cux_create_dept;

第八题：
/*declare
b_empno emp%rowtype;
p_no number(4);
empno number(4);
ename varchar2(10);
hiredate date ;

cursor v_empno is
select emp.ename,emp.hiredate,emp.sal,emp.empno from emp where emp.sal>1200;
begin
for b_empno in v_empno
loop
dbms_output.put_line(b_empno.empno||’-‘||b_empno.ename||’-‘||b_empno.hiredate||’—’||b_empno.sal);
end loop;
end;*/
/* declare
b_empno emp%rowtype;
p_no number(4);
empno number(4);
ename varchar2(10);
hiredate date ;

cursor v_empno is
select emp.ename,emp.hiredate,emp.sal,emp.empno from emp where emp.ename like ‘%R%’;
begin
for b_empno in v_empno
loop
dbms_output.put_line(b_empno.empno||’-‘||b_empno.ename||’-‘||b_empno.hiredate||’—’||b_empno.sal);
end loop;
end;*/
declare
b_empno emp%rowtype;
p_no number(4);
empno number(4);
ename varchar2(10);
hiredate date;

cursor v_empno is
select emp.ename,
emp.hiredate,
emp.sal,
emp.empno
from emp
where emp.hiredate(month,[dateadd],getdate())=3;
begin
for b_empno in v_empno loop
dbms_output.put_line(b_empno.empno || ‘-’ || b_empno.ename || ‘-’ ||
b_empno.hiredate || ‘—’ || b_empno.sal);
end loop;
end;
第八题：
alter table emp add COMMENTS varchar(20);
update emp set comments =’Have subordinate’ where job=’MANAGER’;
update emp set comments =’Have NO subordinate’ where job=’CLERK’;
update emp set comments =” where job<>’MANAGER’and job<>’CLERK’;
select comments from emp ;
select * from emp;
end ;
第十题：
declare
e_toosmallsalary exception;
hiredate date;
hd date;
sal number;
cursor v_auth is
select emp.hiredate from emp ;

begin

for v_buth in v_auth
loop
if sysdate-v_buth.hiredate>5 then
update emp set emp.sal=emp.sal+2000;
end if;
if sysdate-v_buth.hiredate>3 and sysdate-v_buth.hiredate< 5 then
update emp set emp.sal=emp.sal+1000;
end if ;
if sysdate-v_buth.hiredate<3 then
raise e_toosmallsalary;
dbms_output.put_line(‘The employee is out the range of adding salary’);
end if ;
end loop;
end;