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

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

صورة
- - - - -

سؤال في ال Pl/sql


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

#1 mr_khaledali

mr_khaledali

    عضو

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

تاريخ المشاركة 18 January 2008 - 05:29 PM

سؤالين موجودين في ليسون 7 ( cursor) يعني كل ماحل السؤال الاول البرنامج دايما يهنك مني مش عارف الجواب غلط ولا هوا البرنامج قلبه ضعيف مساعدتكو معايا يااخوانا يعني سؤال بسؤاله مافيش اي مشاكل


Write a PL/SQL block, which declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table for a department whose department_id is less than 100. Pass the department number to another cursor as a parameter to retrieve from the employees table the details of employee last name, job, hire date, and salary of those employees whose employee_id is less than 120 and who work in that department.
In the declarative section declare a cursor dept_cursor to retrieve department_id, department_name for those departments with department_id less than 100. Order by department_id.
Declare another cursor emp_cursor that takes the department number as parameter and retrieves last_name, job_id, hire_date, and salary of those employees with employee_id of less than 120 and who work in that department.
Declare variables to hold the values retrieved from each cursor. Use the %TYPE attribute while declaring variables.
Open the dept_cursor, use a simple loop and fetch values into the variables declared. Display the department number and department name.
For each department, open the emp_cursor by passing the current department number as a parameter. Start another loop and fetch the values of emp_cursor into variables and print all the details retrieved from the employees table. Note: You may want to print a line after you have displayed the details of each department. Use appropriate attributes for the exit condition. Also check if a cursor is already open before opening the cursor.
Close all the loops and cursors, and end the executable section. Execute the script.
------------------------------------ السوال التاني
Load the script lab_06_04_soln.sql.
Look for the comment “DECLARE A CURSOR CALLED emp_records TO HOLD salary, first_name, and last_name of employees” and include the declaration. Create the cursor such that it retrieves the salary, first_name, and last_name of employees in the department specified by the user (substitution variable emp_deptid). Use the FOR UPDATE clause.
Look for the comment “INCLUDE EXECUTABLE SECTION OF INNER BLOCK HERE” and start the executable block.
Only employees working in the departments with department_id 20, 60, 80,100, and 110 are eligible for raises this quarter. Check if the user has entered any of these department IDs. If the value does not match, display the message “SORRY, NO SALARY REVISIONS FOR EMPLOYEES IN THIS DEPARTMENT.” If the value matches, then, open the cursor emp_records.
Start a simple loop and fetch the values into emp_sal, emp_fname, and emp_lname. Use %NOTFOUND for the exit condition.
Include a CASE expression. Use the following table as reference for the conditions in the WHEN clause of the CASE expression. Note: In your CASE expression use the constants such as c_range1, c_hike1 which are already declared.
For example, if the salary of the employee is less than 6500, then increase the salary by 20 percent. In every WHEN clause, concatenate the first_name and last_name of the employee and store it in the INDEX BY table. Increment the value in variable i so that you can store the string in the next location. Include an UPDATE statement with the WHERE CURRENT OF clause.
Close the loop. Use the %ROWCOUNT attribute and print the number of records that were modified. Close the cursor.
Include a simple loop to print the names of all the employees whose salaries were revised. Note: You already have the names of these employees in the INDEX BY table. Look for the comment “CLOSE THE INNER BLOCK” and include an END IF statement and an END statement.
Save your script as lab_07_04_soln.sql.


ORACLE CERTIFIED PROFESSIONAL
ORACLE APPLICATION DEVELOPER

#2 mr_khaledali

mr_khaledali

    عضو

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

تاريخ المشاركة 20 January 2008 - 02:08 AM

ها وضح اكتر بالنسبة للسؤال الاول هوا بينفذ الكرسور الاولانية وبيطلعلي نتيجتها بس الكرسور التانية مش مطلع نتيجتها يعني

الكرسور التانية مافهمش ال dept id as aparemter من الكرسور الاولى ازاي الكرسور التانية تفهم
ORACLE CERTIFIED PROFESSIONAL
ORACLE APPLICATION DEVELOPER