On Cursors
-
I want to use a cursor and the columns I will be using is quite a lot.. say more than 20,
is there any way I could fetch this 20 one by one, without using too many local variables?
For instance in oracle they have a row data type which could hold an entire row data thus, accessing each column is a breeze.
-
Firstly, to avoid the "Don't use cursors" mantra, you should be explaining why it is necessary to use the cursor.
If you have to use the cursor, can you just have key fields in the cursor and the retrieve other fields in a select statment?
--------------------
Colt 45 - the original point and click interface
-
You gave me an idea there, but actually I'm converting an Oracle PL SQL into SQLServer T-SQL, then the columns will be outputted to a csv file, although the data to be outputted is not that many, the data to be exported will be different based on some conditions. the biggest problem of all is that I'm doing this without knowing the relationships of the tables in fact the tables is not yet created.. (Still currently being "inputted").
So I'm stuck to following whatever the existing Oracle code is doing... here take a look at some code in PLSQL
----------------------------------------------------------------------
open cur_TRNFA_SEISIJI_T (
rec_WRKFA_SYORI_CTL.TEMPOCODE,
rec_WRKFA_SYORI_CTL.KEI_BMN_CODE,
rec_WRKFA_SYORI_CTL.S_DATE,
rec_WRKFA_SYORI_CTL.E_DATE,
rec_WRKFA_SYORI_CTL.KUBUN
 
;loop
fetch cur_TRNFA_SEISIJI_T into rec_TRNFA_SEISIJI_T;
exit when cur_TRNFA_SEISIJI_T%notfound;
/* ??(????) */
-- if n_csvSeq is null then
-- SELECT FA001.NEXTVAL INTO N_CSVSEQ FROM DUAL;
-- end if;
/* 03/03/31 start
if w_csvTEMPOCODE <> rec_TRNFA_SEISIJI_T.TEMPOCODE then
end */
/* ???? */
/* 03/03/31 start
w_csvTEMPOCODE := rec_TRNFA_SEISIJI_T.TEMPOCODE;
end */
/* ???CSV??? */
/* 03/03/31 start
if UTL_FILE.IS_OPEN( h_csvFile ) then
UTL_FILE.FCLOSE( h_csvFile );
end if;
end */
/* ???CSV??? */
/* 03/03/31 start
c_csvPutName :=
c_csvPrefix ||
lpad( to_char( rec_TRNFA_SEISIJI_T.TEMPOCODE,'000' ), 3, '0' ) ||
lpad( to_char( n_csvSeq ), 4, '0' ) ||
c_csvExtension;
end */
-- c_csvPutName :=
-- c_csvPrefix ||
-- '0000' ||
-- lpad( to_char( n_csvSeq ), 4, '0' ) ||
-- c_csvExtension;
/* ???CSV??? */
-- h_csvFile := UTL_FILE.FOPEN( SPG_FACM0001.g_DATDIR, c_csvPutName, 'W' );
/* ???????? */
-- if UTL_FILE.IS_OPEN( h_csvFile ) then
-- SUB_MK_HEADER ( n_Ret );
-- if n_Ret <> 0 then
-- raise e_PRC_FABI120C;
-- end if;
-- UTL_FILE.PUT_LINE( h_csvFile, w_csvOutline );
-- end if;
/* 03/03/31 start
end if;
end */
for i in rec_WRKFA_SYORI_CTL.S_TIME..rec_WRKFA_SYORI_CTL.E_TIME loop
/* ?????????????????????????? */
SUB_CP_WORK_UPTODATE ( n_Ret, i );
if n_Ret <> 0 then
raise e_PRC_FABI120C;
end if;
--------------------------------------------------------------------------
here's the definition of the cursor
--------------------------------------------------------------------------
cursor cur_TRNFA_SEISIJI_T (
p_TEMPOCODE in number,
p_KEI_BMN_CODE in number,
p_S_DATE in date,
p_E_DATE in date,
p_KUBUN in number
 
isselect
trn.TEMPOCODE "TEMPOCODE",
trn.KANRIDATE "KANRIDATE",
trn.KEI_BMN_CODE "KEI_BMN_CODE",
trn.KEI_CALL_CODE "KEI_CALL_CODE",
decode( p_KUBUN, 0, trn.HON_SEISANSU_1, 1, trn.TUIKA_SEISANSU_1 ) "HON_SEISANSU_1",
decode( p_KUBUN, 0, trn.HON_SEISANSU_2, 1, trn.TUIKA_SEISANSU_2 ) "HON_SEISANSU_2",
decode( p_KUBUN, 0, trn.HON_SEISANSU_3, 1, trn.TUIKA_SEISANSU_3 ) "HON_SEISANSU_3",
decode( p_KUBUN, 0, trn.HON_SEISANSU_4, 1, trn.TUIKA_SEISANSU_4 ) "HON_SEISANSU_4",
decode( p_KUBUN, 0, trn.HON_SEISANSU_5, 1, trn.TUIKA_SEISANSU_5 ) "HON_SEISANSU_5",
decode( p_KUBUN, 0, trn.HON_SEISANSU_6, 1, trn.TUIKA_SEISANSU_6 ) "HON_SEISANSU_6",
decode( p_KUBUN, 0, trn.HON_SEISANSU_7, 1, trn.TUIKA_SEISANSU_7 ) "HON_SEISANSU_7",
decode( p_KUBUN, 0, trn.HON_SEISANSU_8, 1, trn.TUIKA_SEISANSU_8 ) "HON_SEISANSU_8",
decode( p_KUBUN, 0, trn.HON_SEISANSU_9, 1, trn.TUIKA_SEISANSU_9 ) "HON_SEISANSU_9",
decode( p_KUBUN, 0, trn.HON_SEISANSU_10, 1, trn.TUIKA_SEISANSU_10 ) "HON_SEISANSU_10",
decode( p_KUBUN, 0, trn.HON_SEISANSU_11, 1, trn.TUIKA_SEISANSU_11 ) "HON_SEISANSU_11",
decode( p_KUBUN, 0, trn.HON_SEISANSU_12, 1, trn.TUIKA_SEISANSU_12 ) "HON_SEISANSU_12",
decode( p_KUBUN, 0, trn.HON_SEISANSU_13, 1, trn.TUIKA_SEISANSU_13 ) "HON_SEISANSU_13",
decode( p_KUBUN, 0, trn.HON_SEISANSU_14, 1, trn.TUIKA_SEISANSU_14 ) "HON_SEISANSU_14",
decode( p_KUBUN, 0, trn.HON_SEISANSU_15, 1, trn.TUIKA_SEISANSU_15 ) "HON_SEISANSU_15",
decode( p_KUBUN, 0, trn.HON_SEISANSU_16, 1, trn.TUIKA_SEISANSU_16 ) "HON_SEISANSU_16",
decode( p_KUBUN, 0, trn.HON_SEISANSU_17, 1, trn.TUIKA_SEISANSU_17 ) "HON_SEISANSU_17",
decode( p_KUBUN, 0, trn.HON_SEISANSU_18, 1, trn.TUIKA_SEISANSU_18 ) "HON_SEISANSU_18",
decode( p_KUBUN, 0, trn.HON_SEISANSU_19, 1, trn.TUIKA_SEISANSU_19 ) "HON_SEISANSU_19",
decode( p_KUBUN, 0, trn.HON_SEISANSU_20, 1, trn.TUIKA_SEISANSU_20 ) "HON_SEISANSU_20",
decode( p_KUBUN, 0, trn.HON_SEISANSU_21, 1, trn.TUIKA_SEISANSU_21 ) "HON_SEISANSU_21",
decode( p_KUBUN, 0, trn.HON_SEISANSU_22, 1, trn.TUIKA_SEISANSU_22 ) "HON_SEISANSU_22",
decode( p_KUBUN, 0, trn.HON_SEISANSU_23, 1, trn.TUIKA_SEISANSU_23 ) "HON_SEISANSU_23",
decode( p_KUBUN, 0, trn.HON_SEISANSU_24, 1, trn.TUIKA_SEISANSU_24 ) "HON_SEISANSU_24"
from
TRNFA_SEISIJI_T trn
where
trn.TEMPOCODE = p_TEMPOCODE
and trn.KANRIDATE between p_S_DATE and p_E_DATE
/*** 02.08.09
and trn.KANRIDATE >= p_S_DATE
and trn.KANRIDATE < p_E_DATE
***/
and trn.KEI_BMN_CODE = decode( p_KEI_BMN_CODE, 0, trn.KEI_BMN_CODE, p_KEI_BMN_CODE )
order by
trn.TEMPOCODE,
trn.KANRIDATE,
trn.KEI_BMN_CODE,
trn.KEI_CALL_CODE
;
rec_TRNFA_SEISIJI_T cur_TRNFA_SEISIJI_T%rowtype;
---------------------------------------------------------------------------
The function above (boldfaced) is done this way
---------------------------------------------------------------------------
procedure SUB_CP_WORK_UPTODATE (
n_Ret out number,
i in number
 
isbegin
n_Ret := 0;
w_csvKANRIDATE := rec_TRNFA_SEISIJI_T.KANRIDATE;
w_csvTEMPOCODE := rec_TRNFA_SEISIJI_T.TEMPOCODE;
w_csvKEI_BMN_CODE := rec_TRNFA_SEISIJI_T.KEI_BMN_CODE;
w_csvKEI_CALL_CODE := rec_TRNFA_SEISIJI_T.KEI_CALL_CODE;
/* i:????????? */
w_csvTIMEBAND := i;
if i = 1 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_1;
elsif i = 2 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_2;
elsif i = 3 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_3;
elsif i = 4 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_4;
elsif i = 5 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_5;
elsif i = 6 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_6;
elsif i = 7 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_7;
elsif i = 8 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_8;
elsif i = 9 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_9;
elsif i = 10 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_10;
elsif i = 11 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_11;
elsif i = 12 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_12;
elsif i = 13 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_13;
elsif i = 14 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_14;
elsif i = 15 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_15;
elsif i = 16 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_16;
elsif i = 17 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_17;
elsif i = 18 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_18;
elsif i = 19 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_19;
elsif i = 20 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_20;
elsif i = 21 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_21;
elsif i = 22 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_22;
elsif i = 23 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_23;
elsif i = 24 then
w_csvHON_SEISANSU_1 := rec_TRNFA_SEISIJI_T.HON_SEISANSU_24;
end if;
exception
when OTHERS then
n_Ret := 1;
end SUB_CP_WORK_UPTODATE;
---------------------------------------------------------------------------
I'd like some Idea on how this would be done.. It would be a great help.. I'm quite new to SQLServer and I did not even do Stored Procedures for Oracle at school so I'm relatively new to both technologies.
-
Moreover, I had the idea of creating another table but I did not design this database so I'm not sure If I'm allowed to create on though... a global temporary table perhaps?...
-
So basically,
- you have information in a SQL table that you want to export to CSV
- you have an existing Oracle PL/SQL that you want to reproduce in SQL Server
Have I got that right?
If so, I can't comment on the PL/SQL code ( I haven't had any exposure to it ), but trying to reproduce the logic used may not be the best place to start. Grab hold of some business documentation that describes what needs to be output to the file, then use DTS, or a mix of T-SQL and BCP, to create the file.
--------------------
Colt 45 - the original point and click interface -
Thanks for the reply.. yup yo got it right but if I'm to request for a business logic that might take a lot of work.. longer than converting the said procedures really... You see I'm here in japan and I don't understand the language yet, much less read some kanji. It would have been fine if they would give me softcopies of the logic but they would only give me the hardcopy and for me to translate one sentence takes at least an hour.. (I had to look it up character per character, if its not in the dictionary then I have to recreate the kanji to the software, for it to be properly translated but its not easy reading and writing kanji characters).. so you see. I just try to understand the code as much as I can.
BTW, what's DTS, I have been reading about it in the BOL but I still don't have any idea. what it is how is it made and how is it run what is it best for.
as for the BCP meaning I would have to use the xp_cmdshell right? -
DTS = Data Transformation Services. Basically given an known datasource, DTS can extract and transform data very easily.
There are different methods of calling BCP, xp_cmdshell is one of them, Others include, executing it as a process in a DTS task, use automation procedures within SQL Server.
--------------------
Colt 45 - the original point and click interface -
See if I have understood this correctly
('cause It's not easy to practice this here.. the environment is in japanese so I get into trouble a lot.
)say for example I have created a DTS Package for this package, then I want that to be run in T-SQL.. do I still have to use xp_cmdshell together with sp_OACreate, sp_OAGetProperty, sp_OASetProperty, sp_OAMethod, sp_OAGetErrorInfo and sp_OADestroy?
-
The sp_OAxx procs are for using/accessing COM objects from within SQL Server. However... don't go there.
xp_cmdshell is essentially a DOS prompt for use from Transact SQL. Whatever you can type at the prompt you can do through xp_cmdshell (given the proper permissions)
For running DTS packages, I believe that you could use xp_cmdshell 'dtsrun.exe <package>' or similar... I don't use it, so I don't know the syntax offhand.
/Kenneth
-
Trying to debug a script without knowing the business logic will turn your hair grey
In my experience a quick, simple conversion of a function from one language to another is neither quick or simple. Have you tried "babel fish" it claims to translate Japanese to english. Just a thought is Oracle PL SQL, SQL 92 compliant? If so you might get away with just cutting and pasting the code to T_SQL.
HTH
Mike
-
I guess so, but there are some codes in Oracle that is in Oracle only so that's where the trouble comes from.
... As for the debug type, I don't have any choice.. as long as I stick to purpose of the function I think it is ok...
... Truth is.. I have a lot of japanese conversion software here but it's a hassle trying to translate several pages of text really...
.. sometimes the resulting conversion is not even what it really means.. so have to be careful to break down the sentence into it's basic elements.. hehee.. It's a very long process I tell you.. BTW, when is a stored procedure get compiled anyway? because I have converted several sp's already and it's okey.. only testing is not done yet.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply