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 Go to next message
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

Re: PL/SQL help, I need to write tigger, function, procedure [message #319428 is a reply to message #319427] Sat, 10 May 2008 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Which of the posting guidelines as stated above did you follow?

Did you notice the Homework sub-forum near the bottom of the main page?

>PL/SQL help, I need to write tigger, function, procedure
So, please proceed to do what you need to do.

[Updated on: Sat, 10 May 2008 23:27] by Moderator

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 Go to previous messageGo to next message
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 #319431 is a reply to message #319427] Sun, 11 May 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We will be glad to help you solve these exercise as soon as you'll post what you already tried.
But before, you have to read the link posted by Ana to how to format your post.

Regards
Michel
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 Go to previous messageGo to next message
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
/
Re: PL/SQL help, I need to write tigger, function, procedure [message #319476 is a reply to message #319475] Sun, 11 May 2008 16:00 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Have a look at RPAD function and dbms_output package (for instance).


Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: OracleSQL - Exam Revision help
Next Topic: how can i catch prime numbers with While Loop ?
Goto Forum:
  


Current Time: Thu Mar 28 13:11:49 CDT 2024