sql: support notices for cursors (and other places where internal executor is used)

See the background in https://github.com/cockroachdb/cockroach/issues/94344 and https://github.com/cockroachdb/cockroach/pull/94425

We can get a notice like this
```
root@localhost:26257/defaultdb> select * from crdb_internal.gossip_network;;
NOTICE: This table is no longer supported/populated, and will be removed in a future version.```
```

But not like this
```
BEGIN; declare a cursor for select * from crdb_internal.gossip_network;
```

This issue tracks support for displaying notices in this case

Jira issue: CRDB-22890