Home » Other » Training & Certification » convert a plsql code into 1 sql statement
convert a plsql code into 1 sql statement [message #309770] Fri, 28 March 2008 14:04 Go to next message
kanjiri123
Messages: 3
Registered: March 2008
Junior Member
i have a plsql stored proc as below and i want to convert that proc into 1 sql statement:
CREATE OR REPLACE PROCEDURE emp_bonus IS
SALES NUMBER(8,2) := 12100;
QUOTA NUMBER(8,2) := 10000;
bonus NUMBER(6,2);
emp_id NUMBER(6) := 120;
BEGIN
IF SALES > (QUOTA + 200) THEN
bonus := (SALES - QUOTA)/4;
ELSE
bonus := 50;
END IF;
UPDATE EMPLOYEES SET salary = salary + bonus WHERE employee_id = emp_id;
END;

Can someone help me this?
thanks in Advance.
Re: convert a plsql code into 1 sql statement [message #309773 is a reply to message #309770] Fri, 28 March 2008 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems a homework.
Post what you already tried.

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
Re: convert a plsql code into 1 sql statement [message #309778 is a reply to message #309770] Fri, 28 March 2008 14:36 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
This is very simple.

CLUES:
Try to learn about DECODE, SIGN Functions in Oracle.

Give it a try after reading about these functions.

Let me know if you need more help regarding this.

Regards,
Joice
problem with converting loop in plsql stores proc in to a sql statement [message #309803 is a reply to message #309770] Fri, 28 March 2008 21:25 Go to previous messageGo to next message
kanjiri123
Messages: 3
Registered: March 2008
Junior Member
i want to convert a loop logic in plsql stored proc into a sql statement. how can achieve this?
Thanks in advance.
Re: problem with converting loop in plsql stores proc in to a sql statement [message #309806 is a reply to message #309803] Fri, 28 March 2008 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Read & follow posting guidelines which include do NOT cross/multipost!
Re: problem with converting loop in plsql stores proc in to a sql statement [message #309829 is a reply to message #309803] Sat, 29 March 2008 01:43 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to convert a loop logic in plsql stored proc into a sql statement. how can achieve this?

It depends on your code.

Regards
Michel
Previous Topic: OCA & OCP Details
Next Topic: RMAN
Goto Forum:
  


Current Time: Wed Dec 07 05:08:05 CST 2016

Total time taken to generate the page: 0.06120 seconds