can i write procedural scripts in sql? or do i need to embed it in another language (which one)?

Answer 1

Take a look at Transact SQL

Answer 2

Or PL/SQL By Oracle