Solved

T-SQL Convert to PL/SQL

Posted on 2016-10-26
23
61 Views
Last Modified: 2016-11-03
I have a need to convert a T-SQL stored procedure script from MS SQL to PL/SQL.  I tried using the following website, but isn't correct.

http://www.sqlines.com/online

SET NOCOUNT ON;

DECLARE @ORDER_ID VARCHAR(30), @SERVICE_ID VARCHAR(15), @TASK VARCHAR(255), @HOURS DECIMAL(15,4), @LINE_NO INT

--temporary table from excel read into this table
DECLARE @EXCEL_TEMP TABLE(
		CUSTOMER_ID VARCHAR(30),
		ORDER_ID VARCHAR(80),
		SERVICE_ID VARCHAR(30),
		TASK VARCHAR(255),
		HOURS DECIMAL(15,4)
		);
INSERT INTO @EXCEL_TEMP(CUSTOMER_ID, ORDER_ID, SERVICE_ID, TASK, HOURS)
VALUES('CUST1', '1035', 'ENG', 'ENG DOC', 8.50),
('CUST1', '1035', 'ENG', 'ENG DOC', 18.50),
('CUST1', '1035', 'ENG_ELEC', 'ELECTRICAL', 5.00),
('CUST1', '1035', 'ENG_ELEC', 'ELECTRICAL', 2.00),
('CUST1', '1035', 'ENG_TEST', 'TESTING', 7.00),
('CUST2', '0960', 'PROG_MGT', 'MANAGEMENT', 3.50),
('CUST2', '0960', 'PROG_MGT', 'MANAGEMENT', 18.50),
('CUST2', '0960', 'QM', 'QUALITY MANAGEMENT HOURS', 7.00),
('CUST2', '0960', 'SR ENG', 'SOFTWARE INSTALL', 3.00),
('CUST2', '0960', 'SR ENG', 'SOFTWARE INSTALL', 5.00);


--will be reading from actual table
DECLARE	@CUSTOMER_ORDER TABLE(
		ID VARCHAR(80),
		CUSTOMER_ID VARCHAR(30)
		);
INSERT INTO @CUSTOMER_ORDER(ID, CUSTOMER_ID)
VALUES('1035', 'CUST1'),
('0960', 'CUST2')

--will be reading from actual table
DECLARE @CUST_ORDER_LINE TABLE(
		CUST_ORDER_ID VARCHAR(30),
		LINE_NO INT,
		SERVICE_CHARGE_ID VARCHAR(30),
		QTY DECIMAL(15,4)
		);
INSERT INTO @CUST_ORDER_LINE(CUST_ORDER_ID, LINE_NO, SERVICE_CHARGE_ID, QTY)
VALUES('1035', 1, 'ENG', 1),
('0960', 1, 'PROG_MGT', 1),
('0960', 2, 'QM', 1)



DECLARE LABOR_CURSOR CURSOR FOR
SELECT	T1.ORDER_ID, T1.SERVICE_ID, T1.TASK, T1.HOURS, (ROW_NUMBER() OVER(PARTITION BY T1.ORDER_ID ORDER BY T1.ORDER_ID) + X.LINE_NO) - X.LINE_NO LINE_NO
FROM	(
		SELECT	T.ORDER_ID, T.SERVICE_ID, T.TASK, SUM(T.HOURS) HOURS
		FROM	@EXCEL_TEMP T
		GROUP BY T.ORDER_ID, T.SERVICE_ID, T.TASK
		) T1

		LEFT OUTER JOIN
		(
		SELECT COL.CUST_ORDER_ID, MAX(COL.LINE_NO) LINE_NO FROM @CUST_ORDER_LINE COL
		GROUP BY COL.CUST_ORDER_ID
		) X ON X.CUST_ORDER_ID = T1.ORDER_ID


OPEN LABOR_CURSOR

FETCH NEXT FROM LABOR_CURSOR INTO @ORDER_ID, @SERVICE_ID, @TASK, @HOURS, @LINE_NO
WHILE (@@FETCH_STATUS = 0)
BEGIN


IF EXISTS(SELECT 1 FROM @CUSTOMER_ORDER CO WHERE CO.ID = @ORDER_ID)
	BEGIN
		IF NOT EXISTS(SELECT 1 FROM @CUST_ORDER_LINE COL WHERE COL.CUST_ORDER_ID = @ORDER_ID AND COL.SERVICE_CHARGE_ID = @SERVICE_ID AND COL.LINE_NO = @LINE_NO)
			BEGIN
				INSERT INTO CUST_ORDER_LINE(CUST_ORDER_ID, LINE_NO, SERVICE_CHARGE_ID, QTY)
				VALUES(@ORDER_ID, @LINE_NO, @SERVICE_ID, @HOURS)
			END
	END


IF EXISTS(SELECT 1 FROM @CUSTOMER_ORDER CO WHERE CO.ID = @ORDER_ID)
	BEGIN
		IF EXISTS(SELECT 1 FROM @CUST_ORDER_LINE COL WHERE COL.CUST_ORDER_ID = @ORDER_ID AND COL.SERVICE_CHARGE_ID = @SERVICE_ID AND COL.LINE_NO = @LINE_NO)
			BEGIN 
				UPDATE CUST_ORDER_LINE
				SET	QTY = @HOURS
				WHERE	ORDER_ID = @ORDER_ID AND SERVICE_ID = @SERVICE_ID AND LINE_NO = @LINE_NO	
			END
	END

FETCH NEXT FROM LABOR_CURSOR INTO @ORDER_ID, @SERVICE_ID, @TASK, @HOURS, @LINE_NO
END

CLOSE LABOR_CURSOR
DEALLOCATE LABOR_CURSOR

Open in new window

0
Comment
Question by:holemania
  • 12
  • 10
23 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41860932
Oracle doesn't really do temp tables the way SQL Server does.

Do you need them or is that just for show?

Also, porting tends to lead to horrible Oracle code since the two systems do things differently.  If you do a straight port, you might do things considered bad in Oracle.


The INSERT/UPDATE code can be done in a single SQL statement:  MERGE.
http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

If you can post more of straight sample data and expected results we can probably get you the Oracle way to do things.
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41860941
I've tried SqlLines. It misses a lot more often than it hits. It's mostly worthless. I've had much better success with Microsoft's SQL Server Migration Assistant. It's not perfect but it does a pretty good job... and it's FREE!

https://www.microsoft.com/en-us/download/details.aspx?id=43689
0
 

Author Comment

by:holemania
ID: 41861106
The temporary table is just for show.  Eventually that will be replaced with actual database tables.  So for the three temporary tables, ignore that for now and treat them as actual oracle tables.  However, I need to query the first table "EXCEL_TEMP" and put into a variable to loop.
0
 

Author Comment

by:holemania
ID: 41861158
So this is my three tables.  The table CUSTOMER_ORDER and CUST_ORDER_LINE are standard tables and they do have a lot more fields, but the fields that I am working with are in the diagram below.  The table EXCEL_TEMP will be another table that we will importing data from an excel file into that table using a windows tool we create so that user can select the excel file they want to import data into that table.

Raw Data
The final output would be in the CUST_ORDER_LINE.  If the line does not exists, create a new line in the CUST_ORDER_LINE table and auto increment the line number.  If a record exists in CUST_ORDER_LINE table, then update the QTY field with the labor hours from the EXCEL_TEMP table.

Final result would be as the below diagram.  Thus I had created 3 temporary table to illustrate.
Result
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41861364
Try the code below.

I slightly modified the ROW_NUMBER from what you provided in the original post because I didn't understand the need for all the "+ X.LINE_NO) - X.LINE_NO" stuff.

Feel free to change it back if you want.

merge into cust_order_line col
using(
	select 
		order_id,
		row_number() over(partition by customer_id
				order by order_id,service_id) line_no,
		service_id,
	sum(hours) hours
	from excel_temp
	group by customer_id,order_id,service_id,task
) et
on (col.order_id=et.order_id and col.line=et.line_no and col.service_id=et.service_id)
when matched then
	update set col.qty=et.hours
when not matched then
	insert(col.order_id,col.line,col.service_id,col.qty)
	values(et.order_id,et.line_no,et.service_id,et.hours)
/

Open in new window

0
 

Author Comment

by:holemania
ID: 41861506
Thank you.  I will take a look tomorrow and update.

The row_number(), it is looking at the cust_order_line for the max line_no and then append from there.  So as you can see in the example, the order_id 0960 already have 2 lines in the cust_order_line table.  It then take that and increment it by 1 making it line_no 3.

I am not familiar with PL/SQL since that got dumped on me with a site that is on oracle and we currently use MS SQL.  So just a quick question, does the query you provide look at the max line_no and then increment from there?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41861510
>>I am not familiar with PL/SQL

Since it is new, a terminology lesson:  Pl/SQL is Oracle's procedural programming language.  What I posted was just good old SQL.   No procedural code necessary.

>>So as you can see in the example, the order_id 0960 already have 2 lines in the cust_order_line table

Just as long as it is consistent in the calculation 100% of the time.

>> It then take that and increment it by 1 making it line_no 3.

Shouldn't be necessary.
0
 

Author Comment

by:holemania
ID: 41863795
Sorry I didn't get a chance to review this yesterday, but will review today and post back.  I do have a question though since this is just straight SQL, I can use this on a Oracle database without issue correct?

Also another question with the sql query, I also need to check to ensure that the sales order also exists before I do any update or insert "CUSTOMER_ORDER".  

I'm not familiar with the "MERGE INTO", how can I also incorporate that?  If you see my original query,  I checked "IF EXISTS(SELECT 1 FROM @CUSTOMER_ORDER".  The "CUSTOMER_ORDER" is the header and that need to exists before I start update/inserting the line detail to the sales order.  Otherwise, it would skip updating/inserting for that sales order.
0
 

Author Comment

by:holemania
ID: 41863804
Also I know from what you posted, it can be just a straight query from my understanding with your example.  How hard is it to translate the above to use with PL/SQL cursor if I want to do more?  Example, inside the loop, I want to total the total_amt for all the lines and add that to the "CUSTOMER_ORDER" since there's a total_amt field in there that takes the total calculation of all the line detail amt.  There are a few other tables I also need to update inside of that loop as well.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41863806
>>since this is just straight SQL, I can use this on a Oracle database without issue correct?

Yes.  SQL is SQL.

>>I'm not familiar with the "MERGE INTO", how can I also incorporate that?

There is nothing to incorporate.  It is a fully self-contained SQL statement.  If has the "IF" check built into it and decides itself to either INSERT or UPDATE.

>>I also need to check to ensure that the sales order also exists before I do any update or insert "CUSTOMER_ORDER".  

Give me a few minutes and I'll update it.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41863814
>>How hard is it to translate the above to use with PL/SQL cursor if I want to do more?

Still not entirely sure it is necessary.

>>There are a few other tables I also need to update inside of that loop as well.

I can only provide solutions to requirements you provide.  If you have different requirements, you should have provided them.

Since you seem set on a cursor and actual PL/SQL code, below is untested but gives you the general idea :

begin

	for i in (
		SELECT	T1.ORDER_ID, T1.SERVICE_ID, T1.TASK, T1.HOURS, (ROW_NUMBER() OVER(PARTITION BY T1.ORDER_ID ORDER BY T1.ORDER_ID) + X.LINE_NO) - X.LINE_NO LINE_NO
		FROM	(
			SELECT	T.ORDER_ID, T.SERVICE_ID, T.TASK, SUM(T.HOURS) HOURS
			FROM	@EXCEL_TEMP T
			GROUP BY T.ORDER_ID, T.SERVICE_ID, T.TASK
			) T1
			LEFT OUTER JOIN
			(
			SELECT COL.CUST_ORDER_ID, MAX(COL.LINE_NO) LINE_NO FROM @CUST_ORDER_LINE COL
			GROUP BY COL.CUST_ORDER_ID
			) X ON X.CUST_ORDER_ID = T1.ORDER_ID
	) loop
	
		--do whatever you want inside this loop
		dbms_output.put_line('Order id: ' || i.order_id);
	end loop;
end;
/

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41863837
Here is the MERGE that joins to the customer_order table to ensure the order exists:
merge into cust_order_line col
using(
	select 
		order_id,
		row_number() over(partition by et.customer_id
				order by order_id,service_id) line_no,
		service_id,
	sum(hours) hours
	from excel_temp et join customer_order co
		on et.customer_id=co.customer_id and et.order_id=co.id
	group by et.customer_id,order_id,service_id,task
) et
on (col.cust_order_id=et.order_id and col.line_no=et.line_no and col.service_charge_id=et.service_id)
when matched then
	update set col.qty=et.hours
when not matched then
	insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty)
	values(et.order_id,et.line_no,et.service_id,et.hours)
/

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41863843
I should also point out that the PL/SQL loop I posted above just copied and pasted your original select.  It isn't valid Oracle syntax.

I'll work on an actual tested one and post it when I get it.

What is the primary key on cust_order_line?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41863886
Here is the pl/sql loop example.

I assumed a primary key on cust_order_line of:  cust_order_id,line_no,service_charge_id.

The primary key makes things easier so I can leverage the exception handler and don't have to keep querying the tables over and over again.

begin
	for i in (
		select 
			order_id,
			row_number() over(partition by et.customer_id
					order by order_id,service_id) line_no,
			service_id,
		sum(hours) hours
		from excel_temp et join customer_order co
			on et.customer_id=co.customer_id and et.order_id=co.id
		group by et.customer_id,order_id,service_id,task
	) loop 
		begin
			insert into cust_order_line(cust_order_id,line_no,service_charge_id,qty)
				values(i.order_id, i.line_no, i.service_id, i.hours);

			exception when dup_val_on_index then
				update cust_order_line set qty=i.hours where 
					cust_order_id=i.order_id and line_no=i.line_no and service_charge_id=i.service_id;
		end;

	end loop;
end;
/

Open in new window

0
 

Author Comment

by:holemania
ID: 41864128
Thanks.  I can test that script out that you just posted.  I figured if I can get it to work for one table for the insert/update, the rest should be similar.  

As far as the primary key in cust_order_line, it is just the order_id and line_no.

I will update after doing some testing.  Thanks again.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41864135
Be sure to test the MERGE option as well...

MERGE is pretty efficient doing what it does and might be much faster than the loop.

Rule #1 is:  Avoid writing code when you do not have to.
0
 

Author Comment

by:holemania
ID: 41867627
slightwv,

I tried both the query and was able to get the PL/SQL with the loop to work.  Only question is that when it's doing either the update/insert, how can I have it check the cust_order_line table first before determining if it's an update or insert?

begin
	for i in (
		select 
			order_id,
			row_number() over(partition by et.customer_id
					order by order_id,service_id) line_no,
			service_id,
		sum(hours) hours
		from excel_temp et join customer_order co
			on et.customer_id=co.customer_id and et.order_id=co.id
		group by et.customer_id,order_id,service_id,task
	) loop 
		begin
			dbms_output.put_line('Insert -  ' || i.order || ', Line No - ' || i.line_no)

			exception when dup_val_on_index then
				dbms_output.put_line('Update  -  ' || i.order || ', Line No - ' || i.line_no)
		end;

	end loop;
end;

Open in new window


Before making the actual insert/update, I want to output to text to ensure I am updating correctly.  In my test, I created a sales order with 1 line in the cust_order_line table.  In my excel_temp table, I have 2 records with one record same as line 1.  It should update the first record since it match what's already in line 1 in the cust_order_line, but record 2 should be an insert.  However, it is not seeing what's already in the cust_order_line table.  What would I need to add so it check the "CUST_ORDER_LINE" table for existing detail records?  I am checking the line for "ORDER_ID" and "SERVICE_ID".  If both exists, then update else insert.




As for the "Merge Into" example, can I do similar as well by doing the output to test before an actual table insert/update?

I did a bit of search and seems I can use the following:

merge into cust_order_line col
using(
	select 
		order_id,
		row_number() over(partition by et.customer_id
				order by order_id,service_id) line_no,
		service_id,
	sum(hours) hours
	from excel_temp et join customer_order co
		on et.customer_id=co.customer_id and et.order_id=co.id
	group by et.customer_id,order_id,service_id,task
) et
on (col.cust_order_id=et.order_id and col.line_no=et.line_no and col.service_charge_id=et.service_id)
when matched then
	update set col.qty=et.hours
when not matched then
	insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty)
	values(et.order_id,et.line_no,et.service_id,et.hours)
OUTPUT $action, update.*, inserted.*

Open in new window


However, does this actually insert/update and then output or will not insert/update as long as I hav ethe "OUTPUT" at the end?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867640
>>how can I have it check the cust_order_line table first before determining if it's an update or insert?

My code did that.  It tried the INSERT first.  If there was already a row there based in the Primary Key, it drops into the exception handler and does the update.

You can't test with DBMS_OUTPUT because the exception handler for update will never fire.

For a dup_val_on_index exception you need to perform actual DML.

>>As for the "Merge Into" example, can I do similar

No need to check first.  The "check" is build in to the "on (col.cust_order_id=et.order_id and col.line_no=et.line_no and col.service_charge_id=et.service_id)"

If all those columns match, then it has to be an UPDATE otherwise it is an INSERT.
0
 

Author Comment

by:holemania
ID: 41868473
Thank you for the clarification.  Also do I need to add commit for both script you provided since this is for oracle?

Example

when matched then
	update set col.qty=et.hours
        commit;
when not matched then
	insert(col.cust_order_id,col.line_no,col.service_charge_id,col.qty)
	values(et.order_id,et.line_no,et.service_id,et.hours)
        commit;

Open in new window




begin
			insert into cust_order_line(cust_order_id,line_no,service_charge_id,qty)
				values(i.order_id, i.line_no, i.service_id, i.hours);
                          commit;
			exception when dup_val_on_index then
				update cust_order_line set qty=i.hours where 
					cust_order_id=i.order_id and line_no=i.line_no and service_charge_id=i.service_id;
                      commit;
		end;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868480
>>do I need to add commit ... since this is for oracle

I don't think this is Oracle specific.

It depends on your development practices and your code.

If this is part of a larger transaction you may not want to commit in that part of the code.  You may want the calling transaction/program to perform the commit.

If you commit in that code is up to you.  I cannot tell you when or where to commit.
0
 

Author Comment

by:holemania
ID: 41868798
One last question in regard to the oracle loop example you provided.

If I want to sum the "hours * unit_price" from the CUST_ORDER_LINE and update the CUSTOMER_ORDER table, can I just create a variable in the loop and keep adding the value?

So if there are 2 different sales order, it would update according?

SalesOrder1 has a total of $150.
SalesOrder2 has a total of $180.

It would update the CUSTOMER_ORDER in the Total_Amount table accordingly.

Example:

declare 
  total_amt numbe r:=0;
begin
	for i in (
		select 
			order_id,
			row_number() over(partition by et.customer_id
					order by order_id,service_id) line_no,
			service_id,
		sum(hours) hours,
                sum(col.unit_price) unit_price
		from excel_temp et join customer_order co
			on et.customer_id=co.customer_id and et.order_id=co.id join cust_order_line col
                         on co.id = col.order_id and et.service_id = col.service_id
		group by et.customer_id,order_id,service_id,task
	) loop 
		begin
			insert into cust_order_line(cust_order_id,line_no,service_charge_id,qty)
				values(i.order_id, i.line_no, i.service_id, i.hours);
                          total_amt := total_amt + (i.hours*i.unit_price);
			exception when dup_val_on_index then
				update cust_order_line set qty=i.hours where 
					cust_order_id=i.order_id and line_no=i.line_no and service_charge_id=i.service_id;
                              total_amt :=  total_amt + (i.hours*i.unit_price);
		end;

               update customer_order
               set total_amount = total_amt
                where  order_id = i.order_id

	end loop;
end;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868890
I'm not completely following the new requirement but multiplying two values can probably be done in the select statement itself.  If for some reason it cannot be, then yes, you can create variables and perform the math inside the loop itself.
0
 

Author Closing Comment

by:holemania
ID: 41872217
Thank you so much for your assistance.  This got me what I needed it for since I had some other logics I need to perform within the loop.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now