sybase procedures and cursors

How to view stored procedure spec?

 

sp_help ${stored_procedure_name}

 

How to view the source of stored procedure?

 

sp_helptext ${stored_procedure_name}

 

 

Super simple procedure example

 

 

DECLARE @ricCode VARCHAR(20)

 

BEGIN

      SELECT @ricCode = '9779.HK'

      PRINT @ricCode

END

 

 

Simple cursor example—key sqls are highlighted

 

use DRMS

go --sybase sqls are sparated by go, which database are you using. There is no ; in sybase

 

CREATE UNIQUE INDEX ExchangeAccountParam_IDX on ExchangeAccountParam  ( id, parameter )

go

 

DECLARE attrCursor CURSOR

FOR SELECT parameter, value FROM ExchangeAccountParam WHERE id = 4 for update

go -–this go could contain nothing else, only this cursor declare

 

DECLARE @ricCode     VARCHAR(20),

        @brokerId    VARCHAR(20),

        @securityId  INTEGER

   

    OPEN attrCursor 

 

    FETCH attrCursor INTO @ricCode, @brokerId

       

    WHILE (@@sqlstatus = 0)

    BEGIN

        --Get securityId from ricCode

        SELECT @securityId = id FROM ETSalternativeIdentifier WHERE altId = @ricCode

           

        IF @securityId  IS NULL

            SELECT @ricCode, @brokerId, @securityId, 'NULL securityId--Give up!'

        ELSE IF @brokerId = 'HM091'

        BEGIN -- if there are several sqls in this block, must be surrounded by BEGIN/END

            SELECT @ricCode, @brokerId, @securityId, 'Insert HKG-MM,HM091!'

            INSERT INTO QuoterConfigParam VALUES (35, @securityId, @ricCode, 'HKG-MM,HM091')

        END       

        ELSE IF @brokerId = 'HM092'

        BEGIN

            SELECT @ricCode, @brokerId, @securityId, 'Insert TCMECS,HM092!'

            INSERT INTO QuoterConfigParam VALUES (35, @securityId, @ricCode, 'TCMECS,HM092')

        END 

        ELSE IF @brokerId = 'HM093'

        BEGIN

            SELECT @ricCode, @brokerId, @securityId, 'Insert TCMECS,HM093!'

            INSERT INTO QuoterConfigParam VALUES (35, @securityId, @ricCode, 'TCMECS,HM093')

        END 

       

        FETCH attrCursor INTO @ricCode, @brokerId

    END       

    CLOSE attrCursor

go

 

DEALLOCATE CURSOR attrCursor

go -- destroy the cursor

 

 

 

for update and for read only

 

select * from test for update

for update means I am going to change the content of this line, add a lock at it!

 

select * from test for read only

for read only means I simply need to read the line, no lock is needed.