You've got some serious reading to do....there is a major methodolgy switch you'll have to understand (not to mention sytax) between the two...
Oracle has a lot of nice "built in" features, and at the same time is extremely painful...
For example, ylucan not use %TYPE...
YOu must explicitlety declare EVERYTHING...
This is how you write what your example is trying to do...
CREATE PROC mySproc99
DECLARE @vname varchar(40), @Rowcount int, @Error int
SELECT @vname = Lastname
WHERE firstname = 'Nancy'
SELECT @RowCount = @@ROWCOUNT, @Error = @@Error
IF @Error <> 0
PRINT 'Error Condition ' + CONVERT(varchar(5),@Error)
IF @RowCount <> 1
PRINT 'Multiple Rows Found Error'
IF @RowCount = 1
PRINT 'Name is '+ @vname
DROP PROC mySproc99