Home » SQL & PL/SQL » SQL & PL/SQL » NEWB TO PL/SQL
NEWB TO PL/SQL [message #389157] Fri, 27 February 2009 07:51 Go to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
Hello

i have a tiny bit of home work to be done for my database class.. i havnt been in for 2 weeks and im really stuck..

can some one help



a) Write a pl/sql block to declare a variable called sal to store the salary of an employee

b)in the executable part of the program , do the following


    -store an employee name in an SQL*Plus Subsitution variable
    -store his or her salary in the sal variable
    -if the salary is less than 3000, give an employee a raise of 500 and display the message "<Employee Name>'s salary updated" in the output
    -if the salary is more than 3000 , print the employee's salary in the format "<Employee Name> earns..."
    -Test for the last name pataballa
    - undefine the variable at the end of the script

    if anyone could help it would be a great help

Re: NEWB TO PL/SQL [message #389160 is a reply to message #389157] Fri, 27 February 2009 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first post what you tried and we will guide you in the next steps.

Regards
Michel
Re: NEWB TO PL/SQL [message #389164 is a reply to message #389157] Fri, 27 February 2009 08:07 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
hi michel

i am a complete newb.... havnt been to class yet... i know its my own fault...i think once i get started i will be able to understand the syntax alot better. i am a natural programmer using languages such as java and c#. its just this database sql had got me by the neck. i understand any code that you are give me wouold be for my education and not just copying it. a A begining would heelp me ask you guys questions about the cpde.

if you could help me i would greatly appreciate it

stephen
Re: NEWB TO PL/SQL [message #389167 is a reply to message #389164] Fri, 27 February 2009 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Don't use IM speak, it is not professional.

RegardS
Michel
Re: NEWB TO PL/SQL [message #389170 is a reply to message #389157] Fri, 27 February 2009 08:32 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
Hello michel

Yup thank you for the guides.

i will try code an example myself.

stephen
Re: NEWB TO PL/SQL [message #389174 is a reply to message #389157] Fri, 27 February 2009 08:53 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
a) Write a pl/sql block to declare a variable called sal to store the salary of an employee

ok so i have done this i think


DECLARE

sal Number;

BEGIN

name varChar(50);
---- -store an employee name in an SQL*Plus Subsitution variable
--- I will need some sort of loop that will go through the database of employees. i need some help on the syntax here.??

--- sal = employee.Salary
---- i know this not correct but how would i get the salary figure for an employee.

any help would be great
Re: NEWB TO PL/SQL [message #389177 is a reply to message #389157] Fri, 27 February 2009 09:02 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
i forgot to put in the subsitution variable

that would be

DECLARE

sal Number;

BEGIN

employeeName varChar(50) := &employeeName;
--- now i need to store this employee salary in the sal variaable

--how would i search the database for this employee..would it be this??

SELECT salary INTO sal FROM EMPLOYEES
WHERE employeeName = name;
Re: NEWB TO PL/SQL [message #389178 is a reply to message #389174] Fri, 27 February 2009 09:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
molleman wrote on Fri, 27 February 2009 15:53


DECLARE

 sal Number;

BEGIN
 
 name varChar(50);
 ---- -store an employee name in an SQL*Plus Subsitution variable
 ---  I will need some sort of loop that will go through the database of employees. i need some help on the syntax here.??

--- sal = employee.Salary
---- i know this not correct but how would i get the salary figure for an employee.


any help would be great


First: when posting code, enclose them in [code] and [/code] tags. It improves readability enormously.

You declared a new variable "name" in the execution part of the PL/SQL block. That should go into the declaration part.
Furthermore, use varchar2, not varchar. Varchar is not used; it is only there (I think) for compatibility reasons.

Ok. Step 2: design the query. Just a SQL query first, then try to put it in your pl/sql block.
Re: NEWB TO PL/SQL [message #389180 is a reply to message #389177] Fri, 27 February 2009 09:15 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Kudos to you for actually going and having a quick look at the links and trying to build it. Many others would have started complaining and demanding a full code solution. Believe me, your approach will get you plenty of helpful advice. Others should take heed.
Good luck
Re: NEWB TO PL/SQL [message #389182 is a reply to message #389157] Fri, 27 February 2009 09:19 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
Thank you very much for the help. i have tried to write this piece of code here i do not know if its correct but i think i am getting some where

SET SERVEROUTPUT ON
DECLARE

 sal Number
 employeeName varChar2(50) := &employeeName;

BEGIN

 SELECT salary INTO sal FROM EMPLOYEES
 WHERE employeeName = name;
 

 IF sal < 3000 THEN
	UPDATE employees SET salary = salary + 500 WHERE employeeName = name;
 	DBMS_OUTPUT.PUT_LINE (||employeeName||' salary has been updated' );
 ELSIF sal >= 3000 THEN
	DBMS_OUTPUT.PUT_LINE (||employeeName||' earns '||sal|| );

 END IF;
// undefine 
  variables. i do not understand this

END;
/


so if any of you guys can tell me errors i would greatly appreciate it.

[Updated on: Fri, 27 February 2009 09:21]

Report message to a moderator

Re: NEWB TO PL/SQL [message #389185 is a reply to message #389182] Fri, 27 February 2009 09:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You need apostrophes around your substitution variable:
:= '&employeeName';
I would flip the predicate:

employeeName = name
becomes name = employeeName
assuming that name is the name of a column in the table.

Getting there
Re: NEWB TO PL/SQL [message #389188 is a reply to message #389185] Fri, 27 February 2009 09:43 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
Thank you for the help i have updated the code.

SET SERVEROUTPUT ON
DECLARE

 sal Number
 employeeName varChar2(50) := '&employeeName';

BEGIN

 SELECT salary INTO sal FROM EMPLOYEES
 WHERE name = employeeName ;
 

 IF sal < 3000 THEN
	UPDATE employees SET salary = salary + 500 WHERE name = employeeName;
 	DBMS_OUTPUT.PUT_LINE (||employeeName||' salary has been updated' );
 ELSIF sal >= 3000 THEN
	DBMS_OUTPUT.PUT_LINE (||employeeName||' earns '||sal|| );

 END IF;
// undefine 
  variables. i do not understand this

END;
/
END;
/


Could someone help with undefining variable at the end please. i do not understand what i would be doing here. It is in the spec for my project.

Re: NEWB TO PL/SQL [message #389189 is a reply to message #389157] Fri, 27 February 2009 09:50 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
The best way to check for errors is to run the code.
If you don't have oracle on your PC I suggest you install it.

Undefine, like define, is a sqlplus command. So I'd recommend looking at the SQL*Plus User's Guide and Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm
Re: NEWB TO PL/SQL [message #389190 is a reply to message #389188] Fri, 27 February 2009 09:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
That one should be pretty easy to find in the searchable documents.
Re: NEWB TO PL/SQL [message #389192 is a reply to message #389188] Fri, 27 February 2009 09:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
molleman wrote on Fri, 27 February 2009 16:43

 	DBMS_OUTPUT.PUT_LINE (||employeeName||' salary has been updated' );




If you would install Oracle (XE is free and not too big of a footprint), you'd see that this is invalid.
Hint: || is used to concatenate two strings.
Re: NEWB TO PL/SQL [message #389193 is a reply to message #389192] Fri, 27 February 2009 10:06 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
DBMS_OUTPUT.PUT_LINE ('The Employee '||employeeName||' salary has been updated' );


Now if i was to input this it should work. yes?

[Updated on: Fri, 27 February 2009 10:07]

Report message to a moderator

Re: NEWB TO PL/SQL [message #389195 is a reply to message #389157] Fri, 27 February 2009 10:10 Go to previous messageGo to next message
molleman
Messages: 9
Registered: February 2009
Junior Member
UNDEFINE
	
 employeeName


this is the code i put in for undefining the subsitution variable employeeName. i do not know if the syntax is correct.
Re: NEWB TO PL/SQL [message #389203 is a reply to message #389193] Fri, 27 February 2009 10:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
molleman wrote on Fri, 27 February 2009 17:06
DBMS_OUTPUT.PUT_LINE ('The Employee '||employeeName||' salary has been updated' );


Now if i was to input this it should work. yes?

That looks better!
Making great progress, keep it up.
Previous Topic: DAY and DY functions
Next Topic: Insert/Update with cyclic sequence ID (merged 2)
Goto Forum:
  


Current Time: Tue Dec 06 06:14:20 CST 2016

Total time taken to generate the page: 0.06618 seconds