محتاجه مساعده ضروريه

[شبيهة البدر]

السلام عليكم...اخواني اعضاء هذا المنتدى الرائع اضع بين ايديكم هذا الاسايمنت وانا مستقبلي بين ايديكم لانه اذا لم احل هذا لاسايمنت سوف لن اجاز في دبلوم الاوراكل وارجو منكم المساعده لاني لم اعرف حل لهذه المعضله ولن انسى لكم هذا الصنيع ..
اختكم:شبيهة البدر....
(ملاحظه اخر يوم للتسليم هو يوم الاربعاء)


1.1 Objective:
The key objective of the assignment is to understand and construct PL/SQL blocks. It uses SELECT statement in PL/SQL block to interact with the Oracle Server. It also uses the creation and use of cursors and :

1.2: Basic Assignment Task#2
1.2.1 Deliverables:
You are expected to perform the following tasks and submit a report containing appropriate commands/statements in PL/SQL:


1.2.1.1 Write a PL/SQL block to print information about a given country in the HR schema.
a) Declare a PL/SQL record based on the structure of COUNTRIES table.
Use DEFINE command to provide the country ID. Pass the value to PL/SQL block through a substitution variable.
c) Use DBMS_OUTPUT.PUT_LINE to print selected information about the country.


1.2.1.2
a) Create a table topsals with the following structure:
Field No. Field Name Field Type
1. salary NUMBER(8,2)
Create a PL/SQL procedure block using cursor that determines the top employees with respect to salaries. Accept a number n from the user where n represents the top n earners from the EMPLOYEES table. For example, to view the top 3 earners, enter 3. (Hint: Use DEFINE command to provide the value for n. Pass the value to the PL/SQL block through a substitution variable.
c) In a loop use the substitution parameter created in 1.2.1.2 ( above and gather the salaries of the top n people form the EMPLOYEES table. There should be no duplication in the salaries. If two employees earn the same salary, the salary should be picked only once.
d) Store the salaries in topsals table.
e) Test the block for a variety of special cases, such as n=0 or where n is greater than the number of employees in the EMPLOYEES table. Empty topsals table after each test.


1.2.1.3 Write a PL/SQL block to select the name of the employee with a given salary value. Create a table MESSAGES with the following structure:
Field No. Field Name Field Type
1. results varchar2(40)
a) Use the DEFINE command to provide the salary.
Pass the value to the PL/SQL block through a substitution variable. If the salary entered returns more than one row, handle the exception with an appropriate exception handler and insert into MESSAGES table the message “More than one employee with a salary of <salary>”
c) If the salary entered returns only one row, insert into MESSAGES table the employee’s name and salary amount.
d) Handle any other exception with an appropriate exception handler and insert it into the MESSAGES table with the message “Some other error occurred”
e) Test the block for a variety of test cases. Display the rows from the MESSAGES table to check whether the PL/SQL block has executed successfully. [/si

[giga]

بسيطه ان شاء الله

[Afeef]

	



الحل بسيط وهذه الحلول للسبعة السئلة الأولى وذلك لضيق الوقت وايضاً ظهري يؤلمني قليلاً:

The key objective of the assignment is to understand and 

construct PL/SQL blocks. It uses SELECT statement in 

PL/SQL block to interact with the Oracle Server. It also 

uses the creation and use of cursors and : 

1.2: Basic Assignment Task#2 
1.2.1 Deliverables: 
You are expected to perform the following tasks and 

submit a report containing appropriate 

commands/statements in PL/SQL

------------------------------------------------------

Write a PL/SQL block to print information about a given 

country in the HR schema????

بما أنني ليس لدي جدول الدول فسوف أحاول تطبيق الأساينمت على جدول الموظفين 
EMP

----------------
SQL> declare 
 2  name varchar2(10);
 3  begin
 4  select ename into name from emp 
 5						  where empno=7876;
 6  dbms_output.put_line(name);
 7  end;
 8  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
ADAMS

PL/SQL procedure successfully completed.

SQL> 
-----------------------
2-
Declare a PL/SQL record based on the structure of 

COUNTRIES table. 
Use DEFINE command to provide the country ID. Pass the 

value to PL/SQL block through a substitution variable

----------------------
Answer:
SQL>   define a=7876;
SQL>   declare 
 2		   type emp_record_type is RECORD
 3   (
 4   ename emp.ename%type,
 5   salary emp.sal%type,
 6   comm number
 7   );
 8   my_record emp_record_type;
 9   begin
10   select ename,sal,comm into my_record 
11							 from emp		where 

empno=&a;
12  dbms_output.put_line('The Employee Info is : 

'||my_record.ename||' and has '||
13													  

		my_record.salary);
14  end;
15  /
old  11:							from emp		

where empno=&a;
new  11:							from emp		

where empno=7876;
The Employee Info is : ADAMS and has 1100

PL/SQL procedure successfully completed.

SQL> 
_________________________________________
Q:c) Use DBMS_OUTPUT.PUT_LINE to print selected 

information about the country.

Answer:
SQL>  declare 
 2   ename varchar2(20);
 3  salary number;
 4   begin
 5			select ename,sal into ename,salary from 

emp where empno =7876;
 6   dbms_output.put_line('The name is '||ename||' and 

has the salary '||salary);
 7   end;
 8   /
The name is ADAMS and has the salary 1100

PL/SQL procedure successfully completed.

SQL> 
-----------------------------------------------
Q
a) Create a table topsals with the following structure: 
Field No. Field Name Field Type 
1. salary NUMBER(8,2) 
Create a PL/SQL procedure block using cursor that 

determines the top employees with respect to salaries. 

Accept a number n from the user where n represents the 

top n earners from the EMPLOYEES table. For example, to 

view the top 3 earners, enter 3. (Hint: Use DEFINE 

command to provide the value for n. Pass the value to 

the PL/SQL block through a substitution variable. 

Answer:
SQL>  Create table topsals (
 2		   No number,
 3		   Name varchar2(30),
 4  Salary number(8,2)
 5  );

Table created.

SQL>  declare
 2	no number;
 3   name varchar2(20);
 4   cursor my_cursor is  select rownum as rank,ename 

into no,name from 
 5  (select ename  from emp order by sal desc)
 6   where rownum<=&&Ente;
 7   begin 
 8   for my_val in my_cursor loop
 9  dbms_output.put_line('The '||&Ente||' Employee is 

'||my_val.ename);
10   end loop;
11   end;
12   /
Enter value for ente: 3
old   6:  where rownum<=&&Ente;
new   6:  where rownum<=3;
old   9: dbms_output.put_line('The '||&Ente||' Employee 

is '||my_val.ename);
new   9: dbms_output.put_line('The '||3||' Employee is 

'||my_val.ename);
The 3 Employee is KING
The 3 Employee is SCOTT
The 3 Employee is FORD

PL/SQL procedure successfully completed.

SQL> 
------------------------------------------------
Q
c) In a loop use the substitution parameter created in 

1.2.1.2 ( above and gather the salaries of the top n 

people form the EMPLOYEES table. There should be no 

duplication in the salaries. If two employees earn the 

same salary, the salary should be picked only onc-----

Answer:
SQL>   select rownum as rank,ename from 
 2   (select distinct(ename)  from emp )
 3   where rownum<=3;

  RANK ENAME
---------- ----------
	 1 ADAMS
	 2 ALLEN
	 3 BLAKE

SQL> 
--------------------------
Q:
Store the salaries in topsals table?

Answer:
SQL>  declare
 2	no number;
 3  sa  number;
 4   cursor my_cursor is  select rownum as rank,sal 

into no,sa from
 5  (select sal from emp order by sal desc)
 6   where rownum<=&&Ente;
 7   begin
 8  for my_val in my_cursor loop
 9   insert into topsals (salary)
10   Values(my_val.sal);
11  end loop;
12  end;
13  /
old   6:  where rownum<=&&Ente;
new   6:  where rownum<=3;

PL/SQL procedure successfully completed.

SQL> select * from topsals;

	NO NAME							   SALARY
---------- ------------------------------ ----------
											5000
											3500
											3000

SQL>
------------------------------------

تم تعديل 12 ديسمبر 2007 بواسطة Afeef