إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

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


2 رد (ردود) على هذا الموضوع

#1 شبيهة البدر

شبيهة البدر

    عضو

  • الأعضــاء
  • 2 مشاركة

تاريخ المشاركة 10 December 2007 - 08:50 AM

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


1.1[size="4"] 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


#2 giga

giga

    عضو

  • الأعضــاء
  • 43 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 10 December 2007 - 08:31 PM

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

#3 Afeef

Afeef

    عضو

  • الأعضــاء
  • 2 مشاركة

تاريخ المشاركة 13 December 2007 - 01:09 AM








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



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>

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

تم التعديل بواسطة Afeef, 13 December 2007 - 01:12 AM.