Home » Other » Training & Certification » PL/SQL help, I need to write tigger, function, procedure
| PL/SQL help, I need to write tigger, function, procedure [message #319427] |
Sat, 10 May 2008 23:18  |
sqluser87
Messages: 3
Registered: May 2008
|
Junior Member |
|
|
Create table EMP(EmpID Integer Not Null Primary key,
Name Char (10) Not Null,
Salary Number (10, 2) Not Null);
-------------------------------
Function Stars that takes N (an Integer smaller than or equal to 50) as input and
returns N stars in a character string to the calling routine.
Trigger Check_Raise that runs after an employee is given a raise. The trigger
checks the new salary and makes sure that it is more than the old salary. If the
new salary is less than or equal to the old salary, it will raise an application error
and exits. If the raise is a valid raise, it calls the stored procedure (Print_Stars)
and sends the employee name, new and the old salaries to the procedure.
Stored procedure Print_Stars calculates the difference between the old and the
new salaries (rounding it up if necessary) and then prints employee’s name on
the first line and as many stars as the difference in salaries, up to 50 starts per
line, calling the function stars to prepare each stars line.
For example, if Sam’s raise is $115 the output is:
Sam’s raise is:
**************************************************
**************************************************
***************
[Updated on: Sat, 10 May 2008 23:20] Report message to a moderator |
|
|
|
|
|
| PL/SQL help, I need to write tigger, function, procedure [message #319429 is a reply to message #319427] |
Sat, 10 May 2008 23:29  |
sqluser87
Messages: 3
Registered: May 2008
|
Junior Member |
|
|
Need following assignment in PLSQL
Create table EMP(EmpID Integer Not Null Primary key,
Name Char (10) Not Null,
Salary Number (10, 2) Not Null);
Function Stars that takes N (an Integer smaller than or equal to 50) as input and
returns N stars in a character string to the calling routine.
Trigger Check_Raise that runs after an employee is given a raise. The trigger
checks the new salary and makes sure that it is more than the old salary. If the
new salary is less than or equal to the old salary, it will raise an application error
and exits. If the raise is a valid raise, it calls the stored procedure (Print_Stars)
and sends the employee name, new and the old salaries to the procedure.
Stored procedure Print_Stars calculates the difference between the old and the
new salaries (rounding it up if necessary) and then prints employee’s name on
the first line and as many stars as the difference in salaries, up to 50 starts per
line, calling the function stars to prepare each stars line.
For example, if Sam’s raise is $115 the output is:
Sam’s raise is:
**************************************************
**************************************************
***************
|
|
|
|
|
|
| Re: PL/SQL help, I need to write tigger, function, procedure [message #319475 is a reply to message #319431] |
Sun, 11 May 2008 15:46 |
sqluser87
Messages: 3
Registered: May 2008
|
Junior Member |
|
|
I can create table, insert, update, delete and know syntax for trigger,function and procedure.
Im new to programming stuff, Im unable to get the logic, and how to display (*)s instead of numbers. I would be really thankful to you guys...Please
Create table EMP(EmpID Integer Not Null Primary key,
Name Char (10) Not Null,
Salary Number (10, 2) Not Null);
--Function stars
CREATE OR REPLACE FUNCTION stars(v_number n)
RETURN NUMBER
IS
BEGIN
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN
END stars
/
--Trigger check-raise
CREATE OR REPLACE Trigger check_Raise()
on insert, update,
RETURN NUMBER
IS
OLD_SAL EMP.SAL%TYPE;
BEGIN
END check_raise
/
--Procedure Print Star
CREATE OR REPLACE procedure print_stars
BEGIN
END print_stars
/
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 09:37:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
