OraFAQ Forum: Training & Certification » convert a plsql code into 1 sql statement

Home » Other » Training & Certification » convert a plsql code into 1 sql statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

convert a plsql code into 1 sql statement [message #309770]

Fri, 28 March 2008 14:04

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.

Report message to a moderator

Re: convert a plsql code into 1 sql statement [message #309773 is a reply to message #309770]

Fri, 28 March 2008 14:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: convert a plsql code into 1 sql statement [message #309778 is a reply to message #309770]

Fri, 28 March 2008 14:36

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

Report message to a moderator